Marius0188
asked on
Delphi TIBDataset component very slow over LAN
Hi Friends,
I have a Delphi application using an Interbase database over a LAN.
All the pc are high spec Pentium 4's with 1Gbs LAN interface cards.
My database set up looks like this:
TIBDataset -> TIBTransaction -> TDataSource.
(Hope this is graphical enough :) )
Everything is working fine but the speed is tremendous slow.
When I connect a DBGrid to the Customer table's Datasource and scroll
through the records by clicking with the mouse on a row in the DBGrid,
it takes about 2 - 4 seconds to select the row.
Is there any good reason why this is so slow?
Please advice.
Thanks in advance!
I have a Delphi application using an Interbase database over a LAN.
All the pc are high spec Pentium 4's with 1Gbs LAN interface cards.
My database set up looks like this:
TIBDataset -> TIBTransaction -> TDataSource.
(Hope this is graphical enough :) )
Everything is working fine but the speed is tremendous slow.
When I connect a DBGrid to the Customer table's Datasource and scroll
through the records by clicking with the mouse on a row in the DBGrid,
it takes about 2 - 4 seconds to select the row.
Is there any good reason why this is so slow?
Please advice.
Thanks in advance!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
My own opinion is that you should always try and avoid useing table and query components, the best method for returning data is to use a stored procedure both to return data and to do updates,
Why? well using sp's means that only the data that you want is returned to the application rather than the entire table. Take for instance a Client table, this might have many fields from name, address, registration date etc, etc, etc. If you use a table component then evry field will be returned across the network, normally you only want a subset of those fields, you could use a query to return taht subset, but there is inherent overhead in decoding, interpreting and processing that qurey whereas using an sp this query is already stored within the db and is optimised.
Also use sp's for doing updates, is say you just wanted to update a date in a record, it is much more efficient to just pass the new date to a query than to edit a reord locally, which would have to pass bakc the entire record for the update.
regards
Why? well using sp's means that only the data that you want is returned to the application rather than the entire table. Take for instance a Client table, this might have many fields from name, address, registration date etc, etc, etc. If you use a table component then evry field will be returned across the network, normally you only want a subset of those fields, you could use a query to return taht subset, but there is inherent overhead in decoding, interpreting and processing that qurey whereas using an sp this query is already stored within the db and is optimised.
Also use sp's for doing updates, is say you just wanted to update a date in a record, it is much more efficient to just pass the new date to a query than to edit a reord locally, which would have to pass bakc the entire record for the update.
regards
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
1) Where will I find good documentation and/or tutorials on Stored Procedures?
If I understand correctly I can do SELECTS / INSERTS / UPDATES / DELETES by means of Stored Procedures?
2) I have never worked with SP before.
Does every table need a SP for each of the following (for example only):
1. Inserting record (SP1)
2. Deleting record (SP2)
3. Updating record (SP3)
3) Can you show my an example of SP for doing INSERT, please?
If I understand correctly I can do SELECTS / INSERTS / UPDATES / DELETES by means of Stored Procedures?
2) I have never worked with SP before.
Does every table need a SP for each of the following (for example only):
1. Inserting record (SP1)
2. Deleting record (SP2)
3. Updating record (SP3)
3) Can you show my an example of SP for doing INSERT, please?
>it takes about 2 - 4 seconds to select the row.
so the relocation takes time
causes:
- many data in the dataset and no PK or Index is used or contained in the dataset
- not stable network
meikl ;-)
so the relocation takes time
causes:
- many data in the dataset and no PK or Index is used or contained in the dataset
- not stable network
meikl ;-)
ASKER
>>so the relocation takes time
>>causes:
>>- many data in the dataset and no PK or Index is used or contained in the dataset
>>not stable network
Shouldn't take that long.
Just 4 records in the table.
Network is 100%. Tested.
PK Ok.
>>causes:
>>- many data in the dataset and no PK or Index is used or contained in the dataset
>>not stable network
Shouldn't take that long.
Just 4 records in the table.
Network is 100%. Tested.
PK Ok.
>Just 4 records in the table.
only four (in numbers: 4) records???
just a
select * from atable???
thats really too long
do you have any other tool for testing performance than delphi?
(maybe the problem is far on another side, maybe an installation and/or configuration problem)
meikl ;-)
only four (in numbers: 4) records???
just a
select * from atable???
thats really too long
do you have any other tool for testing performance than delphi?
(maybe the problem is far on another side, maybe an installation and/or configuration problem)
meikl ;-)
ASKER
It's already lots faster.
I did allot of
Dataset.Close & Dataset.Open 's in order the update the data thats displaying, so I realize
now this slowed it down allot. Even on only couple on records.
But I would like to do it the correct way.
So now it's more a matter of what is the best and most correct way.
I know there are always lots of correct methods.
But I have heard allot about Stored Procedures and would like to get that
comment of mine answered:
--------------------PREVIO US COMMENT------------------- ---------- ---------- ---------- ---------- ---------- ----------
1) Where will I find good documentation and/or tutorials on Stored Procedures?
If I understand correctly I can do SELECTS / INSERTS / UPDATES / DELETES by means of Stored Procedures?
2) I have never worked with SP before.
Does every table need a SP for each of the following (for example only):
1. Inserting record (SP1)
2. Deleting record (SP2)
3. Updating record (SP3)
3) Can you show my an example of SP for doing INSERT, please?
I did allot of
Dataset.Close & Dataset.Open 's in order the update the data thats displaying, so I realize
now this slowed it down allot. Even on only couple on records.
But I would like to do it the correct way.
So now it's more a matter of what is the best and most correct way.
I know there are always lots of correct methods.
But I have heard allot about Stored Procedures and would like to get that
comment of mine answered:
--------------------PREVIO
1) Where will I find good documentation and/or tutorials on Stored Procedures?
If I understand correctly I can do SELECTS / INSERTS / UPDATES / DELETES by means of Stored Procedures?
2) I have never worked with SP before.
Does every table need a SP for each of the following (for example only):
1. Inserting record (SP1)
2. Deleting record (SP2)
3. Updating record (SP3)
3) Can you show my an example of SP for doing INSERT, please?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
1) Ok, and then from your application how do you call the SP?
2) Where will I find good documentation and/or tutorials on Stored Procedures?
If I understand correctly I can do SELECTS / INSERTS / UPDATES / DELETES by means of Stored Procedures?
2) Where will I find good documentation and/or tutorials on Stored Procedures?
If I understand correctly I can do SELECTS / INSERTS / UPDATES / DELETES by means of Stored Procedures?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
For example:
1. Should I use TIBDataset components in conjuction with DB Components?
2. TIBTable components in conjuction with DB components?
3. TIBQuery components in conjunction with NON-DB components?
etc...
etc...
Cause I have changed all by TIBDatasets to TIBTables and there is already a little speed improvement.