?
Solved

need to optimize the query- pls help

Posted on 2012-03-28
5
Medium Priority
?
172 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

592 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