Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JimJudge
Starting Member
7 Posts |
 Posted - 2003-07-29 : 09:22:56
|
| I am fairly new to SQL Server. I'm using SQL Server 2000.The app we are writting is a .NET web app that receives messages from pagers via WCTP (Wireless Communications Transfer Protocol). The WCTP messages are processed by a .NET assembly that INSERTS the message content into an SQL table.My question is about concurrent INSERTS and how locks will affect this. The messages are coming from equipment around the world. All pieces of equipment send a message every hour on the hour. There could be upto 1,000 pieces of equipment sending messages which will lead to 1,000 INSERTs at roughly the same time. I'm trying to antisipate if each INSERT will put a lock on the table that will cause another INSERT to fail. Any ideas on this?A suggestion was made to use MSMQ to handle the flow of the messaging to the SQL db. Any comments on this?Thanks,Jim Judge |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-29 : 14:20:15
|
| There really is only one way to find out, and that's to test it out. I would run SQL Profiler during the test.You don't have a clustered index on this table, do you? If you do, remove it and create a nonclustered one instead. A clustered index would seriously impact the performance of INSERTs.Tara |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-07-29 : 19:38:31
|
| Well, don't just go ahead and drop the clustered index without testing the performance first. Depending upon which column(s) the table is clustered you won't necessarily see a performance problem. It's better to leave the clustered index in place unless it shows demonstrably worse performance (especially if your primary key is clustered, which is the default)Since SQL Server defaults to page locks, single-row INSERTs should not escalate to table locks. You can also try specifying WITH ROWLOCK in your INSERT statement; the worst that can happen is it escalates it to a page lock anyway. It will only block other processes for as long as the INSERT takes (which isn't long at all for a single row) As Tara suggested, make tests while running SQL Profiler and make sure to include all of the lock events in the trace.If you're inserting multiple rows in a single transaction or statement, then you'll probably get stuck with page locks anyway. Regardless of what happens, I don't think setting up MSMQ will benefit you much. Since there is only one action to perform, a queue doesn't make much sense in this situation. You'd still end up with 1,000 separate connections hitting the DB at one time.If immediate inserts are not required, why not pour the individual rows into a text file and then use BULK INSERT or bcp to upload into the database? That way there's no sustained load on the DB, and all the inserts are done in one operation. |
|
|
|
|
|
|
|
|
Locks