Parallel/Concurrent Inserts in One Table

Question

Post reply

Parallel/Concurrent Inserts in One Table

sohailsoh

SSC-Addicted

Points: 479
More actions
September 15, 2008 at 6:46 am

#198151

Dear All,
I am in a situation where i need to insert bulk of records(Inserts) in a database(SQL Server 2005 Enterprise). Actualy i am listening on a TCP/IP port where near about 25 MB data is coming. Initialy the system was going into the non-responsive state due to the large no of inserts at the same time. so i used the .NET worker and ISBUSY property to avoide the system choking.
But now the system is failed to insert all the rescords/messages coming on this port.Because untill one insert doesnot finish the work doesnot allow other insert statements.
For Example in inserting one records it is discarding 6-7 records.
Please let me have your suggestions to insert all the records in one database and in one table(More than 300 tables) at the same time.
You may suggest any type of solution/scenario.
Thank you,
Kind Regards

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

You haven't really described very well what you're doing.

Integration Services can handle over 1TB an hour of loads, so I might suggest you look at that.

sohailsoh SSC-Addicted Points: 479 More actions · Answer 2

I have a syslog listener and i am saving all the data coming from near about 300 devices on a TCP/IP Port into the database, every device has its own table to insert the messages but every device is sending 8-10 messages in 1 second on this single port. so 8*300=2400 messages in one seconds.

I want to save all these messages in database.

I will look into the Integration Services but please let me know if it clears the things now or you may ask any question so i will explain in detail.

Thank you for your reply

Kind Regards

sohailsoh SSC-Addicted Points: 479 More actions · Answer 3

Please if you can provide me an example link for Integration Services then it will be a great help

Thank you

Harveysburger SSCommitted Points: 1562 More actions · Answer 4

so you've got one database that has 300 tables... and 8 to 10 new entries are recorded in each one of these 300 tables every second... and these are coming from 300 different sources

don't think integration services will help in this scenario

I wonder what is causing the unresponsiveness... I/O?

noeld SSC Guru Points: 96590 More actions · Answer 5

I am sure your bottleneck is not SQL Server. I definitely handle a much higher rate.

- You should profile what is really going on.

- Make sure you have pre-allocated enough space on the DB so that no autogrow kicks in in the middle of the transactions.

- Ensure that all those devices are really sending data to the DB server.

* Noel

sohailsoh SSC-Addicted Points: 479 More actions · Answer 6

Thank you.

I have already looked on these points and if i remove the ISWORKERBUSY check the system does down due to the large no of inserts in Database at the same time.

Please if you may help me in explaining the scenario how you are handling a Higher Rate in yor case?

I hope it would help me with some point.

sohailsoh SSC-Addicted Points: 479 More actions · Answer 7

We have Quard HP server with 8 GB of RAM, 160 GB of Hard Disk and the total size of Database is near about 13-14 GB, we are deleting the records daily from this Database so it does not exceed from this space.