SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need help speeding up query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pjyelton
Starting Member

4 Posts

Posted - 09/10/2013 :  18:46:30  Show Profile  Reply with Quote
Hello, pretty new to SQL and need help optimizing this statement as it is VERY slow. Basically what I am doing is getting the monthly counts and percentages from a table of over 1.5 million records. There are four categories that I needs counts from based on a value in a text field. I'm sure I am missing some obvious way to completely rewrite this query but right now it alludes me. Any help would be greatly appreciated!


select distinct MONTH(o.order_create_dt) + YEAR(o.order_create_dt) * 100 as order_id 
	, DATENAME(month, o.order_create_dt) + ' ' + DATENAME(year, o.order_create_dt) as Month_Name
	, convert(decimal(6,2),convert(numeric,(select COUNT(*) from sp_tmp_ifo_adoption i where MONTH(i.order_create_dt) = MONTH(o.order_create_dt) and year(i.order_create_dt) = year(o.order_create_dt) and quote_src_cd = 'IFO_A')) / convert(numeric,(select COUNT(*) from sp_tmp_ifo_adoption i where  MONTH(i.order_create_dt) = MONTH(o.order_create_dt) and year(i.order_create_dt) = year(o.order_create_dt))) * 100.0) as IFO_A_PERCENT
	, convert(decimal(6,2),convert(numeric,(select COUNT(*) from sp_tmp_ifo_adoption i where MONTH(i.order_create_dt) = MONTH(o.order_create_dt) and year(i.order_create_dt) = year(o.order_create_dt) and quote_src_cd = 'IFO_B')) / convert(numeric,(select COUNT(*) from sp_tmp_ifo_adoption i where  MONTH(i.order_create_dt) = MONTH(o.order_create_dt) and year(i.order_create_dt) = year(o.order_create_dt))) * 100.0) as IFO_B_PERCENT
	, convert(decimal(6,2),convert(numeric,(select COUNT(*) from sp_tmp_ifo_adoption i where MONTH(i.order_create_dt) = MONTH(o.order_create_dt) and year(i.order_create_dt) = year(o.order_create_dt) and quote_src_cd = 'IFO_C')) / convert(numeric,(select COUNT(*) from sp_tmp_ifo_adoption i where  MONTH(i.order_create_dt) = MONTH(o.order_create_dt) and year(i.order_create_dt) = year(o.order_create_dt))) * 100.0) as IFO_C_PERCENT
	, convert(decimal(6,2),convert(numeric,(select COUNT(*) from sp_tmp_ifo_adoption i where MONTH(i.order_create_dt) = MONTH(o.order_create_dt) and year(i.order_create_dt) = year(o.order_create_dt) and quote_src_cd = 'IFO_D')) / convert(numeric,(select COUNT(*) from sp_tmp_ifo_adoption i where  MONTH(i.order_create_dt) = MONTH(o.order_create_dt) and year(i.order_create_dt) = year(o.order_create_dt))) * 100.0) as IFO_D_PERCENT
	, (select COUNT(*) from sp_tmp_ifo_adoption i where  MONTH(i.order_create_dt) = MONTH(o.order_create_dt) and year(i.order_create_dt) = year(o.order_create_dt)) as TOTAL_COUNT
        , (select COUNT(*) from sp_tmp_ifo_adoption i where MONTH(i.order_create_dt) = MONTH(o.order_create_dt) and year(i.order_create_dt) = year(o.order_create_dt) and quote_src_cd = 'IFO_A') as IFO_A_COUNT
        , (select COUNT(*) from sp_tmp_ifo_adoption i where MONTH(i.order_create_dt) = MONTH(o.order_create_dt) and year(i.order_create_dt) = year(o.order_create_dt) and quote_src_cd = 'IFO_B') as IFO_B_COUNT
        , (select COUNT(*) from sp_tmp_ifo_adoption i where MONTH(i.order_create_dt) = MONTH(o.order_create_dt) and year(i.order_create_dt) = year(o.order_create_dt) and quote_src_cd = 'IFO_C') as IFO_C_COUNT
        , (select COUNT(*) from sp_tmp_ifo_adoption i where MONTH(i.order_create_dt) = MONTH(o.order_create_dt) and year(i.order_create_dt) = year(o.order_create_dt) and quote_src_cd = 'IFO_D') as IFO_D_COUNT
	from sp_tmp_ifo_adoption o

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1911 Posts

Posted - 09/11/2013 :  01:31:59  Show Profile  Visit jackv's Homepage  Reply with Quote
There are some areas to investigate:
1) use of DISTINCT. Consider alternative -
2)the select (count(*) statements .
3)The use of functions on the WHERE

These are costly steps .Have you looked at the execution plan? There are usually some good clues
Have you considered use a common table expression (CTE) and creating an INNER JOIN to the CTE


Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
283 Posts

Posted - 09/11/2013 :  08:00:07  Show Profile  Reply with Quote
Think about using a cte to get your counts.

What data type is "order_create_dt"? Why not just i.order_create_dt = o.order_create_dt? (I now see you are only doing month/year)

djj

Edited by - djj55 on 09/11/2013 10:11:52
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 09/11/2013 :  08:23:11  Show Profile  Reply with Quote
Can you try if this does it any better? I'm a little rusty on the PIVOT syntax and I just created it on the fly so there might be some tweaking to do:
WITH cte AS (
	SELECT 
		YearNr = YEAR(order_create_dt),
		MonthNr = MONTH(order_create_dt)
		quote_src_cd,
		IFOCount = COUNT(*)
	FROM sp_tmp_ifo_adoption o
	WHERE quote_src_cd IN ('IFO_A', 'IFO_B', 'IFO_C', 'IFO_D')
	GROUP BY 
		YEAR(order_create_dt),
		MONTH(order_create_dt)
		quote_src_cd
	)
SELECT
	YearNr,
	MonthNr,
	IFO_A_Percent = ([IFO_A]*1.0) / ([IFO_A] + [IFO_B] + [IFO_C] + [IFO_D]),
	IFO_B_Percent = ([IFO_B]*1.0) / ([IFO_A] + [IFO_B] + [IFO_C] + [IFO_D]),
	IFO_C_Percent = ([IFO_C]*1.0) / ([IFO_A] + [IFO_B] + [IFO_C] + [IFO_D]),
	IFO_D_Percent = ([IFO_D]*1.0) / ([IFO_A] + [IFO_B] + [IFO_C] + [IFO_D]),
	IFO_A_Count = [IFO_A], 
	IFO_B_Count = [IFO_B], 
	IFO_C_Count = [IFO_C], 
	IFO_D_Count = [IFO_D],
FROM cte PIVOT (
	SUM(IFOCount) FOR quote_src_cd IN ([IFO_A], [IFO_B], [IFO_C], [IFO_D])
	) AS pivottable
ORDER BY YearNr, MonthNr


- Lumbago
My blog-> http://thefirstsql.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000