By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,759 Members | 959 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,759 IT Pros & Developers. It's quick & easy.

Views / Performance - 2 Rows to 2 Columns

gateshosting
P: 25
Good day,

I am programming my Stored Procedures and Views for performance, since we are dealing with millions of records. I know this question is going to sound like I do NOT want to do it this way, but in this case, it may work...


I have an Invoice LineItems table which will only ever contain up to 3 items. It will NEVER contain any more than that. We only deal with 3 items/services, and it will never change. So let's call them A, B, and C.

I have two options and I need to know if number (1) is doable, and/or if it will be faster, and more efficient to do (2).

(1)
Create a view that has 4 columns: Invoice Number, Product A Price, Product B Price, and Product C Price. I want to do it this way because it will work perfectly for a Stored Procedure I wrote which is running very quickly and does exactly what I need...

(2)
Create 3 views that get Product A, Product B Price, and Product C Price, and then just join them in another view. Is this going to be too much overhead? I mean, it will be running 4 views essentially... What do you think?

Here is something I wrote hoping it would work... but no go. The result was not what was expected:


Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     [invoice number],
  3.  
  4.     CASE WHEN [Item Number] = 'A' THEN [Price]) END
  5.     AS APRICE,
  6.  
  7.     CASE WHEN [Item Number] = 'B' THEN [Price]) END
  8.     AS BPRICE
  9. FROM
  10.     [Invoice Lineitems]
  11.  
  12. GROUP BY
  13.     [invoice number]
  14.  
  15.  
Dec 6 '06 #1
Share this Question
Share on Google+
4 Replies


almaz
Expert 100+
P: 168
...
(1)
Create a view that has 4 columns: Invoice Number, Product A Price, Product B Price, and Product C Price. I want to do it this way because it will work perfectly for a Stored Procedure I wrote which is running very quickly and does exactly what I need...

(2)
Create 3 views that get Product A, Product B Price, and Product C Price, and then just join them in another view. Is this going to be too much overhead? I mean, it will be running 4 views essentially... What do you think?
...
Looking at your code: you've almost done your task, just need to make a final step. You know that there will be only one record per Item Number for each invoice number, but SQL Server doesn't. So you have to specify what it should do with potentially multiple values:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     [invoice number],
  3.  
  4.     AVG(CASE WHEN [Item Number] = 'A' THEN [Price]) END)
  5.     AS APRICE,
  6.  
  7.     AVG(CASE WHEN [Item Number] = 'B' THEN [Price]) END)
  8.     AS BPRICE,
  9.  
  10.     AVG(CASE WHEN [Item Number] = 'C' THEN [Price]) END)
  11.     AS CPRICE
  12. FROM
  13.     [Invoice Lineitems]
  14.  
  15. GROUP BY
  16.     [invoice number]
Dec 6 '06 #2

gateshosting
P: 25
Awesome... I will give it a try.

Do you think that script will perform better than joining the line items table three times to create the values? I did a script that works, but think (logically) that it will be too much overhead... but maybe not. I don't know much about specifics of performance in SQL Server.
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     i.[invoice number] AS [Invoice Number],
  3.     [i].[Paid] as [Paid],
  4.  
  5.     SUM([li1].[quantity] * [li1].[Price]) AS TransportPrice,
  6.     SUM([li2].[quantity] * [li2].[Price]) AS InopPrice,
  7.     SUM([li3].[quantity] * [li3].[Price]) AS FeeAdvancePrice
  8.  
  9. FROM
  10.     [Invoice] i LEFT OUTER JOIN
  11.     [Invoice Lineitems] li1 ON [i].[invoice number] = [li1].[invoice number] and [li1].[Item Number] = 'A' LEFT OUTER JOIN
  12.     [Invoice Lineitems] li2 ON [i].[invoice number] = [li2].[invoice number] and [li2].[Item Number] = 'B' LEFT OUTER JOIN
  13.     [Invoice Lineitems] li3 ON [i].[invoice number] = [li3].[invoice number] and [li3].[Item Number] = 'C'
  14.  
  15. GROUP BY
  16.     [i].[invoice number], [i].[Paid]
  17.  
Thanks,

Michael C. Gates
Dec 6 '06 #3

iburyak
Expert 100+
P: 1,017
Just a note:
When you do case in select statement search goes once through the table using indexes if possible.

If you have 3 separate views it goes 3 times through the same table and then you join all of them which is work on the background where each view doesn't have an index I assume.

You have to check a showplan to be sure how it works.
Dec 6 '06 #4

gateshosting
P: 25
Just FYI for anyone reading... I ran tests:

1. Joining the same table three times, to get the 3 different products was a little slower than the script almaz gave me.
Expand|Select|Wrap|Line Numbers
  1. AVG(CASE WHEN [Item Number] = 'A' THEN [Price] END) AS A,
  2. AVG(CASE WHEN [Item Number] = 'B' THEN [Price] END) AS B,
  3. AVG(CASE WHEN [Item Number] = 'C' THEN [Price] END) AS C
  4.  
Best regards,

Michael C. Gates
Dec 7 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.