...
PDA

checking SPEED of different INSERT solutions in ASP

jeskel
10-30-2003, 08:23 AM
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:


<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:thumbsup:
Spudhead
10-30-2003, 09:47 AM
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.
jeskel
10-30-2003, 11:14 AM
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:) .
M@rco
11-02-2003, 01:11 AM
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 (http://www.sitepointforums.com/showthread.php?threadid=60643)) 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?

;)
jeskel
11-03-2003, 01:12 PM
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...
jeskel
11-03-2003, 01:18 PM
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?:)
Spudhead
11-03-2003, 04:30 PM
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.
M@rco
11-03-2003, 05:24 PM
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....!
:p


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.

;)
jeskel
11-03-2003, 07:47 PM
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/showthread.php?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...
BigDaddy
11-03-2003, 09:06 PM
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.
M@rco
11-04-2003, 02:41 AM
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.
BigDaddy
11-04-2003, 02:12 PM
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.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum