asked on

need to optimize the query- pls help

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

OCDan

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?

ASKER CERTIFIED SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jogos

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.

Scott Pletcher

Are you running the query from "DSDEV" instance or from "WORLD" instance?

[Or, yikes, an entirely different instance.]

Which table is column "[status]" in?

sqlcurious

ASKER

thanks