Can this Query be tuned ? Mystery

Question

Can this Query be tuned ? Mystery

John Burchel

Hall of Fame

Points: 3611
More actions
July 20, 2006 at 7:05 am

#61424

I have got the query below in form of a cursor, but this is all it does, a lot of I/O resources are spent on this query.
I hvae an index on summary table, as follows
There are no indexes on any table used here..
UPDATE D SET D.Summary_Table = ISNULL(D.Summary_Table,' ')+ ' ' + 'NM23' + ' ' + ',' + ' ' + 'ENERGIS NM23' + ' ' + ',' + ' ' + 'ENERGIS' + ' ' + ',' + ' '
               ,D.COUNTER = D.COUNTER + 1
   FROM #Source D JOIN #Calculator_Table C ON D.ID = C.ID
   WHERE D.COUNTER = 1--2
   AND C.TEST = D.MinID + @MyCounter
   AND
       (
        (
         C.Cgroup IN ('8973','8974','8975')
   AND C.CustType <> 'DLE'
   AND ( D.Cgroup_1st_iteration = '8970'
         OR ( D.Cgroup_1st_iteration = '8971'
                    AND F.OPR_1st_Run = 'BT'
                  AND D.Source_type <> 'DLE')
             )
        )
        OR
        (
         C.Cgroup IN ('8977','8978','8979')
   AND C.CustType <> 'DLE'
   AND ( D.Cgroup_1st_iteration = '8976'
              AND F.OPR_1st_Run = 'BT'
              AND D.Source_type <> 'DLE' )
        )
      )

Kindest Regards,
John Burchel (Trainee Developer)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

well .. indexes on join columns and sargs may help, depends upon the size of the tables. You might want to try a few compound indexes to cover the sargs.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Lowell SSC Guru Points: 323518 More actions · Answer 2

here's my suggestions, but by no means do i purport to be the ultimate guru of query tuning;

#Source D and #Calculator_Table C are temp tables,make sure you put indexes on #Source.Id and #Calculator_Table if they are more than a hundred rows apiece.

As I understand it,small tables would most likely get table scanned regardless, but bigger tables need indexes, even when temporary.

#Calculator_Table.TEST and #Calculator_Table.Cgroup,#Source.Cgroup_1st_iteration are used for joins, so that implies they might need to be indexed as well ;

as Colin identified, a covering index is better than 3 separate indexes, so i'd look at creating indexes like:

CREATE INDEX IX_CALC_TMP ON #Source(ID,Cgroup_1st_iteration)

CREATE INDEX IX_SOURCE_TMP ON #Calculator_Table(ID,TEST,Cgroup)

C.CustType <> 'DLE'

As I understand it, it is better to say what it is, instead of what it is not, in order to use SARGABLE arguments,

so if possible, make this C.CustType IN('DOC','DMM') instead of <>

HTH

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Need more info. Please post the DDL of the Temp tables in full and please explain how this code is used, is in a stored procedure?

Lastly is that the condition

C.CustType <> 'DLE'

occurrs with way but you have it twice in the OR on each side, it might help to move to the base of the were say like this

D.COUNTER = 1

AND

C.TEST = D.MinID + @MyCounter

AND

C.CustType != 'DLE'

AND

(

C.Cgroup IN ('8973','8974','8975')

AND (

D.Cgroup_1st_iteration = '8970'

OR (

D.Cgroup_1st_iteration = '8971'

AND F.OPR_1st_Run = 'BT'

AND D.Source_type != 'DLE'

&nbsp

&nbsp OR (

C.Cgroup IN ('8977','8978','8979')

AND (

D.Cgroup_1st_iteration = '8976'

AND F.OPR_1st_Run = 'BT'

AND D.Source_type != 'DLE'

&nbsp

David le Quesne SSCertifiable Points: 5229 More actions · Answer 4

Do you need a cursor ?

Perhaps if we could see a bit more of the box this mystery comes in...?

David

If it ain't broke, don't fix it...

John Burchel Hall of Fame Points: 3611 More actions · Answer 5

Thanks everyone for replying.

Please let me know exactly what you need ?

Kindest Regards,

John Burchel (Trainee Developer)

Antares686 SSC Guru Points: 125444 More actions · Answer 6

Please post the DDL of the Temp tables in full and please explain how this code is used, is in a stored procedure?