Solved

Query running slower on 2005 than 2000

Posted on 2008-10-21
10
140 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
10 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 150 total points
Comment Utility
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
Comment Utility
0
 

Author Comment

by:mdalemail
Comment Utility
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
 
LVL 39

Assisted Solution

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

Author Comment

by:mdalemail
Comment Utility
Hmmm. No.
I guess I should re-rate myself to beginner.
So... Indexing maintenance plans. Any good links?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:mdalemail
Comment Utility
Should I be Reorganising or Reindexing?
0
 

Author Comment

by:mdalemail
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now