Optimizing 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 2000 Forums

SQL Server Development (2000)

Optimizing Query?

Author

Topic

phoenix22
Starting Member

20 Posts

Posted - 2006-01-13 : 10:20:14

The query below is taking too long to run, does anyone have any suggestions on how to optimize it? Thanks.

SELECT MIN(DTVAL_DATE), DTVAL_UDF2 FISCAL_YEAR, DTVAL_UDF1 PAY_PERIOD, 'Pay-Period-to-Date' Type,
(SELECT MAX(DTVAL_DATE)
FROM DATE_VALUE
WHERE DTVAL_UDF1 = (SELECT DTVAL_UDF1
FROM DATE_VALUE
WHERE to_date(DTVAL_DATE, 'YYYY-MM-DD') = to_date(sysdate - 30, 'YYYY-MM-DD'))
GROUP BY DTVAL_UDF2, DTVAL_UDF1, 'Pay-Period-to-Date') END_DATE
FROM DATE_VALUE
WHERE DTVAL_UDF1 = (SELECT DTVAL_UDF1
FROM DATE_VALUE
WHERE to_date(DTVAL_DATE, 'YYYY-MM-DD') = to_date(sysdate - 30, 'YYYY-MM-DD'))
GROUP BY DTVAL_UDF2, DTVAL_UDF1, 'Pay-Period-to-Date'

union

SELECT MIN(DTVAL_DATE) - 14 , (
SELECT DTVAL_UDF2 FROM DATE_VALUE
WHERE DTVAL_DATE = (SELECT MIN(DTVAL_DATE) - 14
FROM DATE_VALUE
WHERE to_date(DTVAL_DATE, 'YYYY-MM-DD') = to_date(sysdate - 30, 'YYYY-MM-DD'))
GROUP BY DTVAL_UDF2, DTVAL_UDF1, 'Previous-Pay-Period') FISCAL_YEAR,

(SELECT DTVAL_UDF1 FROM DATE_VALUE
WHERE DTVAL_DATE = (SELECT MIN(DTVAL_DATE) - 14
FROM DATE_VALUE
WHERE to_date(DTVAL_DATE, 'YYYY-MM-DD') = to_date(sysdate - 30, 'YYYY-MM-DD'))
GROUP BY DTVAL_UDF2, DTVAL_UDF1, 'Previous-Pay-Period')PAY_PERIOD, 'Previous-Pay-Period' Type,

(SELECT MAX(DTVAL_DATE) - 14
FROM DATE_VALUE
WHERE DTVAL_UDF1 = (SELECT DTVAL_UDF1
FROM DATE_VALUE
WHERE to_date(DTVAL_DATE, 'YYYY-MM-DD') = to_date(sysdate - 30, 'YYYY-MM-DD')
) GROUP BY DTVAL_UDF2, DTVAL_UDF1, 'Previous-Pay-Period') END_DATE
FROM DATE_VALUE
WHERE DTVAL_UDF1 = (SELECT DTVAL_UDF1
FROM DATE_VALUE
WHERE to_date(DTVAL_DATE, 'YYYY-MM-DD') = to_date(sysdate - 30, 'YYYY-MM-DD'))
GROUP BY DTVAL_UDF2, DTVAL_UDF1, 'Previous-Pay-Period'

Kristen
Test

22859 Posts

Posted - 2006-01-13 : 10:36:18

Is this SQL Server?

Not sure what to_date and sysdate do ...

Kristen

SamC
White Water Yakist

3467 Posts

Posted - 2006-01-13 : 10:37:42

Wow! I'm sure this could be optimized, but first, here's an indented version.

Anyone?


SELECT	MIN(DTVAL_DATE), 
	DTVAL_UDF2 FISCAL_YEAR, 
	DTVAL_UDF1 PAY_PERIOD, 
	'Pay-Period-to-Date' Type,  
	(SELECT	MAX(DTVAL_DATE)
		FROM	DATE_VALUE
		WHERE	DTVAL_UDF1 = (SELECT	DTVAL_UDF1
						FROM DATE_VALUE
						WHERE to_date(DTVAL_DATE, 'YYYY-MM-DD')  = to_date(sysdate - 30, 'YYYY-MM-DD'))
		GROUP BY DTVAL_UDF2, DTVAL_UDF1, 'Pay-Period-to-Date') END_DATE
	FROM DATE_VALUE
	WHERE DTVAL_UDF1 = (SELECT	DTVAL_UDF1
					FROM DATE_VALUE
					WHERE to_date(DTVAL_DATE, 'YYYY-MM-DD')  = to_date(sysdate - 30, 'YYYY-MM-DD'))
	GROUP BY DTVAL_UDF2, DTVAL_UDF1, 'Pay-Period-to-Date'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-13 : 21:37:11

Thanks Sam. Ah ... now it is readable. Next step optimization

-----------------
'KH'
if you can't beat them, have someone else to beat them

SamC
White Water Yakist

3467 Posts

Posted - 2006-01-14 : 14:35:29

The components in red can be replaced with INNER JOINS with a little work.

The query should fly if it can be done.


SELECT	MIN(DTVAL_DATE), 
	DTVAL_UDF2 FISCAL_YEAR, 
	DTVAL_UDF1 PAY_PERIOD, 
	'Pay-Period-to-Date' Type,  
	(SELECT	MAX(DTVAL_DATE)
		FROM	DATE_VALUE
		WHERE	DTVAL_UDF1 = (SELECT	DTVAL_UDF1
						FROM DATE_VALUE
						WHERE to_date(DTVAL_DATE, 'YYYY-MM-DD')  = to_date(sysdate - 30, 'YYYY-MM-DD'))
		GROUP BY DTVAL_UDF2, DTVAL_UDF1, 'Pay-Period-to-Date') END_DATE
	FROM DATE_VALUE
	WHERE DTVAL_UDF1 = (SELECT	DTVAL_UDF1
					FROM DATE_VALUE
					WHERE to_date(DTVAL_DATE, 'YYYY-MM-DD')  = to_date(sysdate - 30, 'YYYY-MM-DD'))
	GROUP BY DTVAL_UDF2, DTVAL_UDF1, 'Pay-Period-to-Date'

SamC
White Water Yakist

3467 Posts

Posted - 2006-01-14 : 14:36:38

quote:
Originally posted by Kristen

Is this SQL Server?

Not sure what to_date and sysdate do ...

Kristen

Also, you never clarified what to_date and sysdate do... hard to guess.

Kristen
Test

22859 Posts

Posted - 2006-01-16 : 05:35:47

I remember now, its Oracle ... so this site may not be a good place to ask the question.

But given that phoenix22 has not returned to nearly all the questions s/he posted here I guess its a dead-duck.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources