Optimal indexes for these queries

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)

Optimal indexes for these queries

Author

Topic

abdou_kadri
Starting Member

13 Posts

Posted - 2009-06-16 : 12:44:56

HI All,

I have the following queries are still running slow eventhough I added some indexes it didn't get the indexes and still giving me table scan and table delete.

Have the following 2 tables each have more then 20 million rows.


CREATE TABLE [dbo].[STATUS](
	[NAME] [nvarchar](10) NOT NULL,
	[TIME] [datetime] NOT NULL,
	[POPULATION] [int] NULL,
	[OLDEST_TIME] [datetime] NULL,
	[COUNTRY] [nchar](2) NOT NULL,
	[WAIT_TIME [decimal](8, 2) NULL,
PRIMARY KEY CLUSTERED 
(
	[TIME] ASC,
	[COUNTRY] ASC,
	[NAME] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

first query gets the max time in all the table


Select max(time)as lastrundate 
from status  WITH(NOLOCK)

Second this is my issue her takes long time to run


 DELETE FROM status   WHERE time <=( 
 SELECT MAX( time ) 
 FROM status   WITH(NOLOCK)  
 WHERE time IN ( 
		SELECT TOP time
		FROM status   WITH(NOLOCK)  
		WHERE time < '2009-06-16 12:31:59.877' ))

second table is my issue includes more then 40 millions rows without a primary key


CREATE TABLE [dbo].[FILES](
	[NAME] [nvarchar](10) NOT NULL,
	[TIMESTAMP] [datetime] NOT NULL,
	[FILE_NAME] [nvarchar](200) NOT NULL,
	[FILE_TIMESTAMP] [datetime] NOT NULL,
	[COUNTRY [nchar](2) NULL,
	[FILE_SIZE_BYTES] [int] NULL,
	[FILES_COUNT] [int] NULL,
	[WAIT_TIME] [decimal](8, 2) NULL
) ON [PRIMARY]

as in the above table is very slow running query


 DELETE FROM files WHERE timestamp <=( 
 SELECT MAX( timestamp ) 
 FROM files WITH(NOLOCK)  
 WHERE timestamp IN ( 
		SELECT TOP 1000 timestamp  
		FROM files WITH(NOLOCK)  
		WHERE timestamp < '2009-06-15 05:06:11.200' ))

Thanks in advance for any suggestion and idea to have the optimal indexes on these tables

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-16 : 12:50:56

you're not giving any ORDER BY inside subquery so what you're doing is selecting 1000 random records and then taking max value of it. is this what you're actually looking for?

abdou_kadri
Starting Member

13 Posts

Posted - 2009-06-16 : 13:28:36

quote:
Originally posted by visakh16

you're not giving any ORDER BY inside subquery so what you're doing is selecting 1000 random records and then taking max value of it. is this what you're actually looking for?

ok I rewrite this query to the following but still slow


 DELETE FROM queuefiles WHERE timestamp <= (
 SELECT TOP 1 	timestamp
		FROM   files WITH (NOLOCK)
		WHERE	timestamp < '2009-11-11 12:31:59.877'
		ORDER BY timestamp desc )

these are the indexes I have for both

indexes on status


PK__LOADERWATCH_LOAD__2779CBAB
    clustered, unique, primary key located on PRIMARY    TIME, COUNTRY, NAME

indexes on files


LLQ_K1    nonclustered located on INDEXES    NAME, TIMESTAMP

LLQ_K2    nonclustered located on INDEXES    TIMESTAMP,FILE_COUNT

LLQ_K3    nonclustered located on INDEXES    COUNTRY,NAME

NO TRIGGERS ON both tables

Any Suggestion Guys Thanks in advance

abdou_kadri
Starting Member

13 Posts

Posted - 2009-06-16 : 13:53:26

these are the execution plans for the above queries


|--Stream Aggregate(DEFINE:([Expr1003]=MAX([STATUS].[TIME])))
       |--Top(TOP EXPRESSION:((1)))
            |--Clustered Index Scan(OBJECT:([STATUS].[PK__LOADERWATCH_LOAD__2779CBAB]), ORDERED BACKWARD)


  |--Clustered Index Delete(OBJECT:([STATUS].[PK__LOADERWATCH_LOAD__2779CBAB]))
       |--Table Spool
            |--Top(ROWCOUNT est 0)
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1009]))
                      |--Stream Aggregate(DEFINE:([Expr1009]=MAX([partialagg1014])))
                      |    |--Merge Join(Inner Join, MERGE:([STATUS].[TIME])=([STATUS].[TIME]), RESIDUAL:([STATUS].[TIME]=[STATUS].[TIME]))
                      |         |--Stream Aggregate(GROUP BY:([STATUS].[TIME]) DEFINE:([partialagg1014]=MAX([STATUS].[TIME])))
                      |         |    |--Clustered Index Seek(OBJECT:([STATUS].[PK__LOADERWATCH_LOAD__2779CBAB]), SEEK:([STATUS].[TIME] < '2009-06-01 12:31:59.877') ORDERED FORWARD)
                      |         |--Stream Aggregate(GROUP BY:([STATUS].[TIME]))
                      |              |--Top(TOP EXPRESSION:((1000)))
                      |                   |--Clustered Index Seek(OBJECT:([STATUS].[PK__LOADERWATCH_LOAD__2779CBAB]), SEEK:([STATUS].[TIME] < '2009-06-01 12:31:59.877') ORDERED FORWARD)
                      |--Clustered Index Seek(OBJECT:([STATUS].[PK__LOADERWATCH_LOAD__2779CBAB]), SEEK:([STATUS].[TIME] <= [Expr1009]) ORDERED FORWARD)


  |--Table Delete(OBJECT:([FILES]), OBJECT:([FILES].[LLQ_K1]), OBJECT:([FILES].[LLQ_K3]), OBJECT:([FILES].[LLQ_K2]))
       |--Table Spool
            |--Top(ROWCOUNT est 0)
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1012]))
                      |--Stream Aggregate(DEFINE:([Expr1012]=MAX([FILES].[TIMESTAMP])))
                      |    |--Nested Loops(Left Semi Join, WHERE:([FILES].[TIMESTAMP]=[FILES].[TIMESTAMP]))
                      |         |--Index Seek(OBJECT:([FILES].[LLQ_K2]), SEEK:([FILES].[TIMESTAMP] < '2009-11-11 12:31:59.877') ORDERED FORWARD)
                      |         |--Top(TOP EXPRESSION:((1000)))
                      |              |--Index Seek(OBJECT:([FILES].[LLQ_K2]), SEEK:([FILES].[TIMESTAMP] < '2009-11-11 12:31:59.877') ORDERED FORWARD)
                      |--Index Seek(OBJECT:([FILES].[LLQ_K2]), SEEK:([FILES].[TIMESTAMP] <= [Expr1012]) ORDERED FORWARD)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 13:58:15

You are still using the TOP 1000 technique, why!?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=126391

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 14:00:58

Your overcomplicated query can be rewritten as this,
and it will do exactly the same thing!

Post back the execution plan for this...

DELETE	
FROM	Files
WHERE	TimeStamp < '2009-06-15 05:06:11.200'

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 14:43:13

And for not filling the log file

WHILE @@ROWCOUNT > 0
	DELETE	TOP 10000	
	FROM	Files
	WHERE	TimeStamp < '2009-06-15 05:06:11.200'

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources