July 22, 2004 at 7:22 am
I am supporting the db and o/s to someone-else’s (i.e. development section) application (VB front end to SQL Server 2000 on W2k) and am still very much learning the ropes with SQL so please be gentle with me! A stored procedure inserts data (3140 rows, one row at a time) into a table, Table1 and it takes 1min 40sec. This is currently acceptable although it will be re-written with bulk insert and other refinements to improve this. If the index to this table is clustered, it times out after 25 minutes and fails. Previously it had been Primary Key (on the 3 columns) and clustered… but was failing. So I deleted the original index and created a new constrained one and it does the job nicely. (I tested the constrained index when clustered as well and that failed as before, which suggests it is the Clustered/Not Clustered that is the issue). A connection is opened for each row inserted (I know this is inefficient, hence the switch over to bulk inserts in the near future). However the single connection is used for importing within the acceptable time frame as well as the unacceptable time frame so I do not believe it to be the problem. Details of the table and index are given below.
Any suggestions why it might take so long/fail? Why would clustering/not clustering make such a difference to this? I hope to know whether the different index is dealing with a symptom or the initial problem itself. And, of course, for myself I need to understand why. Many thanks to all, in anticipation of your help – it is much appreciated.
Table Name: Table1
Item1int4(a value from 1 to 60)
Item2int4(a value from 1 to 5)
Time_Fromdatetime8
Time_Todatetime8
Item3int4(a value from 50 to 600)
Item4int4(a value from 50 to 600)
Price1money8
Price2money8
Index Name: IX_Table1
Just over 1 Million rows in the table.
Old Index info:
Type of Index: Primary Key
Index had 3 columns: Item1, Item2, Time_From
Clustered
1 Foreign Key relationship (Item1)
New Index info:
Type of Index: Unique
Index has 3 columns: Item1, Item2, Time_From
Has a Unique Constraint applied.
Is not clustered
1 Foreign Key relationship (Item1)
Many thanks,
Mark Taylor
July 22, 2004 at 8:09 am
Clustering an index affects the physical layout of the data on disk. I think you can adjust free space in your cluster to make inserts a little faster. You can run a nightly maintenance job to rebuild your table with a certain percentage of free space.
Its really about tuning the performance to what is most important to you, inserting quickly and often or selecting quickly and often. Of course you can strike a balance anywhere between these two.
Hope this helps,
Aaron
July 23, 2004 at 8:48 am
Using a clustered index on a table that has a lot of inserts/updates/deletes is not always a good idea. Clustered indexes keep the data ordered in PHYSICAL order. So the DB is constantly reorganizing the data in the table for each record insert. Not good. Unless the data is being inserted IN ORDER (which rarely happens), then I would suggest getting rid of the clustered index, and just use a regular index.
July 23, 2004 at 9:18 am
Thanks for the replies.
The data is not being inserted in order and with a clustered index does appear to be sorted on every insert. With the 2nd index, which is a unique covering index on the 3 pertinent columns, it is being added to the end of the table and a reference to it placed in the index (or so I understand it - please correct me if I am wrong). Hence much quicker. But I gather this is only quicker if fields being updated are in the covered columns otherwise the default hashing algorithm is used - but this doesn't apply for the vast majority of the time as rows are being inserted not updated.
One question I do still have is why the clustered index worked OK in SQL7 and not in SQL2k? Is there a difference in the behaviour of indexing or hashing bewteen the two releases of SQL Server?
Thanks again for your help.
Mark Taylor
July 26, 2004 at 9:06 am
How often you run maintenance on the table may be more improtant than whether the index is clustered or non clustered.
Run DBCC SHOWCONTIG and if the table is less that 80% contigious, run a DBCC DBREINDEX on the indexes and then try your inserts.
A clusted index may be better, if you do the maintenance when the Showcontig goes below 80%.
It seems that you should be able insert 3 or 4 thousand rows in a table is a few seconds if the indexes are maintained regularly.
July 30, 2004 at 8:14 am
A clustered index is SORTED. So after each and every insert you are re-sorting your data. A few things come to mind:
1) remove the index, insert batch, re-build the index
2) the index will negate any bcp gains
3) re-structure the insert statement to run in one batch/statement
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply