Solved

Best (Speed) changing order by for a lookup

Posted on 2001-07-26
4
241 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
code issue 8 186
how to center only a line in richedit? 4 69
delphi parse string to params 3 156
Delphi and Access based Enumeration 9 78
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…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

726 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