Slow query missing indexes?

paul_anthony · #1 (**permalink**) 11-24-08, 05:52

Hi Guys,

I've a query that runs on two different machines with two different result. One machine it takes 12 seconds - the other it is almost instant. I think perhaps I have missing indexes on one server - anyone any advice on speeding the query up?

Alternatively any tools for mirroring database setups would be useful. Would the output of the execution plan make any sense to anyone?

I've attempting to script the indexes / primary / foreign keys between the two - to no avail.

Really tearing the hair out now as to where the bottle neck is.

PHP Code:


			
SELECT distinct top 8 dbo.tblProductDetail.EuroStopPLU As ssEuro,

newid(), dbo.tblProductDetail.PLU,  

dbo.tblProductDetail.ColourID,

dbo.tblProductDetail.id as ProductID 

FROM dbo.tblOrderLines 

INNER JOIN dbo.tblProductDetail ON dbo.tblOrderLines.ProductID = dbo.tblProductDetail.ID INNER JOIN dbo.tblProductMaster ON tblProductMaster.ID=tblProductDetail.ProductMasterID INNER JOIN tblWebBrand ON tblProductMaster.WebBrandID=tblWebBrand.ID WHERE (dbo.tblOrderLines.OrderID IN (SELECT DISTINCT orderID FROM tblOrderLines WHERE productID = 2548)) AND (dbo.tblOrderLines.ProductID <> 2548) AND (dbo.tblProductDetail.Active = 1) AND (dbo.tblProductDetail.StockExceedsBuffer = 1) AND tblWebBrand.Active=1 and tblWebBrand.Overrideactive = 1 AND tblProductDetail.sex=1 group by dbo.tblProductDetail.EuroStopPLU,tblProductDetail.sex, dbo.tblProductDetail.id,dbo.tblProductDetail.PLU, dbo.tblProductDetail.price, dbo.tblProductDetail.ColourID, dbo.tblProductDetail.Description order by newid()

r937 · #2 (**permalink**) 11-24-08, 06:59

just thought i'd point out that with NEWID() in the SELECT clause, every row returned will be distinct, so you don't need the DISTINCT keyword

Thrasymachus · #3 (**permalink**) 11-24-08, 07:04

I do not know. Do you have any missing indices? Are they same on both tables? Do you have a lazy good fer nuthin' DBA hanging around? Does DBCC SHOWCONTIG show a high level of fragmentation on those indexes? Do both machines run similar hardware? Do both machines contain similar amounts of data? Yes, post the text version of the execution plan.

paul_anthony · #4 (**permalink**) 11-24-08, 07:57

I have ran a stored proc to check indexes, both seem to be fine..(i.e. Identical on both systems) - hardware the same. Indexes have been rebuilt - and are unfragmented, its a relatively new DB. The fast machine has more data than the slow one. And the lazy good fer nuthin' DBA hanging around would be me. A junior dev - thanks for the support.

The text based execution for the slow query is here.

http://docs.google.com/Doc?id=dgvz75z2_0d4gcs5dk

PMASchmed · #5 (**permalink**) 11-24-08, 11:05

Stupid question, same version of SQL Server ?

paul_anthony · #6 (**permalink**) 11-25-08, 05:15

Fraid so. Yep.

Just used Red Gates SQL compare to test, and things are defo identical. The execution plans look very different between databases though. V Weird.

aflorin27 · #7 (**permalink**) 11-25-08, 06:00

Because one machine has more data than the other, it is normal that the execution plans will look different. And also, more data could mean a different distribution on columns.