The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Making a database index faster...

I'm using SQL Server and a couple tables have indexes on them to speed up reads.  The problem I'm having is that inserts to the table now are sluggish because of rebuilding/reordering the index.  I know this is part of the tradeoff of using indexes but is there any way to make the insert more performant while the index is there?
Friday, December 30, 2005
Is this for bulk loads or just individual operations?

I know MySQL has all sorts of tunable buffer space paramaters that make a huge performance difference. Perhaps your database does too?
son of parnas
Friday, December 30, 2005
Just individual operations.  It's not a killer but it takes a couple seconds to create a new record when there's a few thousand records already in the table.  It just makes my app seem sluggish and I'm afraid that the average user won't realize that the performance hit is because I'm optimizing for faster read times in other parts of the app.

I did notice some keywords in MySql to defer index rebuilding while I was Googling but didn't find anything for SQL Server.
Friday, December 30, 2005
I'm pretty surprised at the times you give. I haven't observed such durations for an insertion, especially with only a few thousands rows.
Did you have a look at the profiler ? If your insertions are that slow, look at their execution plan.
Friday, December 30, 2005
You need to find out why.  I suspect that it is IO contention on the same disk.  You could test that by doing a lot of inserts intot he table and watching to see if the Disk Write Queue Length goes up.  You could also look at CPU and RAM consumption.  Are you running out of horsepower or swapping?  You should be able ot set up a test and determine what is going on.

If you have disk write queue then you could stripe the data across more disks or get faster disks. 

But you need to find the bottleneck before you can attempt to address it.  You can't fix it if you don't know what is causing the problem.  Standard perfomance stuff.
Jim Send private email
Friday, December 30, 2005
Can you tell us a little more about the index?  Is it based on a function?  Is it clustered?  Anything else worth knowing about it?  I'm kind of reaching, but in general I would say that that several seconds seems like an unusually high hit for inserting into a few thousand rows.
Chaz Haws Send private email
Friday, December 30, 2005
Are there any triggers on the table that could be causing increased insertion time with other related tables? Are there views against the table? I'm just looking for any explanation as to why it would take that long.

I agree with everyone else. A couple of seconds is an eternity for only a few thousand rows. Even for a million rows I wouldn't expect it to take that long. The server doesn't need to re-index the entire table on each insertion. It just needs to figure out the new order using some sort of binary search algorithm.
Friday, December 30, 2005
OK...strike this's my fault it's taking so long.  In the stored procedure that was running long I do a few lookups on tables to get some information and those tables didn't have indexes in the right spots, thus causing the slowdown.  So it wasn't actually the insert that was bottlenecking, it was the lookups before the insert.

Thanks much for all the advice.  Boos and raspberries to me for not profiling/debugging my code before posting the question.  :-/
Saturday, December 31, 2005

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz