SQL Server 7, strange, query running slow

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

Transact-SQL (2000)

SQL Server 7, strange, query running slow

Author

Topic

palvinder
Starting Member

4 Posts

Posted - 2006-11-30 : 17:19:04

Hello,

Firstly like to say hello to everyone at the SQL Team, I'm abit of a regular but its my first post.

Right then, i'm running SQL Sevrer 7 on windows 2000 server (with all latest service packs) and i have a query as follows, which returns three columns with data between two timestamps:

SELECT CROTRD.timestamp AS timestamp , CROTRD.DR_PIP001_SPD021_VAL0 AS DR_PIP001_SPD021_VAL0
FROM CROTRD
WHERE CROTRD.timestamp >= (
select min(tb.timestamp) as timestamp
from (
select max(CROTRD.timestamp) as timestamp
from CROTRD
where timestamp<{ts '2006-06-25 17:22:24'}

UNION

select min(timestamp)
FROM CROTRD
WHERE timestamp>={ts '2006-06-25 17:22:24'}
) tb
WHERE NOT timestamp IS NULL )

AND CROTRD.timestamp <= (
select max(tb.timestamp) as timestamp
from (
select min(CROTRD.timestamp) as timestamp
from CROTRD
where timestamp>{ts '2006-06-25 17:25:35'}

UNION

select max(timestamp)
FROM CROTRD
WHERE timestamp<={ts '2006-06-25 17:25:35'}
) tb
where not timestamp is null )

ORDER BY CROTRD.timestamp

This table has a primary key set to the timestamp column only which is of type 'datetime'. The table has over 2 million records, with a total of 20 columns, built up over 2 years. All timestamp values are unique.

So the query above takes about 40 seconds to run as a whole, but if i break up the query into smaller sections, i.e. run the bold section of the query above (take less than a second to execute) to get ther values and then execute the query again with those values replacing the bold sections above, as follows:

SELECT CROTRD.timestamp AS timestamp , CROTRD.DR_PIP001_SPD021_VAL0 AS DR_PIP001_SPD021_VAL0
FROM CROTRD
WHERE CROTRD.timestamp >= ('2006-06-25 17:22:03.030') AND CROTRD.timestamp <= ('2006-06-25 17:26:02.327')
ORDER BY CROTRD.timestamp

results in the query taking less than a second to complete.

So i was wondering why is this happing (why does it take 40 seconds to execute as a whole compared to less than a second for total sum of all sections)? Plus is there anyway to tune the performance?

Please note that this query is coming from an application so cannot in fact change the query itself, but settings in the database and table are editable.

Finally one thing i have noticed on a test PC is that if i delete all the data in the table except the current two months the query takes only a couple of seconds to run.

Any help appreciated.

Regards

Palvinder Singh

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-11-30 : 23:14:32

enable STATISTICS IO and STATISTICS TIME in your query analyzer session and then re-run your query. You can enable these settings with the following commands:

SET STATISTICS IO ON
SET STATISTICS TIME ON

paste the results here. also, we will need to see an execution plan. in another QA session SET SHOWPLAN_TEXT ON and paste the execution plan here.

-ec

palvinder
Starting Member

4 Posts

Posted - 2006-12-01 : 04:46:45

Thanks for you reply, below is the result of the Statistics query:

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 27 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
timestamp DR_PIP001_SPD021_VAL0
------------------------------------------------------ -----------------------------------------------------
2006-06-25 17:22:03.030 43.05881203595154
2006-06-25 17:22:24.330 NULL
2006-06-25 17:22:33.030 43.351895271590465
2006-06-25 17:22:54.320 NULL
2006-06-25 17:23:03.030 42.912270418132081
2006-06-25 17:23:24.310 NULL
2006-06-25 17:23:33.030 43.42516608050019
2006-06-25 17:23:56.327 NULL
2006-06-25 17:24:03.030 42.88784681516217
2006-06-25 17:24:26.317 NULL
2006-06-25 17:24:33.030 43.42516608050019
2006-06-25 17:24:58.330 NULL
2006-06-25 17:25:03.030 43.42516608050019
2006-06-25 17:25:28.320 NULL
2006-06-25 17:25:33.030 43.400742477530287
2006-06-25 17:26:02.327 NULL

(16 row(s) affected)

Table 'CROTRD'. Scan count 5, logical reads 50853, physical reads 0, read-ahead reads 45190.

SQL Server Execution Times:
CPU time = 1078 ms, elapsed time = 42874 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 42874 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

And here is the SHOWPLAN_TEXT:

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET SHOWPLAN_TEXT ON

