April 13, 2015 at 9:34 am
What would be the proper way to add this to my where clause.
This would be in my sales order table and/or sales order lines table. so if one is true then show results
(select distinct omlSalesOrderID from m1_dc.dbo.SalesOrderLines where omlPartID='finalmile')
or
(select distinct ompSalesOrderID from m1_dc.dbo.SalesOrders where ompShippingMethodID='JBFM')
April 13, 2015 at 9:40 am
Something like this?
SELECT omlSalesOrderID
FROM m1_dc.dbo.SalesOrderLines
WHERE omlPartID='finalmile'
UNION
SELECT ompSalesOrderID
FROM m1_dc.dbo.SalesOrders
WHERE ompShippingMethodID='JBFM'
April 13, 2015 at 12:07 pm
And if you are getting multiple results, instead of DISTINCT which is an aggregation command, use TOP 1 with an ORDER BY of some sort. That's much more likely to get treated well by the optimizer.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 13, 2015 at 12:28 pm
Works Great Thanks
April 13, 2015 at 2:37 pm
Go ahead and add DISTINCT to each separate query on which it's applicable:
SELECT DISTINCT omlSalesOrderID
FROM m1_dc.dbo.SalesOrderLines
WHERE omlPartID='finalmile'
UNION
SELECT ompSalesOrderID
FROM m1_dc.dbo.SalesOrders
WHERE ompShippingMethodID='JBFM'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 13, 2015 at 2:45 pm
ScottPletcher (4/13/2015)
Go ahead and add DISTINCT to each separate query on which it's applicable:
SELECT DISTINCT omlSalesOrderID
FROM m1_dc.dbo.SalesOrderLines
WHERE omlPartID='finalmile'
UNION
SELECT ompSalesOrderID
FROM m1_dc.dbo.SalesOrders
WHERE ompShippingMethodID='JBFM'
Why? :unsure:
April 13, 2015 at 2:58 pm
Luis Cazares (4/13/2015)
ScottPletcher (4/13/2015)
Go ahead and add DISTINCT to each separate query on which it's applicable:
SELECT DISTINCT omlSalesOrderID
FROM m1_dc.dbo.SalesOrderLines
WHERE omlPartID='finalmile'
UNION
SELECT ompSalesOrderID
FROM m1_dc.dbo.SalesOrders
WHERE ompShippingMethodID='JBFM'
Why? :unsure:
It's typically faster to de-dup two smaller sets of rows than one larger set, i.e., 250K rows and 250K rows and key-merge them rather than de-duping all 500K rows at once. I'm expecting SQL would be able to effectively merge-join the rows in the final UNION, without re-sorting those that had DISTINCT, but haven't actually tested that.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 13, 2015 at 3:53 pm
It made me curious, so I put this together real quick:
SELECT a.City,
a.PostalCode
FROM Person.Address AS a
WHERE a.City = 'York'
UNION
SELECT a.City,
a.PostalCode
FROM Person.Address AS a
WHERE a.City = 'Westminster';
GO
SELECT DISTINCT
a.City,
a.PostalCode
FROM Person.Address AS a
WHERE a.City = 'York'
UNION
SELECT DISTINCT
a.City,
a.PostalCode
FROM Person.Address AS a
WHERE a.City = 'Westminster';
GO
SELECT a.City,
a.PostalCode
FROM Person.Address AS a
WHERE a.City = 'York'
UNION
SELECT DISTINCT
a.City,
a.PostalCode
FROM Person.Address AS a
WHERE a.City = 'Westminster';
GO
SELECT DISTINCT
a.City,
a.PostalCode
FROM Person.Address AS a
WHERE a.City = 'York'
UNION
SELECT a.City,
a.PostalCode
FROM Person.Address AS a
WHERE a.City = 'Westminster';
GO
Execution times, in microseconds, in order were:
3280
3396
4120
3921
Each returned exactly the same number of rows and the same number of reads, 438.
There were two different execution plan shapes, one for the UNION and one for the other three, although four different plan hash values, so there are internal differences (not surprising). The first two queries were usually the fastest and over a series of tests, occasionally switched places (internal contention?), but the UNION only was consistently the faster of the two. I've attached two of the plans.
I don't think, based on this, that I'd let both DISTINCT and UNION run together. Although, freely admitted, this is a pretty simple example.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 13, 2015 at 4:32 pm
Grant Fritchey (4/13/2015)
It made me curious, so I put this together real quick:
SELECT a.City,a.PostalCode
FROM Person.Address AS a
WHERE a.City = 'York'
UNION
SELECT a.City,
a.PostalCode
FROM Person.Address AS a
WHERE a.City = 'Westminster';
GO
SELECT DISTINCT
a.City,
a.PostalCode
FROM Person.Address AS a
WHERE a.City = 'York'
UNION
SELECT DISTINCT
a.City,
a.PostalCode
FROM Person.Address AS a
WHERE a.City = 'Westminster';
GO
SELECT a.City,
a.PostalCode
FROM Person.Address AS a
WHERE a.City = 'York'
UNION
SELECT DISTINCT
a.City,
a.PostalCode
FROM Person.Address AS a
WHERE a.City = 'Westminster';
GO
SELECT DISTINCT
a.City,
a.PostalCode
FROM Person.Address AS a
WHERE a.City = 'York'
UNION
SELECT a.City,
a.PostalCode
FROM Person.Address AS a
WHERE a.City = 'Westminster';
GO
Execution times, in microseconds, in order were:
3280
3396
4120
3921
Each returned exactly the same number of rows and the same number of reads, 438.
There were two different execution plan shapes, one for the UNION and one for the other three, although four different plan hash values, so there are internal differences (not surprising). The first two queries were usually the fastest and over a series of tests, occasionally switched places (internal contention?), but the UNION only was consistently the faster of the two. I've attached two of the plans.
I don't think, based on this, that I'd let both DISTINCT and UNION run together. Although, freely admitted, this is a pretty simple example.
Yeah, a couple of hundred rows with no overlap is not much of a test bed. But the plan still shows what I expected: the UNION-only queries concat all the rows, in this case 211, then sort to get the 6 distinct rows. The DISTINCT reduces the rows from each query to 3 rows, then those are concat'ed to get the final 6 distinct rows. I would expect that to be better overall, especially when SQL can use an index to get distinct values more quickly separately from each table, rather than having to combine all the rows first and then find the distinct values.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 13, 2015 at 4:40 pm
ScottPletcher (4/13/2015)
Yeah, a couple of hundred rows with no overlap is not much of a test bed. But the plan still shows what I expected: the UNION-only queries concat all the rows, in this case 211, then sort to get the 6 distinct rows. The DISTINCT reduces the rows from each query to 3 rows, then those are concat'ed to get the final 6 distinct rows. I would expect that to be better overall, especially when SQL can use an index to get distinct values more quickly separately from each table, rather than having to combine all the rows first and then find the distinct values.
Adding indexes is pretty likely to change things. I'm still inclined to separate UNION and DISTINCT. I think it probably depends on what exactly you're performing the DISTINCT operation on. I'm sure there will be situations where it swaps, but I'll bet the standard case of UNION only is likely to be better more often. However, it'll take a lot more extensive testing than I've got time for at the moment.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 14, 2015 at 8:57 am
What about joining the two tables?
SELECT DISTINCT SO.omlSalesOrderID
FROM m1_dc.dbo.SalesOrders AS SO
LEFT OUTER JOIN m1_dc.dbo.SalesOrderLines AS SOI
ON SO.ompSalesOrderID = SOI.ompSalesOrderID
WHERE SO.ompShippingMethodID='JBFM'
OR SOI.omlPartID='finalmile'
April 14, 2015 at 9:12 am
Zingiber (4/14/2015)
What about joining the two tables?
SELECT DISTINCT SO.omlSalesOrderID
FROM m1_dc.dbo.SalesOrders AS SO
LEFT OUTER JOIN m1_dc.dbo.SalesOrderLines AS SOI
ON SO.ompSalesOrderID = SOI.ompSalesOrderID
WHERE SO.ompShippingMethodID='JBFM'
OR SOI.omlPartID='finalmile'
You'll need a FULL OUTER JOIN which is (tipically) more costly than a UNION.
April 14, 2015 at 9:23 am
I used a LEFT join assuming you can't have sales order lines without a sales order, but the performance might be the same as OUTER.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply