Timeout on Insert
-
I know there have been several items on this forum about Timeouts during inserts.
I have an odd issue that seems silimar but nowhere near as complicated.
I have an ASP page that adds a new record into a table:
rsSubItems = Server.CreateObject("ADODB.Recordset")
rsSubItems.Open("tbl_IncidentNotes2", objConn, , 3)
rsSubItems.AddNew()
rsSubItems("systemID").Value = systemID
rsSubItems("noteTypeID").Value = noteTypeID
rsSubItems("ticketNumber").Value = ticketnumber
rsSubItems("noteComment").Value = noteComment
rsSubItems("LastModifiedBy").Value = Session("WANID")
rsSubItems("LastModifiedOn").Value = Now()
rsSubItems.Update()
rsSubItems.close()
The table is very simple:
CREATE TABLE [dbo].[tbl_IncidentNotes] (
[incidentNoteID] [int] IDENTITY (1, 1) NOT NULL ,
[noteTypeID] [int] NULL ,
[systemID] [int] NULL ,
[ticketNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[noteComment] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lastModifiedBy] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lastModifiedOn] [datetime] NOT NULL ,
[partNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[partCategoryID] [int] NULL ,
[partDesc] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_IncidentNotes] WITH NOCHECK ADD
CONSTRAINT [PK_tbl_IncidentNotes] PRIMARY KEY CLUSTERED
(
[incidentNoteID]
 
ON [PRIMARY]GO
There are about 51,000 records in the table.
Recently (last week) we started getting a timeout during the above insert action. All the other tables seems fine and no other databases on the server are impacted.
Last week we rebooted the server overnight, and backed up all the data from the table, and reinserted it which seemed to fix the problem temporarily.
Today, again we have the same issue. I really dont know what the next steps are. Sureley 51k records is not too much to have a timeout happen for inserting one record.
I have played around with the connection string in ASP to increase the timeouts but in any event it times-out. I got a timeout doing exactly the same insert transaction using SQL Analyser.
What am I missing here???
Steve
-
Hello,
I'm not familiar with ado (and its locking) but you could check the following:
*locks: sp_who2
If the function only has to enter a new record, perhaps you may consider a stored procedure to do the inserting.
-
Also, without seeing your code a common thing many people forget to do is close their connections properly in ADO to cleanup the objects. Check your number of Connections (even sleeping) and see if there is a very large number. If say you have 10 users but 50+ connections open you probably have a problem in your code. The unclosed connections can actually be causing issues at the application ad thus you get timeouts. As this is the most common thing to happen in these cases this is where I would start.
-
Thanks all for the ideas. I'm actually pretty anal about cleaning up connections properly, so at any time (even with a few production database apps running) I have very few sleeping connections showing.
UPDATE: I recreated the table and transfered all 51K records from a backup into the newlyt created table. I got exactly the same error (I figure if nothing else this ruled out corrupted indexes or something). So then I removed the Primary Key from the table and when I did that, the problem went away. Is it possible that the primary key (and consequent index) actually caused the table to stop functioning correctly? The odd thing is that its not like it gets progressively slower as more records are added, until it seems like its past the timeout period - it either works great (instantly) or it times out...
I would really love to get to the bottom of this - any other ideas?
-
I've had this happen before. It did have something to do with the clustered index in my case.
Keep in mind that the clustered index determines how the data is stored in the DB (it determines the physical structure of the DB). By having a sequential number be the clustered index, you are in essence forcing all of the inserts to happen into the last data page, which can be fine, except that only one process at a time can lock that page to do an insert (or a write or delete). That means that at peak times you might have a bunch of processes all trying to lock the same area of the DB (that last 8K data page): sooner or later, they will then start getting messy with each other, and the problem you see happens.
A few suggestions to try:
- make sure that the DB always has extra space in it, so that any maintenance plans you have insert xx% free space in the db as you reorg it.
- add a uniqueidentifier in (a.k.a. a GUID field) as a new field, and make it the clustered index. Remember you will need to make the default value of that field be NEWID() (to generate the GUID). By making it the clustered index - you in effect spread the writes throughout the DB.
- if your record size is small compared to 8K (meaning - a lot of records fit within a single data page), then consider dropping your fill factor on the clustered index to allow for at least one insert into a data page without causing a page split (which happens if you need to insert something into it but there isn't enough room). Roughly - the way that works is to add up the max sizes of the fields making up the record, turning that size into a % of 8K, and taking x numbers of that percent out of 100%. So - if your record size was 250, and you wanted to be able to insert 4 without a split - 4x250/8000=12.5% so you could make your fill factor 87.5%.
- make sure you have optimistic locks or no locks turned on on your connections from ADO/DAO. The inserts will require locks, but the selects you might be doing on this at the same time shouldn't, so make sure they don't grab one just because....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
I faced same timeout error even for Select Query in VB.net. I used command object to resolve the issue. Using command object you can set Command Timeout to our need (in Seconds) . Setting 0 is infinity.
Here you are just inserting record by recordset but if you use command object with timeout option will resolve the issue.
Try this...Surely will resolve the problem...
Have a nice day...
-
You know there is a correlation between the index and the timeout. And looking at your DDL I can tell you we have tables with 5mil plus records doing 10's of thousands of inserts per hour with no timeout issues and 50k should be a walk in the park to deal with.
The only difference between with the key and without is the fact the table is a heap but unless you removed the INDENTITY constraint it should have the same overall behavior other than if your issueing exclusive locks during your inserts.
Now the difference between what you are doing and what I do is your method which I think might be causing a locking issue.
Consider maybe revising your insert to use a Command object instead of recordset with AddNew method. I have fewer issues with this than any other method.
This should be close to what we do based on your code example. You might want to try this and see if it doesn't get you past the issue.
------------------------------Ex----------------------------
' Datatype constants for parameter input of ADO Command object.
Const adInteger = 3 'Indicates a four-byte signed integer (DBTYPE_I4).
Const adVarChar = 200 'Indicates a string value (Parameter object only).
' Direction options for parameter input of ADO Command Object
Const adParamInput = 1 'Default. Indicates that the parameter represents an input parameter.
' ADO Command Object command type options I may use.
Const adCmdText = 1 'Evaluates CommandText as a textual definition of a command or stored procedure call.
' Execute options for ADO
Const adExecuteNoRecords = &h80 'Indicates that the command text is a command or stored procedure that does not return rows (for example, a command that only inserts data). If any rows are retrieved, they are discarded and not returned.
'
DIM sqlCMD
SET sqlCMD = Server.CreateObject("ADODB.Command")
objConn.Open
with sqlCMD
.ActiveConnection = objConn
.CommandType = adCmdText
.Text = "INSERT dbo.tbl_IncidentNotes2 (systemID, noteTypeID, ticketNumber, noteComment, LastModifiedBy, LastModifiedOn) VALUES (@systemID, @noteTypeID, @ticketNumber, @noteComment, @LastModifiedBy, GetDate())"
.Parameters.Append .CreateParameter("systemID", adInteger, adParamInput, , systemID)
.Parameters.Append .CreateParameter("noteTypeID", adInteger, adParamInput, , noteTypeID)
.Parameters.Append .CreateParameter("ticketNumber", adVarChar, adParamInput, 50, ticketnumber)
.Parameters.Append .CreateParameter("noteComment", adVarChar, adParamInput, 3000, noteComment)
.Parameters.Append .CreateParameter("LastModifiedBy", adVarChar, adParamInput, 30, Session("WANID"))
.Execute ,,adExecuteNoRecords
end with
SET sqlCMD = nothing
-
Thanks All for your feedback.
Antares686 - Are you suggesting that removing the index but leaving the IDENTITY leaves me open to the same issue? Or are you saying that the Identity column prevents the table from being a heap?
Thanks,
Steve
-
No I am saying that even thou the table is a heap it really inserts the data in the last data page just as having the IDENTITY column as the clustered index. Regardless of the IDENTITY coulmn or not.
My thought is there is possibly something in having the clustered index regarding the constraint checking process or the leaf page process for the index itself that may be at the root of the issue as those are the major differences between having the IDENTITY column as the clustered index and having a heap table.
-
UPDATE: This morning when we started back to working within the tool we are getting timeouts again without the Primary Key. I'm wondering if the recoding of the asp will have an impact. Why would inserting 1 record into a table cause a timeout??
The really odd thing is that there appears to be no mid point (ie slow but working). Its either working great or its timeing out...
-
When it is timing out run Profiler to capture the system activity and locks to see if anything is pointed out. I still think Recordset.Open method with .AddNew is at the root. As I said we have systems doing the way I showed with 5mill + records and they don't even hiccup with reports being run at same time unless someone use a range of 3 months or more in their query (which is millions of records).
-
While i agree that the .AddNew seems to be part of th issue, I get the same problem if I simply run an INSERT INTO command using Query Analyser, I get the same timeout problem.
I did something similar (I think) to you what you suggested in that I have created a stored procedure that takes the parameters and inserts the record. It seems to run perfectly.
CREATE PROCEDURE [dbo].[sp_InsertIncidentNote]
@noteTypeID INT,
@systemID INT,
@ticketNumber VARCHAR(50) = NULL,
@noteComment VARCHAR(3000) = NULL,
@lastModifiedBy VARCHAR(30),
@lastModifiedOn DATETIME
AS
INSERT INTO tbl_IncidentNotes (noteTypeID,systemID,ticketNumber,noteComment,lastModifiedBy,lastModifiedOn)
VALUES(@noteTypeID, @systemID, @ticketNumber, @noteComment, @lastModifiedBy, @lastModifiedOn)
GO
I guess I just dont understand enough about the inner workings of SQL to understand why a stored procedure works when exactly the same query that the stored procedure runs times out when run in Query Analsyer.
Profiler just shows the execprep line once the timeout occurs - I'm not sure what else I should see there.
-
If I think of or come across anything else I will pass along.
-
Thanks - I would really appreciate any addition info anyone can offer.
I know that the next step is going to be to have to explain a) why this is happening and b) why this solution (as opposed to the other that seemed to work last night) will fix the problem
LOL
-
Oh do keep in mind you need to test further as you thought you had a solution with removing the clustered index and found it ultimately failed. Could be something else still that has various thresholds for failure. Don't assume to fast.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply