Re: What to use for ADO parameter type and size

Will have a look at those options.
I once did look at making a big IN construction, instead of the loop, so PATIENT_ID IN(1, 2, 3, etc.
but somehow that didn't gain much.
As you say, maybe it has to be divided in smaller chunks.

Yes, you are right about losing the PATIENT_ID column, thanks for the suggestion.

Another possible gain is in the transfer from the ADO recordsets to the SQLite table:

1790 Set rs = cmdADO.Execute(, arr2(i, 0))

1800 If Not rs.EOF Then

1810 With Cmd 'this is the dhSQLite cCommand
1820 While Not rs.EOF

1830 For c = 0 To LC
1840 Select Case arrDataTypes2(c)
Case 0
1850 .SetText c + 1, rs(c)
1860 Case 1
1870 .SetInt32 c + 1, rs(c)
1880 Case 2
1890 .SetDouble c + 1, rs(c)
1900 End Select
1910 Next c

1920 .Execute

This must be inefficient as every recordset row needs looping through the columns to determine the data-type
and move to SQLite accordingly. Will have a look at that as well.


RBS


"Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom> wrote in message news:uBVG78H0HHA.1164@xxxxxxxxxxxxxxxxxxxxxxx

"RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> wrote in message news:OV7c9GH0HHA.1168@xxxxxxxxxxxxxxxxxxxxxxx
Can be single can be multiple.
G2 is a code for hypertension and one patient could
have more than one of these entries.

I was going to suggest using a Record object in combination with a Command object.
Depends if the Interbase provider supports output parameters.
You lose the overhead of constructing a rowset. It is about 15% faster for single rows.
I do this when looking up large number of single rows that may or may not exist.

At the very least you can lose one column

This can be

SELECT
ENTRY_ID,
READ_CODE,
ADDED_DATE,
START_DATE
FROM
ENTRY
WHERE
READ_CODE LIKE 'G2%' AND
(NOT DORMANT_FLAG = 1) AND
PATIENT_ID = 979

as you already know PATIENT_ID

The other possibility is to constuct a command object to take 5 PATIENT_ID's and look up 5 at a time (beating Network traffic).

Stephen Howe


.