Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

need to optimize the query- pls help

Posted on 2012-03-28
5
Medium Priority
?
170 Views
Last Modified: 2012-06-11
SELECT  VH.Carrier_Description AS Carrier, MH.shipped_VIA AS [Service], MH.Country AS CountyShippedTo,
 CONVERT(VARCHAR, PA.[Length]) + ',' + CONVERT(VARCHAR, PA.[Width]) + ',' + CONVERT(VARCHAR, PA.[Height]) AS BoxDimensions, MH.[Weight],  MH.Reference as
 [SaleNumber/BidderNumber], MH.totalvalue AS Value, MH.Customer_No AS CustomerNumber, MH.Shipping_Cost AS ShippingCharges
  FROM DSDEV.He.dbo.Ml_HCC_Ship_Info MH
  INNER JOIN WORLD.wldb.dbo.vw_heCI VH on MH.Tracking_Number = VH.TrackingNumber
  INNER JOIN WORLD.wldb.dbo.Packages PA ON MH.Tracking_Number = PA.TrackingNumber
  WHERE [status] = 'ship' and MH.[TimeStamp] > Dateadd(m, -6, Getdate()) and MH.Country NOT IN('UNITED STATES', 'US', 'USA')
  ORDER BY MH.Customer_No
0
Comment
Question by:sqlcurious
5 Comments
 
LVL 9

Expert Comment

by:OCDan
ID: 37778797
The query itself seems perfectly fine, nothing complicated there to cause problems.

What are the indexes on those tables?
Any chance you could post the execution plan?
0
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 37778803
In my opinion you can't do much as it is accros diferent servers by the statement below from your whole query above:

...
FROM DSDEV.He.dbo.Ml_HCC_Ship_Info MH
  INNER JOIN WORLD.wldb.dbo.vw_heCI VH on MH.Tracking_Number
...

What I would do if possible - bring data set you need into a "staging" working table on eiter side of the linked servers and re-write the code to use that one instead. This way you can add indexes that SQL optimizer can actualy use.
0
 
LVL 25

Expert Comment

by:jogos
ID: 37778824
There is not much to optimise in the script itself.

Status theres no table-alias given, The 'not in' for MH.count is something that is not so optimal.
Can you post the execution plan so we can see if there are indexes failing, I think there is the problem.  And tell aprox how many rows per table and the duration of query.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37783482
Are you running the query from "DSDEV" instance or from "WORLD" instance?

[Or, yikes, an entirely different instance.]

Which table is column "[status]" in?
0
 

Author Closing Comment

by:sqlcurious
ID: 38071939
thanks
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

886 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