Performance problems

[Rauken]

I'm creating a webbased report that print all costs of a vehicle during a specific period of time, usually a month.

I've created a view called Invoices that lists all costs of all vehicles in the system (detail rows). Then I have a stored procedure that query that view with a number of parameters, nothing strange. I loop the result and print it to the webpage. The report can be up to 100 vehicles and is printed in just seconds. My customer thought the report was too large and wanted to have the fuel/gas costs grouped per month and gas company. My first thought was to run a stored procedure on each vehicle and retreive that data. Perfomance seems to be terrible doing that and I've tried differents ways to come around this.

The result of my invoice view looks like this:



LicenseNo Organisation InvoiceNo TypeOfCost Company DateOfPurchase Cost Description
ABC 123 Org1 2342 Fuel Chevron 4/12/04 12.34 Regular
ABC 123 Org1 2342 Fuel Chevron 6/12/04 23.45 Regular
ABC 123 Org1 53422 Fuel Exxon 7/12/04 34.23 Regular



I want to create a stored procedure that gives the output:



Month Company MonthCost
December Chevron 35.79
December Exxon 34.23



This can be done like this:



SELECT DATEPART(month, DateofPurchase) AS 'Month',
Company,
SUM(Cost) AS 'MonthCost'

FROM Invoices

WHERE DateOfPurchase >= @date_from AND DateOfPurchase <= @date_to
AND TypeOfCost = 'Fuel'
AND LicenseNo = @license_no
GROUP BY DATEPART(month, DateofPurchase), Company



However this is what gives the bad performance. I'm thinking of creating a second view based on Invoices that has already calculated the sum of each vechicle per month and company but I need to have DateOfPurchase to be able to enter search critera and it ruins the Group by clause, anyone who can help me?

 

[vongrunt]

My first thought was to run a stored procedure on each vehicle and retreive that data.
...
This can be done like this:

If you are running this code for each vehicle (LicenseNo?), this is probably main reason for bad performance. Try to get summary for all vehicles with a single query.

If that doesn't help, next suspect on list is view. Quite often reused views lead to poor performance.

 

[Rauken]

It only run once per licenseno but if I leave all criteria blank except for dates it will probably run 2500 times (2000 vehicles) and it seems to stop after a while.

I didn't know that often reused views leads to bad performance, I thought it was the opposite! What should I use instead?

I created a view with all the records for that reason, not needing to query each vehicle. But to get this fuel grouping per vehicle I don't see any way around it.

 

[Rauken]

Correction it should be 2500 vehicles

 

[SQLSister]

UNless you are using Enterprise Edition and have indexed the view, I would never use a view and then run an sp which filters the view. This is very inefficient as SQL Server first has to retrun the whole view and then do the filtering. If you do all the work in the sp, it will run much faster.

Vongrunt is right, this statment should be run once for all the vehicles involved. YOu should think in terms of opperating on sets of data rather tahn looping through data. If you want just totals try:

SELECT     DATEPART(month, DateofPurchase) AS 'Month',
    Company,
    SUM(Cost) AS 'MonthCost'

FROM     Invoices 

WHERE     DateOfPurchase >= @date_from AND DateOfPurchase <= @date_to
AND    TypeOfCost = 'Fuel'
GROUP BY DATEPART(month, DateofPurchase), Company

If you want it by liscense no. then try:

SELECT     LicenseNo, DATEPART(month, DateofPurchase) AS 'Month',
    Company,
    SUM(Cost) AS 'MonthCost'

FROM     Invoices 

WHERE     DateOfPurchase >= @date_from AND DateOfPurchase <= @date_to
AND    TypeOfCost = 'Fuel'

GROUP BY LicenseNo, DATEPART(month, DateofPurchase), Company

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[Rauken]

Thanks for the help! I rewrote the stored proc to go straight against the tables and it boosted the performance quite a bit.

I'd love more than anything else to have just one stored proc that gave me the result but the report is quite complicated with lots of data. On each vehicle the customer wants both detailed info and grouped (fuel) info.

I really don't know when Views should be used.

 

[SQLSister]

Unless you have indexed views, I would pretty much never use them. Sometimes people use them to restrict users to a set of data, but you will pay a performance hit for this without the indexes.

You can return multiple recordsets from an SP. But referring to them is a problem for the user interface. So I can't help you there. I know we do it all the time, but I have no idea how our .Net programmers refer to them in the webpages.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[Rauken]

Ah this an old project still using the old horrible asp-vbscript. It would definately be easier and a lot "cleaner" in .net.

 

[vongrunt]

Both languages have while/if statements and SQL is kinda always the same, correct? .

FYI ADO/VBscript method used for catching multiple recordsets from SP is RecordsetObj.NextRecordset()