Solved

Best (Speed) changing order by for a lookup

Posted on 2001-07-26
4
231 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
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…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
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…

747 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

12 Experts available now in Live!

Get 1:1 Help Now