Need help speeding up query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help speeding up query

Author

Topic

pjyelton
Starting Member

4 Posts

Posted - 2013-09-10 : 18:46:30

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
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-09-11 : 01:31:59

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

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-09-11 : 08:00:07

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

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2013-09-11 : 08:23:11

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources