| Author |
Topic |
|
SQL_ACTUARY
Starting Member
7 Posts |
 Posted - 2007-01-23 : 09:59:40
|
| I have a medical DB with the loads 150,000 transactions per month. Each month, I load the tranactions into a table for the current year. I also have to update records for prior months based on current month information.For example, out of 186,000 dump records...150,000 will be loaded into the main table and 36,000 will be used to update records already loaded into the main table.The tables have 90 columns, I have a clustered PK using [Soc_Sec_Number] & [Month] & [Row Index]. I need the row index counter (like auto number in MS Access) because I can have multiple transactions per month for the same Soc Sec Number.===========================================================My steps are1) Load 150,000 records into main table (For december, this makes the table have 1,800,000 rows2) Run queries for the remaining 36,000 rows to update records already loaded into the table containing the 1,800,000 rows.3) The 36,000 queries have to be splits depending upon the update type code, So I am actually running 6 queries using 6,000 rows each against the 1,800,000 records.The update queries are using inner joins with [Soc Sec #] and [Date], part of my composite Primary Key on both tables.=============================================================ProblemThis process takes forever, about 4 hours per monthly update. As the months go out, the main table gets larger and the time increases. It took almost 24 hrs to get from January 2004 to June 2004.I am running Sql Server on my PC, no seperate workstation. My PC has 2.8 GHZ with about 1 Gig in RAM. Could my PC specs be too low. I noticed that the task mamager shows sqlservr.exe using over 657,000 mb of RAM when running.I also ran a simple Select MAX(Soc_Sec_Number) query that took over 5 minutes. This is way too long especially since Soc_sec_Number is part of the composite PK.Could my queries actually take that long or are my pC specs too low. MY PC seemed to freeze after the JUNE update? Any help appreciated. |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-23 : 11:56:26
|
| The PK is not an ideal candidate for a clustered index and as a result I would say you have severe page fragmentation within the table. That's not an integrity problem but will lead to poor performance.You have two options, either leave the index the way it is but rebuild it after every monthly load (and probably much more frequently than that, about once a week I'd say). Alternatively run the Index Tuning Wizard and it will almost certainly suggest changing the clustered index to be on [Row Index] only and then add a second (non-clusterted) index on the three columns you have now. |
 |
|
|
SQL_ACTUARY
Starting Member
7 Posts |
Posted - 2007-01-23 : 12:29:28
|
| Thanks for your replyWhy is the PK not good for a clustered index? I thought that by keeping the table rows in a certain order, the query would run faster.Also, I am only running the update once a month (only time anything is changed). After that, we are mainly running summary queries for reporting. Right now, I am trying to load historical data up to 2007/01 (which is why I am starting in 2004). |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-23 : 13:09:31
|
| A clustered index determines the actual ordering of the rows in the table. The problem is that because your key includes the SSN first, this means that rows have to be inserted all over the table, not at the end of the table. That is causing fragmentation and also makes inserting rows slower than if they could just be "tacked on" the end of the table. So it typically (not always) better to base the clustered index on a key that have new rows added at the end of the table. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-24 : 08:18:19
|
| If you are not already doing so [regularly] it would be worth updating the statistics on that table to see if that helps the query optimiser.UPDATE STATISTICS dbo.MyTable WITH FULLSCANKristen |
|
|
|
SQL_ACTUARY
Starting Member
7 Posts |
Posted - 2007-01-24 : 12:16:13
|
| Thanks for everyone's help.I will try a non-clusetered index and see how it goes. |
|
|
|
ds9
Starting Member
38 Posts |
Posted - 2007-05-11 : 09:03:05
|
| Hi pplI am facing a similar problem, and followed suggestions described in this thread. The thing is that SQL doesn't allow me to uncheck the 'clustered' option. And the Drop Existing option is idle.What should I do?Thanks ds9 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-11 : 09:15:13
|
| Are you using Free Text Indexing on this table?Kristen |
|
|
|
ds9
Starting Member
38 Posts |
Posted - 2007-05-11 : 09:18:41
|
No, I am not using that. (actualy I don't quite know what is a Free Text Indexing  )Thanksds9 |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-11 : 09:30:43
|
| Should be full text index? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-11 : 09:54:50
|
| Yeah, that's the one. Thanks rmiao |
|
|
|
ds9
Starting Member
38 Posts |
Posted - 2007-05-11 : 10:02:45
|
| Free or Full I'm not using that...Do I have to redesign the tables?thanksds9 |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-11 : 10:08:47
|
| Not really, try create a full text index on the table to see if speeds up. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-11 : 10:18:28
|
| "The thing is that SQL doesn't allow me to uncheck the 'clustered' option"Well if there are no Full Text indexes relaying on that index something else must be. Foreign keys?Kristen |
|
|
|
ds9
Starting Member
38 Posts |
Posted - 2007-05-11 : 10:32:29
|
| I don't think so. I just have a table with 3 primary keys that are making up the clustered index. When I 'uncheck' the Clustered index option int the 'Manage Indexes' window, and presso ok it replies with the following error:Server: Msg 1925, Level 16, State 2Cannot convert a clustered index to a nonclustered index using the DROP_EXISTING option.Any idea of what this means?Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-11 : 12:45:07
|
| Ah, I reckon that means that you need to DROP the existing index, and then create a new one from scratch. Note that there will be NO PK between these two steps, which may be important if users will be using the database during that time!Kristen |
|
|
|
ds9
Starting Member
38 Posts |
Posted - 2007-05-11 : 14:21:51
|
| Hi KristenI'm almost there:-)When I drop the index I'm getting another message:Server: Msg 3723, Level 16, State 4An explicit DROP INDEX is not allowed on index 'dbo.table1.PK.table1'. It is being used for PRIMARY KEY constraint enforcement.Don't tell me that I have to delete a 4 million+ lines table and recreate it!Thanks ds9 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-11 : 14:44:36
|
| Sounds like you need to drop the PRIMARY KEY constraint ... ... maybe you had BOTH a PK constraint AND an Index? |
|
|
|
|
Huge Performance Issue