Re: How best to meet these business requirements

Hugo,

Thanks for the other options. I guess when I mean elegent, I really mean a
solution that performs better. But you know looking at the code and being
able to read it and make sense is important, but bottom line is how to meet
the requirements and return the data as fast as possible is really what is
needed. I liked your code from a readability perspective. But when I ran it
using the attached script on a table with 12,000,000+ rows it didn't run any
faster than my query. So if I use the performance yardstick to measure my
approach verses your approach I'm thinking my appoach is better. Any
comment on how I came to that conclusion. Any other thoughts on different
approaches?

SET NOCOUNT ON;

CREATE TABLE #R (ModifiedDate datetime);
INSERT INTO #R VALUES ('2001-09-06');
INSERT INTO #R VALUES ('2001-07-02');
INSERT INTO #R VALUES ('2001-09-29');
INSERT INTO #R VALUES ('2001-07-25');
INSERT INTO #R VALUES ('2001-05-17');
INSERT INTO #R VALUES ('2001-08-14');
INSERT INTO #R VALUES ('2001-06-09');

CREATE TABLE dbo.L(
[SalesOrderID] [int],
[SalesOrderDetailID] [int],
[CarrierTrackingNumber] [nvarchar](25),
[OrderQty] [smallint] ,
[ProductID] [int] ,
[SpecialOfferID] [int] ,
[UnitPrice] [money] ,
[UnitPriceDiscount] [money] ,
[rowguid] [uniqueidentifier],
[ModifiedDate] [datetime]
);
DECLARE @I TINYINT;
SET @I = 0;
WHILE @I < 100
-- Create Table with 12,000,000+ rows for scalability approach
BEGIN
SET @I = @I + 1;
INSERT INTO L SELECT
[SalesOrderID] ,
[SalesOrderDetailID] ,
[CarrierTrackingNumber] ,
[OrderQty] ,
[ProductID] ,
[SpecialOfferID] ,
[UnitPrice] ,
[UnitPriceDiscount] ,
[rowguid] ,
[ModifiedDate]
FROM AdventureWorks.Sales.SalesOrderDetail;
END

SET STATISTICS IO ON;
SET STATISTICS TIME ON

-- Code to meet requirements (note same in both situations)
-- Test Situation 1
SELECT *
FROM dbo.L
LEFT OUTER JOIN #R
ON dbo.L.ModifiedDate = #R.ModifiedDate
WHERE dbo.L.ModifiedDate =
CASE WHEN EXISTS(SELECT * FROM #R)
THEN #R.ModifiedDate
ELSE dbo.L.ModifiedDate end;


SELECT *
FROM dbo.L
WHERE EXISTS
(SELECT *
FROM #R
WHERE dbo.L.ModifiedDate = #R.ModifiedDate)
OR NOT EXISTS
(SELECT *
FROM #R);
DROP TABLE #R
GO

SELECT COUNT(*) FROM dbo.L

-- Test Situation #2
CREATE TABLE #R (ModifiedDate datetime);

SELECT COUNT(*)
FROM dbo.L
LEFT OUTER JOIN #R
ON dbo.L.ModifiedDate = #R.ModifiedDate
WHERE dbo.L.ModifiedDate =
CASE WHEN EXISTS(SELECT * FROM #R)
THEN #R.ModifiedDate
ELSE dbo.L.ModifiedDate end;


SELECT COUNT(*)
FROM dbo.L
WHERE EXISTS
(SELECT *
FROM #R
WHERE dbo.L.ModifiedDate = #R.ModifiedDate)
OR NOT EXISTS
(SELECT *
FROM #R);

DROP TABLE #R, dbo.L

"Hugo Kornelis" wrote:

On Wed, 13 May 2009 14:45:02 -0700, Heath wrote:

I have a customer that would like to issue a single SELECT statement that
join two tables: L and R. The results of which need to be one of the
following:

1) If table R has no rows then return all rows in table L
2) If table R has rows, then return the rows in table L that match by ITEM
with table R


Below is my solution to the above requirements. I basically did it with a
LEFT OUTER JOIN and a CASE statement. Is there a more elegent way to do this?

Hi Heath,

Elegance is in the eye of the beholder, of course. But I think I'd use
something like this:

SELECT #L.Item
FROM #L
WHERE EXISTS
(SELECT *
FROM #R
WHERE #L.Item = #R.Item)
OR NOT EXISTS
(SELECT *
FROM #R);

However, I expect that performance might be better if you do use an IF
condition and two seperate SELECT statements. If I were you, I'd test
that on your hardware and your data, and if it turns out to be
significantly faster, you can advise the customer to drop the silly "one
query" requirement.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

.

Relevant Pages

  • Re: Space required for an empty varchar field?
    ... >who don't understand how to deal with NULLS. ... as they'll be in the result set of an outer join. ... it gets unwieldy fast if you have multiplle NULLable columns. ... Thanks Hugo. ...
    (microsoft.public.sqlserver.programming)
  • Re: Difficult SQL Statment
    ... >LEFT OUTER JOIN POOL_LIEFERDAT l ... I'm not sure where dateXY comes from. ... Hugo Kornelis, SQL Server MVP ...
    (comp.databases.ms-sqlserver)
  • Re: Small join issue
    ... >cars say owned by 'Rob', so there is a bit more to it. ... I just tested it (using the outer join without any other ... Best, Hugo ... (Remove _NO_ and _SPAM_ to get my e-mail address) ...
    (microsoft.public.sqlserver.server)
Loading