SELECT CROTRD.timestamp AS timestamp , CROTRD.DR_PIP001_SPD021_VAL0 AS DR_PIP001_SPD021_VAL0
FROM CROTRD
WHERE CROTRD.timestamp >= (
select min(tb.timestamp) as timestamp
from (
select max(CROTRD.timestamp) as timestamp
from CROTRD
whe

(2 row(s) affected)

StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Inner Join, WHERE:([CROTRD].[timestamp]>=[Expr1007]))
|--Stream Aggregate(DEFINE:([Expr1007]=MIN([Union1006])))
| |--Hash Match(Union)
| |--Filter(WHERE:([Expr1002]<>NULL))
| | |--Stream Aggregate(DEFINE:([Expr1002]=MAX([CROTRD].[timestamp])))
| | |--Top(1)
| | |--Clustered Index Seek(OBJECT:([Table1].[dbo].[CROTRD].[PK_CROTRD]), SEEK:([CROTRD].[timestamp] < Jun 25 2006 5:22PM) ORDERED)
| |--Filter(WHERE:([Expr1005]<>NULL))
| |--Stream Aggregate(DEFINE:([Expr1005]=MIN([CROTRD].[timestamp])))
| |--Top(1)
| |--Clustered Index Seek(OBJECT:([Table1].[dbo].[CROTRD].[PK_CROTRD]), SEEK:([CROTRD].[timestamp] >= Jun 25 2006 5:22PM) ORDERED)
|--Nested Loops(Inner Join)
|--Stream Aggregate(DEFINE:([Expr1015]=MAX([Union1014])))
| |--Hash Match(Union)
| |--Filter(WHERE:([Expr1010]<>NULL))
| | |--Stream Aggregate(DEFINE:([Expr1010]=MIN([CROTRD].[timestamp])))
| | |--Top(1)
| | |--Clustered Index Seek(OBJECT:([Table1].[dbo].[CROTRD].[PK_CROTRD]), SEEK:([CROTRD].[timestamp] > Jun 25 2006 5:25PM) ORDERED)
| |--Filter(WHERE:([Expr1013]<>NULL))
| |--Stream Aggregate(DEFINE:([Expr1013]=MAX([CROTRD].[timestamp])))
| |--Top(1)
| |--Clustered Index Seek(OBJECT:([Table1].[dbo].[CROTRD].[PK_CROTRD]), SEEK:([CROTRD].[timestamp] <= Jun 25 2006 5:25PM) ORDERED)
|--Clustered Index Seek(OBJECT:([Table1].[dbo].[CROTRD].[PK_CROTRD]), SEEK:([CROTRD].[timestamp] <= [Expr1015]) ORDERED)

(23 row(s) affected)

Regards

Palvinder

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-12-01 : 12:26:17

ok,

SELECT  CROTRD.timestamp             AS timestamp , 
        CROTRD.DR_PIP001_SPD021_VAL0 AS DR_PIP001_SPD021_VAL0 
FROM    CROTRD 
WHERE   CROTRD.timestamp >= 
        (SELECT min(tb.timestamp) as timestamp 
        FROM 
                (SELECT max(CROTRD.timestamp) as timestamp 
                FROM    CROTRD 
                WHERE   timestamp<{ts '2006-06-25 17:22:24'} 
                UNION 
                SELECT  min(timestamp) 
                FROM    CROTRD 
                WHERE   timestamp>={ts '2006-06-25 17:22:24'} 
                ) 
                tb 
        WHERE   NOT timestamp IS NULL 
        ) 
    AND CROTRD.timestamp <= 
        (SELECT max(tb.timestamp) as timestamp 
        FROM 
                (SELECT min(CROTRD.timestamp) as timestamp 
                FROM    CROTRD 
                WHERE   timestamp>{ts '2006-06-25 17:25:35'} 
                UNION 
                SELECT  max(timestamp) 
                FROM    CROTRD 
                WHERE   timestamp<={ts '2006-06-25 17:25:35'} 
                ) 
                tb 
        WHERE   not timestamp is null

1. what are the curly braces for?
2. Your WHERE clause syntax is odd, try using WHERE timestampe IS NOT NULL instead.
3. you have used the same alias in both sections of your query for the derived table, try using tb1 and tb2 or something instead.
4. you have a lot of read-ahead i/o. this means physical disk access. How much RAM is in this system? what is your buffer cache hit ratio?

-ec

palvinder
Starting Member

4 Posts

Posted - 2006-12-02 : 05:17:34

For question 1-3, i cannot change the sql statement as it is sent from an application i cannot modify (application called Cimplicity HMI 6.1).

4) it has 1GB as a whole but have set the maximum sql can use to 512Meg. I'm not sure how to get to the buffer cache hit ratio, where would i find this value?

Thanks again for all your help

regards

PS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources