The best way to SELECT

[Andy]

Which is the better method to retrieve data from a database (SQL Server
2000 or Oracle 9i)?

Does one of the following methods perform better than the other?

'METHOD 1
Dim strSql, strValue
strSql = "SELECT X FROM MyTable WHERE Y = '" something "'"
Set rs = conn.Execute(strSql)
strValue = rs("STATUSDATE")
rs.Close
Set rs = nothing
Response.Write(strValue)

'METHOD 2
Dim strValue
Set rs = conn.Execute("SELECT X FROM MyTable WHERE Y = '" something "'")
strValue = rs("STATUSDATE")
rs.Close
Set rs = nothing
Response.Write(strValue)



The conn object is created this way:
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open Application("connstring")

 

[Bob Barrows [MVP]]

Which is the better method to retrieve data from a database (SQL
Server 2000 or Oracle 9i)?

Does one of the following methods perform better than the other?

'METHOD 1
Dim strSql, strValue
strSql = "SELECT X FROM MyTable WHERE Y = '" something "'"
Set rs = conn.Execute(strSql)
strValue = rs("STATUSDATE")
rs.Close
Set rs = nothing
Response.Write(strValue)

'METHOD 2
Dim strValue
Set rs = conn.Execute("SELECT X FROM MyTable WHERE Y = '" something
"'") strValue = rs("STATUSDATE")
rs.Close
Set rs = nothing
Response.Write(strValue)



The conn object is created this way:
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open Application("connstring")

Hopefully connstring contains an OLE DB connection string, not ODBC (that's
the only relevant criterion here).
Neither. You should use parameters, preferably with stored procedures.

However, to answer this particular question:
From a performance standpoint (which I assume is the viewpoint you are
looking for), both will perform equally well.
From a maintainability standpoint, method 1 makes it much easier to debug
your code if errors occur. Being able to see the actual statement being sent
to the server goes a long way toward detecting syntax errors. Using a
variable allows you to do:

Response.Write strSQL

allowing you to see the statement without using Profiler or trace.

One last note:
always tell ADO what the commandtype is. Don't make it guess. It takes a
couple nanoseconds for it to make its guess, so performance really is not
the issue here: in rare cases it can guess wrong, leading to symptoms that
will be very difficult to debug. The Execute method takes one required
argument, and two optional arguments: the third argument is where you can
specify the command type and any execution options that are relevant. In
this case, you need to do this:

const adCmdText=1
Set rs = conn.Execute(strSql,,adCmdText)


Bob Barrows