Solved

Best (Speed) changing order by for a lookup

Posted on 2001-07-26
4
234 Views
Last Modified: 2010-04-04
Here is the setup:

D5 Ent w/IP 3000 components, Interbase 6.01, Two Tiered application.

Here is the question:

I need to be able to have a table searched by any one of 6 different fields. I am using a TwwIncrementalSearch field and a TwwDBGrid. No problems yet. I have a TQuery, to a TDataSetProvider, to a TwwClientDataSet.

I can:

A) Change the SQL string in the query and close/open the CDS.

B) Create 6 IndexDefs for the CDS and swith them as needed, which would also require an close/open.

C) Not sure if it is possible, but put a variable in the SQL "order by :FieldName" or "order by @FieldName", but this also would require a close/open, and therefore a re-execution of my query.

These options all seem to slow, which would be OK in a smalish dataset, but in this case I'm looking for ideas as I expect this table to grow quickly.

Who has a good alternative, or which of the above to feel is the best alternative?
0
Comment
Question by:YodaMage
  • 3
4 Comments
 
LVL 27

Accepted Solution

by:
kretzschmar earned 133 total points
ID: 6325655
an order by clause could be a k.o. criteria of the performance on great datasets

i guess point b would be the best solution

meikl ;-)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6325666
alternative you could get rid of the incrementalsearch and realize your search with an independend query by just retrieving the unique id of the dataset with a dependend where-clause, if found some you could do a locate in the in the original dataset like

this sample with paradox using the dbdemos and the table employee

unit loc_with_q_u;

interface

uses
 Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
 StdCtrls, Db, DBTables, Grids, DBGrids;

type
 TForm1 = class(TForm)
   Edit1: TEdit;
   DBGrid1: TDBGrid;
   Query1: TQuery;  //holds select * from employee
   Query2: TQuery;  //holds a input depnded subselect
   DataSource1: TDataSource;
   Button1: TButton;
   Button2: TButton;
   procedure Edit1Change(Sender: TObject);
   procedure Button1Click(Sender: TObject);
   procedure Button2Click(Sender: TObject);
 private
   { Private declarations }
 public
   { Public declarations }
 end;

var
 Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.Edit1Change(Sender: TObject);
const sql = 'Select Empno from Employee where Upper(LastName) Like %s';
begin
 query2.close;  //lookup if there are records
 query2.sql.text := Format(sql,[QuotedStr(UpperCase(edit1.text+'%'))]);
 query2.Open;
 If query2.IsEmpty then
   ShowMessage('No Match Found')
 else  //if yes locate the first match
   query1.Locate('EmpNo',query2.FieldByName('EmpNo').Value,[]);
end;


//locate next match
procedure TForm1.Button1Click(Sender: TObject);
begin
 query2.Next;
 if Query2.Eof then
   ShowMessage('Last match')
 else
   query1.Locate('EmpNo',query2.FieldByName('EmpNo').Value,[]);
end;

//locate prior match
procedure TForm1.Button2Click(Sender: TObject);
begin
 query2.Prior;
 if Query2.Bof then
   ShowMessage('First match')
 else
   query1.Locate('EmpNo',query2.FieldByName('EmpNo').Value,[]);
end;

end.

meikl ;-)
0
 
LVL 4

Author Comment

by:YodaMage
ID: 6326695
Using a independend query becomes a pain because of the change of field queried constantly. I also have to change the display properites of the dataset anyway.

Is there no way to pass a TField variable into the SQL string?
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6326928
yes of course

like

const sql = 'Select Empno from Employee where %s Like %s';

query2.sql.text := Format(sql,[ATField.fieldName,QuotedStr(UpperCase(edit1.text+'%'))]);

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now