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 |
|
alecl
Starting Member
13 Posts |
 Posted - 2002-02-13 : 14:54:21
|
| I've got a reasonably large amount of users hitting a site and I get timeouts on occasion on the line that tries to open a table for a batch insert:RSBatch.Open "testResultsData", conn, adOpenStatic, adLockBatchOptimistic, adCmdTableThis page is the only place this table is used so any locking is happening due to other batches open here. Although it probably improves insert performance, I'm starting to get the idea that the batch will hold a lock open far longer than just separate inserts and this is a big nono.Any thought? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-13 : 15:04:15
|
| Yeah, that's true. As long as the user has the table open, locks will be held...not necessarily on the entire table, but whichever row the user is looking at will be locked.Do you use rsBatch.AddNew? This will aggravate the problem. Can you rewrite the code to use a SQL Server stored procedure? You can pass the values to be INSERTed to it, and it will pop them in right away. You don't even need to have the recordset or table open in ADO when you do this. |
 |
|
|
alecl
Starting Member
13 Posts |
Posted - 2002-02-13 : 19:02:17
|
| I can look into rewriting it as a stored procedure.I am curious about the locking mechanisms in SQL Server 7 though just to further my understanding. I'm never reading from this table and users add records using .AddNew in that batch. Plus it's opened as a static recordset. In theory there shouldn't be any locks that step on each other unless it's doing a table lock rather than rowlocks. I'm inserting on average 25-30 entries. Perhaps that's enough to set off SQL Server's lock promotion and replace the rowlocks with a table lock? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-13 : 19:28:40
|
| I believe page locks are the default. However, I remember reading somewhere that if the INSERTed rows are being appended to the end of the table's data page chain, and the last page/extent gets filled, it will allocate a new extent (8 pages) and lock the whole extent. This could be why multiple users are having such a hard time; several extents could be allocated and they are all locked by other users (Chad???)Even if you're not reading from the table, you're using a recordset to open it. ADO might do something on its own to lock rows or the table, or it might initiate SQL Server locks anyway, on the assumption that you will be browsing through it (can't find the @#$%!# documentation to find out for sure!) The only way to ensure that the table does not get excessively locked is not to open it.FYI - I only use recordsets to return rows, never for UPDATE/INSERT/DELETE operations. I almost always have stored procedures to handle those actions, and I call them using a Command or Connection .Execute method. I also open the recordset, do whatever I need to do, and close it right away, and I never use loops with a recordset either. The less time it is open the better.If you have a lot of data to INSERT, there are some ways to pass all of it to a stored procedure with one call, and have the stored procedure parse out the values and INSERT them as a set. If you post your code and table structures we might be able to write something for you. |
|
|
|
alecl
Starting Member
13 Posts |
Posted - 2002-02-15 : 13:36:56
|
| Turns out that using the static cursor was the major slowdown. However, the docs state that you shouldn't use a forward only with a batch so I just took out the batch altogether and it knocked down the time used by 75%.The inserts are very simple. The question Ids and correct status are stored in parallel arrays and all the questions on the page are for the same testresult_id.CREATE TABLE [dbo].[testResultsData] ( [testresult_id] [int] NOT NULL , [question_id] [int] NOT NULL , [correct] [tinyint] NOT NULL ) ON [PRIMARY]Set RSTestData = Server.CreateObject("ADODB.Recordset")RSTestData.Open "testResultsData", conn, adOpenForwardOnly, adLockOptimistic, adCmdTableFor I=0 to Ubound(a) ' Store the question right/wrong values RSTestData.AddNew RSTestData("testresult_id")=iTId RSTestData("question_id")=b(i) RSTestData("correct")=a(i) RSTestData.UpdateNextRSTestData.Close |
|
|
|
|
|
|
|
|
ADO Batch a bad idea?