Query optimization advice for custom 12-column sales detail report

Create a stored procedure - this will (usually) save you the query compilation time and is generally the biggest boost you are going to get.

You could add an index on [Transaction].[Time]. If MS ever changes the schema, they would end up dropping your index, so make sure you keep the script to rebuild it.

I don't have a large enough sample dataset to really test this - your original code ran in ~1 second on my test database, and after mucking around with it a bit it ran in ~0 seconds - but the execution plan was almost the same, so I don't really think you would see any improvement. Here's the query I used anyway - It's ugly, I was just sort of hacking around with it...

select i.description, i.id, i.price,i.cost,s.reordernumber, sum(a.Quantity), sum(a.Dollars) FROM SupplierList s INNER JOIN Item i ON s.ItemID = i.ID and s.supplierID = 3 INNER JOIN ( SELECT te.itemID as ItemID, (te.Quantity) AS Quantity, (te.Quantity * te.Price) AS Dollars, t.time FROM TransactionEntry te INNER JOIN [Transaction] t on t.TransactionNumber te.TransactionNumber WHERE t.Time BETWEEN '2004-02-01' AND '2004-03-01' ) a ON s.ItemID = a.ItemID group by i.description, i.id, i.price,i.cost,s.reordernumber

Glenn Adams Tiber Creek C> Hey all,

Thanks for the suggestion. I haven't yet tried stored procedures. I was able to get it down to 7 seconds though (this is querying on 4 million records, 10 years of data).

Here is what I did. Like I said, a stored procedure may be faster and my knowledge of MSSQL is still intermediate.

Added indexes: - Time ASC in [Transaction] - ReorderNumber ASC in SupplierList (This particular index really lowered the speed)

Created a view (copied from script generator:

CREATE VIEW [dbo].[reporting] WITH SCHEMABINDING AS SELECT i.Description, i.ItemLookupCode, i.Price, i.Cost, i.SupplierID, s.ReorderNumber, te.ID, te.ItemID, te.Quantity, (te.Quantity*te.Price) AS Dollars, t.Time FROM dbo.Item i INNER JOIN dbo.TransactionEntry AS te ON i.ID = te.ItemID INNER JOIN dbo.[Transaction] AS t ON te.TransactionNumber t.TransactionNumber INNER JOIN dbo.SupplierList AS s ON i.SupplierID s.SupplierID AND i.ID=s.ItemID

I could even take it one step further and add indexes to the view, but due to some data imperfections in our item database, this is a tad difficult.

My main issue now is that I am running 12 queries (12 periods) for every single item found. The query takes less than a 100th of a second, but it is getting snagged somewhere I think.

I am benchmarking based on a popular vendor of ours and comparing it to a similar report that we had written in DataFlex (we have been using it for years...). DataFlex runs the same report in a minute and 25 seconds. I'm down to a minute and 50 seconds.

I am very excited about this report. I'll try the stored procedure and see if I can reduce the time even further. Let me know if anybody has any other suggestions.

-- Alex

You know that in this version you are getting only the Primary supplier for each item - or only items where the selected supplier is the primary, right?

A stored procedure can return multiple recordsets - call one sp and get all of your result sets with a single round trip - the query execution speed will probably be about the same, but you should see some inprovement in overall response time.

You also may be able to get multiple time periods into a single query - I'd have to think about it a lot more... Look up cross joins or cross tabs in SQL Books OnLine...

Glenn Adams Tiber Creek C> Thanks for the suggestion. I haven't yet tried stored procedures. I was

Something else you might like to try is defragging the database. I used Visual Defrag, which comes as a 30 day eval :) Makes it simpler.

Defrag the actual disk (a w> Thanks for the suggestion. I haven't yet tried stored procedures. I was

Glenn,

Your idea to use a stored procedure was BRILLIANT. I made a stored procedure for the small query that populates every period and it cut the run time down to 20 seconds for 12 periods of data. It runs 4,000 queries in about 9 seconds (queries that are running against 4,000,000 records mind you).

Again, thanks for your help!

-- Alex