Re: Filter Property with ADO, VB6 and SQL extremely slow
- From: "Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx>
- Date: Thu, 13 Mar 2008 17:52:39 -0400
Jim Oliver wrote:
Hello all,
I have found many references to this issue but no answer. I have a
table with approximately 1,000,000 records. I need to find 1 record
by the primary key. I have tried many things, but the only thing
that seems to work is ADO's filter property.
Huh??? How about using a WHERE clause in the SQL statement used to open the
recordset ???
The .Find property won't work because the primary key has more than
one field.
The .Seek property isn't supported under SQL Server.
But the .Filter property takes an enourmous amount of time (I don't
know how much yet, as it has not yet returned the record.)
Well ... duhhh! :-)
mmmmkayyyy ...
The code is basically this:
CON.Open "Provider=SQLOLEDB.1;Server=TheServer;Database=TheDatabase"
RS.Open TableName, CON, nRSCursorMode, nRSLockMode, adCmdTableDirect
RS.Filter = "field1='abc' AND field2='def'"
How do I find a record with the primary key using ADO on a SQL Server
database? Surely it shouldn't take hours just to return 1 record.
Oh no ... it definitely shouldn't. But to return 1 million records so yor
recordset object can inspect all of them looking for that one record ....
I
could understand if the fields weren't the primary key, but in this
case the key consists exactly of "field1" and "field2". Shouldn't
SQL Server just look up the values on the key and return the record?
Not when you don't use a sql statement with a where clause ... By using
adCmdTableDirect, you've told it to give you all the records ...
Isn't there some way to simulate the .Seek method in SQL Server?
Well yeah ... that's what a database engine does ... if you tell it to.
If
I can't do it through ADO, can I do it using direct calls to SQL
Server, and if so, how?
My preference is to use stored procedures, but I will bypass that approach
unless you ask me to elaborate in a subsequent message. To do it in your
code, do this:
Dim sql as string
sql = "select <comma-delimited list of fields you wish to retrieve> " & _
"FROM Tablename " & _
"WHERE field1='abc' AND field2='def'"
RS.Open sql, CON,,,adCmdText
If you need to use variables here for the key values, use parameter markers,
like this:
sql = "select <comma-delimited list of fields you wish to retrieve> " & _
"FROM Tablename " & _
"WHERE field1=? AND field2=?"
dim parmval1 as string, parmval2 as string
parmval1="abc"
parmval2="abc"
dim arParms as variant
arParms - Array(parmval1,parmval2)
Dim cmd as new adodb.command
with cmd
set .activeconnection=con
.commandtext=sql
.commandtype = adCmdText
set RS = .Execute(,arParms)
end with
gotta go - the wife's calling me to supper ... :-)
--
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"
.
- Prev by Date: Re: Google Gadget
- Next by Date: Re: How to create editable recordset without using a database tabl
- Previous by thread: Google Gadget
- Next by thread: Re: How to create editable recordset without using a database tabl
- Index(es):
Relevant Pages
|
|
