• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

Best (Speed) changing order by for a lookup

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
YodaMage
Asked:
YodaMage
  • 3
1 Solution
 
kretzschmarCommented:
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
 
kretzschmarCommented:
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
 
YodaMageAuthor Commented:
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
 
kretzschmarCommented:
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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now