September 15, 2008 at 10:20 am
Hi Guys..
Does anyone see a problem with the following update query? The last time i ran it, i killed the query after 3 hours. It is currently running and at about an hour. The inner query runs as a select query in 1 minute.
UPDATE BSKT_SKUDTL
SET BSKT_SKUDTL.SINGLE_BSKT_COUNT = A.CherryPicks
FROM
(
SELECT dbo.TRNDTL_BTS08.SKU_NUMBER, Count(dbo.TRNDTL_BTS08.TRANS_ID) AS CherryPicks, dbo.TRNSMY_BTS08.WEEK_END_DT
FROM dbo.TRNSMY_BTS08 INNER JOIN dbo.TRNDTL_BTS08 ON (dbo.TRNSMY_BTS08.TRANS_ID = dbo.TRNDTL_BTS08.TRANS_ID)
AND (dbo.TRNSMY_BTS08.WEEK_END_DT = dbo.TRNDTL_BTS08.WEEK_END_DT)
WHERE (((dbo.TRNDTL_BTS08.NET_SLS_UNITS)=dbo.TRNSMY_BTS08.NET_SLS_UNITS))
AND dbo.TRNDTL_BTS08.WEEK_END_DT = 20080913
GROUP BY dbo.TRNDTL_BTS08.SKU_NUMBER, dbo.TRNSMY_BTS08.WEEK_END_DT
)
AS A
INNER JOIN
BSKT_SKUDTL
ON
BSKT_SKUDTL.SKU_NUMBER = A.SKU_NUMBER AND BSKT_SKUDTL.WK_END_DT = A.WEEK_END_DT
WHERE BSKT_SKUDTL.WK_END_DT = 20080913
Background::
table BSKT_SKUDTL is keyed on SKU_NUMBER & WEEK_END_DT
Thank you in advance.
September 15, 2008 at 10:26 am
HI There,
How Many rows is this gonna be updating?
Also is this table being used alot by other uses? (cause locks)
Last but not least do you have an execution plan for us?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 15, 2008 at 10:32 am
PeterG (9/15/2008)
Hi Guys..Does anyone see a problem with the following update query? The last time i ran it, i killed the query after 3 hours. It is currently running and at about an hour. The inner query runs as a select query in 1 minute.
UPDATE BSKT_SKUDTL
SET BSKT_SKUDTL.SINGLE_BSKT_COUNT = A.CherryPicks
FROM
(
SELECT dbo.TRNDTL_BTS08.SKU_NUMBER, Count(dbo.TRNDTL_BTS08.TRANS_ID) AS CherryPicks, dbo.TRNSMY_BTS08.WEEK_END_DT
FROM dbo.TRNSMY_BTS08 INNER JOIN dbo.TRNDTL_BTS08 ON (dbo.TRNSMY_BTS08.TRANS_ID = dbo.TRNDTL_BTS08.TRANS_ID)
AND (dbo.TRNSMY_BTS08.WEEK_END_DT = dbo.TRNDTL_BTS08.WEEK_END_DT)
WHERE (((dbo.TRNDTL_BTS08.NET_SLS_UNITS)=dbo.TRNSMY_BTS08.NET_SLS_UNITS))
AND dbo.TRNDTL_BTS08.WEEK_END_DT = 20080913
GROUP BY dbo.TRNDTL_BTS08.SKU_NUMBER, dbo.TRNSMY_BTS08.WEEK_END_DT
)
AS A
INNER JOIN
BSKT_SKUDTL
ON
BSKT_SKUDTL.SKU_NUMBER = A.SKU_NUMBER AND BSKT_SKUDTL.WK_END_DT = A.WEEK_END_DT
WHERE BSKT_SKUDTL.WK_END_DT = 20080913
Background::
table BSKT_SKUDTL is keyed on SKU_NUMBER & WEEK_END_DT
Thank you in advance.
Remove ALIAS of the target column.
e.g:
UPDATE BSKT_SKUDTL
SET SINGLE_BSKT_COUNT = A.CherryPicks
....
(Make also sure that you have a one-to-one relationship with that join or you can get unexpected results)
* Noel
September 15, 2008 at 10:35 am
bskt_skudtl has 650k rows. the specific week i am updating has approx. 17k rows.
The table i am retrieving from (trndtl_bts08) has 150mil rows.
i know how to display the execution plan - how would i get it to you?
September 15, 2008 at 10:38 am
it is one to one.
i will try your suggestion however i'm curious why that would make a difference
September 15, 2008 at 11:13 am
PeterG (9/15/2008)
bskt_skudtl has 650k rows. the specific week i am updating has approx. 17k rows.The table i am retrieving from (trndtl_bts08) has 150mil rows.
i know how to display the execution plan - how would i get it to you?
In SQL Server 2005 you can save the graphical execution plan as a .SQLPLAN file and then send it around. Or you can make an XML plan. Either way, zip them up to post them here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 15, 2008 at 11:42 am
attached is the execution plan......i did not zip or change the file.....i simply changed the extension so i could upload it......change the extension back to .sqlplan
September 15, 2008 at 12:11 pm
The bookmark lookup on TRNDTL_BTS08 is chewing up 90% of the cost of the query. The actual update changes a single row (according to this execution plan) but it has to filter that from 13000 rows. You need to try to filter that stuff up front where you can. According to the plan you've got an opportunity for an index that will fix the problem. I'd try it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 15, 2008 at 12:36 pm
hi
thanks for taking a look.
If you look at the query i am updating a table with results from a query not from another table. don't know if it is possible to index a query?
i did just test creating a temp table and then run the update. the temp table gets created in 1.5 minutes and the update takes 8 secs as opposed to 60-180 minutes as is.
i guess i'll dump the current method.
September 15, 2008 at 12:44 pm
No, you can't index a query (per se), but it's suggesting an index on the table within the query. That bookmark lookup is costing you. If that index, with the include columns, turns it into a covering index so that no lookup is necessary, I think you'll see a pretty radical increase in performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 15, 2008 at 12:50 pm
can u tell which column within the query appears to be in need of an index??
September 15, 2008 at 12:57 pm
Yeah, open the query plan you posted. It's right there on top, including all the syntax except the query name to create it.
Here's the code (I used SQL 2008 to pluck it out of the XML).
/*
Missing Index Details from qqq.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 90.3878%.
*/
/*
USE [MKTBSK]
GO
CREATE NONCLUSTERED INDEX [ ]
ON [dbo].[TRNDTL_BTS08] ([WEEK_END_DT],[SKU_NUMBER])
INCLUDE ([TRANS_ID],[NET_SLS_UNITS])
GO
*/
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 16, 2008 at 2:25 am
Grant Fritchey (9/15/2008)
Yeah, open the query plan you posted. It's right there on top, including all the syntax except the query name to create it.
Sorry but I got last by that comment.
How did you know from the plan which was the correct columns to index?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 16, 2008 at 5:59 am
HI Grant,
No problem I opened the plan in NOTEPAD ++ and found the text you referring to 🙂
very helpful 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply