ADO RDD xHarbour

[James Bott]

Antonio F,

OK, I exported the customer table from the SQL database to a DBF, dropped the table, then recreated the SQL table so the HBRECNO field would be created. And imported the records back in from the DBF. Then the temp index began working. Problem solved.

Thanks for your help.

James

[AHF]

James,

Glad to know.

By the way did you make all that with APPEND FROM or and COPY TO?

When a table is created with adordd it checks automatically if recno field exists and if not creates it.

When you use copy to from dbf to sql since it calls ado_create the recno field its automatically checked you dont need to include it in the structure.

[James Bott]

Antonio F,

Yes, I did a COPY TO a dbf, then built the SQL table from the structure of the DBF (and I did know that the HBRECNO field would be added automatically). Then I did an APPEND FROM the dbf. Fairly simple now that I have the code written.

I am wondering about these two lists:

SET ADO TABLES INDEX LIST TO ...

SET ADODBF TABLES INDEX LIST TO ...

What is the difference between the ADO and the ADODBF designations?

Since they both contain the same lists aren't they redundant? Or, are there instances where they don't contain the same lists?

James

[James Bott]

Antonio F,

You asked previously about:

SET ADO FORCE LOCK ON

I had a comment in my code that it was required, and you asked why. Well, I found that if you try to do an APPEND FROM with lots of records it will crash without the above statement. I had it crash once at 500 something records and another time at 1300 something records. So there is something going on that is not consistent which leads me to believe it may have to do with memory and/or timing. I am also wondering if it is doing a commit after each record which can account for how slow it appends records. A single commit after every 100 records would increase the speed 100 times. I really can't see any reason why it would need to do a commit after each record.

James

[AHF]

James,

Please check 01_readme.pdf

A) SET ADO TABLES INDEX LIST TO ….
----------------------------------------------------
This Set is used by the SQL engine to build select with order by.
Thus the fields must be separated by comma and it can include SQL functions or ASC DESC This Set cannot include Clipper/(x)Harbour functions as they are unknown to SQL.
Example:
SET ADO TABLES INDEX LIST TO { {"TABLE1",{"FIRST","FIRST DESC"} }, {"TABLE2" ,{"CODID","CODID"}} }

B) SET ADODBF TABLES INDEX LIST TO…
-----------------------------------------------------------
This Set is used to evaluate Clipper/(x)Harbour expressions such as:

&( indexkey( 0 ) )
OrdKey( )
Etc.

So it must contain your actual real index expressions.
Example:

SET ADODBF TABLES INDEX LIST TO {
{"TABLE1",{"FIRST","FIRST"} }, {"TABLE2"
,{"CODID","STR(CODID,2,0)"}} }


Summarize
When we use &(indexkey(0)) we can’t evaluate an index expression in ADO TABLES “NAME, DDATA, NVALUE” we will get an error but we can issue SQL SELECT like that.
Thus we need ADODBF TABLES expression “NAME+DTOS(DDATE )+STR(NVALUE) “ to do it.
So both must be defined one for SQL the other real index clipper type expressions.

[AHF]

James,

Antonio F,

You asked previously about:

SET ADO FORCE LOCK ON

I had a comment in my code that it was required, and you asked why. Well, I found that if you try to do an APPEND FROM with lots of records it will crash without the above statement. I had it crash once at 500 something records and another time at 1300 something records. So there is something going on that is not consistent which leads me to believe it may have to do with memory and/or timing. I am also wondering if it is doing a commit after each record which can account for how slow it appends records. A single commit after every 100 records would increase the speed 100 times. I really can't see any reason why it would need to do a commit after each record.

James

What do you mean by "memory and/or time"?

COPY TO and APPEND FROM the source area rdd calls ado_append and then ado_putvalue for each record.
Both these functions call :update() each time.
This might be the reason of taking so long?

Do you mean we should change the :cursorType to adLockBatchOptimistic start a transaction and :updatebatch only at the end?

May be there is a bug in locks as it should in COPY TO open the destination table exclusively only in APPEND FROM ADORDD locks record by record.
Ill check it.

Thanks for your helpful feed back.

[James Bott]

Antonio F,

Thanks for the detailed explanation of the indexes. It helps a lot. I thought we were going to have to give up the FW functions, but it seems not. This is great news to me.

James

[James Bott]

Antonio F,

What do you mean by "memory and/or time"?

Maybe I should have said RAM and timing. In the past I have had programs crash when they ran out of RAM. And also, the OS sometimes lags and the program is expecting something to be done that isn't so you get a error that isn't trapped in your code. Both of these are very hard to solve since they are not exactly repeatable.

COPY TO and APPEND FROM the source area rdd calls ado_append and then ado_putvalue for each record. Both these functions call :update() each time. This might be the reason of taking so long?

