SQL Query - improve speed using LIKE

[Anesthaesia]

Hi,

I have read through some posts about table scans in SQL, which are performed when using a LIKE query.

Is there any other way to do this and improve performance?

I am currently using ...AND rsRec LIKE '%" & txtSearch.text & "%'... in the query.

I need to be able to do a wildcard search, but was hoping there may be a better way.

Cheers,
D

 

[MikeBronner]

Well, if you can, you should restrict your search variable to use only one wildcard on one side. However, I understand the need to possibly use two wildcards.

The next thing to do is to full-text index the field you are searching on with the wildcards (i.e. rsRec).

Take Care,
Mike

 

[Anesthaesia]

Thanks Mike,

That's something I had a look at but wasn't sure about (not too familiar with SQL indexing/performance)

Going to give it a try now...

Cheers,
D

 

[Golom]

Indexes on text fields are built based on the left-to-right ordering of the characters in the field so a LIKE clause of the form

[myField] LIKE 'ABC%'

can use the index.

Any search involving a prefix wildcard such as

[myField] LIKE '%ABC%'

cannot because it must examine each field searching for "ABC" somewhere in the field (not necessarily at the start of the field)

As Mike says, some systems support full-text indexing and that can substantially improve text search performance.