Board index » delphi » Tricky Theoretical SQL Problem

Tricky Theoretical SQL Problem

In my user interfaces I often give users the ability to select records
based on one or more fields, for example, I might allow them to select
from a list of names and addresses where the State='IL' or the Zip =
'60614'. Normally, I just construct a where clause based on what the
user has selected and pass the string to the SQL server (we use Sybase).

The problem is that this approach is slow. The server has to parse the
string each time. I would like to be able to create a stored procedure,
pass a few parameters to it, and get the result set. It is possible to
do this if you write your stored procedure as follows:

if (@state =  null) and (@zip = null) then
  select * from names
else if (@state <>  null) and (@zip = null) then
  select * from names where state = @state
else if (@state =  null) and (@zip <> null) then
  select * from names where zip = @zip
etc..........

If you have more than a few parameters you want to use, the approach is
not feasible because you need a huge number of SQL statements. So I have
tried another approach:

select * from names
where ((state = @state) or (@state = null)) and ((zip = @zip) or (@zip =
null))

This returns the right result set, but the Sybase compiler can't
optimize it. Sybase always does a full table scan and ignores the
indexes.

Is there some other solution to this problem?

Chris
--
Chris Cleveland
Genesee Development Group, Inc.
2000 North Racine Avenue, Suite 4100
Chicago, Illinois  60614
773.528.1700 voice, 773.528.8862 fax
http://genesee.net
cclevel...@genesee.net

 

Re:Tricky Theoretical SQL Problem


Quote
Chris Cleveland wrote:
>In my user interfaces I often give users the ability to select records
>based on one or more fields, for example, I might allow them to select
>from a list of names and addresses where the State='IL' or the Zip =
>'60614'. Normally, I just construct a where clause based on what the
>user has selected and pass the string to the SQL server (we use Sybase).
>The problem is that this approach is slow. The server has to parse the
>string each time.

I generally use the same approach as you, dynamically generate the query
based on which fields the user has filled in.
This seems to work very well here (on an Oracle database).
The nice thing about doing it this way is that then you don't need to join
in all the tables the user possibly can filter against each time, you
"optimize" the query when you build the SQL string.
(and this string handling stuff is blindingly fast on todays machines)

Perhaps you can cache the queries that the user typically executes?
(in order to save the database engine from optimizing the most used queries
each time)

Quote
> I would like to be able to create a stored procedure,
>pass a few parameters to it, and get the result set. It is possible to
>do this if you write your stored procedure as follows:

>if (@state =  null) and (@zip = null) then
>  select * from names
>else if (@state <>  null) and (@zip = null) then
>  select * from names where state = @state
>else if (@state =  null) and (@zip <> null) then
>  select * from names where zip = @zip
>etc..........

>If you have more than a few parameters you want to use, the approach is
>not feasible because you need a huge number of SQL statements.

Agree, it grows exponentially.

Quote
>select * from names
>where ((state = @state) or (@state = null)) and ((zip = @zip) or (@zip =
>null))

>This returns the right result set, but the Sybase compiler can't
>optimize it.

<Digression>
I haven't used Sybase, but are you sure that expressions like "@state=null"
are working as expected?
In the three-valued logic that SQL uses, "x=null" is never true (and neither
are "null=null" true either)
(One has to use "x is null" / "x is not null" instead)
Since both expressions "x=null" and "x<>null" is guaranteed to be not true
(and not false either), some tools/engines may perhaps fix the obvious
error, and translate "x=null"/"x <> null" into "x is null" / "x is not null"
behind the scenes?
</Digression>

Quote
>Is there some other solution to this problem?

I'm not aware of any.
If there exists a solution to this, it can probably be found in Joe Celko's
"SQL for Smarties" (I don't remember the exact title and may have spelled
his name wrong), but I've sometimes found solutions in articles written by
him, solutions to problems which I was surprised that there existed clean
solutions for in SQL at all.

Cheers,

Jarle stabell
Digital Logikk AS

----
If UWantHelp(And/Or HTML)FilesDocumentingYourDelphiCode Then
   Goto http://www.time2help.com ("JavaDoc" for Delphi)

Re:Tricky Theoretical SQL Problem


I would use parametrized queries, which need being parsed (prepared) only
once.

Re:Tricky Theoretical SQL Problem


It sounds like you need to do some analysis of your data files and indexes.
You could have a main stored procedure call other stored procedures based on
if a parameter has a value in it *instead* of checking against null values.
You will need to find out what kind of selectivity your existing indexes
have and write your code to choose the best stored procedure/index
combination.
Also you might try using any special clauses that Sybase has added to its
transact-SQL. MS SQL allows the developer to specify an index in the query
Sybase -SQL might allow the same.

Wayne

Re:Tricky Theoretical SQL Problem


Can you force the SELECT in your stored procedure to explicitly use an index
(i.e. use optimizer hints)?

I don't know about Sybase, but this would work in MS SQL Server.
Example: SELECT *  FROM tablename (INDEX = indexname)   WHERE fieldname =...

Paul Reszczynski

Re:Tricky Theoretical SQL Problem


I think it would probably make more sense to determine which fields the user
is searching on and then construct the query on the fly and feed it into
TQuery, then ExecSQL.

Quote
Chris Cleveland wrote in message <3533C4ED.C6046...@genesee.net>...
>In my user interfaces I often give users the ability to select records
>based on one or more fields, for example, I might allow them to select
>from a list of names and addresses where the State='IL' or the Zip =
>'60614'. Normally, I just construct a where clause based on what the
>user has selected and pass the string to the SQL server (we use Sybase).

>The problem is that this approach is slow. The server has to parse the
>string each time. I would like to be able to create a stored procedure,
>pass a few parameters to it, and get the result set. It is possible to
>do this if you write your stored procedure as follows:

>if (@state =  null) and (@zip = null) then
>  select * from names
>else if (@state <>  null) and (@zip = null) then
>  select * from names where state = @state
>else if (@state =  null) and (@zip <> null) then
>  select * from names where zip = @zip
>etc..........

>If you have more than a few parameters you want to use, the approach is
>not feasible because you need a huge number of SQL statements. So I have
>tried another approach:

>select * from names
>where ((state = @state) or (@state = null)) and ((zip = @zip) or (@zip =
>null))

>This returns the right result set, but the Sybase compiler can't
>optimize it. Sybase always does a full table scan and ignores the
>indexes.

>Is there some other solution to this problem?

>Chris
>--
>Chris Cleveland
>Genesee Development Group, Inc.
>2000 North Racine Avenue, Suite 4100
>Chicago, Illinois  60614
>773.528.1700 voice, 773.528.8862 fax
>http://genesee.net
>cclevel...@genesee.net

Re:Tricky Theoretical SQL Problem


The problem is that you are trying to build a single query that
handles all the cases.  The best solution is to build your SQL
dynamically and execute it.  You can do that either within Delphi
as you already are doing it, or in your Sybase stored procedure,
and move the logic out of your program.

I am familiar with MSSQL, but the TSQL should be the same:
  /* If a zero-length string is the same as a Null, */
  /* then recode the parameter. */
  If @state is Null
    select @state = ""
  /* etc. */

  If @state > ""
    begin
      select @found = 1
      select @WhereClause = "where state = '" + @state + "'"
    end
  If @var2 > ""
    begin
      if @found = 1
        select @WhereClause = @WhereClause + " and "
      else
        select @WhereClause = @WhereClause + " where "
      select @found = 1
      select @WhereClause = "var2 = '" + @var2 + "'"
    end
  /* etc. */
  /* Then build your whole SQL */
  select @SQL = "select ... " + @WhereClause
  /* Run it! */
  execute(@SQL)

The downside is that you won't get any advantage to precompiled
SQL, but the query won't get confused by tests for Null!

Good luck!

 In article <3533C4ED.C6046...@genesee.net>,
  Chris Cleveland <cclevel...@genesee.net> wrote:

Quote

> In my user interfaces I often give users the ability to select records
> based on one or more fields, for example, I might allow them to select
> from a list of names and addresses where the State='IL' or the Zip =
> '60614'. Normally, I just construct a where clause based on what the
> user has selected and pass the string to the SQL server (we use Sybase).

> The problem is that this approach is slow. The server has to parse the
> string each time. I would like to be able to create a stored procedure,
> pass a few parameters to it, and get the result set. It is possible to
> do this if you write your stored procedure as follows:

[snip]

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/   Now offering spam-free web-based newsreading

Re:Tricky Theoretical SQL Problem


Actually your approach is not slow.  The problem is more likely that a user
provides criteria only on columns that are not indexed

In RAD Objects (http://www.radiantdata.com) we do lots of dynamic building
of where clause and lots of string parsing etc. with minimal performance hit
even on 486s.

We have used the objects against 300,000 record tables where a user issued a
like type of query which didn't use an index.  The only time really taken
was due to the server doing full table scans.

HTH.

Quote
Chris Cleveland wrote:
> In my user interfaces I often give users the ability to select records
> based on one or more fields, for example, I might allow them to select
> from a list of names and addresses where the State='IL' or the Zip =
> '60614'. Normally, I just construct a where clause based on what the
> user has selected and pass the string to the SQL server (we use Sybase).

> The problem is that this approach is slow. The server has to parse the
> string each time. I would like to be able to create a stored procedure,
> pass a few parameters to it, and get the result set. It is possible to
> do this if you write your stored procedure as follows:

> if (@state =  null) and (@zip = null) then
>   select * from names
> else if (@state <>  null) and (@zip = null) then
>   select * from names where state = @state
> else if (@state =  null) and (@zip <> null) then
>   select * from names where zip = @zip
> etc..........

> If you have more than a few parameters you want to use, the approach is
> not feasible because you need a huge number of SQL statements. So I have
> tried another approach:

> select * from names
> where ((state = @state) or (@state = null)) and ((zip = @zip) or (@zip =
> null))

> This returns the right result set, but the Sybase compiler can't
> optimize it. Sybase always does a full table scan and ignores the
> indexes.

> Is there some other solution to this problem?

> Chris
> --
> Chris Cleveland
> Genesee Development Group, Inc.
> 2000 North Racine Avenue, Suite 4100
> Chicago, Illinois  60614
> 773.528.1700 voice, 773.528.8862 fax
> http://genesee.net
> cclevel...@genesee.net

--
?t-

Other Threads