October 8, 2009 at 1:51 pm
I have the following SQL Statement that takes 15 seconds to run in QA.
The table has 8 958 073 records and growing.
Ive indexed the following columns:
F_sales_prod.store_id
F_sales_prod.prod-id
F_sales_prod.date_id (clustered index)
SELECT SUM("F_Sales_Prod"."net_amt"), "D_Dates"."fiscal_year", "D_Product"."level1", "D_Dates"."fiscal_month", "D_Store"."Division"
FROM "D_Dates", "D_Product", "F_Sales_Prod", "D_Store"
WHERE ("D_Store"."store_id" = "F_Sales_Prod"."store_id") AND
("D_Product"."Prod_id" = F_Sales_Prod"."prod_id AND
("D_Dates"."date_id" = "F_Sales_Prod"."date_id") AND
( "D_Dates"."fiscal_year" IN (2008, 2009) ) AND
( "D_Product"."level1" = '(5500) FLOOR COVERINGS/DECO' ) AND
( "D_Store"."Division" = 'SW' )
GROUP BY "D_Dates"."fiscal_year", "D_Product"."level1", "D_Dates"."fiscal_month", "D_Store"."Division"
I might be crazy but i think 15 seconds to return 24 out of 8 958 073 rows is a little much.
Is their anyone who might have some suggestions for me?
October 8, 2009 at 2:23 pm
When you say you've indexed those columns, do you mean that you have one index on each column, or one index that has all of them?
Can you provide a create statement for the table, including the indexes? And an insert statement that would give a few rows of sample data?
It would also be helpful if you provide the execution plan for the query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 8, 2009 at 3:13 pm
Look at the code... look at the GROUP BY... it's grouping a sum from one table by the data in 3 other tables. It's skipping the power you built in by adding the indexes. Let's go back to using that power by doing the sum based on those indexes and let the join "reflect" back into that sum to act as a criteria for it. Yes, the sum of the parts is frequently less than the whole when it comes to duration in SQL Server.
I don't have your data to test with but I believe this will do the trick...
WITH
cteSales AS
( --=== Pre-aggregation using only the information from the single table
SELECT SUM(net_amt) AS TotalAmount,
store_id,
prod_id,
date_id
FROM dbo.F_Sales_Prod
GROUP BY store_id, prod_id, date_id
)
SELECT sales.TotalAmount, date.fiscal_year, prod.level1, date.fiscal_month, store.Division
FROM cteSales sales
INNER JOIN dbo.D_Store store ON sales.store_id = store.store_id
INNER JOIN dbo.D_Product prod ON sales.prod_id = prod.prod_id
INNER JOIN dbo.D_Dates date ON sales.date_id = date.date_id
WHERE store.Division = 'SW'
AND prod.level1 = '(5500) FLOOR COVERINGS/DECO'
AND date.fiscal_year IN (2008, 2009)
Just in case the "reflection" from the outer query doesn't work on the CTE (it sometimes doesn't), the other way to take care of this is to take absolute control of the situation and tell SQL Server the order you want things done...
WITH
cteSales AS
( --=== Pre-aggregation using only the information from the single table
SELECT SUM(sp.net_amt) AS TotalAmount,
sp.store_id,
sp.prod_id,
sp.date_id
FROM dbo.F_Sales_Prod sp
WHERE sp.store_id = (SELECT store_id FROM dbo.D_Store WHERE Division = 'SW')
AND sp.prod_id = (SELECT prod_id FROM dbo.D_Product WHERE level1 = '(5500) FLOOR COVERINGS/DECO')
AND sp.date_id IN (SELECT date_id FROM dbo.D_Dates WHERE fiscal_year IN (2008, 2009))
GROUP BY sp.store_id, sp.prod_id, sp.date_id
)
SELECT sales.TotalAmount, date.fiscal_year, prod.level1, date.fiscal_month, store.Division
FROM cteSales sales
INNER JOIN dbo.D_Store store ON sales.store_id = store.store_id
INNER JOIN dbo.D_Product prod ON sales.prod_id = prod.prod_id
INNER JOIN dbo.D_Dates date ON sales.date_id = date.date_id
... and if that STILL doesn't make a major improvement, then my ol' buddy the Temp Table will... but we'll save that until we see what happens with the two items above.
Lemme know...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 8, 2009 at 6:42 pm
Thanks for the reply. I apoligize I should have mentioned one or two things.
The Database is a datawarehouse and therefore I need the 3 other tables (Dimension and Fact Tables) and the SQL is being generated through a 3rd party BI tool.
I think my question should have been what can I do on the SQL Server side of things to maybe increase the retrieval speed.
I guess thinking about it, it comes down to more server specs and I/O and processor.
Do you know of a good site or article that explains how to read the execution plan that QA provides?
THanks for all the help.
October 8, 2009 at 7:16 pm
I think you may get a performance gain if you change your index to include F_Sales_Prod.net_amt
It would become a covering index for this query. (If I read the SQL statement right, had a tough day today)
If you inculde a query plan, it would help as well.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
October 9, 2009 at 9:15 am
JonJon (10/8/2009)
Do you know of a good site or article that explains how to read the execution plan that QA provides?
Red Gate used to have a free ebook "Dissecting SQL Server Execution Plans" by Grant Fritchey. Check their wbe site...
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
November 26, 2009 at 9:24 pm
Hi
You can use @nalytics Performance Free Data Collector for Microsoft SQL Server & Windows
Server to try look for a index and analyze the execution plan , this tool can help you to solve your performance problems, I have tested it works
excellent and it is very easy to configure and implement it.
Regards
@Analytics Peformance - Microsoft SQL Server & Windwos Server Data Collector
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply