Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2005
    Posts
    10

    Unanswered: Slow query missing indexes?

    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=and tblWebBrand.Overrideactive AND tblProductDetail.sex=1 group by dbo.tblProductDetail.EuroStopPLU,tblProductDetail.sexdbo.tblProductDetail.id,dbo.tblProductDetail.PLUdbo.tblProductDetail.pricedbo.tblProductDetail.ColourIDdbo.tblProductDetail.Description order by newid() 

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Sep 2005
    Posts
    10
    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
    Last edited by paul_anthony; 11-24-08 at 09:05.

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Stupid question, same version of SQL Server ?

  6. #6
    Join Date
    Sep 2005
    Posts
    10
    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.

  7. #7
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •