SQL SERVER WILL NOT UPDATE RECORDSET FROM ASP PAGE

[janpetrucci]

I am having a very unusual problem with SQL Server 2000. Maybe someone can help me with this.

I am running an ASP site. The server is Windows NT 4.

I was using Access as the database and just upgraded to SQL Server 2000. When I had 2000 records in the SQL table, my asp page updated the records perfectly.

When I added 10000 more records to the table I now get Script timeout messages and the page is halting on the Update statements.

strSQL = "UPDATE Orders SET ReceivedDate = '" & strNO6 & "' WHERE GenOrder = '" & strNO & "'"
objCmd.ActiveConnection = strConnect
objCmd.CommandType = adCmdText
objCmd.CommandText = strSQL
objCmd.Execute , ,adCmdText

The strange thing is that the page works with 12000 records in the Access table...it runs well and updates the recordsets pretty quickly. I thought SQL server would be faster.

I thought maybe I was getting locked on a record, so it would not update, but I am closing the recordsets and I get the same results.

The problem seems to happen on records that I have recently updated in the larger table. It doesn't happen at all in the smaller table.

Has any one had a problem like this???

 

[fluteplr]

do you have an index on the field genorder? If not add one.

 

[janpetrucci]

Thanks for your reply. I apologize for the caps and posting in two places. This is the first time I am posting a question and I was not sure what section to put the question in.

GenOrder is the primary key for the table and the page is sitting for a minute or more before I get the timeout message. The same code on the access table goes right through and displays the page.

I seem to get the problem mostly when I try to update the same record twice. I do not completely understand the firehose cursor in SQL Server. Could I be locked on the record so I can view it but not update it? I think if the page times out, the recordset does not get closed.

What I really do not understand is the problem does not happen with a 2000 record table.

 

[tlbroadbent]

Have you rebuilt the indexes since adding 10000 rows to the table? Indexes can become fragmented when rows are inserted. Fragmented indexes can cause slow queries. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[moonshadow]

It sounds like you might be locking yourself out. The first update locks the record - when you come to the second update on the same record, it can't get a lock, and sits there waiting. You could check this by running sp_lock while your web page is running, and see if you've got a lock other than 'S' on the table. I'd be tempted to put the update in a stored procedure and pass it the two parameters. Hopefully it will then do each update as a single transaction, thereby preventing locking problems.
Of course, I could always be completely wrong.