indexes

[swoodring]

I have a table that I recently added an index to. Before it had no indexes. We are using these file in a decision support system so it speed up there report retrieval by having indexes on them. The load up of this one file using DTS with ODBC connection to the as/400 use to take about 10 minutes in now takes 45 or more minutes. and sometimes actually 2 or 3 times in the last week it seem to just be hanging. SO I have to stop it and start it back over. I was wondering if it might be better to drop the index before I do the dts or as the first step in the dts and then add the index after I've done all the inserts. ANy feedback would be greatly appreciated.

Stacy

 

[fluteplr]

If you are doing big loads, it is frequently better to drop the indexes and then do the load and recreate the index.

But usually this only is true if you have a large number of indexes. If you only have one see if you created it as a clustered index. If you did, try dropping it and recreating it as a non-clustered index. There will be some tradeoff on the performance of the index but the load should be much faster.

(Clustered indexs actually control how the data is being stored and the nature of the data comming from your other system may be such that, sql server is having to do much more work than is really needed.)

 

[swoodring]

actually I checked the index and it is set up as non-clustered.

this is my file:
Dept code
class code
vendor code
color
size
storenumber
century
year
week
vat sales at retail
ending inv units
units sold
saleretail vat incusive
pos markdown
numbstores included
numberstores with inventory
number of stores with sales
cost of sales
ending inventory at cost
ending inventory at retail
vat inventory retail
vat markdown retail
store index
weekending date


I have a an index set up called
PK_weeklysaleshistory
its in the primary index file group
the create as clustered is not check off so its nonclustered.
the fill factor is 65%
the index consist of
class
vendor
style
color
size
store
century
year
week

 

[jballum]

My preference is not to have primary keys with concatenated fields (although in some situations it cannot be helped). In this example I would usually use an identity column as the primary key and have an index on the needed fields (but not a unique one). This way the time is taken to index the new records but no time is wasted in checking for uniqueness.

Now you might even not need an index on all the fields to keep the queries for the reports speedy so you can relook at this.

Keep in mind again that sometimes you cannot get around needing the safety of the uniqueness of the combination of fields if the source of the data coming in is not reliable.

JB

 

[foxdev]

I have to agree with JB. When I first read the original post, I was dubious that a single index would have that much of a drag on INSERT performance, but now that I see the size of your index expression, it becomes the likely culprit.

One thing to consider when deciding whether to drop the index before loading is if your table needs be remain online and accessible by users. Robert Bradley

http://www.foxdev.com/

Coming Soon:

http://www.lowcostprints.com

http://www.agrainofhope.org

 

[swoodring]

I think I'll try Jb suggestion with the primary index being an identity. And then try creating another index with the keys I currently have in my primary index and see if that improves performance. Thanks for all your help and responses.


Stacy

 

[fluteplr]

I have to agree with most of the above, that kind of index is a recipe of lousy performance in all sorts of areas. It is also basically useless unless you are doing a search on all nine fields. I would not recreate that index unless I absolutely had to, I would create individual indexs on those fields which are most likely to optimize a query. For example if century really is century how many different entries could there be, probably 2 (19,20) , the chances of that index being used is virtually nonexistant.