SQL Performance Issues 1

[wyldsider]

A quick question for the performance gurus out there.

We have an application that we have ported over to SQLServer 2K, and the reads seem to be happening pretty quickly now. For the most part, we are about 98% reads and 2% deletes and updates, so getting the reads optimized was pretty high up on the list of priorities.

Now I need to focus on the Updates and Deletes. When we do updates and deletes, they are usually in groups of 2-4 objects that are linked and get acted upon at the same time.

Whenever we do these updates, it seems that the first one is taking 2 seconds, the second object is taking 2 seconds, and then it tails off to a more respectable 50-150ms per object. The tables in question have 16,000 entries each, and the column I am deleting on (ObjectId) is the primary key for each table.

Is there any way I can get this to perform better?

thanks,
Jack

Jack De Winter
Software Developer
MedTel Software

http://www.medtel.on.ca

 

[NoCoolHandle]

Indexing is probably the issue here.

Too many indexes means slow updates as every insert and delete affects every index on a table and every update affects every index which contains the column you just updated.. Many indexex and you get many updates for each table.. These updates will inturn have more locks that are likley to cause problems with querys.. (which will slow things down)

Also if you have many PK to FK realationships.. You will need to make sure that every FK column is indexed (see you need indexes (just be picky about the number and what columns

Might not be a bad idea to run the index tuning wizard on your database for a while.

Be verrrry carefull about multicolumn clustered indexes, evaluate covering indexes as a solid strategy and reevaluate many single col indexes and see if you can create less but more effective covering indexes instead (fewer objects to update but still very fast with query performance)

HTH


Rob

 

[clarkin]

If you're looking for indexing tips this site is pretty thorough:

http://www.sql-server-performance.com/default.asp

It has 7 different pages of tips on indexing.

Or if you just want a short, simple intro to indexes try this page:

http://www.aspfaq.com/show.asp?id=2231

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[wyldsider]

Good ideas to check into... here is what I found by doing some high level debugging.

We have a database that is modelled after what we have in memory for the objects, so we have a User, that is a Person, that is an Object, so when we add a User, we add a Person and Object as well, all with the same objectId. The ObjectId is PrimaryKey on all tables, and is set to identity on the Object table, and is the only key on the object table. Just for clarity, the other 2 tables have 3 indexes each.

In the above case, doing an Insert into the Object table is taking 2.1 seconds, while the other 2 inserts are taking 0.1 seconds or less.

ideas?

Jack De Winter
Software Developer
MedTel Software

http://www.medtel.on.ca