By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,296 Members | 1,723 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,296 IT Pros & Developers. It's quick & easy.

ADO query question - which is faster?

P: n/a
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?

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"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

Nov 13 '05 #2

P: n/a
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?

Nov 13 '05 #3

P: n/a
"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
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.