Massive Inserts - Non-Sequencial Keys

[cfaulkner]

I need to generate a key for a feed from a couple mainframe systems to a SQL 2000 box. This will be a new high end server. The feeds will be dumping data into SQL at up to 300 TPS. Is there anyone already doing something like this?

Also, I'm told that it is more efficient and less likely to lock when doing inserts to use a non-sequencial key. In other words instead of using 1, 2, 3… Use 1000, 2000, 3000… Anyone have any experience with this and table locking during massive inserts from different sources at the same time?

Thanks
Craig

 

[donutman]

The 1,2,3 and 1000, 2000, 3000 point seems very suspect to me. BTW, how many rows will this table have! If it's going to be as large as it sounds, maybe you should consider a partitioned table. That might be a reasonable way to speed everything up depending on how this data is going to be queried.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[cfaulkner]

This database will be sort of a router. The data streem will come in and be split off to different tables depending on the looup results of another table. If a perfect world the tables should not get large as this is not a repository. Of course nothing is perfect and at some point the data will be backed up and grow at the rate mentioned above. Most of the data short only be queried first in first out for processing.

This table should never have more than a few days of data, so I'd say a max of 6 millions rows.

Now the logical extension of this is to add more storage and keep the data for 30 days or so, to replace a mainframe store. I at least need to consider that as a possibility in the future.

 

[vongrunt]

1/2/3 or 1000/2000/3000, what is the difference? Both are sequences - as long as you don't insert anything between 1000 and 2000 (for example) later.

 

[cfaulkner]

vongrunt, that was my question too. One of them at least. I was told this be a high level engineer for a mostly Java company.

I hate it when I read me posts and find a bunch of typos! Sorry about that.

Anyone have any more ideas on how to approach this?

Thanks,
Craig