July 17, 2012 at 9:03 am
First of all, I am fairly new to SQL as you will be able to tell. What kind of things could I possibly do to speed up a query like this? There are millions of rows and I just can't seem to get the execution time down no matter what I try.
SELECT FKStoreId,
SUM(CASE FKCategoryId WHEN 210 THEN Price ELSE 0 END) AS SUM1,
SUM(CASE FKCategoryId WHEN 180 THEN Price ELSE 0 END) + SUM(CASE FKCategoryId WHEN 190 THEN Price ELSE 0 END) AS SUM2, SUM(CASE FKCategoryId WHEN 107 THEN Price ELSE 0 END) AS SUM3
FROM dpvHstGndItem
WHERE (DateOfBusiness BETWEEN @StartDate AND @EndDate)
GROUP BY FKStoreId
ORDER BY FKStoreId
July 17, 2012 at 9:48 am
pistolpete (7/17/2012)
First of all, I am fairly new to SQL as you will be able to tell. What kind of things could I possibly do to speed up a query like this? There are millions of rows and I just can't seem to get the execution time down no matter what I try.SELECT FKStoreId,
SUM(CASE FKCategoryId WHEN 210 THEN Price ELSE 0 END) AS SUM1,
SUM(CASE FKCategoryId WHEN 180 THEN Price ELSE 0 END) + SUM(CASE FKCategoryId WHEN 190 THEN Price ELSE 0 END) AS SUM2, SUM(CASE FKCategoryId WHEN 107 THEN Price ELSE 0 END) AS SUM3
FROM dpvHstGndItem
WHERE (DateOfBusiness BETWEEN @StartDate AND @EndDate)
GROUP BY FKStoreId
ORDER BY FKStoreId
No idea how your tables, indexes and data look like but if you're interested in only those FKCategoryIds then why don't you change your where clause to this?
WHERE ( DateOfBusiness BETWEEN @StartDate AND @EndDate )
AND FKCategoryId IN ( 210, 180, 190, 107 )
July 17, 2012 at 10:37 am
Check out Gail's article on how to post performance problems.
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 17, 2012 at 1:45 pm
Thanks guys. I attached my execution plan. The table I am trying to query is very large (150 Million rows). There is a clustered index on FKStoreId and DateofBusiness. It's currently taking around 7 minutes to return data for YTD. I have exhausted my limited knowledge at this point. No matter what I do seems to speed it up.
July 17, 2012 at 1:51 pm
pistolpete (7/17/2012)
Thanks guys. I attached my execution plan. The table I am trying to query is very large (150 Million rows). There is a clustered index on FKStoreId and DateofBusiness. It's currently taking around 7 minutes to return data for YTD. I have exhausted my limited knowledge at this point. No matter what I do seems to speed it up.
Go back and read that article again. You need to post the actual execution plan not an excel spreadsheet. Also, most people around here won't open zip files and/or office files.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 17, 2012 at 1:55 pm
Unless you're using SQL 2000, please post the xml plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 17, 2012 at 1:57 pm
I did read the article and it that's how it says to post the execution plan with 2000.
July 17, 2012 at 2:01 pm
If I recall, the article also says to post index and table definitions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 17, 2012 at 2:27 pm
I apologize guys. Thank you for being patient. I have updated my Excel with the table def and index info.
July 17, 2012 at 3:16 pm
I was hoping you'd just post it so I wouldn't have to download, virus scan then open a file. No matter.
Clustered index is on the Date column and then the StoreID (so first filter and then group/order column), honestly that query's just about as efficient as it's going to get.
If you can rewrite it as Clayman suggested, we might be able to get it slightly more optimal, but other than that, there's not much that can be done.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 17, 2012 at 4:54 pm
Guessing from a previous post that this is SQL Server 2000, correct?
July 17, 2012 at 4:56 pm
Not knowing anything more about the table, it looks like this query as written in the original post would benefit from a filtered covering index in SQL Server 2008.
July 17, 2012 at 8:22 pm
It is SQL 2000.
July 17, 2012 at 10:27 pm
Actually, your clustered index isn't on FKStoreId and DateOfBusiness. It's on DateOfBusiness and FKStoreId.
I'd first add the criteria that "clayman" suggested.
Your clustered index also doesn't appear to be unique which mean you have an 8 byte row uniquefier added in the clustered index. That could hurt.
You're also doing an Order by on a column that you really don't have an index on. The clustered index on DateOfBusiness and FKStoreId (In that order) probably isn't helping there but I don't know for sure because a lot of things where cut short in your spreadsheet. Yeah... I know... ORDER BY is supposed to be working on the result but try the query without it and see if it makes a difference.
Last but not least, think "Divide'n'Conquer". Like Lynn suggested, we can't see what you see but, on such a large table, I'd try splitting things up a bit. You probably don't have a huge number of stores. Try just doing the SUMS with the FKStoreID as the GROUP BY (like it is now) without any addition between them and dump that much smaller result to a Temp Table. Then, run a query against the temp table to add the columns you want together and do the final sort-for-display on that instead of in the same query as the GROUP BY.
Once that's done, take a look at the execution plan and see if adding an index would actually help. I'm thinking that none of the indexes will currently help the criteria you need to add as suggested by "clayman".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply