SQL Server 2005 running real slow

[vba317]

I am running a stored procedure that usually takes 45 minutes and now I am cancelling out the job after 3 or 4 hours. I have rebooted the server and I have rebuilt the indexes in most of the tables. When I tried to go though code I found that a typical example of code that is taking hours instead of minutes is :
I have rebiult the indexes on this table imp_worx_ar_process
Any help is greatly appreciated.

UPDATE imp_worx_ar_process
SET closedt = cldt.clsdt
FROM imp_worx_ar_process i
	INNER JOIN tmp_CloseDts cldt on i.clntid = cldt.clntid AND i.rptpd = cldt.rptpd;

 

[gmmastros]

How many rows do you have in imp_worx_ar_process and how many rows in tmp_CloseDts?

More importantly, what indexes do you have on these tables?


Please run the following code and paste it back here.

sp_helpindex 'imp_worx_ar_process'
sp_helpindex 'tmp_CloseDts'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[vba317]

Currently in the process tmp_CloseDts hasnt been created yet.

The results for sp_helpindex 'imp_worx_ar_process' is

Indx_ARProc_UCIPd nonclustered located on PRIMARY UCI, clntid, rptpd

 

[fredericofonseca]

that index not being used as column UCI is not part of the join
Either add a new index to have clntid and rptpd as the first 2 columns of the index, change the join so UCI is part of the join if at all possible, or try the following

UPDATE imp_worx_ar_process
SET closedt = cldt.clsdt
FROM imp_worx_ar_process i
inner join (select distinct i.UCI
                           ,i.clntid
                           ,i.rptpd
                           ,cldt.clsdt
              FROM imp_worx_ar_process i
              INNER JOIN tmp_CloseDts cldt 
              on i.clntid = cldt.clntid 
              AND i.rptpd = cldt.rptpd; 
            ) t
on i.clntid = t.clntid 
AND i.rptpd = t.rptpd
and i.UCI = t.UCI 
;

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[vba317]

I tried your code Frederico and I got an error The multi-part identifier "cldt.clsdt" could not be bound.

 

[vba317]

Do I have to add something to this?

CREATE TABLE tmp_CloseDts (
clntid int
,rptpd int
,clsdt datetime );

 

[fredericofonseca]

typo

UPDATE imp_worx_ar_process
SET closedt = [highlight #CC0000][b]t.clsdt[/b][/highlight]
FROM imp_worx_ar_process i
inner join (select distinct i.UCI
                           ,i.clntid
                           ,i.rptpd
                           ,cldt.clsdt
              FROM imp_worx_ar_process i
              INNER JOIN tmp_CloseDts cldt 
              on i.clntid = cldt.clntid 
              AND i.rptpd = cldt.rptpd; 
            ) t
on i.clntid = t.clntid 
AND i.rptpd = t.rptpd
and i.UCI = t.UCI 
;

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[gmmastros]

try this:

Create 
Index   idx_imp_worx_ar_process_clntid_rptpd
On      imp_worx_ar_process(clntid, rptpd)

I am suggesting that you add this index because those two column are used in the join condition.

Also... After you run this line:

CREATE TABLE tmp_CloseDts (
clntid int
,rptpd int
,clsdt datetime );

Add this:

Create 
Index   idx_tmp_CloseDts _clntid_rptpd
On      tmp_CloseDts(clntid, rptpd)

By adding these indexes, SQL Server should be able to use them to improve the performance of your query.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[vba317]

I have decided to do a restore and now the Database is working. Thanks for the assistance.