Solved

Query running slower on 2005 than 2000

Posted on 2008-10-21
10
145 Views
Last Modified: 2012-05-05
I have SQL 2000 and SQL 2005 running on the same (Dev) server. The attached query will run in 5 secs on 2000 and 3:54 on SQL 2005 - Same DB restored from backup.

Worse still it takes 15minutes on Production.

What have I missed?



SELECT o.OrderDate, o.OrderRef, c.ClientCode, oi.Quantity, sno.Snos
FROM tblOrders o
	INNER JOIN tblOrderItems oi ON o.OrderID = oi.OrderID
	INNER JOIN (
		SELECT CatID, ClientCode FROM tblCatalogue WHERE TrackSerialNumbers = 1
		) c ON oi.CatID = c.CatID 
	INNER JOIN (
		SELECT a.OrderID, s.CatID, COUNT(s.SerialNumber) AS Snos
			FROM tblSerialNumbers s 
			INNER JOIN tblAirwayBills a ON s.AirwayBillID = a.AirwayBillID
			GROUP BY OrderID, CatID
	) sno ON o.OrderID = sno.OrderID AND sno.CatID = oi.CatID
WHERE o.OrderID > 184876
AND (oi.Quantity <> sno.Snos)
Order By o.OrderID Desc

Open in new window

0
Comment
Question by:mdalemail
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 150 total points
ID: 22771860
after restore, you need to
? update statistics
? check the execution plan of the query

-> is the hardware specs the same on both machines? I guess the "old" machine was single-cpu, and the new one multi-cpu?
try to append this to your query.
OPTION (MAXDOP 1)
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 300 total points
ID: 22771867
0
 

Author Comment

by:mdalemail
ID: 22771973
Thanks for the posts - I'll have a look.
One bizarre twist - if I change the WHERE clause to search for o.OrderID > 172666 instead of 184876, which of course brings back a greater results set, the query takes 1 second!
What may be relevant is that this takes the Query back to include records created prior to the uupgrade to 2005.

Any thoughts?
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 300 total points
ID: 22771982
Indexes.  When you copied your DB over did you create an indexing maintenance plan?
0
 

Author Comment

by:mdalemail
ID: 22772002
Hmmm. No.
I guess I should re-rate myself to beginner.
So... Indexing maintenance plans. Any good links?
0
 

Author Comment

by:mdalemail
ID: 22772061
Should I be Reorganising or Reindexing?
0
 

Author Comment

by:mdalemail
ID: 22772237
Disgusted Eh?
Thanks anyway - Ran a reorganise followed by rebuild - down to 8 seconds.

Better, but still not as good as when running from older files.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22772850
Not abandoned, just traveling home from work.

SSMS (management studio) should have a pretty good wizard mode for creating an indexing maintenance plan.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
ID: 22782509
Don't make the assumption that what ran fine in SQL Server 2000 will work as well in 2005.  Sometimes queries will not even run.  Every query needs to be inspected.  In my experience, the queries that did not execute as well or failed to compile were using lousy code and should be re-written in any case.  Your miles may vary.
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 300 total points
ID: 22782767
but 4 minutes to 8 seconds after a a defrag is a smoking gun to at least EQUALIZE performance based upon environment, not optimization.  

That sounds confusing.  So I guess what I'm saying is that getting it from 4 minutes to 8 seconds, when it took 5 before, is probably as good as it's going to get WITHOUT rewriting the process.  So many things change from version to version that make certain things faster.  I swear that M$ must put in speedbumps to functionality when they don't want you using it any more.

And by speedbump I mean something that serves no purpose other than slowing something down.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to import SQL 2000 database to SQL 2014 5 181
Need help with a query 6 82
Caste datetime 2 73
Are triggers slow? 7 28
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question