Rewrite /Optimize 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)

Rewrite /Optimize query

Author

Topic

snipered
Starting Member

9 Posts

Posted - 2011-12-09 : 11:23:01

Hi, my query takes 1m 4sec to complete. I am going to put a frontend on this, so 1m 4sec is way too long. Is there a better way to write this query? I wrote this yesterday.


SELECT dt.pCBUDescription, 
		[Monthly Comp]=sum(dt.[Month Comp]),
		[Month Quality Comp] = sum(dt.[Month Quality Comp]),
		[Cumulative Comp] = sum(dt.[Cumulative Comp]),
		[Cumulative  Quality Comp] = sum(dt.[Cumulative  Quality Comp]),
		[PY Comp] = sum(dt.[PY Comp]), 
		[PY Cumulative Comp] = SUM(dt.[PY Cumulative Comp]),
		[YTD Variance] = round((SUM(dt.[Cumulative Comp]) * 100) / (SUM(dt.[PY Cumulative Comp]) - 100), 0),
		[CPM Month Comp] = SUM(dt.[CPM Month Comp]),
		[CPM Quality Comp] = SUM(dt.[CPM Quality Comp]),
		[CPM Cumulative Comp] = SUM(dt.[CPM Cumulative Comp]),
		[CPM Cumulative Quality] = SUM(dt.[CPM Cumulative Quality]),
		[CPM PY Comp] = SUM(dt.[PY Comp])
FROM
(
SELECT		C.pCBUDescription, A.CSNCode,
			[Month Comp] = dbo.ufn_fcKF1(A.CSNCode, 12,2011),
			[Month Quality Comp] = dbo.ufn_fcKF2(A.CSNCode, 12, 2011),
			[Cumulative Comp] = dbo.ufn_fcKF3(A.CSNCode, 12, 2011),
			[Cumulative  Quality Comp] = dbo.ufn_fcKF4(A.CSNCode, 12, 2011),
			[PY Comp] = dbo.ufn_fcKF5(A.CSNCode, 12, 2011),
			[PY Cumulative Comp] = dbo.ufn_fcKF6(A.CSNCode, 12, 2011),
			[CPM Month Comp] = (dbo.ufn_fcKF1(A.CSNCode, 12, 2011)) / (dbo.ufn_fcKF8(A.CSNCode, 12, 2011)) * 1000000,		
			[CPM Quality Comp] = (dbo.ufn_fcKF2(A.CSNCode, 12, 2011)/ NullIf(dbo.ufn_fcKF8(A.CSNCode, 12,2011), 0)) * 1000000,
			[CPM Cumulative Comp] = (dbo.ufn_fcKF3(A.CSNCode, 12, 2011) / Nullif(dbo.ufn_fcKF9(A.CSNCode, 12, 2011),0)) * 1000000,
			[CPM Cumulative Quality] = (dbo.ufn_fcKF4(A.CSNCode, 12, 2011) / Nullif(dbo.ufn_fcKF9(A.CSNCode, 12, 2011), 0)) * 1000000,
			[CPM PY Comp] = (dbo.ufn_fcKF5(A.CSNCode, 12, 2011) / NullIf(dbo.ufn_fcKF5(A.CSNCode, 12, 2011),0)) * 1000000 --,
FROM		dbo.t_Prm_Fct_Comp A
INNER JOIN	dbo.t_Prm_Dim_Product C ON C.pProductCode = A.CSNCode
) dt
GROUP BY dt.pCBUDescription

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-12-09 : 11:33:50

It looks to be more likely an issue of your indexes rather than your coding. Take a look at the execution plan to see how it is accessing the data. In particular, see if it is performing table scans or lookups.

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

Kristen
Test

22859 Posts

Posted - 2011-12-10 : 07:11:31

What does the dbo.ufn_fcKFxxx functions do? Processing every value through those may be slowing things down.

snipered
Starting Member

9 Posts

Posted - 2011-12-10 : 17:18:33

Well, i inserted an index as per suggested from the optimizer.

I've reduced the time from 1m 4sec to 31sec. I need to try to reduce it more.

The functions are there because there will be 33 reports and i don't want to have to rewrite the same calculation all over.

Is there any more suggestions to reduce the time further?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-10 : 17:23:32

Show us the execution plan. We need it to help you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-11 : 07:02:17

I would honestly consider trying to remove the user defined functions. If they're doing data access, they're likely slow.
http://sqlinthewild.co.za/index.php/2009/04/29/functions-io-statistics-and-the-execution-plan/

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2011-12-11 : 16:19:52

quote:
Originally posted by snipered

The functions are there because there will be 33 reports and i don't want to have to rewrite the same calculation all over.

In-line them in this report as a test. If it makes no difference then you are fine for the rest of the reports, and can look elsewhere for performance enhancements.

If performance is improved like night-and-day then you will know that that use of Functions comes at a price ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources