PDA

View Full Version : DB2 vs DATAFLEX in Performance



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.

Raveen Ryan Sundram
5-Dec-2005, 04:54 PM
Stephen,

Your points are noted but I issue is that the SQL statement produced by the
DB2-CK2.2 is flawed.

In my code below, there are 2 kinds of searches - Code and keywords. If it
does a keyword search - its basically will do a full table-scan. But it is
constrained by the iMaxRows variables, which in this case is set to (25 rows
per page * 10 pages = 250 rows).

The WebReport (oReport) is ran first, outputing the first 25 rows and then
the BuildPageIndex is ran to display the Number of Pages and Hyperlinks to
the pages.

I found the DB2-CK2.2 (dies or) time-outs in 2 of 5 attempts of running
BuildPageIndex . Wherelse, the native dataflex executes the function with no
problems and in 1/3 of the time.

See below for Code and ASP Error:

--------------------------------------------------------------------
My Code:
--------------------------------------------------------------------
// Builds a Page Index for users to jump to specific pages rather than
// just using the "next" and "previous" options

Function BuildPageIndex Integer iIndex Integer iMaxCount Integer iStartRec
String sURL Returns Integer
Integer hoDD iRelate iCurPage iPage iGenerate
Integer iOK iCount iMaxRows
String sValue

Get Main_DD To hoDD
Get piMaxPages To iMaxRows // default: 10 pages

Move 0 To iGenerate
Move 0 To iCount
Move 0 To iPage
Move 1 To iCurPage

Move (iMaxCount*iMaxRows) To iMaxRows // default: 250 max-rows

// check if re-generation of the page-index array is necessary.
If iStartRec Eq 0 Move 1 To iGenerate
Else Begin
Get PageExist Of oPageArray iStartRec To iCurPage
If iCurPage Eq 0 Begin
Get Item_Count Of oPageArray To iCurPage
Decrement iCurPage
Move iCurPage To iPage
Move 1 To iGenerate
End
End

If iGenerate Begin
If iStartRec Eq 0 Send Delete_Data To oPageArray
//
Send Clear To hoDD
Send Rebuild_Constraints To hoDD // re-use constraint-set
If iStartRec Begin
Send Find_By_recnum To hoDD Stock.File_number iStartRec
[Found] Send Find To hoDD Lt iIndex
End
Repeat
Send Find To hoDD Gt iIndex
If (Found) Begin
//
************************************************** *****************************
// RPT_OK (0) The record is valid.
// RPT_END (1) The record represents the End Of the File.
// RPT_NOT_SELECT (2) The record Is Not valid, but Is Not At the
End Of the File.
// RPT_CANCEL (3) Cancel A report. This would be An unusual
return value.
//
************************************************** *****************************
Get OnSelection Of oReport To iOK // re-use selection criteria
used in the WebReport
If (iOK=RPT_OK) Begin
If (Mod(iCount,iMaxCount)=0) Begin
Increment iPage
Set Value Of oPageArray iPage To Stock.Recnum
End
Increment iCount
End
[Found] Indicate Finderr as (iCount >= iMaxRows)
[Found] Indicate Finderr as (iOK=RPT_END)
End
Until [Finderr]
Indicate Finderr False
End

// Write Page Index
Get Item_Count Of oPageArray To iCount
For iPage From 1 To (iCount-1)
Get Value Of oPageArray iPage To sValue
If (iPage=iCurPage) Send WriteHtml (String(iPage)+" ")
Else Send WriteHtml (HtmlLink(Self,
(Replace("$RECNUM",sURL,sValue)),(String(iPage)+" ") ))
If (Mod(iPage,20)=0) Send WriteHTML "<BR/>"
Loop

End_Function

--------------------------------------------------------------------
ASP Error:
--------------------------------------------------------------------
Active Server Pages error 'ASP 0113'

Script timed out

/db2.retailplus.563/StockReport.asp

The maximum amount of time for a script to execute was exceeded. You can
change this limit by specifying a new value for the property
Server.ScriptTimeout or by changing the value in the IIS administration
tools.

--------------------------------------------------------------------


Regards,
Raveen Ryan Sundram

Ben Weijers
6-Dec-2005, 04:28 AM
The database drivers do not know anything about constraints. As a result of
that they cannot generate statements that take constraints into account. If
you want to statements to be smarter you can use the DF_FILE_SQL_FILTER and
DF_FILE_SQL_FILTER_ACTIVE attirbutes (support by CK 3.x and up).

The question marks you see in the statements are so called statement
paramters. These parameters are passed to DB2 by calling certain API
functions.

Regards,

Ben Weijers
Data Access Worldwide

Raveen Ryan Sundram
6-Dec-2005, 01:40 PM
Ben,

So you're saying that if upgrade to CK4, the new CK4 SQL generated
statements will take constraints into account? (if the DF_FILE_SQL_FILTER
and DF_FILE_SQL_FILTER_ACTIVE attributes are set)

Raveen

Ben Weijers
6-Dec-2005, 02:06 PM
No, drivers do not take constraints into account. I am saying that if you
use a CK version 3.0 or higher you can use the DF_FILE_SQL_FILTER and
DF_FILE_SQL_FILTER_ACTIVE atributes to improve performance. This is
something you will have to program yourself.

Regards,

Ben Weijers
Data Access Worldwide

Marco
8-Dec-2005, 01:37 AM
Hi Raveen,

Basically you set the DF_FILE_SQL_FILTER to a part a SQL statement that
is going to be inserted in the Where clause.

So
If you Set DF_FILE_SQL_FILTER to "State = 'NY'" and set the
DF_FILE_SQL_FILTER_ACTIVE to Yes, the a statement like:

Find gt customer by Recnum

will be 'translated' to:

"Select * from Customer where State = 'NY' and recnum > ?"

where the ? is set by API calls as stated by Ben.

Cheers,
Marco

Btw: The use of these kind of constrains will make things a lot faste
(than DF one user even). But in some cases, you could concider to
rewrite to SQL (eg where a little number of fields from many big files
are used).




Raveen Ryan Sundram wrote:
> Ben,
>
> So you're saying that if upgrade to CK4, the new CK4 SQL generated
> statements will take constraints into account? (if the DF_FILE_SQL_FILTER
> and DF_FILE_SQL_FILTER_ACTIVE attributes are set)
>
> Raveen
>
>
>
>
>

Raveen Ryan Sundram
8-Dec-2005, 01:13 PM
Marco,

Is it possible to use SQL Statements in DataFlex? Rather than the normal
constrain and find statements?

Regards,
Raveen Ryan Sundram


"Marco Kuipers" <marco.kuipers@nci.com.au> wrote in message
news:Fa3KfH8%23FHA.944@dacmail.dataaccess.com...
> Hi Raveen,
>
> Basically you set the DF_FILE_SQL_FILTER to a part a SQL statement that is
> going to be inserted in the Where clause.
>
> So
> If you Set DF_FILE_SQL_FILTER to "State = 'NY'" and set the
> DF_FILE_SQL_FILTER_ACTIVE to Yes, the a statement like:
>
> Find gt customer by Recnum
>
> will be 'translated' to:
>
> "Select * from Customer where State = 'NY' and recnum > ?"
>
> where the ? is set by API calls as stated by Ben.
>
> Cheers,
> Marco
>
> Btw: The use of these kind of constrains will make things a lot faste
> (than DF one user even). But in some cases, you could concider to rewrite
> to SQL (eg where a little number of fields from many big files are used).
>

phvwijk
8-Dec-2005, 08:28 PM
Raveen,

Read about embedded SQL

Peter H. van Wijk
X-Organize Consultancy N.V.

"Raveen Ryan Sundram" <raveen@xls.co.nz> wrote in message
news:m5oZnMC$FHA.5176@dacmail.dataaccess.com...
> Marco,
>
> Is it possible to use SQL Statements in DataFlex? Rather than the normal
> constrain and find statements?
>
> Regards,
> Raveen Ryan Sundram
>
>
> "Marco Kuipers" <marco.kuipers@nci.com.au> wrote in message
> news:Fa3KfH8%23FHA.944@dacmail.dataaccess.com...
>> Hi Raveen,
>>
>> Basically you set the DF_FILE_SQL_FILTER to a part a SQL statement that
>> is going to be inserted in the Where clause.
>>
>> So
>> If you Set DF_FILE_SQL_FILTER to "State = 'NY'" and set the
>> DF_FILE_SQL_FILTER_ACTIVE to Yes, the a statement like:
>>
>> Find gt customer by Recnum
>>
>> will be 'translated' to:
>>
>> "Select * from Customer where State = 'NY' and recnum > ?"
>>
>> where the ? is set by API calls as stated by Ben.
>>
>> Cheers,
>> Marco
>>
>> Btw: The use of these kind of constrains will make things a lot faste
>> (than DF one user even). But in some cases, you could concider to rewrite
>> to SQL (eg where a little number of fields from many big files are used).
>>
>
>

Peter van Mil
9-Dec-2005, 01:58 AM
Raveen,

You can use SQL Filters for constraints. I have a table with addresses
in MSSQL, that has to be replicated to another database. The original
database has a last edit date/time. Put a SQL filter on that date and
all the find statements work on that result set. Very fast. You have to
use the SQL filters in stead of the DataFlex constraints.

Peter van Mil

------------------------------------------------------------

Procedure DoSQLFilters
Handle hTable
String sDate
Date dLeDtTm

Move NameAdd.File_Number to hTable

Get pdLastEditDate to dLeDtTm

If (dLeDtTm <> 0) Begin
Move (Right(dLeDtTm,4)+"-"+Mid(dLeDtTm,2,4)+"-"+Left(dLeDtTm,2))
;
to sDate // '2005-12-31'.
Set_Attribute DF_FILE_SQL_FILTER of hTable to ;
("NameAdd.LeDtTm > '" + sDate + "'")
Set_Attribute DF_FILE_SQL_FILTER_ACTIVE of hTable to True
End
Else Begin
Set_Attribute DF_FILE_SQL_FILTER_ACTIVE of hTable to False
End
End_Procedure




Raveen Ryan Sundram schreef:
> Marco,
>
> Is it possible to use SQL Statements in DataFlex? Rather than the normal
> constrain and find statements?
>
> Regards,
> Raveen Ryan Sundram
>
>
> "Marco Kuipers" <marco.kuipers@nci.com.au> wrote in message
> news:Fa3KfH8%23FHA.944@dacmail.dataaccess.com...
>
>>Hi Raveen,
>>
>>Basically you set the DF_FILE_SQL_FILTER to a part a SQL statement that is
>>going to be inserted in the Where clause.
>>
>>So
>>If you Set DF_FILE_SQL_FILTER to "State = 'NY'" and set the
>>DF_FILE_SQL_FILTER_ACTIVE to Yes, the a statement like:
>>
>>Find gt customer by Recnum
>>
>>will be 'translated' to:
>>
>>"Select * from Customer where State = 'NY' and recnum > ?"
>>
>>where the ? is set by API calls as stated by Ben.
>>
>>Cheers,
>>Marco
>>
>>Btw: The use of these kind of constrains will make things a lot faste
>>(than DF one user even). But in some cases, you could concider to rewrite
>>to SQL (eg where a little number of fields from many big files are used).
>>
>
>
>

Raveen Ryan Sundram
19-Feb-2006, 10:04 PM
Thanks for all the help.