By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,449 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Searching with leading wildcard

P: 67
I'm using a FindFirst to find a part by its part number and make it the active record. I would like to have a leading wildcard in the criteria for the search, but I have heard that it is an inefficient search method in this case. Is it better to use an SQL SELECT WHERE? How significant is the performance difference with a large database?

Thanks!

CB55
Mar 14 '08 #1
Share this Question
Share on Google+
4 Replies


MindBender77
100+
P: 234
I'm using a FindFirst to find a part by its part number and make it the active record. I would like to have a leading wildcard in the criteria for the search, but I have heard that it is an inefficient search method in this case. Is it better to use an SQL SELECT WHERE? How significant is the performance difference with a large database?

Thanks!

CB55
Honestly, I think it really depends on how many records you have to search through. If you only have, say, 5000 records using a wildcard shouldn't cause that much of a problem.

Bender
Mar 14 '08 #2

missinglinq
Expert 2.5K+
P: 3,532
Why would you want to have a wildcard chatracter at the beginning of the criteria if you're searching for a particular part by its part number?

Linq ;0)>
Mar 14 '08 #3

P: 67
Why would you want to have a wildcard chatracter at the beginning of the criteria if you're searching for a particular part by its part number?

Linq ;0)>
Because my part numbering system was outvoted. It is now X-YY-ZZZZ, where X is part category prefix, YY is part subcategory prefix, and ZZZZ is a unique number for each part, regardless of category. So to refer to a part or find it in the database, you could use just ZZZZ. The X-YY is for information and sorting purposes only.

CB55
Mar 17 '08 #4

Scott Price
Expert 100+
P: 1,384
Although I don't really like them, you could use a composite (two-field) primary key in this instance, especially if you experience a huge performance hit by searching with wildcards. That would mean you split the part number into two columns: X-YY in one column and ZZZZ in the other. Then you can do a simple search either by the recordset method through code, or the WHERE criteria through a query on the one field.

btw I believe the Seek method actually is faster than the Find methods of the Recordset object.

Regards,
Scott
Mar 17 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.