August 9, 2006 at 4:50 am
Hi All,
I have this update statement in a stored procedure, and after investigating, I found out that this might be where the problem lies.
All the tables do have clustered indexes on them, but below is the order in which things happen.
The code is in a cursor, The temp table created, a clustered index put on it, Inserts done, tables are used for updates.
UPDATE D SET D.Summary_Table = ISNULL(D.Summary_Table,' ')+ ' ' + CASE WHEN D.COUNTER >= 1
THEN convert(varchar(20),C.switch) + ' ' + ',' + ' ' + convert(varchar(200),C.exchangename) + ' ' + ',' + ' ' + convert(varchar(10),C.cbandalias) + ' ' + ',' + ' '
ELSE ''
END
,D.COUNTER = D.COUNTER + 1
FROM #DATA D JOIN #CALCULATE C ON D.ID = C.ID
WHERE D.COUNTER < 11
AND D.COUNTER >= 1
AND C.TEST = D.MinID + @LoopCounter
AND C.cband <> '520'
AND (C.cband in ('527','528','529') OR (C.cBand = '526' AND D.Owner = 'BCK') OR (C.cBand = '526' AND D.Owner <> 'BCK' AND C.Type <> 'BT WAT')) --GTOW 18.01.06 changes to exclude OLO @ 526 WAT
AND C.exchangetype <> 'DLE'
AND (D.cband_1st_iteration = '526' AND D.Owner_1st_iteration <> 'BCK' AND D.Type_1st_iteration = 'BT WAT') --GTOW 18.01.06 changes to exclude OLO @ 526 WAT
AND D.exchange_type_1st_iteration <> 'DLE'
I look forward to hearing from you all.
Thanks
August 9, 2006 at 6:24 am
Hi John,
The fact that this is inside of a cursor is more than likely where the performance drain lies.
You have to figure out a way to NOT use the cursor. At first glance it looks like the only reason you're using the cursor is to maintain a variable called @LoopCounter. Obviously though I cannot see whether you have another need for the cursor.
Anyway, as far as the update statement is concerned that should and probably could be moved out of the cursor. You could implemente similar functionality as provided by @LoopCounter by inserting an incrementing value (identity field) into #Data and then using that field in place of @LoopCounter:
AND C.TEST = D.MinID + D.NewField
Beyond that it is difficult to offer much more advice as you have only provided the update statement itself.
Hope that helps,
August 9, 2006 at 6:41 am
I have some issues with the logic
What is the column COUNTER set to in #DATA before the update?
WHERE D.COUNTER < 11
AND D.COUNTER >= 1
this will retrict the selection to COUNTER value of 1 to 10 before any update is actioned
D.COUNTER = D.COUNTER + 1
this will increment COUNTER value on the row not incremented throughout the update
CASE WHEN D.COUNTER >= 1
the THEN will not be actioned unless COUNTER is > 0 in #DATA
(if COUNTER is > 0 then the ELSE portion if CASE will never be actioned)
Far away is close at hand in the images of elsewhere.
Anon.
August 11, 2006 at 10:58 am
August 11, 2006 at 2:49 pm
Looking over the query, I see several indications that this one "just grew", instead of being designed.
Below, I have cleaned up the query by
1) removing the redundant checks in the WHERE clause
2) Merging the WHERE clause checks that can be easily combined
3) Merging the concatenation of the strings (... + ' ' + ',' + ' ' + ...) into one statement (... + ' , ' + ...)
4) Removed the CASE statement, since the WHERE clause made the ELSE clause of the CASE statement redundant.
5) Changed the test on d.Counter in the WHERE clause to be a BETWEEN test
Other things that I would look at depend on the actual reason for this query.
It appears that you are concatenating a number of data elements into a single string, and updating the D.Summary_Table data element with this list of concatenated strings. To do this, you are CONVERTing various data elements to maximum varchar sizes (what data type are the source fields?) However, analyzing what needs to be done from a fragment of a query, with no understanding of the underlying query, only leads to wild assumptions.
I would consider adding indexes on d.counter and c.test at a minimum, and see if this increases your performance.
Brendt
August 11, 2006 at 5:18 pm
Thanks for the reply, let me know if you would like anything else. The code is in a stored procedure, and this update fragment is what i believe is causing a gridlock....
The reason why i have pasted the fragment of code is because, the code is in dynamic sql, therby making it hard to post, as you do not know what the variables would be, but if i sent a fragment of the update statement, that would make it easier to debug I believe.
Let me know
August 11, 2006 at 7:02 pm
Yech. Tuning dynamic queries can be one of the more troublesome issues. However, there is a way to start.
Get the SQL Statement that would be generated by the dynamic SQL, and place it in QA. Then, get the estimated execution plan, and see where the hold-up is expected to be. Tweak your SQL, indexes, and values, and then see what changes would be made to the plan.
BTW, since I apparently did not attach the 'cleaned up' version, here it is:
UPDATE D
SET D.Summary_Table =
ISNULL(D.Summary_Table,' ')+ ' ' +
convert(varchar(20),C.switch) + ' , ' +
convert(varchar(200),C.exchangename) + ' , ' +
convert(varchar(10),C.cbandalias) + ' , '
,D.COUNTER = D.COUNTER + 1
FROM #DATA D
INNER JOIN #CALCULATE C
ON D.ID = C.ID
WHERE D.COUNTER BETWEEN 1 AND 10
AND C.TEST = D.MinID + @LoopCounter
AND (C.cband in ('527','528','529')
OR (C.cBand = '526' AND (D.Owner = 'BCK' OR C.Type 'BT WAT'))) --GTOW 18.01.06 changes to exclude OLO @ 526 WAT
AND C.exchangetype 'DLE'
AND (D.cband_1st_iteration = '526' AND D.Owner_1st_iteration 'BCK' AND D.Type_1st_iteration = 'BT WAT') --GTOW 18.01.06 changes to exclude OLO @ 526 WAT
AND D.exchange_type_1st_iteration 'DLE'
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply