?
Solved

Delphi TIBDataset component very slow over LAN

Posted on 2006-04-24
14
Medium Priority
?
779 Views
Last Modified: 2010-04-05
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!

0
Comment
Question by:Marius0188
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 4

Assisted Solution

by:JDSkinner
JDSkinner earned 260 total points
ID: 16530461
Hi
You may have already done this but:-
Try using TibQuery linked to a DataAccess Datasource component
For basic setup look have a look at
http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_21733796.html

To speed up any updates then use local dataset caching:
While TibQuery.Active is set to false, set TibQuery.CachedUpdates to true
then reset TibQuery.Active to true. This allows for local caching of Inserts, updates and deletes.

Make sure that the TibQuery.SQL is set to something like
Select <fieldlist> from table
Where < some condition > (optional)
Order by <field or fieldlist>

The Order by field / fieldlist should ideally is already indexed although an index will be created if its not already available.
Run the SQL query on a database tool such as Database Workbench
http://www.upscene.com/
This will show you how fast the query can be run.

If you use the TibQuery.CachedUpdates don't forget to use TibQuery.ApplyUpdates befor you commit your changes.

0
 

Author Comment

by:Marius0188
ID: 16531715
What will be the best configuration / set up for interbase database over LAN in terms of components to use?

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.
0
 
LVL 11

Expert Comment

by:pcsentinel
ID: 16532216
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Assisted Solution

by:JDSkinner
JDSkinner earned 260 total points
ID: 16533025
Don't use TIBTables over a LAN they are more for desktop applications
TibQuery is more scaleab;e to LAN requirements

Never use a TibQuery filter over the network as all records are called and the filter applied locally, this is SLOW.
If you use TiBQuery make sure that the SQL query is addressed to just the required subset.

In general TibStoredProcs are the thing to go for as these will connect you to the database's Stored Procedures. You can centralise you business logic within the database. Different delphi units can address the common logic stored within the Special Procedures, this cuts down on logic errors and is as fast way to extract a data subset as all the work in done on the server.
 
0
 

Author Comment

by:Marius0188
ID: 16533104
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?
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 16533137
>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 ;-)
0
 

Author Comment

by:Marius0188
ID: 16533197
>>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.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 16533241
>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 ;-)
0
 

Author Comment

by:Marius0188
ID: 16533293
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:

--------------------PREVIOUS 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?
0
 
LVL 11

Assisted Solution

by:pcsentinel
pcsentinel earned 240 total points
ID: 16533311

****Insert procedure*****

CREATE PROCEDURE TESTINSERT (
    ACCOUNT INTEGER,
    MTD DECIMAL(15,2))
AS
begin
  INSERT INTO "2002Aug" ("Account", "Dept")
  VALUES (:account, :MTD);
end

0
 
LVL 11

Assisted Solution

by:pcsentinel
pcsentinel earned 240 total points
ID: 16533363
***select procedure****

CREATE PROCEDURE TESTSELECT (
    SELACCOUNT INTEGER)
RETURNS (
    ACCOUNT INTEGER,
    MTD DECIMAL(15,2))
AS
begin
  FOR SELECT "Account", "MTD"
  FROM "2002Aug" WHERE "Account"=:selaccount
  INTO :Account,:mtd DO
  suspend;
end
0
 

Author Comment

by:Marius0188
ID: 16533410
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?
0
 
LVL 11

Assisted Solution

by:pcsentinel
pcsentinel earned 240 total points
ID: 16533534
Check out the Interbase Developers guide for onfo on writing SPs

http://firebird.sourceforge.net/index.php?op=doc&id=userdoc

0
 
LVL 4

Accepted Solution

by:
JDSkinner earned 260 total points
ID: 16548145
You can also do a variety of other functions with Stored Procedures such as count totals rows in Table, get column totals
for various conditions etc.
         
In the example setup hyperlink that I referred to in an earlier comment add the following component to the Form:
   
Interbase Tab:-  ibStoredProc
   
If you use the Borland interbase sample database Employee.gdb that ships with interbase when packaged with Delphi, then you can list the Procedures via the ibStoredProc.StoredProcName property.
   
But first set the ibStoredProc.database property to point to the required ibDatabase component. ie. Employee.gdb
          
When you select one of the Procedures from the drop down list, the ibStoredProc.Params property will have a list of the Params required for the selected Procedure. If you highlight any Paramin the Params list, its properties can be viewed.
   
Set a button to run the Procedure via the ibStoredProc using the Buttons OnClick event.

Use the code template indicated below to load parameters, if required and run the procedure
   
{ Load input parameters }
with <SpecialProcName1> do
begin
      Params.Clear; { Wipe any previous params }
      ParamByName('<InputName1>').AsInteger:= <inputVariable1>;
      ParamByName('<InputName2>').AsString:= <inputVariable2>;
      // ... etc:
      Prepare;
      ExecProc;
      { If there are any returned results, load them here }
      <ResultVariable>:= ParamByName('<OutputName>').AsInteger;
      // ... etc:
end;

As for documentation, try Chapter 10 of the Embedded SQL Guide that ships with Interbase in PDF format, this covers the Interbase end.

All you need for the Delphi end can be picked up by clicking on the Interbase Tab:-  ibStoredProc component and then pressing F1 and reading the help. Its a very simple component to use.
            
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month14 days, 15 hours left to enroll

839 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