×
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!
  • Students Click Here

*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.

Students Click Here

Jobs

SQL Server 2005 running real slow

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!

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