Update Query taking more then expected time

Question

Post reply

Update Query taking more then expected time

vishal.sanu

Grasshopper

Points: 16
More actions
December 27, 2010 at 4:55 am

#231020

I am using a Stored procedure consist of some Update queries. These update query taking more then expected time around 4 hours to execute
I have two tables Test1 and Test2, Test3
Test1 Contains 4500000 of rows
Test2 Contains 6000000 of rows
Test3 Contains 1 row
here is the update query
UPDATE Test1
SET Test1.INT_TBL_CODE = ITC.INT_TBL_CODE,
Test1.CUST_CR_PREF_PCNT =ITC.CUST_CR_PREF_PCNT,
Test1.ID_CR_PREF_PCNT =ITC.ID_CR_PREF_PCNT ,
Test1.ID_DR_PREF_PCNT =ITC.ID_DR_PREF_PCNT ,
Test1.MIN_INT_PCNT_CR=ITC.MIN_INT_PCNT_CR,
Test1.MAX_INT_PCNT_CR =ITC.MAX_INT_PCNT_CR,
Test1.END_DATE =ITC.END_DATE,
Test1.PEGGED_FLG = ITC.PEGGED_FLG,
Test1.INT_TBL_VER_NUM = ITC.INT_TBL_CODE_SRL_NUM,
Test1.BASE_PCNT = 0
FROM Test1,Test2 t2, Test3 GCT
WHERE Test1.acid =t2.ENTITY_ID AND
Test1.SCHM_TYPE IN ('PCA') AND
t2.int_tbl_code_srl_num =
(SELECT MAX(int_tbl_code_srl_num)
FROM Test2 subt2, Test1 a
WHERE a.acid =subt2.ENTITY_ID And
subt2.entity_id = Test1.ACID And
CONVERT(datetime,subt2.start_date,103) <= CONVERT(datetime,GCT.AS_ON_DATE, 103)
AND CONVERT(datetime,subt2.end_date,103) >= CONVERT(datetime,GCT.AS_ON_DATE, 103))
Indexes are
on test 1
CL_T1clustered located on PRIMARYSCHM_TYPE
NONCL_T1nonclustered located on PRIMARYSOL_ID
NONCL_T1_CustIDnonclustered located on PRIMARYCUST_ID
PK_T1nonclustered, unique, primary key located on PRIMARYACID
On Test2
PK_T2clustered, unique, primary key located on PRIMARYENTITY_ID, ENTITY_TYPE, START_DATE, INT_TBL_CODE_SRL_NUM
Any advise how to reduce the time ...............

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

For a better answer we would need to take a look at the actual execution plan. Can you capture and upload it?

Also, the table and index script would help a lot.

Please, read this article on how to post performance problems[/url], you will help us help you.

-- Gianluca Sartori

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 2

While we're waiting for the Execution Plan...

There's no way for the following to use an index because the columns have to be converted to the value inteneded by the formulas first and then compared. That means a full table scan of one sort or another

CONVERT(datetime,subt2.start_date,103) <= CONVERT(datetime,GCT.AS_ON_DATE, 103)

AND CONVERT(datetime,subt2.end_date,103) >= CONVERT(datetime,GCT.AS_ON_DATE, 103))

Also, what is the datatype of the columns involved in the above?

My final question would be... did you make an error in the Copy'n'Paste of the code because without an additional cup of coffee, I'm just not seeing where the "ITC." table alias is being formed which also means it's not likely the code will run as is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sturner SSC-Insane Points: 22411 More actions · Answer 3

I do not see where the test3 table is being properly joined to the other two.

First thing I would recommend is to re-write the query using standard join syntax.

The probability of survival is inversely proportional to the angle of arrival.

vishal.sanu Grasshopper Points: 16 More actions · Answer 4

Dear All,

Thanks for your valuable help

Here is the changed Query ....

UPDATE TEST1

SET TEST1.INT_TBL_CODE = T2.INT_TBL_CODE,

