INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL Server 2005 running real slow

SQL Server 2005 running real slow

(OP)
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.

CODE

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; 

RE: SQL Server 2005 running real slow

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.

CODE

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

RE: SQL Server 2005 running real slow

(OP)
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

RE: SQL Server 2005 running real slow

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

CODE

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: SQL Server 2005 running real slow

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

RE: SQL Server 2005 running real slow

(OP)
Do I have to add something to this?

CODE

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

RE: SQL Server 2005 running real slow

typo

CODE

UPDATE imp_worx_ar_process
SET closedt = t.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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: SQL Server 2005 running real slow

try this:

CODE

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:

CODE

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

RE: SQL Server 2005 running real slow

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close