Stephen W. Meeley
5-Dec-2005, 03:04 PM
Hi Raveen,
Having seen a lot of developers go through this transition, here are
some things to keep in mind:
1. Many developers start by timing the embedded database running with
only one user against data on their local machine. The timing should
always be measured across the network and with at least two users having
the same tables opened. Otherwise, Windows will cache the heck out of
the data and that won't hold up in the real world.
2. In client / server environments, performance of the server can be
significantly improved by adding more memory and/or more processing
power. Doing the same for the embedded database won't make that much of
a difference.
3. As the load increases, one of the most limiting factors in the
performance of the embedded database is the choke-point of table
locking. Using client / server gives you record-locking, which can make
a massive performance difference, but only starts showing itself as the
number of users increases (and really shines on systems with very large
numbers of users).
4. Besides table-locking being a choke point, just the overhead of
shared-table locking in Windows degrades performance of the embedded
database as the number of users increases. Even if you can simulate lots
of users, simulate as many as you can to get the best comparison.
5. One of the really big advantages of going to a client / server
environment is being able to use the SQL interface for reports and batch
processes. A simple, but powerful, example of this is switching Crystal
reports from the DataFlex driver to the DB2 driver. Developers have
reported massive performance improvements for that type of use, but it
won't show up in a one-to-one comparison of a data entry screen. As you
test under load, don't forget to throw reports into the mix. Every
report or batch process that you can switch to utilizing a set mentality
will give immediate (and significant) performance gains, even for the
first user.
The bottom line on all of this is that the easiest stuff to compare
(simply converting local data and doing single-user comparisons of some
data entry programs) is the least meaningful comparison that you can do.
It's very easy to get the wrong impressions early-on because of this.
That's about as specific as I'm qualified for...
Best regards,
-SWM-
-----Original Message-----
From: Raveen Ryan Sundram [mailto:raveen@xls.co.nz]
Posted At: Monday, December 05, 2005 1:54 PM
Posted To: data-connectivity
Conversation: DB2 vs DATAFLEX in Performance
Subject: DB2 vs DATAFLEX in Performance
Hi,
Has anyone clocked read/write performance between native dataflex and
db2? I
recently installed DB2 for testing and am using DB2-CK2.2 with VDF10.1
We have a WebApplication running, and I converted the database and
recompiled the program with DB2. I found the following:
1. Slower reads / queries. Database searches are returned faster using
native dataflex.
2. Used DB2 monitor to look at the SQL being processed, I found that the
SQL
statements were not optimised. An example was, I used a constrain in my
webreport as follows "Constrain Stock.Brch eq sBrch", where sBrch is
"18".
But the SQL statement produced was "Select ... from Stock where Brch >
?"
thats a bit weird. With this statement, it shouldn't even find records.
Being new to the CKs and DB2 - I could have the wrong parameters set.
Note:
I merely installed the DB2 system and am using a standard DB2 database.
I've
not changed any of its defaults.
Any advice?
--
Regards,
Raveen Ryan Sundram
Excellent Software (NZ) Ltd.
Having seen a lot of developers go through this transition, here are
some things to keep in mind:
1. Many developers start by timing the embedded database running with
only one user against data on their local machine. The timing should
always be measured across the network and with at least two users having
the same tables opened. Otherwise, Windows will cache the heck out of
the data and that won't hold up in the real world.
2. In client / server environments, performance of the server can be
significantly improved by adding more memory and/or more processing
power. Doing the same for the embedded database won't make that much of
a difference.
3. As the load increases, one of the most limiting factors in the
performance of the embedded database is the choke-point of table
locking. Using client / server gives you record-locking, which can make
a massive performance difference, but only starts showing itself as the
number of users increases (and really shines on systems with very large
numbers of users).
4. Besides table-locking being a choke point, just the overhead of
shared-table locking in Windows degrades performance of the embedded
database as the number of users increases. Even if you can simulate lots
of users, simulate as many as you can to get the best comparison.
5. One of the really big advantages of going to a client / server
environment is being able to use the SQL interface for reports and batch
processes. A simple, but powerful, example of this is switching Crystal
reports from the DataFlex driver to the DB2 driver. Developers have
reported massive performance improvements for that type of use, but it
won't show up in a one-to-one comparison of a data entry screen. As you
test under load, don't forget to throw reports into the mix. Every
report or batch process that you can switch to utilizing a set mentality
will give immediate (and significant) performance gains, even for the
first user.
The bottom line on all of this is that the easiest stuff to compare
(simply converting local data and doing single-user comparisons of some
data entry programs) is the least meaningful comparison that you can do.
It's very easy to get the wrong impressions early-on because of this.
That's about as specific as I'm qualified for...
Best regards,
-SWM-
-----Original Message-----
From: Raveen Ryan Sundram [mailto:raveen@xls.co.nz]
Posted At: Monday, December 05, 2005 1:54 PM
Posted To: data-connectivity
Conversation: DB2 vs DATAFLEX in Performance
Subject: DB2 vs DATAFLEX in Performance
Hi,
Has anyone clocked read/write performance between native dataflex and
db2? I
recently installed DB2 for testing and am using DB2-CK2.2 with VDF10.1
We have a WebApplication running, and I converted the database and
recompiled the program with DB2. I found the following:
1. Slower reads / queries. Database searches are returned faster using
native dataflex.
2. Used DB2 monitor to look at the SQL being processed, I found that the
SQL
statements were not optimised. An example was, I used a constrain in my
webreport as follows "Constrain Stock.Brch eq sBrch", where sBrch is
"18".
But the SQL statement produced was "Select ... from Stock where Brch >
?"
thats a bit weird. With this statement, it shouldn't even find records.
Being new to the CKs and DB2 - I could have the wrong parameters set.
Note:
I merely installed the DB2 system and am using a standard DB2 database.
I've
not changed any of its defaults.
Any advice?
--
Regards,
Raveen Ryan Sundram
Excellent Software (NZ) Ltd.