TEST1.CUST_CR_PREF_PCNT =T2.CUST_CR_PREF_PCNT,

TEST1.ID_CR_PREF_PCNT =T2.ID_CR_PREF_PCNT ,

TEST1.ID_DR_PREF_PCNT =T2.ID_DR_PREF_PCNT ,

TEST1.MIN_INT_PCNT_CR=T2.MIN_INT_PCNT_CR,

TEST1.MAX_INT_PCNT_CR =T2.MAX_INT_PCNT_CR,

TEST1.END_DATE =T2.END_DATE,

TEST1.PEGGED_FLG = T2.PEGGED_FLG,

TEST1.INT_TBL_VER_NUM = T2.INT_TBL_CODE_SRL_NUM,

TEST1.BASE_PCNT = 0

FROM TEST1,TEST2 T2, TEST3 T3

WHERE TEST1.acid =T2.ENTITY_ID AND

TEST1.SCHM_TYPE IN ('PCA') AND

T2.int_tbl_code_srl_num =

(SELECT MAX(int_tbl_code_srl_num)

FROM TEST2 subT2, TEST1 a

WHERE a.acid =subT2.ENTITY_ID And

subT2.entity_id = TEST1.ACID And

CONVERT(datetime,subT2.start_date,103) <= CONVERT(datetime,T3.AS_ON_DATE, 103)

AND CONVERT(datetime,subT2.end_date,103) >= CONVERT(datetime,T3.AS_ON_DATE, 103))

Datatype

START_DATEcharno10

END_DATE charno10

AS_ON_DATEcharno10

Thanks in advance

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 5

vishal.sanu (12/27/2010)
Dear All,
Thanks for your valuable help
Here is the changed Query ....
UPDATE TEST1
SET TEST1.INT_TBL_CODE = T2.INT_TBL_CODE,
TEST1.CUST_CR_PREF_PCNT =T2.CUST_CR_PREF_PCNT,
TEST1.ID_CR_PREF_PCNT =T2.ID_CR_PREF_PCNT ,
TEST1.ID_DR_PREF_PCNT =T2.ID_DR_PREF_PCNT ,
TEST1.MIN_INT_PCNT_CR=T2.MIN_INT_PCNT_CR,
TEST1.MAX_INT_PCNT_CR =T2.MAX_INT_PCNT_CR,
TEST1.END_DATE =T2.END_DATE,
TEST1.PEGGED_FLG = T2.PEGGED_FLG,
TEST1.INT_TBL_VER_NUM = T2.INT_TBL_CODE_SRL_NUM,
TEST1.BASE_PCNT = 0
FROM TEST1,TEST2 T2, TEST3 T3
WHERE TEST1.acid =T2.ENTITY_ID AND
TEST1.SCHM_TYPE IN ('PCA') AND
T2.int_tbl_code_srl_num =
(SELECT MAX(int_tbl_code_srl_num)
FROM TEST2 subT2, TEST1 a
WHERE a.acid =subT2.ENTITY_ID And
subT2.entity_id = TEST1.ACID And
CONVERT(datetime,subT2.start_date,103) <= CONVERT(datetime,T3.AS_ON_DATE, 103)
AND CONVERT(datetime,subT2.end_date,103) >= CONVERT(datetime,T3.AS_ON_DATE, 103))
Datatype
START_DATEcharno10
END_DATE charno10
AS_ON_DATEcharno10
Thanks in advance

My first suggestion stands with the additional suggestion that the date columns you have above should, in fact, be changed to the DATE or DATETIME datatype. Storing formatted dates is a form of "Death By SQL".

We're also waiting for you to post the actual execution plan so we can try to assist further.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

As usual I am with Jeff. We need table definitions and actual query plans. And those functions around columns in the WHERE clause simply HAVE to go. Very common "Bad TSQL" form there, with multiple disasterous effects on performance.

Oh, and ignore Joe's comments.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service