SQL & VB question

what is the most efficient way to open a recordset. Using jet I mad the following call:
stsql = "Select * from Catalog where descript like '*hello*' AND dealer < 50 AND stock > 1 AND DESCRIPT2 LIKE '*cd*'"
SET RECSET = DBS.OPENRECORDSET(STSQL)


Using RDO, I coded as follows After changing the * to %
set rs = cn.openresultset(stsql)
It does not seem to be terribly faster than JET, on Access. The table has 50,000 records, and is approx 8mb in size. The problem is that it is a network app, and lot's of differnt searches are done. Any suggestions, and/or comments would be appreciated. Thanks
LVL 1
redbaron082997Asked:
Who is Participating?
 
cymbolicConnect With a Mentor Commented:
It's not going to be terribly fast in any of the access methods, because from the looks of your query, a table scan is being used .  Anytime you use something like the "Like" operatoer and especially with masking, you are asking the server to look at all records to see which qualify.  There is no question that in continued use under all types of queries, an ODBC interface to SQL Server using RDO will be better and faster than using the Jet/DAO methods.

However, some queries are not very good under either methods, because they present no opportunity to the server of optimizing lookups using previously defined indexes.
0
 
vvkCommented:
What indexes you have on this table?
Try to use OLE interface to SQL server as described in SQL Books on-line or sqlole.hlp file.
0
 
redbaron082997Author Commented:
would using a stored procedure help?  All values being searched are indexed.
0
 
rferlyCommented:
I agree with the first anwer but if it helps I would add that you can use a like, providing you dont use a like "%something"

With a like "something%" the right index can be used by the optimiser
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.