September 9, 2016 at 8:54 am
Good Day,
I have a performance issue where I run a query/script on different SQL server versions, on the SQL Server 2012 this script runs for 2 secs, but on the 2008 Server version, it runs for 20 secs. i have used index scan and , all tables in the 2012 version and 2008 version, have the same indexes, what can i do to check and also improve the performance and also decrease the run-time to less than 5 secs as it is on the 2012 version.
Please assist. Thanks
see script below
DECLARE @flexOrderDefault INT = 1
DECLARE @rnetOrderDefault INT = 2
DECLARE @flexCpOrderDefault INT = 3
DECLARE @flex9CpOrderDefault INT = 5
DECLARE @cumulusPush INT = 1
DECLARE @marketronPush INT = 3
DECLARE @inventoryLogStatusUnverified INT = 0
DECLARE @inventoryLogStatusScheduledInMarket INT = 1
DECLARE @orderId INT = 0
SELECT
ooh.id Id,
a.Name AdvertiserName,
ag.name AgencyName,
CONVERT(BIT, CASE ooh.countCumulus WHEN 0 THEN 0 ELSE 1 END) hasSpotsForCumulus,
CONVERT(BIT, CASE ooh.countMarketron WHEN 0 THEN 0 ELSE 1 END) hasSpotsForMarketron
FROM
(
SELECT ioh.id,
SUM(CASE ioh.DispatchMethodID WHEN @cumulusPush THEN 1 ELSE 0 END) countCumulus,
SUM(CASE ioh.DispatchMethodID WHEN @marketronPush THEN 1 ELSE 0 END) countMarketron
FROM
(
SELECT
oh.id, acd.DispatchMethodID
from Inventory i
JOIN InventoryOrderSpot ios ON ios.InventoryID = i.ID
JOIN OrderSpot os ON ios.OrderSpotID = os.ID
JOIN OrderDetailWeek odw on odw.ID = os.OrderDetailWeekID
JOIN OrderDetail od ON od.ID = odw.OrderDetailID
JOIN OrderHeader oh ON oh.ID = od.OrderHeaderID
JOIN ContractDetail acd ON acd.ID = i.ContractDetailID
JOIN Station AS s ON s.id = acd.stationid
JOIN Package as p on od.PackageID = p.ID
JOIN PackageVehicle as pv on p.ID = pv.PackageID
JOIN Vehicle V ON oh.ID = v.OrderHeaderID AND V.id = pv.VehicleID
WHERE (oh.CumulusOrderDefaultID = @flexOrderDefault OR oh.CumulusOrderDefaultID = @rnetOrderDefault OR oh.CumulusOrderDefaultID = @flexCpOrderDefault OR oh.CumulusOrderDefaultID = @flex9CpOrderDefault)
AND (ios.Status = @inventoryLogStatusUnverified OR ios.Status = @inventoryLogStatusScheduledInMarket)
AND (@orderId = 0 OR oh.ID = @orderId)
AND acd.DispatchMethodID = 1
) ioh
GROUP BY ioh.ID
) ooh
join OrderHeader oh on ooh.id = oh.id
JOIN Company a ON oh.AdvertiserID = a.ID
JOIN Company ag ON oh.AgencyID = ag.ID
ORDER BY a.Name
I learn from the footprints of giants......
September 9, 2016 at 9:01 am
Looks like you're using optional parameters, have you tried using OPTION(RECOMPILE)?
have been caught out with that before:
http://www.sommarskog.se/dyn-search-2008.html#static
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
September 9, 2016 at 9:30 am
First, simplify your query. Second, split it into two different queries. The execution plan for a single orderID is unlikely to be the same as the execution plan for them all.
SELECT oh.id,
CAST(MAX(CASE acd.DispatchMethodID WHEN @cumulusPush THEN 1 ELSE 0 END) AS BIT) hasSpotsForCumulus,
CAST(MAX(CASE acd.DispatchMethodID WHEN @marketronPush THEN 1 ELSE 0 END) AS BIT) hasSpotsForMarketron,
MAX(a.[Name]) AdvertiserName,
MAX(ag.[name]) AgencyName
FROM Inventory i
JOIN InventoryOrderSpot ios ON ios.InventoryID = i.ID
JOIN OrderSpot os ON ios.OrderSpotID = os.ID
JOIN OrderDetailWeek odw on odw.ID = os.OrderDetailWeekID
JOIN OrderDetail od ON od.ID = odw.OrderDetailID
JOIN OrderHeader oh ON oh.ID = od.OrderHeaderID
JOIN ContractDetail acd ON acd.ID = i.ContractDetailID
JOIN Station AS s ON s.id = acd.stationid
JOIN Package as p on od.PackageID = p.ID
JOIN PackageVehicle as pv on p.ID = pv.PackageID
JOIN Vehicle V ON oh.ID = v.OrderHeaderID AND V.id = pv.VehicleID
JOIN Company a ON oh.AdvertiserID = a.ID
JOIN Company ag ON oh.AgencyID = ag.ID
WHERE oh.CumulusOrderDefaultID IN (@flexOrderDefault, @rnetOrderDefault, @flexCpOrderDefault, @flex9CpOrderDefault)
AND ios.Status IN (@inventoryLogStatusUnverified, @inventoryLogStatusScheduledInMarket)
AND (@orderId = 0 OR oh.ID = @orderId)
AND acd.DispatchMethodID = 1
GROUP BY oh.ID
ORDER BY a.Name
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 9, 2016 at 10:32 am
Thanks Guys, This is Fixed,
i wrapped it Up in a procedure and it worked just fine.
Thanks
I learn from the footprints of giants......
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply