BorlandTalk.com

Morpheus · Guest

I've written an app. using IBExpress components and i find it quite fast and
snappy.
The client requires fairly sophisticated sorting and searching capability so
I have provided what is required. Only problem, is, when applying an order
by clause, and then repositioning the record pointer in the datset after
closing and opening the dataset is painfully slow.
I find this is also the case when resorting a dataset ascending or
descending.
I am using Locate to do this BTW. Is there any alternative to Locate to
quickly position a record pointer in a data set?
my code would typically look something like this:

// Procedure for applying a parameterised sort to a dataset and to
re-position the record pointer after applying the roder by clause.

procedure TfrmQuotation.sbApplyClick(Sender: TObject);
var sOrderByClause, sOrderByLabel: string;
i,j, iQuoteID: Integer;
begin
try
Screen.Cursor := crHourGlass;
for i := 0 to lbAssign.Items.Count-1 do begin
for j := 0 to dbgGrid.Columns.Count - 1 do begin
if lbAssign.Items[i]=dbgGrid.Columns[j].Title.Caption then begin
sOrderByClause := sOrderByClause+dbgGrid.Columns[j].FieldName;
sOrderByLabel := sOrderByLabel+dbgGrid.Columns[j].Title.Caption;
if i < lbAssign.Items.Count-1 then begin
sOrderByClause := sOrderByClause+',';
sOrderByLabel := sOrderByLabel+', ';
end;
end;
end;
end;
if sbDesc.Down then begin
sOrderByClause := sOrderByClause+' DESCENDING';
sOrderByLabel := sOrderByLabel+' DESCENDING';
end else begin
sOrderByClause := sOrderByClause+' ASCENDING';
sOrderByLabel := sOrderByLabel+' ASCENDING';
end;
try
with (dbgGrid.DataSource.DataSet as TIBDataSet) do begin
iQuoteID := FieldByName('QuoteID').AsInteger;
Close;
SelectSQL.Strings[SelectSQL.Count-1] := 'order by '+sOrderByClause;
Open;
// If I comment out this piece of code, the refresh is lightning fast!!
//-----------------------------
if not Locate('QuoteID',iQuoteID,[]) then
MessageDlg('Error re-locating Quote record.',mtError,[mbOK],0)
else
dbgGrid.SetFocus;
// -----------------------------
end;
lblSortOrder.Caption := 'Sort Order: '+sOrderByLabel;
eFindLabel.Caption := 'Find Quotation by '+lbAssign.Items[0];
for j := 0 to dbgGrid.Columns.Count - 1 do
if dbgGrid.Columns[j].Title.Caption = lbAssign.Items[0] then
dbgGrid.Tag := j;
except
on E:Exception do
MessageDlg('Error re-ordering '+GridLabel.Caption+' view:
'+E.Message,
mtError,[mbOK],0);
end;
finally
Screen.Cursor := crDefault;
end;
end;
I also have a find function which uses Locate to position the record pointer
based on a search argument, and this is of course also painfully slow the
depper into the dataset that it searches. For e.g. if the dataset is sorted
on Name, and the current record Name column starts with A and I search for
AD it is lightning fast. If I search for Z it is impossibly slow.
short of scoping the dataset based in a parameter, I don't know what el;se
to do.
Just for the record, the search is in qQuotes (Master), which moves a record
pointer in qQuoteDetail (Detail).qQuote's
SQL statement looks like this:
select
Q.QuoteID,
Q.CustID,
C.NAME,
Q.QUOTENO,
Q.PARTNUMBER,
Q.COMPONENT,
Q.JOBNO
from QUOTE Q
inner join Customer C on (Q.CustID=C.CustID)
order by C.Name ASCENDING

qQuoteDetail's query looks like this:
select
Q.QUOTEID,
Q.CustID,
C.Contact,
C.Name,
Q.QUOTENO,
Q.CUSTREF,
Q.QUOTEDATE,
Q.COMPONENT,
Q.PARTNUMBER,
Q.PARTDENSITY,
Q.DRAWINGREF,
Q.MATERIALSPECIFIED,
M.MaterialCost,
Q.STANDARDSPECIFICATION,
Q.PARTSELLINGPRICE,
Q.TOOLPRICE,
Q.TOOLLEADTIME,
Q.SAMPLELEADTIME,
Q.PRODLEADTIME,
Q.QTYANNUAL,
Q.QTYMINIMUMBATCH,
Q.BATCHMIXWEIGHT,
Q.COMMENTS,
Q.SCRAPALLOWANCE,
Q.MAINTENANCECENTSEACH,
Q.TOOLINGMAINTENANCE,
Q.DELIVERYCHARGES,
Q.PROFITMARGIN,
Q.AGENTSCOMMISSION,
Q.CONTRIBUTION,
Q.JOBNO,
Q.FIXEDRATE
from Quote Q
inner join Customer C on (Q.CustID=C.CustID)
left outer join MaterialCostEach M on (Q.QuoteID=M.QuoteID)
where Q.QuoteID=:QuoteID

MaterialCostEach is a view which has the following statement:
CREATE VIEW MATERIALCOSTEACH (
QUOTEID,
MATERIALCOST
) AS SELECT
QuoteID,
Sum(Cost*Weight)
from QuoteMaterial
group by QuoteID

Any help with this would be most appreciated.

Regards,
Morpheus

Bill Todd (TeamB) · Guest

How many records are returned by your SQL statement?

You should consider using ClientDataSets so you can resort the data in
memory. It is very fast. See
http://www.dbginc.com/tech_pprs/ibxcds.html

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

Wayne Niddery [TeamB] · Guest

Morpheus wrote:

Morpheus · Guest

Hi Bill,
Thanks very much. I've switched to IBClientDataSet and it works like a
charm. It is now lightning fast. Much obliged!!
Regards,
Morpheus

"Bill Todd (TeamB)" <no (AT) no (DOT) com> wrote

Morpheus · Guest

Hi Wayne,
I've taken Bill's advice and switched to an IBClientDataSet and am fetching
100 rows at a time. It works perfectly and is now MUCH faster. Thanks for
the response, though.
Regards,
Morpheus

"Wayne Niddery [TeamB]" <wniddery (AT) chaffaci (DOT) on.ca> wrote

Jeff Overcash (TeamB) · Guest

"Morpheus" <morpheusNOPSAM (AT) redpoint (DOT) co.za> wrote:

Morpheus · Guest

Bill,
I've browsed through your article and have a question. In the article you
refer to using TClientDataset and tDataSetProvider. I have used a TIBClient
Dataset instead, and it works fine. You further note that one must include
the MIDAS.DLL in your deployment. Does this also apply if you are using
TIBCLientDataSet rather than TClientDataset and tDataSetProvider?
Regards,
Norman
"Bill Todd (TeamB)" <no (AT) no (DOT) com> wrote

Morpheus · Guest

Deprecated?? Really?? Where do you see that TIBCLientDataSet is deprecated?
I am using D7 and do not see ANYWHERE that it is a deprecated component. Or
perhaps you know something that I don't know?
And besides, I do not take kindly to being scolded like a naughty little
boy!!
Bill, if you see this I'd appreciate your comments, seeing as how I used
your recommendation as a point of departure.
Morpheus

"Jeff Overcash (TeamB)" <jeffovercash (AT) mindspring (DOT) com> wrote

Jeff Overcash (TeamB) · Guest

"Morpheus" <morpheusNOPSAM (AT) redpoint (DOT) co.za> wrote:

Bill Todd (TeamB) · Guest

Jeff is the author of IBX. All of the ??ClientDataSet components were
deprecated in D7. IIRC this is in the readme.

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

Morpheus · Guest

I bow to the Master. You are all knowing, Master Yoda. I humbly beg
forgiveness for doubting your word. I was wrong!

I've implemented CDS with a DataSetProvider and an IBQuery. I must say ti si
quite a bit more complex to use than the TIBClientDataSet, with which i had
direct access to the SQL. Using CDS means my ancestor form containing the
generic code for grid ordering breaks.
What was the reason for deprecating the TIBLCientDataSet, as it appers to be
an eloquent solution?

Morpheus

"Jeff Overcash (TeamB)" <jeffovercash (AT) mindspring (DOT) com> wrote