SQL tuning?

[Ivan Smahin]

Hello Tiemo,

Friday, November 21, 2008, 11:34:41 AM, you wrote:

> Hi,

> 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. 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.

> Is there anything what I can design better while keeping my code as simple
> and compact, as it is right now?

The first thing comes to my mind is:

If you can make some assumption like: t1.f3 LIKE " & s3 " is most probably gets to fail
you should put it on the first place in "where" clause (In case of
fail on this statement there is no need to check other conditions).
It is general "rule"

On the other hand, probably there is not the best db-design if you
need such queries. There is too much of string comparison. Try to
find better conditions to select records or redesign the db.

The second thing is:
You can write own stored procedure which will build and execute the query "on the
fly" depending on passed 4 values. I mean if second value is empty
you omit "and t1.f2 LIKE " & s2 "" condition in the query and so on.


-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com