Optimizing a query using LIKE

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following query on a 154mb table and the query runs really slow.
there is not an index on the role as there are only three distinct values in
the role field. any ideas how I can speed this query up?


SELECT field1, field2, role role INTO tbl_apl1
FROM tblassociatedpartylinks
WHERE role Like "BORROWER*" Or
role Like "COLLATERAL OWNER*";
 
I realize that the field is way too large. It should be only 50 and it is
defined as 250. Also, the BORROWER and COLLATERAL OWNER always start in
position one. So how can I take advantage of that?
 
I have the following query on a 154mb table and the query runs really slow.
there is not an index on the role as there are only three distinct values in
the role field. any ideas how I can speed this query up?


SELECT field1, field2, role role INTO tbl_apl1
FROM tblassociatedpartylinks
WHERE role Like "BORROWER*" Or
role Like "COLLATERAL OWNER*";
Click to expand...

A couple of suggestions:

Even with only three values, an index will help.
You can use the Left() function to extract the leftmost few characters
but this will NOT help - in fact it will slow the query down since the
function must be called on each row.

But the biggest issue is that you're using a MakeTable or Append
query. This more than doubles the time, since the records must be
retrieved from tblassociatedpartylinks and *then* tbl_apll must be
updated, all its indexes reindexed, etc. etc. If you just want to
display the data on a Form or Report, a simple SELECT query may be all
you need.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top