October 4, 2010 at 9:25 am
Hello,
I have a temporary table that I am creating for reporting purposes. This is the table design:
CREATE TABLE #temp_client_spend(
period varchar(4),
budget_period varchar(8),
budget_month varchar(4),
actual decimal(16,2),
budget decimal(16,2),
begdate datetime,
enddate datetime,
client_name varchar(500))
The table will be populated with values from two different servers. The temporary table is one of of the servers where I am doing my reporting from. I am doing my first populating without any issues - this data is all on the same server; all of the fields are filled with exception to the Actual column. When I go to update this field, the query is taking over 30 seconds to complete which is unacceptable to my users. The key field in the query is period, which will contain a month/year combination like this 0110, 0210, 0310, etc....this will match up with woper from the other server/database.
Here is my slow update command:
UPDATE #temp_client_spend SET actual = (SELECT sum(billdol) FROM tc WHERE mat IN (SELECT mmat FROM mat WHERE client = @client) and woper = period)
Any suggestions on a way to speed up this command? I've tried to break it into pieces, cursors, etc...but nothing has been able to speed it up. It seems like a fairly simple query; with exception of needing to go back and forth between the two different servers and databases to make it work. I believe that this is the what is causing my issue. And bringing the data into a single database is not an option (unfortunately).
Thanks so much in advance!
jamey
October 4, 2010 at 9:29 am
The issue is you're updating every row in the table by using a subquery within a subquery..causing a huge number of reads.
You should consider rewriting it to use inner joins instead (yes, you can join on update statements).
edit: Or even a CTE that inner joins the two subqueries, and then join the update statement to the CTE.
October 4, 2010 at 9:43 am
Derrick,
Thanks for the quick response; so something like this:
UPDATE #temp_client_spend SET actual = (SELECT sum(billdol) FROM tc
inner join mat on mat = mmat
WHERE client = @client and woper = period)
It is quicker, but still an unacceptable amount of processing time for users.
I'm going to investigate the CTE now to see if that will give better results.
October 4, 2010 at 9:53 am
jameyschaetzel (10/4/2010)
Derrick,Thanks for the quick response; so something like this:
UPDATE #temp_client_spend SET actual = (SELECT sum(billdol) FROM tc
inner join mat on mat = mmat
WHERE client = @client and woper = period)
It is quicker, but still an unacceptable amount of processing time for users.
I'm going to investigate the CTE now to see if that will give better results.
Looks like a covering index might speed this up a lot. Can you post the .sqlplan?
October 4, 2010 at 9:54 am
the CTE method worked perfectly!
create table #cte( period varchar(4), actual decimal(16,2))
insert into #cte (period, actual)
select woper, sum(billdol)
from tc
inner join mat on mat = mmat
where client = @client
group by woper
update #temp_client_spend set actual = (select actual from
#cte where period = period)
October 4, 2010 at 10:05 am
The true CTE way would be like this:
WITH CTE
AS (SELECT woper
,SUM(billdol)
FROM tc
INNER JOIN mat
ON mat = mmat
WHERE client = @client
GROUP BY woper)
UPDATE #temp_client_spend
SET actual = actual
FROM CTE
WHERE period = period
but the idea is the same..gather your data first and put it into one flat table, and then for every update row it just matches on the table instead of running two subqueries. Depending how many records there are and how fast this runs, creating a temp table like you did and then creating an index might speed it up even more.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply