We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL & VB question

redbaron082997
on
Medium Priority
227 Views
Last Modified: 2010-03-19
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
Comment
Watch Question

vvk

Commented:
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.
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
would using a stored procedure help?  All values being searched are indexed.

Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.