SQL tuning?

[Ruslan Zasukhin]

On 11/21/08 11:34 AM, "Tiemo Hollmann TB" <toolbook at kestner.de> wrote:

Hi Tiemo,

> beeing not a SQL expert I wonder if the performance of my plain SELECT
> statement can be designed faster.
> 
> What I am doing right now (and that is already pretty good performing) is
> the following. To make my code easy to maintain I have only one single build
> of the SQL string. I have to compare 4 indexed fields in a WHERE clause.
> Each of the 4 fields can be included in the selection or not and every
> combination of the 4 fields can be selected. So what I did is:
> 
>     put "SELECT " & tSelect & " FROM t1" &\
> 
>     " WHERE t1.f1 LIKE " & s1 " \
> 
>     and t1.f2 LIKE " & s2 " \
> 
>     and t1.f3 LIKE " & s3 " \
> 
>     and t1.f4 LIKE " & s4 into tSQL
> 
> and my search variables s1,s2,s3,s4 can contain a search value
> ("%searchstring%") or, if I want to exclude this field from the search I
> just put a "%" into that search variable.

THIS IS THE FIRST what you can optimize!

If you do not need search on field DO NOT include it into SELECT at all!

> Btw I need the comparison with
> LIKE "%searchstring%" for each field. I wanted to avoid to build 16
> different SQL stings, depending on which one of the search values has a
> content or not.

No need build 16.

You can use IF ELSE
 
> Is there anything what I can design better while keeping my code as simple
> and compact, as it is right now?
> 
> Thanks for any coaching

-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]