February 6, 2016 at 10:01 am
Thanks for the test-harness Jacob! I think I managed to remove one use of LAG from my query (and still attain the correct result) and it seems to have dramatically reduced the elapsed time, at least on my machine. Here is the query:
WITH cte
AS (
SELECT o.OrdID,
o.OrdDt,
o.CustID,
o.MtchID,
p.Price,
p.OrdLoc,
LAG(o.OrdID, 1, 0) OVER (PARTITION BY o.OrdID ORDER BY p.OrdLoc DESC) AS LastOrdID,
LEAD(o.OrdID, 1, 0) OVER (PARTITION BY o.OrdID ORDER BY o.OrdID) AS NextOrdID
FROM ##Ord o
INNER JOIN ##Prs p ON o.MtchID = p.MtchID
WHERE p.OrdLoc IN ('Phone', 'Online')
)
SELECT cte.OrdID,
cte.OrdDt,
cte.CustID,
cte.MtchID,
cte.Price,
cte.OrdLoc
FROM cte
WHERE -- online orders (second in sequence of two OrdIDs)
(
cte.LastOrdID = cte.OrdID
AND cte.NextOrdID != cte.OrdID
)
OR
-- phone orders where there is no online order for the same OrdID
(
cte.LastOrdID != cte.OrdID
AND cte.NextOrdID != cte.OrdID
AND cte.OrdLoc = 'Phone'
);
Results for 100K:
solution last_logical_reads last_worker_time last_elapsed_time
-------------- ------------------ ------------------- --------------------
test_Orlando 2335 1371935 364541
test_Jacob 12118 4422595 1548922
test_Drew 4610160 8930213 2844414
PS I realized I added some indexes too:
create unique clustered index [ci1] on ##Ord (OrdID, MtchID)
CREATE NONCLUSTERED INDEX [nci1] ON [dbo].[##Ord] ([MtchID]) INCLUDE ([OrdID],[OrdDt],[CustID])
create clustered index [ci1] on ##Prs (MtchID, OrdLoc)
Feel free to tune away!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 6, 2016 at 10:46 am
Very nice Orlando. This latest script also handles the many-to-many relationship better. I.e. when I ran your previous script against an extended #Ord table, the output had a duplicate OrdID. Of course, this doesn't matter now, the new script looks great 🙂
FYI additional records in test:
union all
select 102, (GETDATE()-1), 126, 112
union all
select 102, (GETDATE()-1), 126, 111
union all
select 102, (GETDATE()-1), 126, 182
February 6, 2016 at 3:50 pm
Arrgh, so ignore my last set of results. I was not comparing apples to apples. I just ran all three with the indexes I built and while my solution continues to be the lightest on I/O overall performance is still lagging behind:
solution last_logical_reads last_worker_time last_elapsed_time
----------- ------------------- ---------------- -----------------
test_Jacob 11762 7159864 1942721
test_Drew 4605897 11244540 3296805
test_Orlando 5881 13960564 3710847
The Sort that kills performance in my query goes across the two tables. If this were a critical query to the app denormalizing would be the next thing I would look at. I would also consider an indexed view.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply