Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

Best (Speed) changing order by for a lookup

Posted on 2001-07-26
4
Medium Priority
?
266 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 532 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

649 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