Oh, it absolutely is the reason.

Do you mean we should change the :cursorType to adLockBatchOptimistic start a transaction and :updatebatch only at the end?

Well it does seem that there is very little chance of someone else trying to access a DBF while it is being created. This is generally a maintenance process, and probably being done when nobody is using the system. Or possibly an export to be used by another program.

May be there is a bug in locks as it should in COPY TO open the destination table exclusively only in APPEND FROM ADORDD locks record by record.

It would seem proper to COPY TO exclusively.

With the APPEND FROM, possibly there could be a programming option to set the locking interval; x records or all records. Like I mentioned in a previous post, my calculations were that it would take about 12 hours to append a million records to a local drive (longer on a network drive). So, initializing a large database could take several days with multiple millions of records. A business would have to shut down to do this.

If I remember correctly, it takes almost the same time to append 100 records (or even 500) followed by a single update, as it does to append 1 record and update. Updates are really slow for some reason.

Yesterday I showed a client a sample of one of his data tables running in an SQL engine. He was very pleased to see it.

Keep up the great work you are doing.

James

[James Bott]

Antoino F,

I forgot to mention that an UPDATE forces the disk buffer to be written to disk, so this the reason the update is so slow. If there is only an UPDATE after 100 records (or even 500) there is still probably (depending on the buffer's size) only one disk write, thus the great increase in speed.

James

[AHF]

James,

Unfortunately I haven't find any way to adordd copy to has enough speed to allow copy to from dbf to sql millions of records. It is really to slow.

adordd copy to is good for copying a small set of records ( couple hundreds ) from or to dbfs or from sql to sql.

In order to import big dbfs file to sql you can use adofuncs that might do the job much quicker.

I've tried to work with :updatebatch() but then I have then problems with recno autoinc field.
May be Mr Rao can help on this subject.

I've made in meantime some improvements in speed and corrected some index management to mimic exactly index behavior and copy to now copies all records both with SET ADO FORCE LOCK ON or OFF.

Ill post a new version within the next days.

[James Bott]

Antonio F,

Thanks for the update.

I didn't see any mention of changing the commit frequency. Have you looked at that?

I will be looking forward to the updated ADORDD.

I have been working on tests here comparing the results of ADORDD vs DBFCDX drivers and I haven't found any problems yet (other than the APPEND speed). Thanks again for all your work.

James

[AHF]

James,

In adordd we cant control commit frequency, as far as I know, with :addnew method.
As soon as we move or add new record the previous its auto committed because ado auto calls :update().

Being other rdd than ado in source area the loop its controlled by that rdd and we cant do anything else.

On the other hand when the source rdd its adordd then we could be much faster because its adordd controlling the loop through ado_trans.
Here we could use the following:

SELECT * INTO newfile FROM currentfile

But we have a limitation we can evaluate the while or for code blocks because we only receive it as a code block thus disabling us to use it as WHERE clause expression so we will end with the same speed problem.

The only way out is to have a hb_adorddcopyto( origin file, dest file, where clause ).
Here its very fast because its pure SQl treated as above.

But I dont know if it is worth while to build such function because you can do it directly from app code and doesn't interfere or take advantage with adordd.

Ex

Code: Select all  Expand view

//get adordd connection to have it in same transaction processing
hb_GetAdoConnection():Execute( "SELECT *  INTO newfile FROM currentfile WHERE expression" )

USE newfile  /this is adordd
....
 

If there is any way to get from the code block the char expression then we could do it completed integrated in adordd otherwise I cant see how we can do it.

[Antonio Linares]

Antonio,

If there is any way to get from the code block the char expression

Unfortunately no, unless we save it as a string value in advance

[AHF]

James,

Yes, I did a COPY TO a dbf, then built the SQL table from the structure of the DBF (and I did know that the HBRECNO field would be added automatically). Then I did an APPEND FROM the dbf. Fairly simple now that I have the code written.

I forgot to mention that you can do it with just one step

Code: Select all  Expand view

USE dbf VIA "DBFCDX"
COPY TO sqltable  VIA "ADORDD" //if not the default rdd
 

After investigating, the fastest way to import big amounts of data to MySql is to export it first to csv file and then import it using LOAD DATA statement. I think other DBs engines have alternatives like this.
You can try it like using the example in my previous post.

In adordd we can do anything else to improve it and maintaining 100% compatibility with existing app code.

[AHF]

New version of ADORDD at https://github.com/AHFERREIRA/adordd.git

Changes:

Faster opening recordsets
Recordsets are now opened by recno order when no index is active as any dbf
Tables without auto inc fieldto be used as recno throws an error whe opening it
Reccount faster
Copy To (from other rdd to adordd) works ok with both set ado force locks on / off