ADO query question - which is faster?

[mathilda]

When I'm opening a recordset for add new, is it faster to put a
limiting clause on it?

i.e. does

"Select [Fields] from [Table] Where Keyfield = " & value & "

return a recordset faster than

"Select [Fields] from [Table]"
given that I know that the value I query for will not be found?

[Darryl Kerkeslager]

"mathilda" <sm***********@yahoo.com> wrote

When I'm opening a recordset for add new, is it faster to put a
limiting clause on it?

i.e. does

"Select [Fields] from [Table] Where Keyfield = " & value & "

return a recordset faster than

"Select [Fields] from [Table]"
given that I know that the value I query for will not be found?

B. But it would be faster still to just use:

rs.Open "table", cnxn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
rs.AddNew

Faster still:

cnxn.Execute "INSERT INTO table ..."
Darryl Kerkeslager

[mathilda]

Thanks!

But a follow up question if I could. I haven't used adOpenKeyset
before. When I will be writing to a table I have always been using
adOpenDynamic because I was under the impression that Keyset did not
create a writeable cursor. What are the advantages of Keyset?

[Darryl Kerkeslager]

"mathilda" <sm***********@yahoo.com> wrote

Thanks!

But a follow up question if I could. I haven't used adOpenKeyset
before. When I will be writing to a table I have always been using
adOpenDynamic because I was under the impression that Keyset did not
create a writeable cursor. What are the advantages of Keyset?

In a way, none. When you use a server-side cursor in ADO (nearly always),
and request a Dynamic cursor, ADO actually creates a Keyset cursor, except
in the case when you request a Dynamic ReadOnly cursor (which I see no
reason to do), when you get a Static type.

So there is probably no performance difference; I've just always used Keyset
on the assumption that I should "ask for what I'm going to get anyways".

DISCLAIMER: Does not apply to confrontational social situations.
Darryl KerkeslAger