Views / Performance - 2 Rows to 2 Columns

[gateshosting]

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:

SELECT
    [invoice number],
 
    CASE WHEN [Item Number] = 'A' THEN [Price]) END
    AS APRICE,
 
    CASE WHEN [Item Number] = 'B' THEN [Price]) END
    AS BPRICE
FROM
    [Invoice Lineitems]
 
GROUP BY
    [invoice number]
 
 

[almaz]

...
(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:

SELECT
    [invoice number],
 
    AVG(CASE WHEN [Item Number] = 'A' THEN [Price]) END)
    AS APRICE,
 
    AVG(CASE WHEN [Item Number] = 'B' THEN [Price]) END)
    AS BPRICE,
 
    AVG(CASE WHEN [Item Number] = 'C' THEN [Price]) END)
    AS CPRICE
FROM
    [Invoice Lineitems]
 
GROUP BY
    [invoice number]

[gateshosting]

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.

SELECT
    i.[invoice number] AS [Invoice Number],
    [i].[Paid] as [Paid],
 
    SUM([li1].[quantity] * [li1].[Price]) AS TransportPrice,
    SUM([li2].[quantity] * [li2].[Price]) AS InopPrice,
    SUM([li3].[quantity] * [li3].[Price]) AS FeeAdvancePrice
 
FROM
    [Invoice] i LEFT OUTER JOIN
    [Invoice Lineitems] li1 ON [i].[invoice number] = [li1].[invoice number] and [li1].[Item Number] = 'A' LEFT OUTER JOIN
    [Invoice Lineitems] li2 ON [i].[invoice number] = [li2].[invoice number] and [li2].[Item Number] = 'B' LEFT OUTER JOIN
    [Invoice Lineitems] li3 ON [i].[invoice number] = [li3].[invoice number] and [li3].[Item Number] = 'C'
 
GROUP BY
    [i].[invoice number], [i].[Paid]
 

Thanks,

Michael C. Gates

[iburyak]

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.

[gateshosting]

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.

AVG(CASE WHEN [Item Number] = 'A' THEN [Price] END) AS A,
AVG(CASE WHEN [Item Number] = 'B' THEN [Price] END) AS B,
AVG(CASE WHEN [Item Number] = 'C' THEN [Price] END) AS C
 

Best regards,

Michael C. Gates