Stored Procedure Execution is slow

[swreng]

Dear all,

I have this stored procedure:

update newcampaign set apoklisi=
(SELECT 8-Avg(diffprev)
from v_periodresults where username=newcampaign.username
group by username)

This procedure is be execute in 28 seconds from a specific sql server.

The problem is that i have restored the db in to another Sql server and it's very slow about 30 minutes.

The v_periodresults is a view and i detect the problem there. i did a simple select in this view from both sql server the resultset came in 50 seconds for one and 20 for the other.
Any ideas for what's wrong with slow server.
Both are 2000 with sp4 also i executed the sp_updatestats
Best Regards!

 

[ca8msm]

What does the execution plan say? Are there any tables scans? Do you have any indexes setup? What does the view look like?


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[gmmastros]

Try this....

(but make a backup of your database first)

update newcampaign 
set    newcampaign.apoklisi = A.AvgDiffPref
From   newcampaign
       Inner Join 
         (
         SELECT UserName,
                8-Avg(diffprev) As AvgDiffPref
         from   v_periodresults 
         Group By username
         ) As A	
         On newcampaign.username = A.username

If this doesn't solve the problem, then I would suggest you post the code behind the view.

[tt][blue]sp_helptext 'v_periodresults'[/blue][/tt]



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[genomon]

Assume you have SET NOCOUNT ON?

"Before you criticize someone, you should walk a mile in their shoes.
That way, when you criticize them, you're a mile away and you have their shoes."

 

[SQLSister]

You might also want to update statistics/indexes on the slower server.

"NOTHING is more important in a database than integrity." ESquared

 

[JackVam]

Break the query down into parts via the Execution plan and monitor if there are any scans which may be slowing things down.
Also are there functions which could be causing this situation?

Jack Vamvas

All the IT jobs in one place -

http://www.ITjobfeed.com

 

[swreng]

Sorry i didn't respond all this dates.
I tried everything you told me but the problem is still remaining.
The problem is not on the query or on a view it's on server. I tried on a third server and it's working perfect (15 Seconds).

I think a solution it's to uninstall the sql server and install it again. Maybe this fix the problem


Thank you very much for you interesting!

 

[gmmastros]

I would encourage you to update statistics and your indexes. This would be better than re-installing the server.

Also, like JackVam suggests... you should examine the execution plan on both servers and identify the differences. This may (and likely will) help you identify the problem.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[swreng]

i did update stats by executing the sp_updatestats on my database.
Must i do and something else?

Best Regards!

 

[SQLSister]

Check to see if the tables involved actually have the same indexes bewteen the two servers.

"NOTHING is more important in a database than integrity." ESquared

 

[SQLSister]

also you might try DBCC DBREINDEX

"NOTHING is more important in a database than integrity." ESquared

 

[Crowley16]

what about the hardware between the 2 servers?


--------------------
Procrastinate Now!