Results 1 to 7 of 7

Thread: SQL tuning help

  1. #1
    Join Date
    Jul 2004
    Posts
    306

    Unanswered: SQL tuning help

    Hey,

    SQL Server 7.

    I have a view which joins 3 tables. One has 15 million rows the next another 5 million and the third 500k.
    When I join them the execution plan tells me that 15 million rows were retrieved from the first (taking about 5 mins) 1.5 million from the 2nd taking 3 mins and 4.5 million from the third taking almost no time.

    The first two ause a clustered index, one being a seek the other a scan and the third a regular index seek. All followed by a hash match/inner join which takes 2 mins.

    Any ideas on optimizing the SQL?

    Here is the syntax:
    SELECT b.packno, b.COMM_DATE, a.ben_grp_cr, a.ben_dsc_cr, c.gst_inc,
    SUM(a.credit)
    FROM TABC c INNER JOIN TABB B
    ON b.PACKNO = c.PACKNO AND
    b.COMM_DATE = BENDTL.COMM_DATE AND
    b.BEN_NUM = c.BEN_NUM INNER JOIN
    TABA a ON b.tran_id = a.tran_id
    WHERE b.tran_date > '20040401' AND c.gst_inc = 0
    GROUP BY b.packno, b.COMM_DATE, a.ben_grp_cr, a.ben_dsc_cr,
    c.bendtl.gst_inc

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Make sure PACKNO, COMM_DATE, BEN_NUM, and tran_id are indexed in each table.

    It may also help to index TABB.tran_date and TABC.gst_inc.

    Your SQL Statement looks fine, so if indexing fails to solve the problem you may have to adjust some server settings or add hardware.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    If you're still stuck, provide DDL for all three tables and their indexes, view code, and sample data in the form of Insert statements. Also, provide hardware configuration and database size(s).

    I'd suggest starting your optimization w/o the view. Use the tables. Start eliminating Joins and/or Predicates to see which table/column may be your bottleneck. Figure out what performs up to your expections (sub second) and then figure out what is slowing things down.
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

  4. #4
    Join Date
    Jul 2004
    Posts
    306
    Quote Originally Posted by blindman
    Make sure PACKNO, COMM_DATE, BEN_NUM, and tran_id are indexed in each table.

    It may also help to index TABB.tran_date and TABC.gst_inc.

    Your SQL Statement looks fine, so if indexing fails to solve the problem you may have to adjust some server settings or add hardware.
    Yep PACKNO, COMM_DATE, BEN_NUM are all part of a single index, tran_date and gst_inc have their own.

    The main think I'm wondering is in a 3 table join why the optimizer doesn't use the range of values being joined on from the smallest tqable to limit the scan of the larger ones, or am I just misinterpreting the Execution Plan?

  5. #5
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    Without the things I asked for, that is a difficult question to answer. However, one of the gurus on this board may be able to glance at the information you've provided and give you a reasonable answer. Blindman is one of 'em, so indications are that more info. is needed.

    If not, provide the information I requested and me and/or one of the intelligent members will have a look. I suspect the view is your enemy, thus the reason you should strip down the statement to the barebones and start hunting down where the problem is.

    For example, go here to learn about parameter sniffing, a very cool but sometimes costly SQL Server feature.
    Last edited by MaxA; 02-23-05 at 21:13.
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What kind of field is tran_date? String?

    Please supply the DDL for each of the tables involved.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Index scan may not necessary be a bad thing, while index seek, while being a seemingly "good" thing, - may result in thousands of logical reads. And what about bookmark lookups, you mentioned nothing of those? Set statistics_io on and see what those values are. The decisive factor is the number of reads that the query generates, not if you achieved index seek on every object.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •