comparison | stored procedure | stored procedure Beerfroth (original)
I used SQL Server and ASP wrote a simple message book, in the constant attempt to find, pagination display messages, different ways of implementation, time of some differences.
The following comparison is used to see how the comparisons are used in several ways.
One, there are two ways to use stored procedure paging:
First, 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 type is executed directly using the connection object's execution method, as follows:
Set Rs=conn.execute ("Execute ycuu_gb_getmsg" &page& "," &pagesizeconst)
Second, do not use stored procedures, directly using the Adodb.recordset function to pagination, 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 ' number of messages displayed per page,
Total = Rs. RecordCount
Mypagesize = Rs.pagesize
Rs.absolutepage = page
To show the speed more clearly, I've increased the number of messages displayed on each page to 150 (in fact, I wouldn't have set such a large value). As for the configuration of my machine, I omit to say it, because it is mainly speed contrast.
Found that the time of execution is as follows:
The first: stable in 0.1953125 seconds to 0.2109375 seconds, the average is about: 0.20 seconds
The second: stable between 0.1716875 seconds to 0.1857 seconds, the average is approximately: 0.177 seconds
Third: Stable between 0.4375 seconds to 0.4632 seconds, the average is approximately: 0.45 seconds
However, when the number of record bars read is 20, the result is as follows:
Found that the time of execution is as follows:
First: Stable in. 0390625 seconds to 0546875 seconds, the average is about 0.045 seconds.
Second: Stable in 0.046875 seconds to. 0546875 seconds, the average is approximately: 0.050 seconds
Third: Stable in. 09375 seconds to 0.1015625 seconds, the average is approximately: 0.97 seconds
In this view, it seems that the two ways of Conn.execute and Command.Execute seem to be very different,
And the former way of calling seems simpler.
At the same time, you can see that the paging stored procedure is much faster than the recordset's paging speed.
PS: The first time the younger brother, Woo Hum, found that write a good really difficult, I will work hard. I hope you'll excuse me for not writing this time. By the way, I also want to ask you heroes Conn.execute and Command.Execute These two ways of the better, hehe, because I found on the Internet are the latter way to execute the stored procedure. I don't know why I don't have to be that simple before.
