I posted a topic just recently but it seems like I gave a bad explanation of my problem. I'll try again :-)I'm creating a webbased report that print all costs of a number o0f vehicles 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 for each vehicle. 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 DescriptionABC 123 Org1 2342 Fuel Chevron 4/12/04 12.34 RegularABC 123 Org1 2342 Fuel Chevron 6/12/04 23.45 RegularABC 123 Org1 53422 Fuel Exxon 7/12/04 34.23 Regular
I want to create a stored procedure that gives the output:Month Company MonthCostDecember Chevron 35.79December Exxon 34.23
This can be done like this:SELECT DATENAME(month, DateofPurchase) AS 'Month', Company, SUM(Cost) AS 'MonthCost'FROM Invoices WHERE DateOfPurchase >= @date_from AND DateOfPurchase <= @date_toAND TypeOfCost = 'Fuel'AND LicenseNo = @license_noGROUP BY DATEPART(month, DateofPurchase), Company
However this is what gives the bad performance, anyone who can help me?
Performance problem
Posted - 2005-01-12 : 10:15:40
