September 30, 2010 at 11:01 am
I have a stored proc that takes about 15-17 seconds to run. I would like to get it to run in 5 seconds or less. Here are the basic steps:
1) Create a temp table
2) Create an index on temp table
3) Insert into temp table with a select
4) Insert into temp table with a select again
5) Then there are 13 additional update statements to the temp table to fill in all of the appropriate values
I know this doesn't provide you with a lot of details but are there any thoughts you give from a general perspective in which I should condsider?
If I run steps 1-4 w/out the updates, it takes about 3 seconds.
September 30, 2010 at 11:21 am
Create a copy of the procedure and do timestamp wrappers around each of the updates. Find out where the pain in time is. Then start pulling out execution plans to find the bottleneck.
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
September 30, 2010 at 11:22 am
OK - that's a good start - thank you.
September 30, 2010 at 11:47 am
Craig Farrell (9/30/2010)
Create a copy of the procedure and do timestamp wrappers around each of the updates. Find out where the pain in time is. Then start pulling out execution plans to find the bottleneck.
After the first 2 insert statements I did a start and end date before and after each update. Here are my results. The far right column are the millisecond differences. This whole query took 17 seconds. I'm actually working on a test DB but the normal DB takes 40 seconds for this to run. Rows 4 and 5 take the longest but I'm nut sure how to optiize it because it is doing a bunch of updates based on values from the temp table.
12010-09-30 12:39:58.2632010-09-30 12:39:59.3001036
22010-09-30 12:39:59.3032010-09-30 12:40:00.097793
32010-09-30 12:40:00.0972010-09-30 12:40:00.820723
42010-09-30 12:40:00.8202010-09-30 12:40:02.9232103
52010-09-30 12:40:02.9272010-09-30 12:40:05.4532526
62010-09-30 12:40:05.4532010-09-30 12:40:06.103650
72010-09-30 12:40:06.1072010-09-30 12:40:06.667560
82010-09-30 12:40:06.6672010-09-30 12:40:07.450783
92010-09-30 12:40:07.4502010-09-30 12:40:07.790340
a2010-09-30 12:40:07.7902010-09-30 12:40:08.480690
b2010-09-30 12:40:08.4802010-09-30 12:40:09.183703
c2010-09-30 12:40:09.1832010-09-30 12:40:09.497313
d2010-09-30 12:40:09.4972010-09-30 12:40:10.130633
e2010-09-30 12:40:10.1302010-09-30 12:40:10.20373
September 30, 2010 at 11:52 am
For example, here is one of the longer updates - is there a better way to do this?
UPDATE #temp_table
set clm_loc_city = (select loc_city
from LOCATION_TABLE
where #temp_table.car_init = LOCATION_TABLE.car_init
and #temp_table.car_no = LOCATION_TABLE.car_no),
clm_loc_state = (select loc_state
from LOCATION_TABLE
where #temp_table.car_init = LOCATION_TABLE.car_init
and #temp_table.car_no = LOCATION_TABLE.car_no),
clm_sight_code = (select sight_code
from LOCATION_TABLE
where #temp_table.car_init = LOCATION_TABLE.car_init
and #temp_table.car_no = LOCATION_TABLE.car_no),
clm_date_time = (select clm_date_time
from LOCATION_TABLE
where #temp_table.car_init = LOCATION_TABLE.car_init
and #temp_table.car_no = LOCATION_TABLE.car_no),
clm_le = (select le
from LOCATION_TABLE
where #temp_table.car_init = LOCATION_TABLE.car_init
and #temp_table.car_no = LOCATION_TABLE.car_no),
clm_road = (select road
from LOCATION_TABLE
where #temp_table.car_init = LOCATION_TABLE.car_init
and #temp_table.car_no = LOCATION_TABLE.car_no),
clm_trn_jct = (select trn_jct
from LOCATION_TABLE
where #temp_table.car_init = LOCATION_TABLE.car_init
and #temp_table.car_no = LOCATION_TABLE.car_no),
clm_dest_city = (select dest_city
from LOCATION_TABLE
where #temp_table.car_init = LOCATION_TABLE.car_init
and #temp_table.car_no = LOCATION_TABLE.car_no),
clm_dest_state = (select dest_state
from LOCATION_TABLE
where #temp_table.car_init = LOCATION_TABLE.car_init
and #temp_table.car_no = LOCATION_TABLE.car_no),
days_at_location = (select DATEDIFF(dd,LOCATION_TABLE.clm_date_time,getdate())
from LOCATION_TABLE
where #temp_table.car_init = LOCATION_TABLE.car_init
and #temp_table.car_no = LOCATION_TABLE.car_no)
September 30, 2010 at 12:05 pm
Expand this code to each field. You're running subqueries for each line, that's going to hurt.
UPDATE tt
SETclm_loc_city = lt.loc_city,
clm_loc_state = lt.loc_state
-- ... keep going on
FROM
#temp_table AS tt
JOIN
LOCATION_TABLE as lt
ONtt.car_init = lt.car_init
AND tt.car_no = lt.car_no
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
September 30, 2010 at 12:07 pm
That update looks very RBAR to me (google RBAR if you are not familiar with the term).
Not knowing much about the guts of what you are actually trying to accomplish, I would initially say you will probably find your biggest gains by converting these updates into more set-based solutions. Familiarize yourself with set-based solutions vs programmatic solutions, because there are distinct differences.
For example, instead of saying UPDATE <temptab> SET <col1> = (SELECT col1 FROM <realtab> WHERE <temptab>.id = <realtab>.id), try something like this -
SELECT <temptab>.id, <realtab>.<col1> INTO #temp2 FROM <temptab>, <realtab> WHERE ....
Then, you are creating the SAME result set by doing SELECT/JOIN in one operation instead of making sql server do work for every row as it would with your update.
September 30, 2010 at 12:11 pm
Craig's sql statement is cleaner - do something like what he wrote.
September 30, 2010 at 12:13 pm
you are trying to update the LOCATION_TABLE columns into #temp_tables column. am i right.
if that is true you don't need to use the co-related sql statement on this cause.
you can simply use simple inner join because the outer query and the co-related query is using/referring the same column to match both the tables.
let me know we are on the same page.
Regards,
Subbu
Click here to Get Speedy answer or solution
September 30, 2010 at 12:51 pm
I couldn't agree more with Joe on this one. You're really bashing your fingers with a hammer.
While there are situations where breaking up statements and using a temp table can make things run faster, most of the time, the real value is in using all the tools at your disposal to create a set based query to do the update in a single pass.
In general, instead of working on the UPDATE or INSERT queries, I work on defining a SELECT statement that gets me exactly the data I want. From there, I can determine if I need to then break it up into temp tables or not (and the answer 99/100 is Not).
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 30, 2010 at 1:51 pm
Here's one that is taking a while:
UPDATE #temp_trips
set tcomment_type = (SELECT RT_TRIP_COMMENTS.comment_type
FROM RT_TRIP_COMMENTS
where #temp_trips.car_init = RT_TRIP_COMMENTS.car_init
and #temp_trips.car_no = RT_TRIP_COMMENTS.car_no
and #temp_trips.ship_date_time = RT_TRIP_COMMENTS.ship_date_time
and (RT_TRIP_COMMENTS.comment_type <> 'EX' or RT_TRIP_COMMENTS.comment_type is null)
and comment_date = (SELECT max(comment_date)
FROM RT_TRIP_COMMENTS
where #temp_trips.car_init = RT_TRIP_COMMENTS.car_init
and #temp_trips.car_no = RT_TRIP_COMMENTS.car_no
and #temp_trips.ship_date_time = RT_TRIP_COMMENTS.ship_date_time
and (RT_TRIP_COMMENTS.comment_type <> 'EX' or RT_TRIP_COMMENTS.comment_type is null))
GROUP BY RT_TRIP_COMMENTS.car_init, RT_TRIP_COMMENTS.car_no, RT_TRIP_COMMENTS.ship_date_time, RT_TRIP_COMMENTS.comment_type
)
September 30, 2010 at 2:15 pm
Rog Saber (9/30/2010)
Here's one that is taking a while:[Snip beginning of statement]
and (RT_TRIP_COMMENTS.comment_type <> 'EX' or RT_TRIP_COMMENTS.comment_type is null)
The <> here is probably blowing up an index seek. Need to do an index review.
and comment_date = (SELECT max(comment_date)
FROM RT_TRIP_COMMENTS
where #temp_trips.car_init = RT_TRIP_COMMENTS.car_init
and #temp_trips.car_no = RT_TRIP_COMMENTS.car_no
and #temp_trips.ship_date_time = RT_TRIP_COMMENTS.ship_date_time
and (RT_TRIP_COMMENTS.comment_type <> 'EX' or RT_TRIP_COMMENTS.comment_type is null))
GROUP BY RT_TRIP_COMMENTS.car_init, RT_TRIP_COMMENTS.car_no, RT_TRIP_COMMENTS.ship_date_time, RT_TRIP_COMMENTS.comment_type
)
To paraphrase Mr. Jeff Moden: Welcome to RBAR. Row by agonizing Row. You would probably be better off with the 'group by' in a sub query, then reconnecting back in on four fields, rather then have the 3 fields and then a select statement that runs a group by per row. Explore using an exists clause, or a straight join, off your #table to build the subquery to find the max date.
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply