Board index » delphi » Interbase, dbExpress and Large Number of Tables in Database

Interbase, dbExpress and Large Number of Tables in Database

I originally posted the following on December 15, 2001.  I was hoping that
updated
drivers for dbExpress and a Delphi update would solve the problem, but that
has not been the case.
Am I doing something wrong?  Is this just not considered a problem by anyone
else?
I have 2 databases.  One has about 40 tables.  The other has 400 tables.
When I access a query in the 40 table database, the queries open quicky
through TSQLDataSet-TDataSetProvider-TClientDataSet.  However it takes up to
50-60 seconds to open  a query in 400 table database.

I read a post back in September that this was because the query was
attempting to bring back the metadata for ALL tables in the database.  If I
turn the NoMetaData property to true then the queries open quickly in the
400 table database.  But I need metadata on an individual table basis.

If anyone from Borland reads this, can you address when this will be fixed?

Is there a workaround?

From the Delphi help on NoMetaData:

Use NoMetadata to turn off the fetching of metadata when it is not needed.
Setting NoMetadata to True can improve performance if the dataset has a lot
of indexes, because they do not need to be fetched from the server. However,
it will also slow any attempts to update data using a client dataset that is
linked via a dataset provider. NoMetadata is intended primarily for datasets
that are used in a read-only manner.

NoMetadata must be False to perform any operations that require an index.
These include setting up master/detail relationships, updating data from a
client dataset when the provider's UpdateMode is not upWhereAll, and delayed
fetching of BLOB data.

I have all of the conditions in the above paragraph except for the delayed
fetching of BLOB data.

As far as I can tell, this only happens in Interbase.  I've also tried DB2
v7, Oracle 8i, Adaptive Server Anywhere 7 (via 3rd party dbExpress ODBC
driver), and SQL Server 2000 (via 3rd party dbExpress ODBC driver).  They DO
NOT have the above problem as far as I can tell.  Why is Interbase, a
Borland product, the only one with a dbExpress speed problem???

Thanks,

Al Willis

 

Re:Interbase, dbExpress and Large Number of Tables in Database


Setting NoMetadata = False will fetch index info only for the table in use.
Set Nometadata= True when you intent to only fetch data. If you want to
resolve back changes we need INDEX info. to generate SQL with better
selectivity. Will have to investigate the performance with INTERBASE.

T.Ramesh.

Quote
"Al Willis" <alwil...@pdq.net> wrote in message news:3cd06493$1_2@dnews...
> I originally posted the following on December 15, 2001.  I was hoping that
> updated
> drivers for dbExpress and a Delphi update would solve the problem, but
that
> has not been the case.
> Am I doing something wrong?  Is this just not considered a problem by
anyone
> else?
> I have 2 databases.  One has about 40 tables.  The other has 400 tables.
> When I access a query in the 40 table database, the queries open quicky
> through TSQLDataSet-TDataSetProvider-TClientDataSet.  However it takes up
to
> 50-60 seconds to open  a query in 400 table database.

> I read a post back in September that this was because the query was
> attempting to bring back the metadata for ALL tables in the database.  If
I
> turn the NoMetaData property to true then the queries open quickly in the
> 400 table database.  But I need metadata on an individual table basis.

> If anyone from Borland reads this, can you address when this will be
fixed?

> Is there a workaround?

> From the Delphi help on NoMetaData:

> Use NoMetadata to turn off the fetching of metadata when it is not needed.
> Setting NoMetadata to True can improve performance if the dataset has a
lot
> of indexes, because they do not need to be fetched from the server.
However,
> it will also slow any attempts to update data using a client dataset that
is
> linked via a dataset provider. NoMetadata is intended primarily for
datasets
> that are used in a read-only manner.

> NoMetadata must be False to perform any operations that require an index.
> These include setting up master/detail relationships, updating data from a
> client dataset when the provider's UpdateMode is not upWhereAll, and
delayed
> fetching of BLOB data.

> I have all of the conditions in the above paragraph except for the delayed
> fetching of BLOB data.

> As far as I can tell, this only happens in Interbase.  I've also tried DB2
> v7, Oracle 8i, Adaptive Server Anywhere 7 (via 3rd party dbExpress ODBC
> driver), and SQL Server 2000 (via 3rd party dbExpress ODBC driver).  They
DO
> NOT have the above problem as far as I can tell.  Why is Interbase, a
> Borland product, the only one with a dbExpress speed problem???

> Thanks,

> Al Willis

Re:Interbase, dbExpress and Large Number of Tables in Database


Ramesh,

I'm confused.  Are you saying that there is another reason for the slow
opening of a table in a database that has about 400 tables in it?

Is the Delphi help documentation that I referenced wrong?  I have not tried
to open all of my tables in the application with NoMetaData set to true
because of the effort needed to change all of the propeties on the affected
components.  But I will do so if that's all that it takes.

Will master/client arrangements using nested datasets work with NoMetaData
set to true?

Will TClientDataSet.ApplyUpdates work when the provider's UpdateMode is not
upWhereAll with NoMetaData set to true?

Thanks,

Al

Quote
"Ramesh Theivendran" <rtheivend...@borland.com> wrote in message

news:3cd06d8d$1_1@dnews...
Quote
> Setting NoMetadata = False will fetch index info only for the table in
use.
> Set Nometadata= True when you intent to only fetch data. If you want to
> resolve back changes we need INDEX info. to generate SQL with better
> selectivity. Will have to investigate the performance with INTERBASE.

> T.Ramesh.

Re:Interbase, dbExpress and Large Number of Tables in Database


This is using Oracle, but should work the same with Interbase

Quote
Al Willis wrote:
> Ramesh,

> I'm confused.  Are you saying that there is another reason for the slow
> opening of a table in a database that has about 400 tables in it?

> Is the Delphi help documentation that I referenced wrong?  I have not tried
> to open all of my tables in the application with NoMetaData set to true
> because of the effort needed to change all of the propeties on the affected
> components.  But I will do so if that's all that it takes.

> Will master/client arrangements using nested datasets work with NoMetaData
> set to true?

I do it all the time.

Quote

> Will TClientDataSet.ApplyUpdates work when the provider's UpdateMode is not
> upWhereAll with NoMetaData set to true?

Yes.  You have to set the Provider Flags on the TFields of your source
TDataset (not the TClientDataset).  Set the KeyFields and Where flags
for just the primary key fields.  Unselect Where for all other fields.
Works great!!!

--
Thomas Miller
Delphi Client/Server Certified Developer
BSS Accounting & Distribution Software
BSS Enterprise Accounting FrameWork

http://www.bss-software.com

Re:Interbase, dbExpress and Large Number of Tables in Database


Al and Thomas:

Quote
> This is using Oracle, but should work the same with Interbase

It works the same with Interbase.

NoMetadata := false can't be used. The performance is more than very
bad.!!!!!!!!!! THIS MUST BE FIXED FOR INTERBASE.
Do the other drivers work better? I want to know.

Quote
>Yes.  You have to set the Provider Flags on the TFields of your source
>TDataset (not the TClientDataset).  Set the KeyFields and Where flags
>for just the primary key fields.  Unselect Where for all other fields.
>Works great!!!

I don't use persistent fields in the source dataset. I use
DatasetProvider.OnUpdateData to set the provider flags.
For example, the two fields CUENTA and CUENTA_TIPO are the result of a join
with other table (for viewing) and they must not be applied. Setting de
flags in the clientDataset fields at design time doesn't work for me.(I
don't know why)

procedure TFormCobranzas.DSPAsientosRenglonesUpdateData(Sender: TObject;
  DataSet: TCustomClientDataSet);
begin
  if dataset.FindField('cuenta') <> nil then        // Working with nested
datasets. I need to know wich dataset is this.
  begin
    dataset.Fields.FieldByName('CUENTA').ProviderFlags := [];
    dataset.Fields.FieldByName('CTA_TIPO').ProviderFlags := [];
  end;
end;

Al , be strong, set noMetadata to True!!!!!

Re:Interbase, dbExpress and Large Number of Tables in Database


Because the Provider picks up the flags from the source, not the
TClientDataset.  Just as you are setting the Update and Where
flags to false for the Read Only fields, you need to set the Where
flag to false for the non Key Fields and Key Field = True for the
Key fields.

Quote
Hernan Alizieri wrote:
> Al and Thomas:

>>This is using Oracle, but should work the same with Interbase

> It works the same with Interbase.

> NoMetadata := false can't be used. The performance is more than very
> bad.!!!!!!!!!! THIS MUST BE FIXED FOR INTERBASE.
> Do the other drivers work better? I want to know.

>>Yes.  You have to set the Provider Flags on the TFields of your source
>>TDataset (not the TClientDataset).  Set the KeyFields and Where flags
>>for just the primary key fields.  Unselect Where for all other fields.
>>Works great!!!

> I don't use persistent fields in the source dataset. I use
> DatasetProvider.OnUpdateData to set the provider flags.
> For example, the two fields CUENTA and CUENTA_TIPO are the result of a join
> with other table (for viewing) and they must not be applied. Setting de
> flags in the clientDataset fields at design time doesn't work for me.(I
> don't know why)

> procedure TFormCobranzas.DSPAsientosRenglonesUpdateData(Sender: TObject;
>   DataSet: TCustomClientDataSet);
> begin
>   if dataset.FindField('cuenta') <> nil then        // Working with nested
> datasets. I need to know wich dataset is this.
>   begin
>     dataset.Fields.FieldByName('CUENTA').ProviderFlags := [];
>     dataset.Fields.FieldByName('CTA_TIPO').ProviderFlags := [];
>   end;
> end;

> Al , be strong, set noMetadata to True!!!!!

--
Thomas Miller
Delphi Client/Server Certified Developer
BSS Accounting & Distribution Software
BSS Enterprise Accounting FrameWork

http://www.bss-software.com

Re:Interbase, dbExpress and Large Number of Tables in Database


"Thomas Miller" <tmil...@bss-software.com> escribi en el mensaje
news:3CD2D85B.6000808@bss-software.com...

Quote
> Because the Provider picks up the flags from the source, not the
> TClientDataset.  Just as you are setting the Update and Where
> flags to false for the Read Only fields, you need to set the Where
> flag to false for the non Key Fields and Key Field = True for the
> Key fields.

Thank you, Thomas.

At this moment I'm working with UpdateWhereAll option, and I'm not having
any problem.
But soon I'll tune up this.

If I want to test concurrent changes by other users in some nonkey fields,
then I need to set UpdateWhereChanged option and setting the Where flag to
true in these fields. Is it OK?
--
Hernn Alizieri
www.che-botija.com.ar

Re:Interbase, dbExpress and Large Number of Tables in Database


I went back through my application and set NoMetaData = True everywhere.  I
also set the provider flags at runtime.  I set the DataSetProvider to
upWhereKeyOnly.  I use nested datasets without persistent fields.

Everything is working GREAT so far.

Thanks all.

Al

Quote
"Hernan Alizieri" <halizi...@febicom.com.ar> wrote in message

news:3cd2e203_1@dnews...
Quote

> "Thomas Miller" <tmil...@bss-software.com> escribi en el mensaje
> news:3CD2D85B.6000808@bss-software.com...
> > Because the Provider picks up the flags from the source, not the
> > TClientDataset.  Just as you are setting the Update and Where
> > flags to false for the Read Only fields, you need to set the Where
> > flag to false for the non Key Fields and Key Field = True for the
> > Key fields.

> Thank you, Thomas.

> At this moment I'm working with UpdateWhereAll option, and I'm not having
> any problem.
> But soon I'll tune up this.

> If I want to test concurrent changes by other users in some nonkey fields,
> then I need to set UpdateWhereChanged option and setting the Where flag to
> true in these fields. Is it OK?
> --
> Hernn Alizieri
> www.che-botija.com.ar

Other Threads