 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Lasse
Guest
|
 Posted: Tue Apr 06, 2004 3:05 pm Post subject: How to efficiently Locate records in large table |
 |
|
Hi,
I have SQLquery <- DataSetprovider <- ClientDataSet
From a large table I want to locate records one at the time and have it
"available" in the clientdataset to do something and then save.
I want to do that without retrieving all records which happens if I open the
clientdataset.
How can I do that ?
If I simply open the clientdataset it retreives all records and I can locate
but the open is very expensive for both client and server.
Lasse
using D6 , MS SQLServer
|
|
| Back to top |
|
 |
Lasse
Guest
|
| Posted: Tue Apr 06, 2004 3:15 pm Post subject: Re: How to efficiently Locate records in large table |
|
|
If I set the ClientDataSet.PacketRecord = 100 then only 100 record are
retreived on open, does a Locate or FindKey only look in this 100 records or
does it somehow searches the 20000 records that is in the table?
Lasse
"Lasse" <NOSPAMlars-ake.makiaho (AT) telia (DOT) com> skrev i meddelandet
news:4072c72d$1 (AT) newsgroups (DOT) borland.com...
| Quote: | Hi,
I have SQLquery <- DataSetprovider <- ClientDataSet
From a large table I want to locate records one at the time and have it
"available" in the clientdataset to do something and then save.
I want to do that without retrieving all records which happens if I open
the
clientdataset.
How can I do that ?
If I simply open the clientdataset it retreives all records and I can
locate
but the open is very expensive for both client and server.
Lasse
using D6 , MS SQLServer
|
|
|
| Back to top |
|
|
Dave Rowntree
Guest
|
| Posted: Tue Apr 06, 2004 3:31 pm Post subject: Re: How to efficiently Locate records in large table |
|
|
"Lasse" <NOSPAMlars-ake.makiaho (AT) telia (DOT) com> wrote:
| Quote: | If I set the ClientDataSet.PacketRecord = 100 then only 100 record are
retreived on open,
|
Indeed, but be aware that using CDS.PacketRecords > 0 makes the
appserver stateful.
| Quote: | does a Locate or FindKey only look in this 100 records or
does it somehow searches the 20000 records that is in the table?
|
Depends on the CDS.FetchOnDemand setting.
With CDS.FetchOnDemand = True (the default setting) *all* the records
are fetched into the CDS when you call CDS.Locate, or any CDS method
that locates or filters records.
With CDS.FetchOnDemand = False *only* the records already existing in
the CDS are searched.
Have a look in help at CDS.FetchOnDemaand for more info on this
setting.
It is normal practice in multi-tier design to open a form showing no
records until the user has supplied some search criteria. You then use
the search criteria (normally in an SQL WHERE clause) to limit the
number of records being requested from the DB.
--
Dave Rowntree
|
|
| Back to top |
|
|
Wayne Niddery [TeamB]
Guest
|
| Posted: Wed Apr 07, 2004 1:08 am Post subject: Re: How to efficiently Locate records in large table |
|
|
Lasse wrote:
| Quote: | I have SQLquery <- DataSetprovider <- ClientDataSet
From a large table I want to locate records one at the time and have
it "available" in the clientdataset to do something and then save.
I want to do that without retrieving all records which happens if I
open the clientdataset.
How can I do that ?
|
You can pass parameters via the ClientDataset and, on your app server,
generate an appropriate Where clause for the SQL. If you only need one
record, then be sure you only ask the database for one record.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: http://www.logicfundamentals.com/RADBooks.html
"True peace is not the absence of tension, but the presence of
justice." - Martin Luther King, Jr.
|
|
| Back to top |
|
|
Lasse
Guest
|
| Posted: Wed Apr 07, 2004 5:13 am Post subject: Re: How to efficiently Locate records in large table |
|
|
How do I pass parameters via clientdataset, this sound interesting, can you
perhaps point me to an example?
Where should I generate the where clause. is it the DataSetprovider that
should do that
I have the SQLquery <- DataSetprovider <- ClientDataSet in a fat application
but I guess that doesn't matter.
"Wayne Niddery [TeamB]"
news:407354ba$1 (AT) newsgroups (DOT) borland.com...
| Quote: | Lasse wrote:
I have SQLquery <- DataSetprovider <- ClientDataSet
From a large table I want to locate records one at the time and have
it "available" in the clientdataset to do something and then save.
I want to do that without retrieving all records which happens if I
open the clientdataset.
How can I do that ?
You can pass parameters via the ClientDataset and, on your app server,
generate an appropriate Where clause for the SQL. If you only need one
record, then be sure you only ask the database for one record.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: http://www.logicfundamentals.com/RADBooks.html
"True peace is not the absence of tension, but the presence of
justice." - Martin Luther King, Jr.
|
|
|
| Back to top |
|
|
Wayne Niddery [TeamB]
Guest
|
| Posted: Wed Apr 07, 2004 3:35 pm Post subject: Re: How to efficiently Locate records in large table |
|
|
Lasse wrote:
| Quote: | How do I pass parameters via clientdataset, this sound interesting,
can you perhaps point me to an example?
Where should I generate the where clause. is it the DataSetprovider
that should do that
I have the SQLquery <- DataSetprovider <- ClientDataSet in a fat
application but I guess that doesn't matter.
|
There are two ways. If the parameters are constant, e.g. it will always be
"... where lastname = :lastname" then you can set the SQL in the SQLQuery
with this where clause and using a parameter as shown. In the ClientDataset,
rightclick and select Fetch Params. You can now set the parameter values at
runtime in the CDS:
procedure TForm1.Button1Click(Sender: TObject);
begin
ClientDataset1.Params[0].AsString := Edit1.Text;
ClientDataset1.Refresh;
end;
If the parameters are going to be variable and you need to actually generate
a where clause each time then you can use the BeforeGetRecords event on both
the CDS and the DatasetProvider:
(This example shows passing two parameter strings in order to also show how
to use a variant array)
procedure TForm1.ClientDataSet1BeforeGetRecords(Sender: TObject;
var OwnerData: OleVariant);
var s: string;
begin
if RadioButton1.Checked then
s := 'CUSTOMER starting with ' + QuotedStr(Edit1.Text)
else
s := 'CONTACT_LAST starting with ' + QuotedStr(Edit1.Text);
OwnerData := VarArrayOf([s, 'COUNTRY starting with ' +
QuotedStr(Edit2.Text)]);
end;
procedure TForm1.DataSetProvider1BeforeGetRecords(Sender: TObject;
var OwnerData: OleVariant);
var i: integer;
begin
IBQuery1.SQL.Text := 'select * from customer where ';
for i := 0 to VarArrayHighBound(OwnerData, 1) do
begin
if i > 0 then
IBQuery1.SQL.Add(' and ');
IBQuery1.SQL.Add(OwnerData[i]);
end;
end;
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: http://www.logicfundamentals.com/RADBooks.html
"True peace is not the absence of tension, but the presence of
justice." - Martin Luther King, Jr.
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
