Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts

    checking SPEED of different INSERT solutions in ASP

    Hi,

    I'd like to make some speed tests with my db connection. So I'm going to populate it...

    here is the code I'm using:

    Code:
    <html>
    <head>
    <title>SpeedTests</title>
    </head>
    <body>
    
    <%
    
    'two tables: speedTests (id, data), speedTestsResults (id, resultsEXECUTE, resultsRECORDSET)
    
    data = request.form("data")
    
    if request.form("perform") = "touchmeandthenjustpushme" then
    
    timeBegin_y=Timer
    for y = 1 to 1000
    
    MyConn.execute "INSERT INTO speedTests (data) VALUES ('" & data & "')"
    
    Next
    timeEnd_y=Timer
    
    resultEXECUTE=timeEnd_y-timeBegin_y
    
    timeBegin_x=Timer
    
    set rsSpeedTests = Server.CreateObject("ADODB.RecordSet")
    rsSpeedTests.Open "speedTests", MyConn, adOpenStatic,adLockOptimistic, adCmdTable
    
    for x = 1 to 1000
    
    rsSpeedTests.AddNew
    rsSpeedTests("data") = data
    rsSpeedTests.Update
    
    Next
    
    rsSpeedTests.Close
    set rsSpeedTests = Nothing
    
    timeEnd_x=Timer
    
    resultRECORDSET=timeEnd_x-timeBegin_x
    
    set rsInsertResults = Server.CreateObject("ADODB.RecordSet")
    rsInsertResults.Open "speedTestsResults", MyConn, adOpenStatic,adLockOptimistic, adCmdTable
    
    rsInsertResults.AddNew
    rsInsertResults("resultsEXECUTE") = resultEXECUTE
    rsInsertResults("resultsRECORDSET") = resultRECORDSET
    rsInsertResults.Update
    
    rsInsertResults.Close
    set rsInsertResults = Nothing
    
    sql="SELECT resultsEXECUTE, resultsRECORDSET FROM speedTestsResults WHERE id > 1 order by id" 'id being the pk
    Set rsShowResults=Server.CreateObject("ADODB.Recordset")
    rsShowResults.Open sql, MyConn
    %>
    
    <center>
    <TABLE BORDER=3 CELLSPACING=3 CELLPADING=3>
    <TR>
    <TD>
    x times
    </TD>
    <TD>
    EXECUTE
    </TD>
    <TD>
    RECORDSET
    </TD>
    </TR>
    <%
    z=1
    while not rsShowResults.EOF 
    %>
    <TR>
    <TD>
    <%=z%> &nbsp;
    </TD>
    <TD>
    <%=rsShowResults.Fields("resultsEXECUTE").Value%> &nbsp;
    </TD>
    <TD>
    <%=rsShowResults.Fields("resultsRECORDSET").Value%> &nbsp;
    </TD>
    </TR>
    <%     
    rsShowResults.moveNext
    z=z+1
    wend
    %>
    </TABLE>
    
    <%
    rsShowResults.Close
    set rsShowResults = Nothing
    
    %>
    
    <br>
    
    <%
    sqlAvgEXECUTE="SELECT AVG(resultsEXECUTE) as AVGresultsEXECUTE FROM speedTestsResults"
    set rsAverageEXECUTE = Server.CreateObject("ADODB.RecordSet")
    rsAverageEXECUTE.Open sqlAvgEXECUTE, MyConn
    AverageEXECUTE=rsAverageEXECUTE.Fields("AVGresultsEXECUTE").Value
    %>
    
    average EXECUTE: <%=AverageEXECUTE%> secs.
    
    <%
    rsAverageEXECUTE.Close
    set rsAverageEXECUTE = Nothing
    %>
    
    <br>
    
    <%
    sqlAvgRECORDSET="SELECT AVG(resultsRECORDSET) as AVGresultsRECORDSET FROM speedTestsResults"
    set rsAverageRECORDSET = Server.CreateObject("ADODB.RecordSet")
    rsAverageRECORDSET.Open sqlAvgRECORDSET, MyConn
    AverageRECORDSET=rsAverageRECORDSET.Fields("AVGresultsRECORDSET").Value
    %>
    
    average RECORDSET: <%=AverageRECORDSET%> secs.
    
    <%
    rsAverageRECORDSET.Close
    set rsAverageRECORDSET = Nothing
    %>
    
    <br><br>
    
    <%totalTIME=(AverageEXECUTE+AverageRECORDSET)%>
    
    <br><br>
    
    Average of the total time: <%=totalTIME%> secs.
    
    <br>
    
    percentage of this time taken by EXECUTE: <%=((AverageEXECUTE/totalTIME)*100)%>%
    <br>
    percentage of this time taken by RECORDSET: <%=((AverageRECORDSET/totalTIME)*100)%>%
    
    <br><br>
    
    <a href="<%=Request.ServerVariables ("SCRIPT_NAME")%>">perform again</a>
    </center>
    </body>
    </html>
    <%end if%>
    
    <%if request.form("perform") <> "touchmeandthenjustpushme" then%>
    
    <center>
    compare time execution between MyConn.EXECUTE "INSERT INTO..." and .AddNew METHOD
    </center>
    
    <br><br>
    
    <form ACTION="<%=Request.ServerVariables ("SCRIPT_NAME")%>" METHOD="post">
    <center>
    <br>
    <br>
    <TABLE BORDER=0 CELLSPACING=0 CELLPADING=3>
    <TR>
    <TD>
    data
    </TD>
    <TD>
    <INPUT NAME="data" SIZE="20" MAXLENGTH="50">
    </TD>
    </TR>
    </TABLE>
    </center>
    <br><br>
    <CENTER>
    <INPUT TYPE="SUBMIT" VALUE="touchmeandthenjustpushme" name="perform">
    </CENTER>
    </form>
    
    </body>
    </html>
    
    <%end if%>
    As you can see, I'm also checking the time that it takes to populate my db this way.

    What I'd like is suggestions about different ways I could use to record infos in order to compare
    time responses.

    thanx a lot
    Last edited by jeskel; 11-03-2003 at 01:25 PM.

  • #2
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    This may well be a silly question; I have a talent for it - but why are you using a recordset to do an insert? I always make the connection execute the SQL string, or use a command if I've got stored procs.

  • #3
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Originally posted by Spudhead
    This may well be a silly question; I have a talent for it - but why are you using a recordset to do an insert? I always make the connection execute the SQL string, or use a command if I've got stored procs.
    I also always do that too... 'Don't know why I opened a rs in my code... Well, problably because I was doing twenty things at the same time and working on retrieving data speed test . I'll open rs only with the Methods .AddNew and .Update. I edited the code of my first post. Thanx for pointing it out .
    Last edited by jeskel; 10-30-2003 at 11:17 AM.

  • #4
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    Originally posted by Spudhead
    This may well be a silly question; I have a talent for it - but why are you using a recordset to do an insert?
    Not a silly question, but although not as efficient as an INSERT statement, using a recordset is rather more secure (see here) and also makes handling complex datatypes (like dates & times) easier. And how would you insert a BLOB into a record with a plain SQL statement?

    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #5
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    mmmh.... I've edited the code of my first post to show the code I'm using now... Could anyone comment what I've done? It seems that using the AddNew Method goes much quicker... I didn't expect that so I'm thinking that I've done something wrong...
    Last edited by jeskel; 11-03-2003 at 01:22 PM.

  • #6
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Originally posted by M@rco
    Not a silly question, but although not as efficient as an INSERT statement, using a recordset is rather more secure
    so M@rco you would recommand the use of a recordset?

    about the security issue and the link you gave about sql injection attack... it would make such a good sticky to have a checklist of things that you can do to avoid such problems, the "Single Quotes give me a syntax error!" sticky could be expanded including this checklist... Whammy, can you hear me screaming in the dark?
    Last edited by jeskel; 11-03-2003 at 02:17 PM.

  • #7
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    how would you insert a BLOB into a record with a plain SQL statement?
    I wouldn't. I'd keep it as a file somewhere. I've had traumatic experiences with keeping BLOBs in databases.

  • #8
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    Spudhead, I absolutely agree - BLOBs in DBs are best avoided, but they can be useful sometimes, and it was more of a theoretical question than anything else....!



    bouchel, there are (of course) no hard & fast rules. Plain INSERT statements will *always* be faster, but using a recordset offers so many advantages over SQL statements that 99% of the time you probably should use them instead.

    However, the usual rules of using recordsets sparingly, retrieving minimal datasets, choosing cursors/locktypes/etc carefully, using GetRows & GetString, etc. still apply.

    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #9
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Originally posted by M@rco


    bouchel, there are (of course) no hard & fast rules. Plain INSERT statements will *always* be faster, but using a recordset offers so many advantages over SQL statements that 99% of the time you probably should use them instead.
    yeah.. that's exactly what I expected... But from the results of my code, the recordset way of recording data in a db is much faster... So that's why I'm wondering what is wrong in my code or why in that particular case the rs is faster than the INSERT...
    Originally posted by M@rco

    However, the usual rules of using recordsets sparingly, retrieving minimal datasets, choosing cursors/locktypes/etc carefully, using GetRows & GetString, etc. still apply.
    don't worry, I carefully follow your advices (http://www.sitepointforums.com/showt...threadid=49358) and those of Charles Carroll


    But if anyone feels like creating the two tables (I did it in Access) and cut&paste my code to give comments on my experience, feels free to do it...

  • #10
    Regular Coder
    Join Date
    Sep 2002
    Location
    Bugaha, NE
    Posts
    330
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I wouldn't. I'd keep it as a file somewhere. I've had traumatic experiences with keeping BLOBs in databases.

    We actually use blob files. We use Crystal Reports to display some dynamic reports, and we use logo images on them. To dynamically display an image, you either have to put all the images on the report, and suppress all but the one you want at runtime, or you need to pull it from a database. I hate Crystal Reports...but in this case it's a good thing.

    Since we have the logo database table with the blob files in it, I also use it on our intranet site to display the images.
    This is in spanish when you're not looking.

  • #11
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    BLOBs certainly do have their uses, particularly in CMS's.

    However, one of many major problems with using them is that since almost all ISPs/hosts charge big bucks for SQL Server disk space, it is very expensive to use BLOBs on a server which you don't own/run yourself.
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #12
    Regular Coder
    Join Date
    Sep 2002
    Location
    Bugaha, NE
    Posts
    330
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Very good point. It's also just a lot easier to copy a file into a folder instead of having to use a tool, or write a script yourself to stick it into the database.

    We, of course, are able to run our own Oracle server. If I was doing a site for my personal use, I doubt I would use them.
    This is in spanish when you're not looking.


  •  

    Posting Permissions

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