Query is taking too much time to execute

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

General SQL Server Forums

New to SQL Server Programming

Query is taking too much time to execute

Author

Topic

kartik.kaveeshwar
Starting Member

18 Posts

Posted - 2008-07-24 : 00:47:35

Dear Experts,
I am running the following query in sql server 2000

SELECT ISNULL(COUNT(RECORD_NUMBER),0), ISNULL(SUM(PAGES),0)
FROM PAGES_HISTORY_TABLE WHERE UPDATE_DATE_TIME IS NOT NULL

The table PAGES_HISTORY_TABLE contains nearly 2.5 million entries.
I have put index over the field UPDATE_DATE_TIME. But then also this query takes nearly 6 minute to execute.

when i see the execution plan it takes 99% of the time in clustered index scan which is on the primary key RECORD_NUMBER of this table.

please provide some suggestion so that i can get result from the query quickly.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-07-24 : 00:55:40

[code]
try using derived table

SELECT ISNULL(COUNT(RECORD_NUMBER),0), ISNULL(SUM(PAGES),0)
FROM
( SELECT RECORD_NUMBER, PAGES
FROM PAGES_HISTORY_TABLE
WHERE UPDATE_DATE_TIME IS NOT NULL
) Sub
[/code]

kartik.kaveeshwar
Starting Member

18 Posts

Posted - 2008-07-24 : 01:17:18

This query is also taking nearly same time

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-24 : 02:47:15

How fast is this?

SELECT COUNT(RECORD_NUMBER), SUM(PAGES)
FROM PAGES_HISTORY_TABLE WHERE UPDATE_DATE_TIME IS NOT NULL
AND RECORD_NUMBER>''

Madhivanan

Failing to plan is Planning to fail

kartik.kaveeshwar
Starting Member

18 Posts

Posted - 2008-07-24 : 04:59:38

again taking almost similar time

raky
Aged Yak Warrior

767 Posts

Posted - 2008-07-24 : 05:20:03

Try this

SELECT
ISNULL(COUNT(RECORD_NUMBER),0),
ISNULL(SUM(PAGES),0)
FROM
PAGES_HISTORY_TABLE WITH (NOLOCK)
WHERE
UPDATE_DATE_TIME IS NOT NULL

kartik.kaveeshwar
Starting Member

18 Posts

Posted - 2008-07-24 : 06:22:28

quote:
Originally posted by raky

Try this

SELECT
ISNULL(COUNT(RECORD_NUMBER),0),
ISNULL(SUM(PAGES),0)
FROM
PAGES_HISTORY_TABLE WITH (NOLOCK)
WHERE
UPDATE_DATE_TIME IS NOT NULL

Thanks raky
It really worked. It has reduced the execution time from nearly 6 minutes to 1 min. Thanks again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 06:33:56

You are aware what WITH (NOLOCK) does and how it affects the integrity of your query result?

E 12°55'05.25"
N 56°04'39.16"

kartik.kaveeshwar
Starting Member

18 Posts

Posted - 2008-07-25 : 05:35:26

Yes Peso, I know that when i will use this option it will fetch the result for locked rows as well which might not be correct.

But since i need result in a report which will be generated to see past productionn it will work for me.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-25 : 08:51:52

SELECT ISNULL(rn, 0) AS RecordNumber, ISNULL(p, 0) AS Pages
FROM
( SELECT COUNT(RECORD_NUMBER) AS rn, SUM(PAGES) AS p
FROM PAGES_HISTORY_TABLE WITH (NOLOCK)
WHERE UPDATE_DATE_TIME IS NOT NULL
) AS s

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources