10 replies to this topic

[Parabola]

Have to create a report, which displays each district Manager, his Name, and the sum of the prices he sells at. Each part we have has 7 price levels they can sell at. they may also raise the price slightly to make commission. the prices are in inventory_pricing, and when ordered high to low, are like this:
p4,p3,p2,p5,p6,p7,p1

Anyways, here is my current query, which works exactly as I need it to. However, as you could imagine, it is slow. It needs to be optimized. Anyone care to try?

select p4.DistrictManager, s.Name, p4.Price4, p3.Price3, p2.Price2, p5.Price5, p6.Price6, p7.Price7, p1.Price1

FROM

   (Select ROUND(SUM(l.Price), 2) AS Price4, h.DistrictManager from inventory_pricing i inner join invoices_salesline l on l.PartNumber = i.PartNumber

   inner join invoices_saleshead h on l.InvoiceNumber = h.InvoiceNumber

   where (l.Price >= i.Price4) group by h.DistrictManager) AS p4

   inner join


   (Select ROUND(SUM(l.Price), 2) AS Price3, h.DistrictManager from inventory_pricing i inner join invoices_salesline l on l.PartNumber = i.PartNumber

   inner join invoices_saleshead h on l.InvoiceNumber = h.InvoiceNumber

   where (l.Price >= i.Price3) and (l.Price < i.Price4) group by h.DistrictManager) AS p3

   on p4.districtmanager = p3.districtmanager inner join


   (Select ROUND(SUM(l.Price), 2) AS Price2, h.DistrictManager from inventory_pricing i inner join invoices_salesline l on l.PartNumber = i.PartNumber

   inner join invoices_saleshead h on l.InvoiceNumber = h.InvoiceNumber

   where (l.Price >= i.Price2) and (l.Price < i.Price3) group by h.DistrictManager) AS p2

   on p3.districtmanager = p2.districtmanager inner join


   (Select ROUND(SUM(l.Price), 2) AS Price5, h.DistrictManager from inventory_pricing i inner join invoices_salesline l on l.PartNumber = i.PartNumber

   inner join invoices_saleshead h on l.InvoiceNumber = h.InvoiceNumber

   where (l.Price >= i.Price5) and (l.Price < i.Price2) group by h.DistrictManager) AS p5

   on p2.districtmanager = p5.districtmanager inner join


   (Select ROUND(SUM(l.Price), 2) AS Price6, h.DistrictManager from inventory_pricing i inner join invoices_salesline l on l.PartNumber = i.PartNumber

   inner join invoices_saleshead h on l.InvoiceNumber = h.InvoiceNumber

   where (l.Price >= i.Price6) and (l.Price < i.Price5) group by h.DistrictManager) AS p6

   on p5.districtmanager = p6.districtmanager inner join


   (Select ROUND(SUM(l.Price), 2) AS Price7, h.DistrictManager from inventory_pricing i inner join invoices_salesline l on l.PartNumber = i.PartNumber

   inner join invoices_saleshead h on l.InvoiceNumber = h.InvoiceNumber

   where (l.Price >= i.Price7) and (l.Price < i.Price6) group by h.DistrictManager) AS p7

   on p6.districtmanager = p7.districtmanager inner join


   (Select ROUND(SUM(l.Price), 2) AS Price1, h.DistrictManager from inventory_pricing i inner join invoices_salesline l on l.PartNumber = i.PartNumber

   inner join invoices_saleshead h on l.InvoiceNumber = h.InvoiceNumber

   where (l.Price >= i.Price1) and (l.Price < i.Price7) group by h.DistrictManager) AS p1

   on p1.districtmanager = p7.districtmanager inner join


sales_districtmanagers s on p7.districtmanager = s.districtmanager

[Parabola]

And now for the fun part.... I have to add a few variables- start date, end date (which is l.InvoiceDate), and whether or not the DM is an actual sales rep, or just a territory (s.Human [logical])


AND THE REAL KICKER
I Have to convert it to SQL!

RAWR!!!!!!!!!!

[Parabola]

Ok, got it into SQL, added the variables, which this will be used in VS2008. But... still wonder if it could be optimized, although now that it is in SQL, it seriously takes about 1 second versus the few minutes it took in mySQL.... glad I could help myself lol.
But seriously- if you have any ideas how to optimize the following, feel free to say so

SELECT     p4.DistrictManager, s.Name, SUM(p4.Price4) AS Price4, SUM(p3.Price3) AS Price3, SUM(p2.Price2) AS Price2, SUM(p5.Price5) AS Price5, 
                      SUM(p6.Price6) AS Price6, SUM(p7.Price7) AS Price7, SUM(p1.Price1) AS Price1
FROM         (SELECT     ROUND(SUM(l.Price), 2) AS Price4, h.DistrictManager
                       FROM          inventory_pricing AS i INNER JOIN
                                              invoices_salesline AS l ON l.PartNumber = i.PartNumber AND i.Price4 <= l.Price INNER JOIN
                                              invoices_saleshead AS h ON l.InvoiceNumber = h.InvoiceNumber
                       WHERE      (l.InvoiceDate BETWEEN @startDate AND @endDate)
                       GROUP BY h.DistrictManager) AS p4 INNER JOIN
                          (SELECT     ROUND(SUM(l.Price), 2) AS Price3, h.DistrictManager
                            FROM          inventory_pricing AS i INNER JOIN
                                                   invoices_salesline AS l ON l.PartNumber = i.PartNumber AND i.Price3 <= l.Price AND i.Price4 > l.Price INNER JOIN
                                                   invoices_saleshead AS h ON l.InvoiceNumber = h.InvoiceNumber
                            WHERE      (l.InvoiceDate BETWEEN @startDate AND @endDate)
                            GROUP BY h.DistrictManager) AS p3 ON p4.DistrictManager = p3.DistrictManager AND p4.Price4 > p3.Price3 AND p4.Price4 > p3.Price3 INNER JOIN
                          (SELECT     ROUND(SUM(l.Price), 2) AS Price2, h.DistrictManager
                            FROM          inventory_pricing AS i INNER JOIN
                                                   invoices_salesline AS l ON l.PartNumber = i.PartNumber AND i.Price2 <= l.Price AND i.Price3 > l.Price INNER JOIN
                                                   invoices_saleshead AS h ON l.InvoiceNumber = h.InvoiceNumber
                            WHERE      (l.InvoiceDate BETWEEN @startDate AND @endDate)
                            GROUP BY h.DistrictManager) AS p2 ON p3.DistrictManager = p2.DistrictManager INNER JOIN
                          (SELECT     ROUND(SUM(l.Price), 2) AS Price5, h.DistrictManager
                            FROM          inventory_pricing AS i INNER JOIN
                                                   invoices_salesline AS l ON l.PartNumber = i.PartNumber AND i.Price5 <= l.Price AND i.Price2 > l.Price INNER JOIN
                                                   invoices_saleshead AS h ON l.InvoiceNumber = h.InvoiceNumber
                            WHERE      (l.InvoiceDate BETWEEN @startDate AND @endDate)
                            GROUP BY h.DistrictManager) AS p5 ON p2.DistrictManager = p5.DistrictManager INNER JOIN
                          (SELECT     ROUND(SUM(l.Price), 2) AS Price6, h.DistrictManager
                            FROM          inventory_pricing AS i INNER JOIN
                                                   invoices_salesline AS l ON l.PartNumber = i.PartNumber AND i.Price6 <= l.Price AND i.Price5 > l.Price INNER JOIN
                                                   invoices_saleshead AS h ON l.InvoiceNumber = h.InvoiceNumber
                            WHERE      (l.InvoiceDate BETWEEN @startDate AND @endDate)
                            GROUP BY h.DistrictManager) AS p6 ON p5.DistrictManager = p6.DistrictManager INNER JOIN
                          (SELECT     ROUND(SUM(l.Price), 2) AS Price7, h.DistrictManager
                            FROM          inventory_pricing AS i INNER JOIN
                                                   invoices_salesline AS l ON l.PartNumber = i.PartNumber AND i.Price7 <= l.Price AND i.Price6 > l.Price INNER JOIN
                                                   invoices_saleshead AS h ON l.InvoiceNumber = h.InvoiceNumber
                            WHERE      (l.InvoiceDate BETWEEN @startDate AND @endDate)
                            GROUP BY h.DistrictManager) AS p7 ON p6.DistrictManager = p7.DistrictManager INNER JOIN
                          (SELECT     ROUND(SUM(l.Price), 2) AS Price1, h.DistrictManager
                            FROM          inventory_pricing AS i INNER JOIN
                                                   invoices_salesline AS l ON l.PartNumber = i.PartNumber AND i.Price1 <= l.Price AND i.Price7 > l.Price INNER JOIN
                                                   invoices_saleshead AS h ON l.InvoiceNumber = h.InvoiceNumber
                            WHERE      (l.InvoiceDate BETWEEN @startDate AND @endDate)
                            GROUP BY h.DistrictManager) AS p1 ON p1.DistrictManager = p7.DistrictManager INNER JOIN
                      sales_districtmanagers AS s ON p7.DistrictManager = s.DistrictManager
