FiveTech Support Forums

Dear All,

How can I use SQL Statement with ADORDD together.
If I use FW_OpenRecordSet( oCn, cTable ) and how to combine use with "USE" Statement (ADORDD) for more easy to read/write db.

I try to "USE" with cQuery but it is not successful and "USE" and DbSetFilter() and SET RELATION TO is taking more time, because the Main data has 3300 records but Child data has 33x,xxx records.

Regards,
Dutch

Dutch,

What is the size of the child table (MB) ?
Where is the server located ? LAN, WAN ?

When you say its slow you mean until open the recordset ? After navigating through it the speed its ok?
How long its taking to open it?

The problem its the same with ADO functions or ADORDD if you dot use a WHERE clause you are bringing all the records to the recordset. Depending on table size and connection speed this can take some time.

Try this :

include adordd.ch
USE childtable QUERY "SELECT * FROM childtable WHERE yourcondition"
browse()
quit

Is it fast?

Please note that this way of doing it I never test it because we dont use it but it should work ok.

    cSTime := time()
    USE ccrgst ALIAS 'gst' NEW SHARED
    cETime := time()
    MsgInfo('Open Time : '+cSTime+' - '+cETime+' Reccount() : '+Str(gst->(Reccount())) )

AHF wrote:Dutch,

What is the size of the child table (MB) ?
Where is the server located ? LAN, WAN ?

When you say its slow you mean until open the recordset ? After navigating through it the speed its ok?
How long its taking to open it?

The problem its the same with ADO functions or ADORDD if you dot use a WHERE clause you are bringing all the records to the recordset. Depending on table size and connection speed this can take some time.

Try this :

include adordd.ch
USE childtable QUERY "SELECT * FROM childtable WHERE yourcondition"
browse()
quit

Is it fast?

Please note that this way of doing it I never test it because we dont use it but it should work ok.

Dutch,

Thats a problem with ADO and SQL way of working.
If you have a CursorLocaton := adUseServer it will be fast opening but then navigating it it will be quite slow.

The best way is to create historic files to keep working tables small but this will break compatibility code.

SET RELATION TO does not take any time.
Thats because you are setting another index order and that will initiate a new SELECT with ORDER BY.

You will have this problem working with adordd or adofuncs or opening yourself the recordset directly.

I dont know how libs like tmysql work but I think they load also all the query result to an array.So probably the problem will be the same but you should try it.

By the way these 26 Secs are only open the recordset or are already using a index (ex its AUTO OPEN) ?
Is your DBF recsize 1,2 K ?
Is with SET REALTION the navigation in a browse fast?

AHF wrote:Dutch,

Thats a problem with ADO and SQL way of working.
If you have a CursorLocaton := adUseServer it will be fast opening but then navigating it it will be quite slow.

The best way is to create historic files to keep working tables small but this will break compatibility code.

SET RELATION TO does not take any time.
Thats because you are setting another index order and that will initiate a new SELECT with ORDER BY.

You will have this problem working with adordd or adofuncs or opening yourself the recordset directly.

I dont know how libs like tmysql work but I think they load also all the query result to an array.So probably the problem will be the same but you should try it.

By the way these 26 Secs are only open the recordset or are already using a index (ex its AUTO OPEN) ?
Is your DBF recsize 1,2 K ?
Is with SET REALTION the navigation in a browse fast?

USE CCRGST ALIAS 'gst' ;
     QUERY ("SELECT * FROM ccrgst WHERE GST_INTNO='"+cGstIntno+"'") NEW SHARED

      oRecordSet:Open( aWAData[ WA_QUERY ], aWAData[ WA_CONNECTION ],,,adCmdTableDirect )

Description : Error ADODB.Recordset/6 DISP_E_UNKNOWNNAME: OPEN
Args:
[ 1] = C SELECT * FROM ccrgst WHERE GST_INTNO='0000355406'
[ 2] = O ADODB.Connection
[ 3] = U
[ 4] = U
[ 5] = N 512

Stack Calls
===========
Called from : => TOLEAUTO:OPEN(0)
Called from : D:\V6\AdoRdd\adordd.prg => ADO_OPEN(296)
Called from : => DBUSEAREA(0)
Called from : D:\V6\TADORDD\test.prg => GETGSTNAME(167)

Dutch,

So the tables are:

Parent 4000 recs - Should be opening fast
Child 330.000 recs - Slow.

For sure you dont never need the 330.000 when the parent is only 4000.
I think parent its related with some activity period. For ex. orders in 2015.
If you have any date field in child table refreing the period or last order date then you could do:

cDatePerido := "01/10/2015"

USE child WHERE "child.someFieldDate >= "+dDatePeriod

This should be always much small than parent. So it would pretty fast.

Could you perhaps do it like this?

I have same tables with more records than yours child but because the recsize its small around 200 Bytes its still pretty fast.

In SQL we must pass historic data for new tables or have some kind of date field to test WHERE clause allowing you to load only the records referring to the period we are actually working with.
Bringing all records its not needed and doing it will have a performance cost to pay.

AHF wrote:Dutch,

GST_INTNO its a int nr ?
If so do it like this:

Code (fw): Select all Collapse

USE CCRGST ALIAS 'gst' ;
     QUERY ("SELECT * FROM ccrgst WHERE GST_INTNO="+cGstIntno) NEW SHARED

Ive found a bug in USE with QUERY.
When opening it works ok but when you changed the order index it will not respect the initial query WHERE clause.

Is it working USE like that? How many recs did you load? Is it fast?
Please remember only for trials and make sure you dont change order after otherwise adordd will issue again:

"SELECT * FROM ccrgst ORDER BY ....."

Ill correct this bug but only in August because Ill go on holidays tomorrow.