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!
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
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.
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
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.
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?
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
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?
Check out the Interbase Developers guide for onfo on writing SPs
http://firebird.sourceforg
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.StoredProcNam
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>'
ParamByName('<InputName2>'
// ... etc:
Prepare;
ExecProc;
{ If there are any returned results, load them here }
<ResultVariable>:= ParamByName('<OutputName>'
// ... 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.
Business Accounts
Answer for Membership
by: JDSkinnerPosted on 2006-04-24 at 16:47:56ID: 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-exchang
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.