Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Question Unanswered: Too many writes at once causing time outs?

    Hi everyone! I'm new to this forum and I suspect I'll be using this forum frequently. Good stuff.

    Allow this question may appear to be Web-related, I think the problem is with what I'm doing with the database. Please read.

    I'm trying to implement a page tracking solution using ASP and SQL 2000. It basically writes a new record to a table every time a user visits a page on the site. It appeared to work fine at first, then I've increasingly been getting time out errors on my pages -- all pointing to the include file that fires the database write.

    Here's the code that's referenced on every page:

    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open "dsn=x;uid=y;pwd=z;"

    Set objRecordset1= Server.CreateObject("ADODB.Recordset")
    objRecordset1.Open "SELECT * FROM table",Conn,1,2
    objRecordset1.AddNew
    objRecordset1.Fi elds("PAGE") = Left(request.servervariables("SCRIPT_NAME"),100)
    objReco rdset1.Fields("QUERY_STRING") = Left(request.servervariables("QUERY_STRING"),100)
    objRec ordset1.Fields("DATE") = Date()
    objRecordset1.Fields("TIME") = Time()
    objRecordset1.Fields("PLATFORM") = Left(request.servervariables("HTTP_USER_AGENT"),10 0)
    obj Recordset1.Fields("REFERRER") = Left(request.servervariables("HTTP_REFERER"),100)
    objRec ordset1.Fields("USER_IP") = Left(request.servervariables("REMOTE_ADDR"),20)
    If Request.Cookies("TEST")("ID")<>"" Then
    objRecordset1.Fields("VISITOR_ID") = Request.Cookies("TEST")("ID")
    End If
    objRecordset1.Update

    Conn.Close
    Set Conn=Nothing
    %>

    After taking out the reference to the above code everything speeds back up. So, I know the performance hit and time out issues have to do with the code above.

    Is it the simultaneous write to the table, the constant opening and closing of the recordset, the cursor type, the lock type or combination of things?

    HELP!! Thanks!

    David

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    well technically you are grabing the whole table and then inserting a record. You are probably locking the table or the amount of data in the table has grown so much that the query to grab everything is table forever to complete...

    You don't need to select everything in the table to do what you want to do.

    try something like this instead
    Code:
    Set Conn = Server.CreateObject("ADODB.Connection") 
    Conn.Open "dsn=x;uid=y;pwd=z;"
    
    Dim strSQL
    strSQL = "insert into table (PAGE, QUERY_STRING,...) values ('" & Left(request.servervariables("SCRIPT_NAME"),100) & "','" & Left(request.servervariables("QUERY_STRING"),100) & "'....)"
    
    Conn.Execute(strSQL)
    Conn.Close
    Set Conn = nothing
    It's much cleaner....

  3. #3
    Join Date
    Feb 2004
    Posts
    3

    Question

    That's definitely cleaner and it makes a lot of sense NOT selecting the whole table. However, in testing this newly inserted code I found that, although it did successfully write to the table, it was NOT any faster. My page response without code is 1/2 of a second -- with the table write code it increases to 5 seconds per page.

    I guess I'm not sure if this is a database performance/optimization issue or not. I mean, there are thousands of Web sites that do what I'm trying to do and their page loads are much faster.

    Could it be the connection -- should I just open ONE connection during a visitors session and just keep it open while the script writes the pages to the table, then close it when he leaves? Or maybe this should be setup as a stored procedure? I'm not sure how I'd do that or if it would even help my page response times.

    I realize this may stray a bit from the intention of this forum, but if you have any other ideas I'd greatly appreciate them. Thanks!

    David

    Originally posted by rokslide
    well technically you are grabing the whole table and then inserting a record. You are probably locking the table or the amount of data in the table has grown so much that the query to grab everything is table forever to complete...

    You don't need to select everything in the table to do what you want to do.

    try something like this instead
    Code:
    Set Conn = Server.CreateObject("ADODB.Connection") 
    Conn.Open "dsn=x;uid=y;pwd=z;"
    
    Dim strSQL
    strSQL = "insert into table (PAGE, QUERY_STRING,...) values ('" & Left(request.servervariables("SCRIPT_NAME"),100) & "','" & Left(request.servervariables("QUERY_STRING"),100) & "'....)"
    
    Conn.Execute(strSQL)
    Conn.Close
    Set Conn = nothing
    It's much cleaner....

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Check your PM's shortly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •