| Author | Topic: ISAM-Emulation in xBase++ 2.0 |
---|
 | Dietmar Schnitzer | ISAM-Emulation in xBase++ 2.0
on Wed, 26 Nov 2014 11:32:01 +0100Hello,
the long awaited xBase++ 2.0 is now available. Hip hip hurray.
I don’t know, why you are waiting for 2.0 but we are in need to support
SQL-DBMS in large environments with many clients.
So using SQL in xBase is no problem for years, but we have millions of
lines of code using SKIP an EOF() and all the other things that we are
used to, but not a SQL-DBMS.
So we are longing for the ISAM-Emulation with ‚PGDBE‘
We recompiled our application with xBase++ 2.0 and added ‚PGDBE‘ support.
The whole application compiled without error, but data access was… to
say poor would be playing down the problem…
We made a test scenario with a small dbf database with 30 fields. Record
size was 250 bytes. Record count was 300000.
We used a simple scenario: we read all the fields in one record, and
skipped to the next.
With DBFCDX on the local machine 10000 records were read in 0.6 Seconds.
With PGDBE 100 records were read in 8 seconds.
So we had a huge problem…. A factor greater thousand against us…
Working in and with our application wasn’t possible.
Fortunately our test were a few days before xBase++ Conference near
Frankfurt.
So we went to Frankfurt and asked Steffen for help.
Steffen did the trick
Now we came down to the factor 20-30 (please have in mind
SQL-ISAM-emulation against local ISAM!)
What was the trick? So simple, that we didn’t thought it would get such
an enormous boost.
Steffen added the fields that Alaska added in each table in the
migration process from DBF to SQL for each ISAM-index-key (Index in
cdx-file) as new indices in the POSTGRESQL-table.
Sounds complicated, but is very easy.
You use the pgAdmin-Tool, to open your database. Get to the table. Under
the table you find the Indizes for this table.
Right-klick and use ‚new index‘. Give it a name and click on the
columns-tab. Open the dropdown and choose one or all fields beginning
with ‚__order‘
Click add and you are done.
You have added a new index in POSTGRESQL analog to your ‚old‘ ISAM-index
to optimize the access.
Access in ISAM-Emulation with this order is now 500 times faster as
before in our scenario… Wow…
To be honest: We didn’t optimize the POSTGRESQL-server because we
couldn’t believe that such a acceleration would be possible.
Our last test was a compare in our scenario to DBFCDX access in a LAN
environment (without concurrent access).
POSTGRES-ISAM-emulation to 'old fashioned‘ Access via DBFCDX is now 5 to
10 times times slower, but Steffen has promised that this is not the end
of the road and we didn’t do any other optimization in pgAdmin yet. And
the LAN-scenario didn't have any concurrent access either.
At last, our application can be used with PGDBE and we can start to
optimize the parts that are designed against the idea of SQL
We post this news, because a lot of attendees of the xBase++ Conference
were eager to get news about the state of ISAM-Emulation and perhaps so
are you...
Greetings
Dietmar & Matthias |
 | Pedro Hernßndez | Re: ISAM-Emulation in xBase++ 2.0
on Mon, 19 Jan 2015 17:59:00 +0100Great Thanks !!!
Dietmar Schnitzer wrote in message
news:5aaac7d4$246396e9$af8d8@news.alaska-software.com...
>Hello,
>
>the long awaited xBase++ 2.0 is now available. Hip hip hurray.
>I don’t know, why you are waiting for 2.0 but we are in need to support
>SQL-DBMS in large environments with many clients.
>So using SQL in xBase is no problem for years, but we have millions of
>lines of code using SKIP an EOF() and all the other things that we are
>used to, but not a SQL-DBMS.
>So we are longing for the ISAM-Emulation with ‚PGDBE‘
>
>We recompiled our application with xBase++ 2.0 and added ‚PGDBE‘ support.
>The whole application compiled without error, but data access was… to
>say poor would be playing down the problem…
>We made a test scenario with a small dbf database with 30 fields. Record
>size was 250 bytes. Record count was 300000.
>We used a simple scenario: we read all the fields in one record, and
>skipped to the next.
>With DBFCDX on the local machine 10000 records were read in 0.6 Seconds.
>With PGDBE 100 records were read in 8 seconds.
>So we had a huge problem…. A factor greater thousand against us…
>Working in and with our application wasn’t possible.
>
>Fortunately our test were a few days before xBase++ Conference near
>Frankfurt.
>So we went to Frankfurt and asked Steffen for help.
>Steffen did the trick
>
>Now we came down to the factor 20-30 (please have in mind
>SQL-ISAM-emulation against local ISAM!)
>
>What was the trick? So simple, that we didn’t thought it would get such
>an enormous boost.
>Steffen added the fields that Alaska added in each table in the
>migration process from DBF to SQL for each ISAM-index-key (Index in
>cdx-file) as new indices in the POSTGRESQL-table.
>Sounds complicated, but is very easy.
>You use the pgAdmin-Tool, to open your database. Get to the table. Under
>the table you find the Indizes for this table.
>Right-klick and use ‚new index‘. Give it a name and click on the
>columns-tab. Open the dropdown and choose one or all fields beginning
>with ‚__order‘
>Click add and you are done.
>You have added a new index in POSTGRESQL analog to your ‚old‘ ISAM-index
>to optimize the access.
>Access in ISAM-Emulation with this order is now 500 times faster as
>before in our scenario… Wow…
>
>To be honest: We didn’t optimize the POSTGRESQL-server because we
>couldn’t believe that such a acceleration would be possible.
>
>Our last test was a compare in our scenario to DBFCDX access in a LAN
>environment (without concurrent access).
>
>POSTGRES-ISAM-emulation to 'old fashioned‘ Access via DBFCDX is now 5 to
>10 times times slower, but Steffen has promised that this is not the end
>of the road and we didn’t do any other optimization in pgAdmin yet. And
>the LAN-scenario didn't have any concurrent access either.
>
>At last, our application can be used with PGDBE and we can start to
>optimize the parts that are designed against the idea of SQL
>
>We post this news, because a lot of attendees of the xBase++ Conference
>were eager to get news about the state of ISAM-Emulation and perhaps so
>are you...
>
>Greetings
>
> Dietmar & Matthias |
 | Hank Hoving | Re: ISAM-Emulation in xBase++ 2.0
on Wed, 25 Mar 2015 15:15:14 +0100Dietmar,
I am testing with postrgresql with isam. But the seek on an certain index is very slow.
Even I have made the extra indexes the way you describe. The search stays slow. I
have the idea the indexes for the isam doesnot work
If I use the select-statement of sql-express then the search is at least 200 times faster.
I am using Postgres 9,4 and Xbase 2.0 554
What is the version of your postgres and xbase
Hank
Pedro Hernßndez wrote in message news:2649cd2a$5fda5625$59b@news.alaska-
software.com...
>Great Thanks !!!
>
>
>
>Dietmar Schnitzer wrote in message
>news:5aaac7d4$246396e9$af8d8@news.alaska-software.com...
>>Hello,
>>
>>the long awaited xBase++ 2.0 is now available. Hip hip hurray.
>>I don’t know, why you are waiting for 2.0 but we are in need to support
>>SQL-DBMS in large environments with many clients.
>>So using SQL in xBase is no problem for years, but we have millions of
>>lines of code using SKIP an EOF() and all the other things that we are
>>used to, but not a SQL-DBMS.
>>So we are longing for the ISAM-Emulation with ‚PGDBE‘
>>
>>We recompiled our application with xBase++ 2.0 and added ‚PGDBE‘ support.
>>The whole application compiled without error, but data access was… to
>>say poor would be playing down the problem…
>>We made a test scenario with a small dbf database with 30 fields. Record
>>size was 250 bytes. Record count was 300000.
>>We used a simple scenario: we read all the fields in one record, and
>>skipped to the next.
>>With DBFCDX on the local machine 10000 records were read in 0.6 Seconds.
>>With PGDBE 100 records were read in 8 seconds.
>>So we had a huge problem…. A factor greater thousand against us…
>>Working in and with our application wasn’t possible.
>>
>>Fortunately our test were a few days before xBase++ Conference near
>>Frankfurt.
>>So we went to Frankfurt and asked Steffen for help.
>>Steffen did the trick
>>
>>Now we came down to the factor 20-30 (please have in mind
>>SQL-ISAM-emulation against local ISAM!)
>>
>>What was the trick? So simple, that we didn’t thought it would get such
>>an enormous boost.
>>Steffen added the fields that Alaska added in each table in the
>>migration process from DBF to SQL for each ISAM-index-key (Index in
>>cdx-file) as new indices in the POSTGRESQL-table.
>>Sounds complicated, but is very easy.
>>You use the pgAdmin-Tool, to open your database. Get to the table. Under
>>the table you find the Indizes for this table.
>>Right-klick and use ‚new index‘. Give it a name and click on the
>>columns-tab. Open the dropdown and choose one or all fields beginning
>>with ‚__order‘
>>Click add and you are done.
>>You have added a new index in POSTGRESQL analog to your ‚old‘ ISAM-index
>>to optimize the access.
>>Access in ISAM-Emulation with this order is now 500 times faster as
>>before in our scenario… Wow…
>>
>>To be honest: We didn’t optimize the POSTGRESQL-server because we
>>couldn’t believe that such a acceleration would be possible.
>>
>>Our last test was a compare in our scenario to DBFCDX access in a LAN
>>environment (without concurrent access).
>>
>>POSTGRES-ISAM-emulation to 'old fashioned‘ Access via DBFCDX is now 5 to
>>10 times times slower, but Steffen has promised that this is not the end
>>of the road and we didn’t do any other optimization in pgAdmin yet. And
>>the LAN-scenario didn't have any concurrent access either.
>>
>>At last, our application can be used with PGDBE and we can start to
>>optimize the parts that are designed against the idea of SQL
>>
>>We post this news, because a lot of attendees of the xBase++ Conference
>>were eager to get news about the state of ISAM-Emulation and perhaps so
>>are you...
>>
>>Greetings
>>
>> Dietmar & Matthias |
 | Zoltan Nagy | Re: ISAM-Emulation in xBase++ 2.0
on Tue, 22 Sep 2015 12:54:27 +0200Hello.
Is possibe this "new index" adding without pgAdmin from my source code?
I have thousand of dbf tables, and I need manually adding this indexes?
Horrible.
Regards, Nagy
"Dietmar Schnitzer" a következőket írta üzenetében
news:5aaac7d4$246396e9$af8d8@news.alaska-software.com...
> Hello,
>
> the long awaited xBase++ 2.0 is now available. Hip hip hurray.
> I don’t know, why you are waiting for 2.0 but we are in need to support
> SQL-DBMS in large environments with many clients.
> So using SQL in xBase is no problem for years, but we have millions of
> lines of code using SKIP an EOF() and all the other things that we are
> used to, but not a SQL-DBMS.
> So we are longing for the ISAM-Emulation with ‚PGDBE‘
>
> We recompiled our application with xBase++ 2.0 and added ‚PGDBE‘ support.
> The whole application compiled without error, but data access was… to say
> poor would be playing down the problem…
> We made a test scenario with a small dbf database with 30 fields. Record
> size was 250 bytes. Record count was 300000.
> We used a simple scenario: we read all the fields in one record, and
> skipped to the next.
> With DBFCDX on the local machine 10000 records were read in 0.6 Seconds.
> With PGDBE 100 records were read in 8 seconds.
> So we had a huge problem…. A factor greater thousand against us…
> Working in and with our application wasn’t possible.
>
> Fortunately our test were a few days before xBase++ Conference near
> Frankfurt.
> So we went to Frankfurt and asked Steffen for help.
> Steffen did the trick
>
> Now we came down to the factor 20-30 (please have in mind
> SQL-ISAM-emulation against local ISAM!)
>
> What was the trick? So simple, that we didn’t thought it would get such an
> enormous boost.
> Steffen added the fields that Alaska added in each table in the migration
> process from DBF to SQL for each ISAM-index-key (Index in cdx-file) as new
> indices in the POSTGRESQL-table.
> Sounds complicated, but is very easy.
> You use the pgAdmin-Tool, to open your database. Get to the table. Under
> the table you find the Indizes for this table.
> Right-klick and use ‚new index‘. Give it a name and click on the
> columns-tab. Open the dropdown and choose one or all fields beginning with
> ‚__order‘
> Click add and you are done.
> You have added a new index in POSTGRESQL analog to your ‚old‘ ISAM-index
> to optimize the access.
> Access in ISAM-Emulation with this order is now 500 times faster as before
> in our scenario… Wow…
>
> To be honest: We didn’t optimize the POSTGRESQL-server because we couldn’t
> believe that such a acceleration would be possible.
>
> Our last test was a compare in our scenario to DBFCDX access in a LAN
> environment (without concurrent access).
>
> POSTGRES-ISAM-emulation to 'old fashioned‘ Access via DBFCDX is now 5 to
> 10 times times slower, but Steffen has promised that this is not the end
> of the road and we didn’t do any other optimization in pgAdmin yet. And
> the LAN-scenario didn't have any concurrent access either.
>
> At last, our application can be used with PGDBE and we can start to
> optimize the parts that are designed against the idea of SQL
>
> We post this news, because a lot of attendees of the xBase++ Conference
> were eager to get news about the state of ISAM-Emulation and perhaps so
> are you...
>
> Greetings
>
> Dietmar & Matthias |