Enterprise Question...
I've got a SQL Server Database for an inventory control application (Multi
client). There are roughly 10 million records in the Inventory Table (tied
by ClientID). We periodically recieve entire inventory updates from clients,
which requires us to "Delete from inventory where clientid=x"... but with
so many records, it's taking forever (especially when we need to do that
for 50+ clients) and is killing the server for the entire process.
Is there a more streamlined approach for replacing an entire clients inventory
in this scenario (client inventories can range from 1000 to 400000 records)?
# 1 Re: Enterprise Question...
I haven't actually tried this in practice, but I've read quite a bit on it...famous
last words...could be dangerous :)
Anyway...partitioning the data could be a solution...there was a good article
on this awhile back in studio magazine...the technique is to partition the
data (in your case by client id) into multiple smaller tables, each table
covering a 'range' of clients...then, an indexed view is created (again,
haven't tried this but the article explained how to set it up) on these tables
so they are accessed from a single place.
When you query, insert, update, etc...the index on the view determines which
table it goes to for the data, the data extraction is then much quicker (or
in your case the delete) because it's dealing with MUCH less data.
I'd look at setting this up and see if it helps...worth a shot anyway...
Good luck,
Chris
"RamblinWreck" <GT@GaTech.edu> wrote:
>
>I've got a SQL Server Database for an inventory control application (Multi
>client). There are roughly 10 million records in the Inventory Table (tied
>by ClientID). We periodically recieve entire inventory updates from clients,
>which requires us to "Delete from inventory where clientid=x"... but with
>so many records, it's taking forever (especially when we need to do that
>for 50+ clients) and is killing the server for the entire process.
>
>Is there a more streamlined approach for replacing an entire clients inventory
>in this scenario (client inventories can range from 1000 to 400000 records)?
# 2 Re: Enterprise Question...
Just make certain that the simple things are taken care of first, like you
actually have indexes on your key fields such as clientid in this case.
Your executing a stored procedure that accepts client ID as the parameter
for the deletion and that the stored procedure is setup with transaction
logic.
If I'm not mistaken there is some priority threading you can do with this
job so that it runs more as a background task. It will take longer to execute,
but won't bring the server to a non-responsive state in the process.
Run it as a scheduled task to impact the least amout of people.
Just my 2cents.
Qbert,
@#&*@
"RamblinWreck" <GT@GaTech.edu> wrote:
>
>I've got a SQL Server Database for an inventory control application (Multi
>client). There are roughly 10 million records in the Inventory Table (tied
>by ClientID). We periodically recieve entire inventory updates from clients,
>which requires us to "Delete from inventory where clientid=x"... but with
>so many records, it's taking forever (especially when we need to do that
>for 50+ clients) and is killing the server for the entire process.
>
>Is there a more streamlined approach for replacing an entire clients inventory
>in this scenario (client inventories can range from 1000 to 400000 records)?
Qbert at 2007-11-11 23:54:25 >