WHERE     (s.Human <> @human)
GROUP BY p4.DistrictManager, s.Name
ORDER BY p4.DistrictManager

[BlaineSch]

Holy **** dude!

[djemmers]

Parabola said:

although now that it is in SQL, it seriously takes about 1 second versus the few minutes it took in mySQL.... glad I could help myself lol.

I know this is a bit off topic but...
This I don't understand.
what do you mean?
I thought sql was the language and mySQL the program?
So how can you change the query from mysql to sql?

[Orjan]

djemmers said:

I know this is a bit off topic but...
This I don't understand.
what do you mean?
I thought sql was the language and mySQL the program?
So how can you change the query from mysql to sql?

Well, each server system has their own dialects of SQL, with add ons and sometimes a little bit different way of expressing certain things. the pure statements normally is very similar, but when it comes to other things as calculation functions and so, it starts to differ, or just limitations of results on how many rows to return differs between the different systems.

[djemmers]

ah ok , I was affraid that I missed something crucial and made all my queries slow up till now ;-)

[Parabola]

Basically here's the reason why SQL is better: mySQL is free. You can get mySQL server and all that stuff for free, which is ok for small things, but not large companies. Microsoft SQL however is not free. It costs a bit. But, that also means you get a better running server. And as Orjan explained, 2 different companies means 2 different dialects of the language. For example, c++ was developed by Bjarne Stroustrup starting in 1979 at Bell Labs (courtesy wikipedia), where as c# (of course developed later) was developed by Microsoft within the .NET initiative (courtesy wikipedia). Developed by two different groups, so they are slightly different. I know they aren't that close, but I say they are similar in that... well they are lol.

It's just how programming is... there are dialects of many different languages lol

[relapse]

No way, SQL is the Language and is a standard. MySQL IS the Program as is MS SQL. There are slight function differences for non-standard functions but SQL is suppose to be universal.

[Parabola]

Not entirely. There are different dialects. Take MSSQL for example- this would not be a valid query, but it would in mySql

SELECT a.Name, a.Phone, b.Address, SUM(b.ShipQuantity) as ShipQuantity
FROM customers_billto AS a LEFT JOIN customers_shipto AS b
ON a.CustomerNumber = b.CustomerNumber
WHERE a.Name = 'Sample Customer'
Group By a.Phone

mySql would run it, but MSSQL, being a different version, would tell you that Name and Address are not valid in the select list because they are not contained in either an aggregate function or the GROUP BY clause.