Filter Property with ADO, VB6 and SQL extremely slow
Oliver@discussions.microsoft.com Jim Oliver
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.
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.)
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'"
I have tried many combinations of cursor mode & Lock mode, with no
improvement. I have also tried using a command object, again with no help.
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. 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?
Isn't there some way to simulate the .Seek method in SQL Server? If I can't
do it through ADO, can I do it using direct calls to SQL Server, and if so,
how?
thanks
Bob Barrows [MVP]
> 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! :-)
>
> 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'"
>
mmmmkayyyy ...
>
> 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"