SQL Server Performance Forum – Threads Archive

Can this query be ever tuned ?? Worth a try

Hello all, I extracted this code from profiler, its in a Stored procedure, which is in turn in a loop, this fragment of code itself takes around 30 minutes to execute, I am sure that there must be a more efficient way of writing this code.
UPDATE D SET D.top10nnis = ISNULL(D.SummaryTable,’ ‘)+ ‘ ‘ + CASE WHEN D.COUNTER = 1
THEN convert(varchar(20),D.switch_1st_iteration) + ‘ ‘ + ‘,’ + ‘ ‘ + convert(varchar(200),D.exchangename_1st_iteration) + ‘ ‘ + ‘,’ + ‘ ‘ + convert(varchar(10),D.cbandalias_1st_iteration) + ‘ ‘ + ‘,’ + ‘ ‘
ELSE ”
END
+ 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 #TmpCALCULATE 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.Operator = ‘Chevy’) OR (C.cBand = ‘526’ AND D.Operator <> ‘BT’ AND C.Type <> ”Chevy”))
AND C.exchangetype <> ‘DLE’
AND (D.cband_1st_iteration in (‘527′,’528′,’529’) OR (D.cband_1st_iteration = ‘526’ AND D.Operator_1st_iteration = ‘BT’) OR (D.cband_1st_iteration = ‘526’ AND D.Operator_1st_iteration <> ‘BT’ AND D.Type_1st_iteration <> Chevy ‘))
AND D.exchange_type_1st_iteration <> ‘DLE’

---

A bit weird to see doubled quotes in AND C.Type <> ”Chevy” – that should at least raise a run-time error. Also, there’s a single quote missing here AND D.Type_1st_iteration <> Chevy ‘)). Again, that would produce a run-time error. Copy the statement into QA, and you’ll see the wrong bits showing up in red. Otherwise, do you know if the #DATA and #TmpCALCULATE temp tables have a PK, and any indexes? Perhaps the sproc was not tested with large enough data in the tables, as with just a small number of records you would not notice the missing indexes.

---

Sorry about those quotes, just typo’s The 2 tables do have clustered indexes on them, but they are also being isnerted into.

---

You didn’t mention the number of rows being inserted into the temp tables, which is kind of relevant. You should perhaps monitor file growth on tempdb, since this is where the temp tables are created. Maybe increase file size settings, perhasp turn off autoshrink. Are the clustered indexes on columns where data is always entered in ascending order, like an IDENTITY column? If not, and you’re inserting lots of rows at a time, then perhaps it should be a non-clustered index. You can add an identity column to the temp table deisgns as clustered PK.

---

Give SQL Optimizer for Visual Studio a Try. It will rewrite your SQL and find the best performing method to run the SQL. You can download a trial at http://www.extensibles.com/modules.php?name=Products The Relentless One
No Bug says alive http://www.extensibles.com

---

Bill, Kindly stop spamming!

---

Relentless, indeed. He’s ensuring I will never try his product.

---

If you want some more positive self-marketing, what about contacting the webmaster and ask for a software review? Should have a much greater, and certainly more positive impact on your product than advertising it in the threads where it is more or less related. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

---

]]>