rs.filter problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

rs.filter problem

Author

Topic

nsrao_1975
Starting Member

13 Posts

Posted - 2004-03-08 : 10:40:19

hi,

i opened a recordset on my sqlserver table which contains 30000 rows

i want to find the record based on primary key value criteria inside a loop.

when i give rs.filter =<condition> it is taking painfully time..

how can i make the filter to work using primary key index ?

can any guru help me out here..?

thanks in advance

ns rao

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-03-08 : 12:50:23

NO LOOPS!!! NO RECORDSET FILTERS!!!

Change your recordset to include a SELECT statement with a WHERE clause that describes the value you're trying to find. If you can post an example someone should be able to write it for you.

nsrao_1975
Starting Member

13 Posts

Posted - 2004-03-09 : 01:29:51

i can't use where clause because each time i have to rerun the query which will do full table scan and ultimately excess overhead ..

so i need to apply rs.filter only.. any possibility is there to filter based on primary key index so that i can make it faster like seek...

thanks

ns rao

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-09 : 01:49:45

A full table scan? Don't you have any indexes on the table? Even if you take network traffic generated by passing the data between the server and the client into consideration, you will still get a faster response by requerying data from the server. SQL Server is much better at handling a large number of records than ADO is...and filtering 30,000 records should be a snap with proper indexing. And how long does this recordset of yours take to fill in the first place? 30,000 records is a lot to be passing to the client, do you really use all of them on the client? Why don't you try and reduce the number of records being retreived the first time around too?

OS

nsrao_1975
Starting Member

13 Posts

Posted - 2004-03-09 : 02:31:51

thanks alot for ur reply

i think i can be much clear to u with the below code..
desconstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sysadmin;Pass Word=****;Initial Catalog=operations;Data Source=NSR;"
desconn.open desconstr
desrs.Open "select * from srtomas", desconn, adOpenDynamic, adLockOptimistic
*** i am having a composite primary key on ser_br,company,type,agnum

while not desrs.eof
** Filter condition keeps changing on some criteria
strfilter ="company='SIL' and type='SR' and gnum='MD345112 /00'"
desrs.filter =""& strfilter ' Here taking time to filter
** some process
desrs.movenext
wend

i would like to know whether i can make the filter use the primary key index so that i can faster the process.

thanks in advance

ns rao

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-09 : 02:54:43

To directly answer that question: no, a recordset filter cannot take advantage of indexes, simply because the filtering takes place on the client, while indexes live on the server. I still don't see why you cannot replace this snippet:


 while not desrs.eof
** Filter condition keeps changing on some criteria
strfilter ="company='SIL' and type='SR' and gnum='MD345112 /00'"
desrs.filter =""& strfilter ' Here taking time to filter
** some process
desrs.movenext
wend

with this:


 while not desrs.eof
** Filter condition keeps changing on some criteria
set rs = desconn.Execute("select * from srtomas WHERE company='SIL' and type='SR' and gnum='MD345112 /00'")
** some process
desrs.movenext
wend

Why are you using adOpenDynamic? Are you actually updating data directly in the recordset? This is about as slow as recordsets can get, use adOpenStatic or adOpenForwardOnly.

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources