I need a query for fast search inside mdb file.
What I initially did is perform an open database then loop inside each record to compare the keyword variable.
Any optimization to make this search faster?
You can use a set based search query instead of a loop. See the below SQL query.
;with dset as(select CustomerID from tblCustomer where Name='abc')
select * Â from tblCustomer c inner join tblCus_Room cr on cr.CustomerID=c.CustomerID
innerjoin tblRoom r on r.RoomID=cr.RoomID inner join dset ss
on ss.CustomerID=c.CustomerID
What the query does is first it select a dataset which satisfy a specified condition.
Then it selects the data from the query according to the dataset selected previously.
Result will be similar to what you get from using a loop but this method is really faster than the looping query.
Â
All you have to do is to set a unique keyword or address in each of every record in your database.
Make a short description in each every record so that if you want to make a query in your database, it is easier for you to locate the one that you are searching for.
If you use multiple form on you database.
Make sure that they are properly related to each other.
