Optimal indexes for these queries

[abkad]

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

 

[gmmastros]

What indexes do you have already? Run this:

sp_helpindex 'files'
sp_helpindex 'status'

It's possible that the performance problems are caused by triggers. So.... run this too...

sp_helptrigger 'files'
sp_helptrigger 'Status'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

OK here my suggestion for first two queries:
Change first to be:

Select max(time)as lastrundate
       from status  WITH(NOLOCK)
WHERE Time > 0

Second (I am not exactly sure what you want there, I assumed you want to delete all records that have MAX() time and that Time is smaller than 16 Jun 2009 12:31:59):

DELETE FROM status  
FROM Status
INNER JOIN (SELECT MAX( time )
                   FROM Status
            WHERE Time < '20090616 12:32') Tbl1
      ON Status.Time = Tbl1.Time

Check BOTH queries Executions plans and tell me what they are

About the last, w/o indexes (there is NO need to be Primary Index) nothing you could do.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[abkad]

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

 

[bborissov]

OK, the last should look like this:

DELETE FROM files 
FROM files 
INNER JOIN (SELECT MAX(timestamp) AS timestamp
                   FROM files WITH(NOLOCK)  
           WHERE timestamp < '2009-06-15 05:06:11.200') Tbl1
      ON Files.TimeStamp = Tbl1.timestamp
WHERE Files.TimeStamp > 0

and correction for DELETE command fo Status table:

DELETE FROM status  
FROM Status
INNER JOIN (SELECT MAX( time )
                   FROM Status
            WHERE Time < '20090616 12:32') Tbl1
      ON Status.Time = Tbl1.Time
WHERE Status.Time > 0

BTW I asked about execution plans, not the list of indexes

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

BTW I asked about execution plans, not the list of indexes

I asked.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[abkad]

These are the execution plan for the queries in the first reply

    |--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)

 

[bborissov]

O!!!
Sorry George,
I just saw abkad answer just after mine and didn't even try to take my head up

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[abkad]

Any Suggestion guys to create new indexes

 

[bborissov]

Why?
I see Index Seek everywhere except the first query where you get Index Scan. Kill me I don't know why SQL Optimizer decides to use Index Scan. Maybe because of (NOLOCK)?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

Try this:

Create Index idx_Status_Time On Status(Time DESC)

This may help. But.... when you are deleting.... on average, how many rows are getting deleted with each query. You may be able to speed up the delete operation by deleting in smaller batches.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom