Wierd Index Issue on DateTime Column

Question

Wierd Index Issue on DateTime Column

Charlottecb

Hall of Fame

Points: 3932
More actions
December 20, 2011 at 3:00 am

#247957

Hi all,
Just wondered if anyone could offer me some advice on a strange issue I'm having with a DateTime index. I have several indexes on a table, one of which is a DateTime column (this is not the Clustered index) for the table.
I have an SP which receives data from the table with a where clause on the DateTime Column. When I run the SP via my front-end C# application, it takes about a minute to load 10,000 records from the DB. If I right-click on the DateTime index and click Rebuild Index through Enterprise Manager, the SP then executes in around 3 seconds. It continues to work fast until the user does a daily import in which up to 1000 new records are inserted into the database (all with the current DateTime the records were created in the indexed datetime field). As soon as I run the SP again from my front-end, it takes around a minute to execute. If I manually rebuild the index again, I get my speed back.
Can anyone suggest what is going on here and how I get round having to keep manually rebuilding the DateTime index?
Many thanks
Charlotte CB

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

The index is fragmenting after data load. You need to add index rebuild / index defragmentation in your database maintenance tasks (or after BULK load).

For More: http://msdn.microsoft.com/en-us/library/ms177571.aspx

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

This likely has nothing to do with fragmentation at all. I suspect you've run into this issue:http://sqlinthewild.co.za/index.php/2011/03/22/statistics-row-estimations-and-the-ascending-date-column/

Very common on ascending columns where the queries want latest data (or reasonably latest). The reason rebuild helps is that rebuilding the index updates the statistics.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Charlottecb Hall of Fame Points: 3932 More actions · Answer 3

Thank you Dev and GilMonster for the responses.

Since I don't know exactly what time of the day the majority of records will be loaded, I was thinking of putting a small utility into the front-end app to run an SP containing Alter Index Rebuild. Any comments?

Thanks Charlotte CB

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 4

Charlottecb (12/20/2011)
Thank you Dev and GilMonster for the responses.
Since I don't know exactly what time of the day the majority of records will be loaded, I was thinking of putting a small utility into the front-end app to run an SP containing Alter Index Rebuild. Any comments?
Thanks Charlotte CB

You might be unaware of time but do you have any idea on data load job? Add it in the job or call it after the job.

fanzhouqi SSC Veteran Points: 267 More actions · Answer 5

if your statistics is the last.

you can try follow code.

may be you will know the reason

USE Northwind

create PROCEDURE List_orders_8 ( @fromdate as DATETIME)

as

SELECT * FROM Orders WHERE OrderDate = @fromdate

go

EXEC List_orders_8 '19980101'

CREATE INDEX OrderDate ON Orders(OrderDate)

EXEC List_orders_8 '19980101'

DROP INDEX dbo.Orders.OrderDate

fanzhouqi SSC Veteran Points: 267 More actions · Answer 6

fanzhouqi (12/20/2011)
if your statistics is the last.
you can try follow code.
may be you will know the reason
USE Northwind
create PROCEDURE List_orders_8 ( @fromdate as DATETIME)
as
SELECT * FROM Orders WHERE OrderDate = @fromdate
go
EXEC List_orders_8 '19980101'
CREATE INDEX OrderDate ON Orders(OrderDate)
EXEC List_orders_8 '19980101'
DROP INDEX dbo.Orders.OrderDate

create index will cause recomplie the SP

Charlottecb Hall of Fame Points: 3932 More actions · Answer 7

Dev (12/20/2011)
Charlottecb (12/20/2011)
Thank you Dev and GilMonster for the responses.
Since I don't know exactly what time of the day the majority of records will be loaded, I was thinking of putting a small utility into the front-end app to run an SP containing Alter Index Rebuild. Any comments?
Thanks Charlotte CB
You might be unaware of time but do you have any idea on data load job? Add it in the job or call it after the job.

Dev, You've lost me here.

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 8

Charlottecb (12/20/2011)
Dev (12/20/2011)
Charlottecb (12/20/2011)
Thank you Dev and GilMonster for the responses.
Since I don't know exactly what time of the day the majority of records will be loaded, I was thinking of putting a small utility into the front-end app to run an SP containing Alter Index Rebuild. Any comments?
Thanks Charlotte CB
You might be unaware of time but do you have any idea on data load job? Add it in the job or call it after the job.
Dev, You've lost me here.

Please disregard. 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Charlottecb (12/20/2011)
Since I don't know exactly what time of the day the majority of records will be loaded, I was thinking of putting a small utility into the front-end app to run an SP containing Alter Index Rebuild. Any comments?

Yes, Please go and read my blog post first and test your queries and see if you are encountering the issue described there (the solutions are noted at the end).

How are the loads done? SQL Agent task? Application? Manual?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Charlottecb Hall of Fame Points: 3932 More actions · Answer 10

The inserts are done from the front-end application in a loop up via a stored procedure which does an Insert one record at a time.