Two ways of calling stored procedures in ASP and their comparison

beerfroth (original work)

I wrote a simple guestbook using sql server and asp. Through constant attempts, I discovered that there are some differences in execution methods and time when displaying messages in pages.

Let’s take a look at the time comparison of several methods through comparison.

First, use stored procedures for paging. This situation is divided into two methods:

The first is to use the command object, as follows:

Set Cmd=server.CreateObject("Adodb.Command")
Cmd.ActiveConnection=conn
Cmd.CommandText="ycuu_gb_getmsg"
Cmd.CommandType=4'adCmdStoredProc
cmd.prepared=true'
set param=Cmd.CreateParameter("@iPageNo",adInteger,1,2,Page)
Cmd.Parameters.Append param
set param=Cmd.CreateParameter("@iPageSize",adInteger,1,2,PageSizeConst)
Cmd.Parameters.Append param
set rs=Cmd.execute

The second method is to execute directly using the execution method of the connection object, as follows:

set rs=conn.execute ("execute ycuu_gb_getmsg "&page&", "&pagesizeConst)

Second, do not use stored procedures, directly use the function of ADODB.RecordSet for paging, the specific code is as follows:

Set rs = Server.CreateObject("ADODB.Recordset")
sql = "Select * FROM Guestbook Order By dateandtime Desc"
rs.open sql,conn,1,1
rs.pagesize = 150'The number of messages displayed on each page,
total = rs.RecordCount
mypagesize = rs.pagesize
rs.absolutepage = page

In order to show the speed more clearly, I increased the number of messages displayed on each page to 150 (in fact, of course I would not set such a large value). As for the configuration of my machine, I will omit it because it is mainly for speed comparison.

It was found that the execution times are as follows:

The first type: stable between 0.1953125 seconds and 0.2109375 seconds, the average value is approximately: 0.20 seconds

The second type: stable between 0.1716875 seconds and 0.1857 seconds, the average value is approximately: 0.177

of seconds

: stable between 0.4375 seconds and 0.4632 seconds, the average value is about: 0.45 seconds

However, when the number of records read is 20, the results are as follows:
It was found that the execution times are as follows:

The first type: stable between .0390625 seconds and .0546875 seconds, the average value is about: 0.045 seconds

The second type: stable between 0.046875 seconds and .0546875 seconds, the average value is about Yes: 0.050 seconds.

The third type: stable between .09375 seconds and 0.1015625 seconds. The average value is about: 0.97 seconds.

From this point of view, it seems that there is not much difference between the two methods conn.execute and command.execute.
The calling method of the former seems to be simpler.
At the same time, it can be seen here that the paging stored procedure speed is indeed much faster than the recordset paging speed.

PS: It’s my first time to publish an article, wow wow, I find it really difficult to write a good article, I will work hard in the future. I hope everyone will forgive me for my poor writing this time. By the way, I also want to ask you heroes which of the two methods conn.execute and command.execute is better, haha, because all I found on the Internet are the latter method of executing stored procedures. I don’t know why I don’t use the simple one like before.