| Subject | Re: [IBO] IBO speed vs BDE? |
|---|---|
| Author | Helen Borrie |
| Post date | 2001-04-18T12:35:15Z |
At 02:32 AM 18-04-01 -0700, you wrote:
I don't want to discourage you from learning to work with client/server but, from your descriptions, local InterBase is not going to improve your application. Local InterBase is *not* comparable to an ISAM database. With FoxPro, your "query" is really a "window" into the physical database. Your application is accessing the data directly by swapping back and forth between memory and disk. That is why ISAM is so fast.
With a client/server database, your application never touches the database physically. It is designed so that sets of data are requested and returned across a network by the server as images. The client application works on these images and sends some instructions back to the server like "Find all the rows that meet these criteria and perform this operation on them."
Local IB was invented, long after IB was already a fully-operational RDBMS, as a convenience to programmers. It allows you to develop and test your client/server application on a stand-alone machine. It was never intended for deployment for desktop solutions and, in fact, until licensing was removed from the open source version of IB, it was actually illegal to deploy an application with local IB.
It certainly cannot compete on performance with any ISAM database operating in its own native application environment.
This is a huge question! Optimization of SQL client/server database applications occurs at several places. I guess the first fundamental that an ISAM developer needs to get to grips with is to realise that your application is always dealing with the data at arm's length. Speed comes from (a) moving the smallest possible sets of data around the network and (b) making the server do as much of the work as possible.
For (a) there's no point in assuming that you will get a speed gain from running a local server. "True" local IBS (connecting directly to the database path) simulates the network architecture (quite inefficiently and with the serious limitation that it is not thread-safe) whilst a "local host" network configuration uses the network protocol and properly separates the client and server communication processes. Under either of these configurations, the server is competing with the client for resources.
Point (b) encompasses a whole raft of techniques involving efficient metadata design, good indexing to keep the query optimizer happy, designing small output sets, keeping transactions short and, most of all, delegating processing to the server, via triggers and stored procedures.
One avoids altogether any client-side process that performs batch operations on datasets. Clients are "thin" - the task of your UI is to present data for user selection and for any operations that users need to perform interactively; and it provides ways for users to instruct the server to perform operations on the server side. This is true whether you are using the BDE or direct-to-API connectivity solution such as IBO.
IBO is not a magic wand to make a badly designed application run faster than it ran under the BDE. But a good design will work faster under IBO, with a huge amount of highly optimized code encapsulating the work that IB Server is capable of. It will also be exponentially more robust because the architecture was designed specifically for InterBase.
The decision to move a Delphi app to IB is often made because ISAM databases are simply TOO fragile for many network environments. IB has a well-earned reputation for being bombproof under hairy conditions - the US Army's operational field kitchens being the classic example. However, I've seen situations where the choice to move a local app from Paradox to IB was (wrongly) made on the perception that, if it was a good move for a networked app, then it would be even better for a local one.
The usual reason for moving from ISAM to client/server is to scale up.
Think hard about what benefit you would get from a single-user InterBase app....
Regards,
Helen
All for Open and Open for All
InterBase Developer Initiative ยท http://www.interbase2000.org
_______________________________________________________
>Nick:Physically, it is *totally* different to a FoxPro cursor, which is a workaround to simulate (cosmetically) what a database cursor does in a client/server RDBMS.
>
>That was a great answer, thanks!
>
>And you're right, it has been taking me a while to get over the habit of
>thinking in terms of local hard disk based tables as in FoxPro. With IB
>everything is returned as a "cursor" as we called it in FoxPro.
I don't want to discourage you from learning to work with client/server but, from your descriptions, local InterBase is not going to improve your application. Local InterBase is *not* comparable to an ISAM database. With FoxPro, your "query" is really a "window" into the physical database. Your application is accessing the data directly by swapping back and forth between memory and disk. That is why ISAM is so fast.
With a client/server database, your application never touches the database physically. It is designed so that sets of data are requested and returned across a network by the server as images. The client application works on these images and sends some instructions back to the server like "Find all the rows that meet these criteria and perform this operation on them."
Local IB was invented, long after IB was already a fully-operational RDBMS, as a convenience to programmers. It allows you to develop and test your client/server application on a stand-alone machine. It was never intended for deployment for desktop solutions and, in fact, until licensing was removed from the open source version of IB, it was actually illegal to deploy an application with local IB.
It certainly cannot compete on performance with any ISAM database operating in its own native application environment.
>As we progress in this project I will definitely be asking the IB communityThe first piece of advice is: if you want a FoxPro-like database, use FoxPro.
>about specifics.
>
>Right now, do you have any suggestions as to where I could go to learn more
>basic information about optimizing IB? Remember we are using IB on a local
>system only (and I keep wanting to think of it as a FoxPro-like database, which
>it isn't.)
This is a huge question! Optimization of SQL client/server database applications occurs at several places. I guess the first fundamental that an ISAM developer needs to get to grips with is to realise that your application is always dealing with the data at arm's length. Speed comes from (a) moving the smallest possible sets of data around the network and (b) making the server do as much of the work as possible.
For (a) there's no point in assuming that you will get a speed gain from running a local server. "True" local IBS (connecting directly to the database path) simulates the network architecture (quite inefficiently and with the serious limitation that it is not thread-safe) whilst a "local host" network configuration uses the network protocol and properly separates the client and server communication processes. Under either of these configurations, the server is competing with the client for resources.
Point (b) encompasses a whole raft of techniques involving efficient metadata design, good indexing to keep the query optimizer happy, designing small output sets, keeping transactions short and, most of all, delegating processing to the server, via triggers and stored procedures.
One avoids altogether any client-side process that performs batch operations on datasets. Clients are "thin" - the task of your UI is to present data for user selection and for any operations that users need to perform interactively; and it provides ways for users to instruct the server to perform operations on the server side. This is true whether you are using the BDE or direct-to-API connectivity solution such as IBO.
IBO is not a magic wand to make a badly designed application run faster than it ran under the BDE. But a good design will work faster under IBO, with a huge amount of highly optimized code encapsulating the work that IB Server is capable of. It will also be exponentially more robust because the architecture was designed specifically for InterBase.
>p.s. I guess the real question is why did the original programmer use IB forIt's a question you should consider seriously. A need for robustness can often be a big factor in this type of decision. The BDE is Paradox's native engine. (dBase was modified considerably between versions III and IV to use the BDE "natively"). All applications that use the BDE are subject to the fragility of broken Paradox tables, because the BDE stores all temporary data in Paradox tables. (So, incidentally, do cached updates under the BDE).
>local data, when it may have been faster to use dBase or Paradox? BTW, I have no
>idea!
The decision to move a Delphi app to IB is often made because ISAM databases are simply TOO fragile for many network environments. IB has a well-earned reputation for being bombproof under hairy conditions - the US Army's operational field kitchens being the classic example. However, I've seen situations where the choice to move a local app from Paradox to IB was (wrongly) made on the perception that, if it was a good move for a networked app, then it would be even better for a local one.
The usual reason for moving from ISAM to client/server is to scale up.
Think hard about what benefit you would get from a single-user InterBase app....
Regards,
Helen
All for Open and Open for All
InterBase Developer Initiative ยท http://www.interbase2000.org
_______________________________________________________