Which is Faster?

Which is faster when accessing a large table.
rst1.open "Select * from Table1 where 1 =2"
rst1.addnew
xx
xx
rst1.update

or

rst1.open "Table1"
rst1.addnew
xx
xx
xx
rst1.update

So does the select table or just table work faster.
LVL 1
jtjcompAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
caraf_gConnect With a Mentor Commented:
(blind guess) The second is faster, as it does not require the server to interpret and execute an SQL statement.
0
 
jtjcompAuthor Commented:
If i open the table without the select statement will i still have access to all of the fields.
rst1.open "Select * from -- gives me all fields
rst1.open "Table1" -- Does it give me all fields?
0
 
p_biggelaarCommented:
Normally "Table1" should be slightly faster, however there are other ways to improve performance, for instance:

Dim arrVal(1)
Dim arrFields(1)
    Dim rs As New ADODB.Recordset
    arrVal(0) = 15
    arrVal(1) = 65
    arrFields(0) = 0
    arrFields(1) = 1
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenKeyset
    rs.LockType = adLockOptimistic
    rs.Open "table1", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents\db1.mdb;Persist Security Info=False", , , adCmdTable
    rs.AddNew arrFields, arrVal
    rs.Update

is faster than

..addnew
xx
xx
xx
..update

Especially when you are working with large tables while you only want to add records, not opening a recordset is fastest, like:

Dim cn as new adodb.connection
cn.open "See above for example"
cn.Execute "Insert into table1 values (" & xx & ", "& xx & ", "& xx & ")", ,adExecuteNoRecords

though I don't know how comfortable you are with SQL

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
p_biggelaarCommented:
Both your options give you all the fields.

caraf_g: you're quite good at blind guessing ;-)
0
 
jtjcompAuthor Commented:
Thanks guys

I'm giving the points to caraf_g because it was the answer to my first question..

P Biggelaar, your suggestion was great but i think a little overkill for what i'm doing.

Thanks again.....
0
 
p_biggelaarCommented:
Don't know what you're doing so: could be, maybe you can use it somewhere in the future.

You are right about giving the points to caraf_g: he was first and correct
0
 
caraf_gCommented:
Huh? Wait.... it *was* just a blind guess. Did someone do a bench test? I don't understand what earned me those points.... though I like it, of course <g>
0
 
jtjcompAuthor Commented:
Your blind guess and then p biggelaar's backing gets you the points..

:)
0
All Courses

From novice to tech pro — start learning today.