March 8, 2012 at 8:08 am
I could use some help,
I have a stored procedure which executes 5 update queries. It always worked fine with SQL2000. After upgrading 4 of the queries take forever to complete. Actually I don't know how long I cancel the query.
I have narrowed it down to the Where clause. It these specific cases the Where clause is a Math Comparison.
For Example
WHERE (v_Job_Master_JCD_Open_sJobs.Budget_Rev<>[tbl_Job_JCD_Projections].[Budget_Rev_Last])
Or
WHERE (v_Job_Master_JCD_Open_sJobs.Costs_TD + v_Job_Master_JCD_Open_sJobs.Committed_Cost)>[tbl_Job_JCD_Projections].[Cost_at_Completion]
What changed that I'm not aware of?
Some background: One table is local, another is to a linked iSeries IBM server. I have run v_Job_Master_JCD_Open_sJobs both as a view and a Select query in the From clause.
Thank you,
P.S. Here is the complete update query which works fine without the where.
Update tbl_Job_JCD_Projections
SET
tbl_Job_JCD_Projections.JCD_Modified = 1
, tbl_Job_JCD_Projections.Budget_Rev_Last = IsNull(vw_Job_Master_JCD_Open_sJobs.Budget_Rev,0)
From
tbl_Job_JCD_Projections
Left Outer Join vw_Job_Master_JCD_Open_sJobs
On vw_Job_Master_JCD_Open_sJobs.Co_No = tbl_Job_JCD_Projections.Co_No
And vw_Job_Master_JCD_Open_sJobs.Div_No = tbl_Job_JCD_Projections.Div_No
And vw_Job_Master_JCD_Open_sJobs.Job_No = tbl_Job_JCD_Projections.Job_No
AND (vw_Job_Master_JCD_Open_sJobs.Main_Job_No = tbl_Job_JCD_Projections.Main_Job_No)
AND (vw_Job_Master_JCD_Open_sJobs.Sub_Job_No = tbl_Job_JCD_Projections.Sub_Job_No)
AND (vw_Job_Master_JCD_Open_sJobs.Cost_Type = tbl_Job_JCD_Projections.Cost_Type)
AND (vw_Job_Master_JCD_Open_sJobs.Cost_Code = tbl_Job_JCD_Projections.Cost_Code)
WHERE (vw_Job_Master_JCD_Open_sJobs.Budget_Rev<>[tbl_Job_JCD_Projections].[Budget_Rev_Last])
March 8, 2012 at 8:37 am
The most likely cause is a predicate that doesn't get pushed to the linked server and gets resolved locally, after copying the entire remote table in tempdb.
I would try to use OPENQUERY to narrow down the number of rows to copy from the remote table. Moreover, with OPENQUERY you can control which predicate gets resolved locally and which gets pushed on the remote server.
Another possible strategy is staging the rows from the remote table in a temporary table and then using it to perform the update, given that you're updating the local table.
It works also the other way round: upload the local table and then update with a pass-through query (EXEC ... AT)
Which table are you updating? The remote one or the local one?
-- Gianluca Sartori
March 8, 2012 at 8:42 am
I'm updating the local table.
My first thought was replacing the view with an OpenQuery within the SP. Narrowed down the amount of data but didn't solve the problem.
I did find an interesting solution. I use a Case within the Set statement that is identical to the Where; I delete the Where and it runs fine.
Of course this means duplicating the Where clause in EACH of the Case When for each field being updated in each of the 5 Update queries.
But it worked???
March 8, 2012 at 9:02 am
With 5 update queries working on the same remote data set, I would download the remote table locally to a temp table.
Have you tried that?
-- Gianluca Sartori
March 8, 2012 at 9:07 am
Wow, what a great idea!
I never actually thought of that; I could down load a data set limited to what I need and it would speed things up.
Thank you,
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply