need help for this select statement [faster query]

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 2005 Forums

Transact-SQL (2005)

need help for this select statement [faster query]

Author

Topic

ajoebs03
Starting Member

3 Posts

Posted - 2009-08-06 : 20:11:18

Hi! Can anyone help me optimize this SELECT statement?

SELECT [ID], CODE, CUST_TEL, ACTION_NO, DATE_TIME, CUST_INPUT, IVRS_RESPONSE, LOCAL_NO, PORT_NO, AUTOER
FROM IVRS_CALL_LOG
WHERE DATE_TIME BETWEEN '07/02/08 07:58:07.000' AND '07/02/08 11:25:06.000' AND ACTION_NO > 0
AND ID in (SELECT TOP 1 [id]-3 FROM IVRS_CALL_LOG
WHERE (DATE_TIME > '07/02/08 07:58:07.000') AND (DATE_TIME < '07/02/08 11:25:06.000') AND CODE = '100000640740'
UNION ALL
SELECT TOP 1 [id]-2 FROM IVRS_CALL_LOG
WHERE (DATE_TIME > '07/02/08 07:58:07.000') AND (DATE_TIME < '07/02/08 11:25:06.000') AND CODE = '100000640740'
UNION ALL
SELECT TOP 1 [id]-1 FROM IVRS_CALL_LOG
WHERE (DATE_TIME > '07/02/08 07:58:07.000') AND (DATE_TIME < '07/02/08 11:25:06.000') AND CODE = '100000640740'
UNION ALL
SELECT [id] FROM IVRS_CALL_LOG
WHERE (DATE_TIME > '07/02/08 07:58:07.000') AND (DATE_TIME < '07/02/08 11:25:06.000') AND CODE = '100000640740')
ORDER BY DATE_TIME, ACTION_NO

Currently if I run this statement, it takes about 2 minutes and 28 seconds to retrieve the data.

Thanks.

Sachin.Nand

2937 Posts

Posted - 2009-08-07 : 02:05:43

I think it is better you put the whole query with union clause in a derived table & add the filter where condition to the derived table.
Something like this
select * from
(
select col1,date from tbl1
union all
select col2,date from tbl2
)A where date>Getdate()

PBUH

ramya.he
Starting Member

2 Posts

Posted - 2009-08-07 : 03:03:55

I think so you improve the performance of your query by creating and Indexed view on the table. Try this option as many suggest the same. Or send me the table structure and i will send you the Indexed view.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-07 : 03:14:50

Since there is not TOP operator for last SELECT in the IN-clause, you get all records back...

SELECT 		[ID],
		CODE,
		CUST_TEL,
		ACTION_NO,
		DATE_TIME,
		CUST_INPUT,
		IVRS_RESPONSE,
		LOCAL_NO,
		PORT_NO,
		AUTOER
FROM		IVRS_CALL_LOG
WHERE		DATE_TIME BETWEEN '07/02/08 07:58:07.000' AND '07/02/08 11:25:06.000'
		AND ACTION_NO > 0
		AND CODE = '100000640740'
ORDER BY	DATE_TIME,
		ACTION_NO

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-07 : 03:15:45

What I think you mean you want to return, are the last 4 records within the date range...

SELECT TOP 4	[ID],
		CODE,
		CUST_TEL,
		ACTION_NO,
		DATE_TIME,
		CUST_INPUT,
		IVRS_RESPONSE,
		LOCAL_NO,
		PORT_NO,
		AUTOER
FROM		IVRS_CALL_LOG
WHERE		DATE_TIME BETWEEN '07/02/08 07:58:07.000' AND '07/02/08 11:25:06.000'
		AND ACTION_NO > 0
		AND CODE = '100000640740'
ORDER BY	DATE_TIME DESC,
		ACTION_NO

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources