473,320 Members | 1,933 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

The best way to SELECT

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")
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 22 '05 #1
1 1327
Andy wrote:
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

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 22 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Irene | last post by:
Hi all, I have set up a simple VB program (and later on an ASP interface) to manage an Athletics database. I'm using Access 2000. To simplify, I have the Athlets, the Competitions and the...
1
by: Chris Uwins | last post by:
Hi there, i know theres a number of ways I can achieve this but want to know the best, (but still quite simple). Up until a year ago I never used Access but have designed a few databases for...
17
by: | last post by:
I have an app that retrieves data from an Access database. At the moment I have the SQL string as a Const in my app. I understand this is not best practice. I don't want the user to have access to...
1
by: Joe Attardi | last post by:
Hi all, On a form on one of my pages I have two <select> elements, and each one is paired up with a radio button. The idea is to choose an item from one list or the other and select the radio...
18
by: Bob Stearns | last post by:
I'm building a web based auction system for multiple clients. I have auctions, items, and invoices I need to assign generated keys for. Since each of these entities is represented by a table, the...
1
by: Muchach | last post by:
Hello, Ok so what I've got going on is a form that is populated by pulling info from database then using php do{} to create elements in form. I have a text box in each table row for the user to...
2
by: rn5a | last post by:
A Form has a select list which is populated from a MS-Access database table. The DB table from where the select list is populated has 2 columns - CountryID & CountryName. When the Form is posted,...
11
by: ankitmathur | last post by:
Hi, I'm trying to overcome a situation whereby I have to search through 4-5 columns and produce the results with an order by according to the values matched in these columns. Example: My...
4
by: trullock | last post by:
Hi, Can anyone suggest the best way to go about the following... I'm tracking clicks (mouse down x,y coordinates) on a web page by using some javascript to create an XHR which sends the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.