| Author |
Topic |
|
benricho
Yak Posting Veteran
84 Posts |
 Posted - 2004-11-11 : 19:59:34
|
| I have a table with the following structure:CampaignID intSubscriberID intDateSent smalldatetimeCampaignID and SubscriberID are both foreign keys, and by themselves they are not unique in the table but together they are.Rows are inserted quite frequently to the table (but speed isn't critial at this stage), but there are no updates or deletes, it's just like a log. Currently has 100,000 rows and should get quite large (few million).The table is also used in a query, where the SubscriberID is used for an inner join clause, and the CampaignID is used in there where clause.At first, I just had a Clustered primary key on the CampaignID and SubscriberID columns (with the default fill factor), but after a few days of inserting rows the query would become very slow. If I used the DBCC dbreindex on the table the query would speed up again. I would also check the SHOWCONTIG and the scan density was pretty low.I have now removed that index, and created a Nonclustered index on the CampaignID and another nonclustered index on the SubscriberID. Will see over the next few days how this will perform but does anyone have any better suggestion? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-11 : 20:07:17
|
| How about dropping the existing indexes, and adding:CREATE CLUSTERED INDEX IX_CLS_myTable ON myTable(DateSent)ALTER TABLE myTable ADD CONSTRAINT PK_myTable PRIMARY KEY NONCLUSTERED(CampaignID, SubscriberID)CREATE INDEX IX_myTable_SubscriberID ON myTable(SubscriberID)The only reason to drop the indexes is to save some time when they get rebuilt. Clustering the table on the date value should help, as I imagine that DateSent will increment as new rows are inserted (like an identity value would) and page splits would be avoided. Setting CampaignId and SubscriberID as primary key is a no brainer, and you'd only need a nonclustered index on SubscriberID to manage its foreign key better (the other columns are already indexed). |
 |
|
|
benricho
Yak Posting Veteran
84 Posts |
Posted - 2004-11-11 : 20:18:45
|
| Thanks Rob, I didn't even think of putting the clustered index on the DateSent column, but it makes perfect sense. |
|
|
|
benricho
Yak Posting Veteran
84 Posts |
Posted - 2004-11-15 : 18:27:42
|
| OK, things were going well, but after I do an insert of of about 2000 new rows (making a total of about 115,000) my query becomes really slow. If I rebuild the index, it picks back up to normal speed again.Is my only solution to rebuild my indexes all the time? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-15 : 19:27:43
|
| Not all the time, but you should have an index rebuild at least once a week. You may also want to turn on auto-update statistics, and perhaps do an UPDATE STATISTICS...WITH FULLSCAN on the table once in a while too.What is the query you're running that is slow? |
|
|
|
benricho
Yak Posting Veteran
84 Posts |
Posted - 2004-11-16 : 23:48:32
|
| When I run EXEC sp_autostats myTableName it shows that AUTOSTATS is on for all indexes. I'll have a look at running UPDATE STATISTICS...WITH FULLSCAN next time my query becomes slow. The query is pretty straightforward, but joins a lot of tables and and has code to facilitate paging of data on a web page (similar technique to this) so might be a bit ugly looking to post here.One thing that I noticed this morning was that only queries on the newly inserted data are slow, running the query against old, indexed data is fine. So I guess I might just have to run a dbcc dbreindex ('myTableName') each time after a few thousand records are inserted.I'm using MSDE by the way, wonder if that makes a difference? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-16 : 23:52:37
|
| It's probably the stats that's slowing it down. An update stats should fix it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
benricho
Yak Posting Veteran
84 Posts |
Posted - 2004-11-16 : 23:55:57
|
| OK, I'll give that a go. I'll make my system run an update stats after it inserts a few thousand rows and see how it goes. Thanks for the help. I'll let you know how it goes. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-11-17 : 08:48:41
|
| Also -- you said the query is joining many tables. What could be slowing things down are the OTHER tables. Be sure to check the indexes on those tables as well.- Jeff |
|
|
|
benricho
Yak Posting Veteran
84 Posts |
Posted - 2004-11-17 : 17:51:41
|
| Good thinking Jeff, I'll check out the other tables as well, but I think the major problem is the table we have been discussing because once I rebuild the indexes on that table the query it quick again. But definitely worth looking into. |
|
|
|
|
Whats the best way to index this table?