ADO RDD xHarbour
Re: ADO RDD xHarbour
Antonio, line 6233 lReval -> lRetval
Re: ADO RDD xHarbour
New build adordd ver 1.0 at https://github.com/AHFERREIRA/adordd.git
New:
1) SET ADODBF INDEX LIST FIELDTYPE NUMBER TO {{ "Table",{ "numfiled", nlen },{"numfield", nLen } } }
adordd need a precise indication of the len of numeric fields used in index expressions.
This is only needed for numeric fields where the SQL type its without field len definition such as:
AUTOINC, MONEY, DOUBLE, INT, SMALLINT, etc.
Corrected Bugs:
1) ADOBEGINTRANS( nWA ) : without work are it starts a transaction in default adordd connection otherwise
it starts in the connection of the current work area.
2) ADOROLLBACKTRANS( nWA ) : without any work area it rolls back all transactions in all open connections
otherwise it rolls back transaction in same connection as that work area.
3) ado_create was adding an extra position in field len of numeric fields
now ADO_FIELDSTRUCT takes that position returning the correct len of a field type "N"
This could lead to side effects in INDEXKEY where the numeric field was part of the expression.
No tables structure corrections are necessary.
4) ADPSEUDOSEEK works now ok with fields type "T"
5) ADODUFINDEX if result of the index expression its numeric its considered as it was a UDF expression
because we cant extract field len to build a find, filter or query expression because the eval result
its the sum of all the numeric fields in the expression.
6) ORDKEYGOTO was not full implemented.
New:
1) SET ADODBF INDEX LIST FIELDTYPE NUMBER TO {{ "Table",{ "numfiled", nlen },{"numfield", nLen } } }
adordd need a precise indication of the len of numeric fields used in index expressions.
This is only needed for numeric fields where the SQL type its without field len definition such as:
AUTOINC, MONEY, DOUBLE, INT, SMALLINT, etc.
Corrected Bugs:
1) ADOBEGINTRANS( nWA ) : without work are it starts a transaction in default adordd connection otherwise
it starts in the connection of the current work area.
2) ADOROLLBACKTRANS( nWA ) : without any work area it rolls back all transactions in all open connections
otherwise it rolls back transaction in same connection as that work area.
3) ado_create was adding an extra position in field len of numeric fields
now ADO_FIELDSTRUCT takes that position returning the correct len of a field type "N"
This could lead to side effects in INDEXKEY where the numeric field was part of the expression.
No tables structure corrections are necessary.
4) ADPSEUDOSEEK works now ok with fields type "T"
5) ADODUFINDEX if result of the index expression its numeric its considered as it was a UDF expression
because we cant extract field len to build a find, filter or query expression because the eval result
its the sum of all the numeric fields in the expression.
6) ORDKEYGOTO was not full implemented.
Regards
Antonio H Ferreira
Antonio H Ferreira
Re: ADO RDD xHarbour
New build adordd ver 1.0 at https://github.com/AHFERREIRA/adordd.git
Corrected new Bugs due to changes previous build:
1) ADO_SQLSTRU with firebird numeric fields precision fieldlen was wrong
2) ADO_FIELDSTRUCT integers doesnt take any extra position.
3) ADO_PUTVALUE trim spaces to check data width error with numbers.
Corrected new Bugs due to changes previous build:
1) ADO_SQLSTRU with firebird numeric fields precision fieldlen was wrong
2) ADO_FIELDSTRUCT integers doesnt take any extra position.
3) ADO_PUTVALUE trim spaces to check data width error with numbers.
Regards
Antonio H Ferreira
Antonio H Ferreira
Re: ADO RDD xHarbour
New AdoRdd Version 1.070317 at https://github.com/AHFERREIRA/adordd.git
Improvements and corrected bugs:
ADO_ORDCREATE after creating index wasnt opening it.
ADO_SEEK key with multiple fields with softseek on was going to eof instead of last key.
ADOFILE if connection not valid returns now .F. instead of HB_FAILURE.
ADOOPENCONNECT if engine its not supported or wrongly set alerts the user.
ADOBEGINTRANS if workarea not specified opens a transactions for each opened connection
ADOCOMMITTRANS and ADOROLLBACKTRANS the same as ADOBEGINTRANS
New function ADONESTEDTRANS( nWA ) returns nr of nested transactions
NEW ERROR CODE 10600 - connection not available cant find transactions
ADO_ALREADYOPEN Test if the open recordset has same Sql query statement.
ADORECCOUNT Table name was not being converted to get fieldrecno if any defined
for that table always returned that default one.
ADOPREOPENTHRESHOLD Faster counting records. Doesn't cache any tables with WHERE clause.
All ADO cursors changed in the code from adopendynamic to adopenstatic, although it always
started as adopenstatic.
New features:
New option PORT in SET ADO DEFAULT DATABASE ...
ADO_ORDINFO ordwildseek implemented
Multibag orders support with new SET:
SET ADODBF MULTIBAG INDEX LIST TO { { "INDEX NAME", {TAGORDER 1","TAGORDER2"} } }
This allows multibag index file support exactly the same as any other DBf rdd.
ATTENTION
Now when SET AUTOOPEN ON doesn't open all index files automatically in SET ADODBF INDEX LIST instead
only opens the INDEX NAME present in SET ADODBF MULTIBAG INDEX LIST TO that match the table name.
Faster opening recordsets:
SET RECORDSET OPEN WHERE CLAUSE TO { { "TABLENAME", "SQL WHERE EXPRESSION" } }
ADOWHERECLAUSE ADORDD function allowing us to change the recordset WHERE clause on the fly keeping all
workarea information as Filters, Scopes, Indexes, etc.
Ex
SET RECORDSET OPEN WHERE CLAUSE TO { { "table", "FIELDANEMYEAR = '2017'" } }
USE table
BROWSE() //only records that meet "where" clause
//we want to see some historic data
cOldQry := ADOWHERECLAUSE( SELECT(), "FIELDANEMYEAR = '2012'" )
BROWSE()
//Get back to current year
ADOWHERECLAUSE( SELECT(), cOldQry )
BROWSE()
Improvements and corrected bugs:
ADO_ORDCREATE after creating index wasnt opening it.
ADO_SEEK key with multiple fields with softseek on was going to eof instead of last key.
ADOFILE if connection not valid returns now .F. instead of HB_FAILURE.
ADOOPENCONNECT if engine its not supported or wrongly set alerts the user.
ADOBEGINTRANS if workarea not specified opens a transactions for each opened connection
ADOCOMMITTRANS and ADOROLLBACKTRANS the same as ADOBEGINTRANS
New function ADONESTEDTRANS( nWA ) returns nr of nested transactions
NEW ERROR CODE 10600 - connection not available cant find transactions
ADO_ALREADYOPEN Test if the open recordset has same Sql query statement.
ADORECCOUNT Table name was not being converted to get fieldrecno if any defined
for that table always returned that default one.
ADOPREOPENTHRESHOLD Faster counting records. Doesn't cache any tables with WHERE clause.
All ADO cursors changed in the code from adopendynamic to adopenstatic, although it always
started as adopenstatic.
New features:
New option PORT in SET ADO DEFAULT DATABASE ...
ADO_ORDINFO ordwildseek implemented
Multibag orders support with new SET:
SET ADODBF MULTIBAG INDEX LIST TO { { "INDEX NAME", {TAGORDER 1","TAGORDER2"} } }
This allows multibag index file support exactly the same as any other DBf rdd.
ATTENTION
Now when SET AUTOOPEN ON doesn't open all index files automatically in SET ADODBF INDEX LIST instead
only opens the INDEX NAME present in SET ADODBF MULTIBAG INDEX LIST TO that match the table name.
Faster opening recordsets:
SET RECORDSET OPEN WHERE CLAUSE TO { { "TABLENAME", "SQL WHERE EXPRESSION" } }
ADOWHERECLAUSE ADORDD function allowing us to change the recordset WHERE clause on the fly keeping all
workarea information as Filters, Scopes, Indexes, etc.
Ex
SET RECORDSET OPEN WHERE CLAUSE TO { { "table", "FIELDANEMYEAR = '2017'" } }
USE table
BROWSE() //only records that meet "where" clause
//we want to see some historic data
cOldQry := ADOWHERECLAUSE( SELECT(), "FIELDANEMYEAR = '2012'" )
BROWSE()
//Get back to current year
ADOWHERECLAUSE( SELECT(), cOldQry )
BROWSE()
Regards
Antonio H Ferreira
Antonio H Ferreira
Re: ADO RDD xHarbour
New AdoRdd at https://github.com/AHFERREIRA/adordd.git
Regards
Antonio H Ferreira
Antonio H Ferreira
Re: ADO RDD xHarbour
New AdoRdd at https://github.com/AHFERREIRA/adordd.git
Regards
Antonio H Ferreira
Antonio H Ferreira
Re: ADO RDD xHarbour
New AdoRdd at https://github.com/AHFERREIRA/adordd.git
Performance enhancement opening tables.
SET RECORDSET OPEN WHERE CLAUSE TO {{"MYTABLE","HBRECNO < 51000" },;
{"MYTABLE2","HBRECNO BETWEEN 1 AND 50000" }}
This SET together ADOWHERECLAUSE( nWa, cNewSql ) enable us to open tables using the set of data we really need without altering any code logic.
This SET is used as default query to open for each table present here.
The ADOWHERECLAUSE( nWa, cNewSql ) can be placed in strategic places within our app code to change or to avoid to bring all the records to the set.
Tables till 100K dont need to worry.
Usually its slower to build the set than to read data from the database.
Recordset s with more than 100 columns will start to take long time to build.
I´ve seen recordset s with 800 columns! This takes long time even with a couple of thousands of records!
Tables with a couple of millions of records if we dont use any WHERE clause will exhaust memory very fast or we even dont have enough memory to run our app.
Caching the tables (recordsets):
This enable to cache the tables to avoid read the data again every time we open these tables
with the same query.
This SET uses the queries defined with SET RECORDSET OPEN WHERE CLAUSE TO.
Setting TO 0 adordd will not cache any table based on nr of records.
In this case adordd will only load the tables in MASK.
In databases with tables of millions of records if we choose to cache tables for ex > 50k records we must have a defined query for those tables otherwise adordd will cache all the records in the table.
If there are 10.000k program most probably will crash!
We need also to pay some attention to MASK to avoid loading tables we dont need.
Suppose that we have loaded, when we import our data on our database, our tables with paths:
These will be the table names in the database.
CURRENTPATH_MYTABLE
HISTORICOATH_MYTABLE
With above SET adordd will cache both.
So in this case we can do:
SET ADO PRE OPEN THRESHOLD TO 0 MASK {"CURRENTPATH\MYTABLE","CURRENTPATH\MYTABLE2"}
To avoid cache for ex other historic tables with same name.
Please remember that even we dont use this SET every time a table is opened it will be cached.
Again if any of those tables have millions of records if we dont have any query to limit the nr of records returned adordd will load all those millions!
ATTENTION
By default adordd caches all tables with more than 6000 recs when opening it.
Please adjust it in adordd to another value of your choice and we should have a query to limit records
for the tables with more than 500k.
Performance enhancement opening tables.
SET RECORDSET OPEN WHERE CLAUSE TO {{"MYTABLE","HBRECNO < 51000" },;
{"MYTABLE2","HBRECNO BETWEEN 1 AND 50000" }}
This SET together ADOWHERECLAUSE( nWa, cNewSql ) enable us to open tables using the set of data we really need without altering any code logic.
This SET is used as default query to open for each table present here.
The ADOWHERECLAUSE( nWa, cNewSql ) can be placed in strategic places within our app code to change or to avoid to bring all the records to the set.
Tables till 100K dont need to worry.
Usually its slower to build the set than to read data from the database.
Recordset s with more than 100 columns will start to take long time to build.
I´ve seen recordset s with 800 columns! This takes long time even with a couple of thousands of records!
Tables with a couple of millions of records if we dont use any WHERE clause will exhaust memory very fast or we even dont have enough memory to run our app.
Code: Select all
....
//Ex
USE "MYTABLE" // 10.000.000 RECS with SET RECORDSET OPEN WHERE CLAUSE TO will load only 51.000 recs
....
// Here we will need other set of data
//we have to be sure that with this new query our app code can run all necessary routines
nRecno := RECNO()
cOldSql := ADOWHERECLAUSE( SELECT(), "INVOICEDATE > '2016/04/01'" )
//working with the new set
//all workarea data, indexes etc continue exactly the same
//finish working get back to previous set
ADOWHERECLAUSE( SELECT(), cOldSql )
GO nRecno
....
Code: Select all
SET ADO PRE OPEN THRESHOLD TO 0 MASK {"MYTABLE","MYTABLE2"}
with the same query.
This SET uses the queries defined with SET RECORDSET OPEN WHERE CLAUSE TO.
Setting TO 0 adordd will not cache any table based on nr of records.
In this case adordd will only load the tables in MASK.
In databases with tables of millions of records if we choose to cache tables for ex > 50k records we must have a defined query for those tables otherwise adordd will cache all the records in the table.
If there are 10.000k program most probably will crash!
We need also to pay some attention to MASK to avoid loading tables we dont need.
Suppose that we have loaded, when we import our data on our database, our tables with paths:
These will be the table names in the database.
CURRENTPATH_MYTABLE
HISTORICOATH_MYTABLE
With above SET adordd will cache both.
So in this case we can do:
SET ADO PRE OPEN THRESHOLD TO 0 MASK {"CURRENTPATH\MYTABLE","CURRENTPATH\MYTABLE2"}
To avoid cache for ex other historic tables with same name.
Please remember that even we dont use this SET every time a table is opened it will be cached.
Again if any of those tables have millions of records if we dont have any query to limit the nr of records returned adordd will load all those millions!
ATTENTION
By default adordd caches all tables with more than 6000 recs when opening it.
Please adjust it in adordd to another value of your choice and we should have a query to limit records
for the tables with more than 500k.
Regards
Antonio H Ferreira
Antonio H Ferreira
Re: ADO RDD xHarbour
New AdoRdd at https://github.com/AHFERREIRA/adordd.git
Improved speed DBEval
Cache tables and recordsets enhancement
Improved speed DBEval
Cache tables and recordsets enhancement
Regards
Antonio H Ferreira
Antonio H Ferreira
Re: ADO RDD xHarbour
New AdoRdd and tryadordd at https://github.com/AHFERREIRA/adordd.git
Regards
Antonio H Ferreira
Antonio H Ferreira
Re: ADO RDD xHarbour
My main work now with AdoRdd its database with tables between 5.000 and 10.000.000 records building recordsets between 5.000 and 100.000 records and 70 columns in average.
The work its on a 1Gb LAN.
In the bigger tables if the server has indexes for the WHERE clause its pretty fast < 3 Secs building the recordset.
The memory consumption gets to max 600Mb.
I can have > 50 recordset opened at same time.
With the cached recordsets memory consumption will not increase.
AdoRdd can cache the recordsets in the beginning of the application with:
SET ADO PRE OPEN THRESHOLD TO
In this case app waits a little more to start but then all table opening its lightning fast.
Normally to display a window with > 20 tables its less < 1sec.
If we choose not to use this SET AdoRdd will use as cache already opened tables.
Thus the second time it will be also very fast.
With tables of this dimension we absolutely need a default open WHERE clause otherwise application would crawl and crash. use SET RECORDSET OPEN WHERE CLAUSE TO.
Please pay attention to these WHERE clauses otherwise you might not be able to see new additions as soon as AdoRdd requery.
Punctuality we might need to reduce the recordset for ex for sum SUM operations.
This is where we use:
cOldSql := adowhereclause( Select(), "where clause" )
SUM ...
adowhereclause( Select(), cOldSql )
Hope this might help you.
The work its on a 1Gb LAN.
In the bigger tables if the server has indexes for the WHERE clause its pretty fast < 3 Secs building the recordset.
The memory consumption gets to max 600Mb.
I can have > 50 recordset opened at same time.
With the cached recordsets memory consumption will not increase.
AdoRdd can cache the recordsets in the beginning of the application with:
SET ADO PRE OPEN THRESHOLD TO
In this case app waits a little more to start but then all table opening its lightning fast.
Normally to display a window with > 20 tables its less < 1sec.
If we choose not to use this SET AdoRdd will use as cache already opened tables.
Thus the second time it will be also very fast.
With tables of this dimension we absolutely need a default open WHERE clause otherwise application would crawl and crash. use SET RECORDSET OPEN WHERE CLAUSE TO.
Please pay attention to these WHERE clauses otherwise you might not be able to see new additions as soon as AdoRdd requery.
Punctuality we might need to reduce the recordset for ex for sum SUM operations.
This is where we use:
cOldSql := adowhereclause( Select(), "where clause" )
SUM ...
adowhereclause( Select(), cOldSql )
Hope this might help you.
Regards
Antonio H Ferreira
Antonio H Ferreira