Problem with Index speed when moving records

Question

Post reply

Problem with Index speed when moving records

S. Abraham

Valued Member

Points: 53
More actions
September 4, 2005 at 6:30 pm

#109909

Hi,
I am facing a very interesting problem with a sql server 2000 table index.
The index is created on a timeStamp (datetime) in DESC order.
That table has no primary key and no other index and has about 10 other integer values fields.
The requirements of this table is that it gets inserted to very often. The problem I am facing is when I am archiving it( Ie moving around 8 million records from source to destination archive table [destination table is a replica of source table but does not have that index] ) it take very long (around 1.5 hours)
Now when I archive my where clause is archive WHERE TimeEntered < @inTime
When I remove the index, it take about 20 minutes. Now I am stumpped here. How is it taking longer with an index, especially that I did the IndexDefragment on it before the archive operation.
I would appreciate any ideas.
Many Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

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

philcart SSC-Forever Points: 47796 More actions · Answer 1

So is this for the insert/select part, the delete, or the whole lot?

When you remove the records from the table, SQL removes the entries from the index as well, so yes it will take longer.

Have you ,

a) looked at the execution plan in query analyzer?

and/or

b) watched the process in profiler to see what actions are taking place?

--------------------
Colt 45 - the original point and click interface

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 2

Is the index a clustered index? If so my guess is that you have a lot of forward-pointers in it.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

David Poole-249495 Ten Centuries Points: 1106 More actions · Answer 3

Does your TEMPDB or your database grow when you Insert into your archive table?

I have had problems where a database decided to increase the size of my MDF file in the middle of a big update and this dragged the performance through the floor.

S. Abraham Valued Member Points: 53 More actions · Answer 4

S. Abraham

Valued Member

Points: 53

September 5, 2005 at 6:05 am

#587558

Hi, tempDB does not seem to increase. Thanks

S. Abraham Valued Member Points: 53 More actions · Answer 5

The index I have is an unclustered index.

The statement that takes the most time is the

"insert INTO select". I tried to view the sql execution plan and also tried to use the withindex to explicitly influence using the index.

Thanks a lot for everybody's help.

Otto Schreibke SSC Eights! Points: 943 More actions · Answer 6

Did you run the select statement in Query Analyzer with the 'Show Execution Plan' option turned on?

I suspect your query is doing an index scan then a bookmark lookup.

With 8million records, this combination is a performance killer.

Normally SQL/Server is intelligent enough not to do this for a large number of records, preferring a table scan instead.

Some more questions:

How many rows are there in your table? Is the 8 million rows to archive a large proportion of the content?

Are your statistics up to date? What is the result of DBCC ShowStatistics('TableToArchive')? Are they current and showing the correct number of rows?

Try running DBCC UpdateStatistics('TableToArchive') before running the query. DBCC IndexDefrag does not update the statistics.

Defining the index as clustered may help too, although there may be a problem with page splits when inserting due to the descending order. This will go away if you can live without the descending order.

Best Regards,

Otto Schreibke

The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)

S. Abraham Valued Member Points: 53 More actions · Answer 7

Thanks for your reply.

The table initially did not have associated statistics but I had created statistics for that index and also made sure I did an update statistics after the defrag operation.

I don't have that machine handy, but I do recall the word bookmark when I viewed the execution plan. How can I get rid of this, I tried the with index option but that did not help.

The total number of records are about 11 millions, so I am moving most of them but not all.

Any suggestions?

Thanks

Otto Schreibke SSC Eights! Points: 943 More actions · Answer 8

Updating the statistics should have helped. If you run the select in query Analyzer with the Execution plan turned on it should now use a table scan and take around the 20 mins you saw when you removed the index.

I'd recommend you turn on the AutoCreateStatistics and AutoUpdateStatistics options. The performance gains in the query optimiser usually outweigh the cost.

If it's acceptable to do so, depending on your other application requirements, try defining the index as clustered without the Desc clause. SQL Server will then do a clustered index scan starting at the lowest value, ending at the value of @inTime. Without the Desc clause, additions to the table will not be affected, as they will go in at the end; you might even see a performance gain as there is no longer a separate index to maintain. Note: defining the index as clustered will take some time as SQL/Server will sort the data into timestamp sequence.

Best Regards,

Otto Schreibke

The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)