August 28, 2014 at 2:28 pm
Hello all,
I inherited a Stored Procedure that has a temp table #results that is updated 65 times throughout the SP . This SP is used for a reporting only. I have cleaned this SP up from 2000+ lines down to a little over 1400. "Generally" speaking would adding a Index to the temp table help performance. Also how does a temp table index work? Is there other things that I should look for to help SP query performance?
I have tried Google but keep getting to many bad explanations. So I thought I'd come to my trusted source to ask.
Thanks in advance.
Example of the basic format:
CREATE TABLE #results
(Field1 INT,
Field2 VARCHAR(20),
Filed3 VARCHAR (20),
ect)
INSERT INTO #results
(Field1,
Field2 ,
Filed3,
ect)
SELECT (Field1, Field2 , Filed3, ect)
FROM ABC
Update #results
SET field1 = CASE WHEN THEN
FROM
WHERE
-----
Update #results
SET field2 = CASE WHEN THEN
FROM
WHERE
-----
Update #results
SET field3 = CASE WHEN THEN
FROM
WHERE
----ect 65 times
***SQL born on date Spring 2013:-)
August 28, 2014 at 3:24 pm
thomashohner (8/28/2014)
Hello all,I inherited a Stored Procedure that has a temp table #results that is updated 65 times throughout the SP . This SP is used for a reporting only. I have cleaned this SP up from 2000+ lines down to a little over 1400. "Generally" speaking would adding a Index to the temp table help performance. Also how does a temp table index work? Is there other things that I should look for to help SP query performance?
I have tried Google but keep getting to many bad explanations. So I thought I'd come to my trusted source to ask.
Thanks in advance.
Example of the basic format:
CREATE TABLE #results
(Field1 INT,
Field2 VARCHAR(20),
Filed3 VARCHAR (20),
ect)
INSERT INTO #results
(Field1,
Field2 ,
Filed3,
ect)
SELECT (Field1, Field2 , Filed3, ect)
FROM ABC
Update #results
SET field1 = CASE WHEN THEN
FROM
WHERE
-----
Update #results
SET field2 = CASE WHEN THEN
FROM
WHERE
-----
Update #results
SET field3 = CASE WHEN THEN
FROM
WHERE
----ect 65 times
Are all the columns being updated from the same table? With nothing indicated in the FROM clause, in fact with such abbreviated update statements, it is hard to tell what is happening.
August 28, 2014 at 3:33 pm
For the most part its being updated from the same 4 or 5 tables. On each Update. With different criteria on each one.
FROM #RESULTS r
INNER JOIN table1 t1 ON r.ID =t1id
***SQL born on date Spring 2013:-)
August 28, 2014 at 3:47 pm
Still not sure what is going on, but it is possible indexes on the temp table would help. Without seeing the actual code, though, it is hard to say.
Oh, it would be interesting to see more than just one update statement.
August 28, 2014 at 3:52 pm
I think I can get some more performance with tweaking and changing some of the underlining logic for the updates. I just didn't know if a Index would help since the #results is so heavily updated and it always is joined on the same ID field all the way down.
***SQL born on date Spring 2013:-)
August 28, 2014 at 4:08 pm
I have personally found that indexing a #temp table to have similar indexes to what else I'm joining it to generally stabilizes and improves my query plans. Like most things working against the black box of the optimizer, it's not a hard and fast rule.
However, if you're looking to optimize the query, you're looking at digging into the execution plans. So, try it in both directions, and check your plans. Use timing wrappers around each piece to find your temporal pain points. Dig into those queries directly.
Admittedly a 1,400 line query is a bit much for me to try to tackle on a forum like this, but others are usually willing on a lazy Sunday to see if they can help. If you're able, post up the code and the execution plans of the problem children, and we'll see if we can't dig into the particular pain points. Worst case scenario we may be able to see some patterns from afar that are difficult to see when you're too close to the trees.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 28, 2014 at 4:14 pm
That might be possible there is a lot of bad stuff I have cleaned up. Such as NOLOCK on every JOIN, but I still have a lot left. its a nasty mess.
***SQL born on date Spring 2013:-)
August 28, 2014 at 4:25 pm
One thing looking at the snippets, it isn't always an issue but I have had problems with UPDATE statements like this:
update #MyTempTable set
somecol = somevalue -- or computed value
where
#MyTempTable mtt
inner join dbo.sometable st
on mtt.acol = st.acol;
I would change the update to the following and things would work correctly:
update mtt set -- Note the use of the table alias here
somecol = somevalue -- or computed value
where
#MyTempTable mtt
inner join dbo.sometable st
on mtt.acol = st.acol;
August 28, 2014 at 4:28 pm
awesome tip Lynn. I'll make those changes
***SQL born on date Spring 2013:-)
August 29, 2014 at 4:40 am
If the temp table is ultimately selected from, you could alternatively create a specific reporting table which is not temporary and apply suitable indices there.
This offers the added benefit of persisting the data for subsequent selects.
Further, you can split your stored procedure into clear/repopulate reporting table, and select data from reporting table - benefits from this this may depend on how often the underlying data may or may not need to change.
August 29, 2014 at 6:26 am
thomashohner (8/28/2014)
For the most part its being updated from the same 4 or 5 tables. On each Update. With different criteria on each one.FROM #RESULTS r
INNER JOIN table1 t1 ON r.ID =t1id
I would consider a Primary Key on the column at least, assuming that it is unique in the temp table, more for ensuring results, but there may be performance benefits as well.
I'd be curious to see the code, because I'm sure there's stuff going on that could be combined or cut out.
September 2, 2014 at 10:05 am
As others have said, you haven't given enough information, but you should first minimize the number of UPDATEs on bigger tables; if the FROM/JOIN clause is the same, you can use CASE in your SET lines, and spend a little more CPU on one pass through the table(s) rather than making multiple passes.
After that, you can experiment with indexes - the usual rules apply, include useful columns from the FROM and WHERE clauses, and of those, generally favor high cardinality as leftmost, etc.
Change where you build the index, see what happens!
On bigger #temp tables, if you're updating columns in the index, build it afterwards. Or rebuild it afterwards!
Use Profiler on the SQL:BatchCompleted event primarily using the CPU, Reads, Writes, and Duration columns; you want to see the aggregate effect on the entire batch, i.e. do the index(es) cost more than they benefit? You'll need to know if you're bottlenecked by CPU or Reads or Writes (most systems I see are IO bottlenecked, so I weight Reads and Writes much higher than CPU; exceptions exist, and all environments are different).
September 2, 2014 at 10:34 am
The only way to be sure is to test. See what columns in your temp table are being used to perform the updates (hopefully a single column), then weigh the cost of creating an index on that column against the time you'll have in doing your updates. If you're using a single column for multiple updates in your 1400-line procedure, you'll likely benefit from indexing it. Again, testing it both ways is the only way to be sure.
September 2, 2014 at 10:47 am
Thanks everyone for the suggestions. I also appoligize about the limited information. Its hard to post the real code because of my company and because its so large.
I will say yes it helped! Went from 5 seconds down to 1 second on my little db for run time.
***SQL born on date Spring 2013:-)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply