2

I have a tquery (going thru BDE or BDE emulating component) that has been used to select either a single record or all records.

Traditionally this has been done as such:

select * from clients where (clientid = :clientid or :clientid = -1)

And then they would put a -1 in the field when they wanted the query to return all values. Going through this code though, I have discovered that when they have done this the query does not use proper indexing for the table and only does a natural read.

Is there a best practices method for achieving this? Perhaps a way to tell a parameter to return all values, or must the script be modified to remove the where clause entirely when all values are desired?

Edit: This is Delphi 7, by the way (And going against Firebird 1.5 sorry for leaving that out)

Improve this question
10
  • Does the BDE allow you to pass through query optimizer hints so it will always use the index? Commented Sep 14, 2012 at 0:13
  • I am unsure, but I will investigate, thanks. Commented Sep 14, 2012 at 0:17
  • Syntax for SQL Server is select * from clients with (index(idx_name)) where (clientid = :clientid or :clientid = -1). Commented Sep 14, 2012 at 0:19
  • @JamesL: I doubt it. I think the BDE has been deprecated so long that optimizer hints didn't exist when it was updated last. :-) It actually uses a fairly generic SQL dialect that it translates into more specific DBMS terminology, but I don't think it's drivers were that advanced. Commented Sep 14, 2012 at 3:39
  • @KenWhite AFAIR there are to cases. Either BDE uses its own SQL (if used over ISAM tables like paradox and dbf, or if used over different aliases like querying both MS SQL server and Firebird server in one SELECT). Then it is limited et all. Or BDE is just a tunnel to real SQL server, like Firebird or MS SQL, then BDE SQL layer is just bypassed and you can pass any hint or whatever the server supports. However the topicstarter told nothing about back-end so i suspect he uses Paradox or DBF ISAM tables. Commented Sep 14, 2012 at 8:08

5 Answers 5

Reset to default
2

As you use deprecated BDE, that may be one more reason to migrate from BDE to 3d party solutions. AnyDAC (UniDAC, probably others too. Most are commercial libraries) has macros, which allow to dynamically change a SQL command text, depending on the macro values. So, your query may be written:

ADQuery1.SQL.Text := 'select * from clients {IF &clientid} where clientid = &clientid {FI}';

if clientid >= 0 then
  // to get a single record
  ADQuery1.Macros[0].AsInteger := clientid
else
  // to get all records
  ADQuery1.Macros[0].Clear;

ADQuery1.Open;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

For the queries with "optional" parameters I always use ISNULL (MSSQL, or NVL Oracle), ie.

SELECT * FROM clients WHERE ISNULL(:clientid, clientid) = clientid

Setting the parameter to NULL then selects all records. You also have to take care of NULL values in the table fields because NULL <> NULL. This you can overcome with a slight modification:

SELECT * FROM clients WHERE COALESCE(:clientid, clientid, -1) = ISNULL(clientid, -1)
Improve this answer

1 Comment

As result of that an indexes will be not used.
0

I would use this:

SELECT * FROM CLIENTS WHERE clientid = :clientid or :clientid IS NULL
Improve this answer

Comments

0

Using two queries is best:

if (clientid <> -1) then
begin
  DBComp.SQL.Text := 'select * from clients where (clientid = :clientid)';
  DBComp.ParamByName('clientid').Value := clientid;
end else
begin
  DBComp.SQL.Text := 'select * from clients';
end;
DBComp.Open;
...

Alternatively:

DBComp.SQL.BeginUpdate;
try
  DBComp.SQL.Clear;
  DBComp.SQL.Add('select * from clients');
  if (clientid <> -1) then
    DBComp.SQL.Add('where (clientid = :clientid)');
finally
  DBComp.SQL.EndUpdate;
end;

if (clientid <> -1) then
  DBComp.ParamByName('clientid').Value := clientid;

DBComp.Open;
...
Improve this answer

6 Comments

In SQL terms that would be UNION ALL
@Arioch'The: Un... No, it isn't. That's an if statement that executes one query or the other. A UNION or UNION ALL would execute them both and, well, UNION the results.
@KenWhite sure, and then one statement or another would be turned off by the condition, yet they would still be different statements able to have different plans. And that IF is either pseudo-code or Embedded-SQL or Stored Procedure maybe, but not a regular DML SQL. PS. Are you nitpicking ? that was so obvious to me... This is just a hint how to re-formulate it in pure DML SQL, not a copy-paste solution (i dislike those, you know)
@Arioch'The: Not nitpicking. Correcting. The term UNION in SQL has a specific meaning, and this isn't an example of it. This snippet has absolutely nothing to do with a UNION, and calling it one can be misleading to someone in the future who doesn't understand the difference.
yes, my example is executing two separate SQL statements depending on the parameter value. The if is not in the SQL itself, it is in the app code that is executing the statements. I have updated the example to make that clearer.
|
0

Remy's answer may be re-formulated as single query. It may be better, if you gonna prepare it once and then re-open multiple times.

select * from clients where (clientid = :clientid)
                        and (:clientid is not null)
  UNION ALL
select * from clients where (:clientid is null)

This just aggregates two distinct queries (with same results vector) together. And condition just turns one of those off. Using would be like that:

 DBComp.Prepare.
   ...
 DBComp.Close;
 DBComp.ParamByName('clientid').Value := clientid;
 DBComp.Open;
   ...
 DBComp.Close;
 DBComp.ParamByName('clientid').Clear;
 DBComp.Open;

However this query would rely on SQL Server optimizer capability to extract query invariant (:clientid is [not] null) and enable/disable query completely. But well, your original query depends upon that too.

Why still use obsolete FB 1.5 ? Won't FB 2.5.2 work better there ? I think your original query is formulated poorly. 

  select * from clients where (:clientid = -1) or  ((clientid = :clientid) and (:clientid <> -1))

would probably be easier on SQL Server optimizer. Yet i think FB could do better job there. Try to download later FB, and run your query in it, using IDEs like IBExpert or FlameRobin. Re-arranging parenthesis and changing -1 to NULL are obvious ideas to try.

Using BDE is fragile now. It is not very fast, limiting in datatypes and connectivity (no FB/IB Events for example). And would have all sorts of compatibility problems with Vista/Win7 and Win64. If FB/IB is your server of choice, consider switching to some modern component set:

Also it would be good thing to show the table and indices definition and selectivity of those indices.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.