Subquery inefficiency - anyone help?

Question

Post reply

Subquery inefficiency - anyone help?

GloriousRuins

Right there with Babe

Points: 730
More actions
September 12, 2007 at 5:06 am

#180957

Hi all,
Having one of those mornings where I could use some fresh perspective on something. I have a View which is laid out as follows:
DATE         NAME         QTY
10/9/07      Item A        12
10/9/07      Item B        7
10/9/07      Item C        192
11/9/07      Item A        13
11/9/07      Item B        2
11/9/07      Item C        1
12/9/07      Item A        162
12/9/07      Item B        13
12/9/07      Item C        27
What I need is an alternative view which looks like:
DATE        Item A        Item B         Item C
10/9/7         12            7             192
11/9/7         13            2              1
12/9/7        162           13             27
(Note: there will be Item D, E, F etc. as well - I have 15 or so categories to include in this)
My code reads something like this:
SELECT DISTINCT Date,
       (SELECT Qty FROM View AS View1 WHERE ([Name] = 'Item A') AND(View.Date = Date)) AS ItemA,
       (SELECT Qty FROM View AS View1 WHERE ([Name] = 'Item B') AND(View.Date = Date)) AS ItemB
FROM View
However, as I add more Items (C,D,E,F etc.) to the list it slows and eventually just times out. Anything I can do to make it more efficient? All suggestions much appreciated!
Scott

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

Are you using SQL 2005?

If so try using a pivot

If you not sure how to do that give me a shout.

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

GloriousRuins Right there with Babe Points: 730 More actions · Answer 2

Yes, using 2005 - anything you can suggest would be great!

Thanks,

Scott

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

HI Scott,

Is what I used to recreate what you are trying to do.

You'll see where you need to Add any other Coloumns such as [Item D] etc

CREATE TABLE SubQuery

(

[DATE] DATETIME

,[NAME] VARCHAR(100)

,[QTY] INT

)

INSERT INTO SubQuery

SELECT '2007-09-10','Item A', 12

UNION

SELECT'2007-09-10','Item B', 7

UNION

SELECT'2007-09-10','Item C', 192

UNION

SELECT'2007-09-11','Item A', 13

UNION

SELECT'2007-09-11','Item B', 2

UNION

SELECT'2007-09-11','Item C', 1

UNION

SELECT'2007-09-12','Item A', 162

UNION

SELECT'2007-09-12','Item B', 13

UNION

SELECT'2007-09-12','Item C', 27

SELECT

[DATE]

,[Item A]

,[Item B]

,[Item C]

FROM

(

SELECT

[DATE]

,[Name]

,[QTY]

FROM SubQuery

) p

PIVOT(SUM([QTY])

FOR [NAME] in ( [Item A],[Item B],[Item C])

) as pv

THanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

GloriousRuins Right there with Babe Points: 730 More actions · Answer 4

Hi Chris,

Did a bit of digging into this - it looks like it would have been perfect for what I needed, but it kept falling over with syntax errors. Problem is my DB is set to SQL Server 2000 Compatibility mode. Am I going to have to write a series of CASE statements to replicate what PIVOT could do?

Scott

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

Hi there,

As far as I remember case statements are the only way in SQL2000

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

This should do it nicely... classic cross-tab... you might be able to use SQL Server 2005 PIVOT as well... either way, it'll solve your slowdown because each row is only touched once...

SELECT [DATE],

MAX(CASE WHEN [Name] = 'Item A' THEN QTY ELSE 0 END) AS ItemA,

MAX(CASE WHEN [Name] = 'Item B' THEN QTY ELSE 0 END) AS ItemB,

MAX(CASE WHEN [Name] = 'Item C' THEN QTY ELSE 0 END) AS ItemC

FROM yourview

GROUP BY [DATE]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

GloriousRuins Right there with Babe Points: 730 More actions · Answer 7

Thanks Guys,

Working on the CASE statement already, and it's absolutely flying through them. Cheers for the help!

Scott

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Glad it helped...

Any chance of you posting your final code?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

GloriousRuins Right there with Babe Points: 730 More actions · Answer 9

Exactly as you posted it earlier, albeit with the correct names inserted in place of Item A, B etc. Works like a charm.

As an aside, the code using PIVOT also works perfectly on a seperate DB I have which is 2005-only, so I guess the "2000 compatibility" on a 2005 DB does cause an issue with PIVOT. Shame, as it's a nice little tool which I'll keep in mind for future use.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

Perfect... thanks for the feedback, Scott.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions