Page 10 of 70
Re: ADO RDD xHarbour
Posted: Wed Mar 25, 2015 4:23 pm
by Antonio Linares
Antonio,
I am checking the properties and methods of the ADO Connection object:
https://msdn.microsoft.com/en-us/library/windows/desktop/ms681546(v=vs.85).aspxPlease do a MsgInfo( oADODB:State ) before opening the recordset
Re: ADO RDD xHarbour
Posted: Wed Mar 25, 2015 6:15 pm
by AHF
Antonio,
We just found out that :
RecordCount isnt always accurate:
with adUseServer (cursor supported by the provider) always return the total nr of records in table even if there is a where clause in the select like Reccount()
with adUseClient (MS ccursor ) always return the true nr of rows in the recordset
Thus the OrdKeyCount mainly needed for scrollbars will not function properly with adUseServer cursor!
Re: ADO RDD xHarbour
Posted: Wed Mar 25, 2015 6:25 pm
by Antonio Linares
Antonio,
Many thanks for your great feedback
Re: ADO RDD xHarbour
Posted: Thu Mar 26, 2015 11:32 am
by AHF
Antonio,
OrdKeyCount and RECORDCOUNT might be a problem!
The only way to solve this is to:
If we use always adUseClient because ADO knows all rows and there ist any problem but performance decreases a lot. Not advisable!
IF we use adUseServer then with a WHERE clause ADO returns wrong RECORDCOUNT and OrdKeyCount!
The only solution we for this is :
If there is a WHERE clause we have to query the table with SELECT COUNT(*) FROM ctable WHERE ....
Thus in each RECORDCOUNT and OrdKeyCount values are correct.
We replace RECORDCOUNT by the following code:
Code: Select all | Expand
STATIC FUNCTION ADORECCOUNT(nWA,oRecordSet) //AHF
LOCAL aAWData := USRRDD_AREADATA( nWA )
LOCAL oCon := aAWData[WA_CONNECTION]
LOCAL nCount := 0, cSql:=""
IF oRecordSet:CursorLocation == adUseClient
nCount := oRecordSet:RecordCount
ELSE
IF LEN(aAWData[WA_INDEXES]) > 0 .AND. aAWData[WA_INDEXACTIVE] > 0
oRecordSet:close()
//Making it lightning faster
oRecordSet:CursorLocation := adUseServer
oRecordSet:CursorType := adOpenForwardOnly
oRsecordSetLockType := adLockReadOnly
//LAST PARAMTER INSERTS cSql COUNT(*) MUST BE ALL FIELDS BECAUSE IF THERE IS A NULL
//FIELD COUNTS RETURNS WRONG
cSql := IndexBuildExp(aAWData[WA_INDEXACTIVE],aAWData,.T.) //INCLUDE COUNT
msginfo("adoreccount 2 "+CSQL)
//LETS COUNT IT
oRecordSet:open(cSql,oCon)
nCount := oRecordSet:Fields( 0 ):Value
msginfo("adoreccount 3 NRREC "+CVALTOCHAR(NCOUNT))
oRecordSet:close()
//RETURNING TO DEFAULT DATA
oRecordSet:CursorType := adOpenDynamic
oRecordSet:CursorLocation := adUseServer //adUseClient never use ths very slow!
oRecordSet:LockType := adLockPessimistic
cSql := IndexBuildExp(aAWData[WA_INDEXACTIVE],aAWData,.F.) //.F. DONT INCLUDE COUNT
msginfo("adoreccount 4 "+CSQL)
oRecordSet:open(cSql,oCon)
ELSE
nCount := oRecordSet:RecordCount
ENDIF
ENDIF
RETURN nCount
Do you have any other alternative? This function its called a lot and I dont know what will be effect in the overall performance. In our small trials it seems ok but .....
I miss good old DBF or ADS:(
Re: ADO RDD xHarbour
Posted: Thu Mar 26, 2015 3:57 pm
by Antonio Linares
Antonio,
As far as I know oRs:RecordCount() returns the number of records of the recordset
so if you use a WHERE clause then you get less records.
Lets ask Mr. Rao
Re: ADO RDD xHarbour
Posted: Thu Mar 26, 2015 4:02 pm
by Enrico Maria Giordano
Antonio,
AHF wrote:If we use always adUseClient because ADO knows all rows and there ist any problem but performance decreases a lot.
Using adUseClient the performance is better than adUseServer!
EMG
Re: ADO RDD xHarbour
Posted: Thu Mar 26, 2015 4:39 pm
by AHF
Enrico,
Enrico Maria Giordano wrote:Antonio,
AHF wrote:If we use always adUseClient because ADO knows all rows and there ist any problem but performance decreases a lot.
Using adUseClient the performance is better than adUseServer!
EMG
What DB you use?
adUseClient would be much easier for adordd (bookmarks etc) but everything I read covering this indicates that adUseClient its much slower and should be avoid.
Please note that the I'm using SELECT * FROM ... in adordd thats its really not good SQL practice but compatibility much be achieved.
Re: ADO RDD xHarbour
Posted: Thu Mar 26, 2015 4:49 pm
by AHF
Antonio,
Antonio Linares wrote:Antonio,
As far as I know oRs:RecordCount() returns the number of records of the recordset
so if you use a WHERE clause then you get less records.
Lets ask Mr. Rao
Opening same dbf in 2 dif areas
One with normal index the RECORDCOUNT = nr of records dbf = ORDKEYCOUNT
Second with conditional index SELECT WHERE ... the RECORDCOUNT = nr of records dbf <> ORDKEYCOUNT that is = nr of records within the WHERE clause.
Moving through the recordset will soon or later get an error.
Please note that the recordset has in fact only the records within the condition although RECORDCOUNT reports all records.
This is using serverside cursor.
Re: ADO RDD xHarbour
Posted: Thu Mar 26, 2015 5:29 pm
by Enrico Maria Giordano
Antonio,
AHF wrote:What DB you use?
MDB, MSSQL and MySQL.
AHF wrote:adUseClient would be much easier for adordd (bookmarks etc) but everything I read covering this indicates that adUseClient its much slower and should be avoid.
adUseClient is faster in my experience.
AHF wrote:Please note that the I'm using SELECT * FROM ... in adordd thats its really not good SQL practice but compatibility much be achieved.
???
SELECT * FROM is the base of all SQL queries. What else would you want to use?
EMG
Re: ADO RDD xHarbour
Posted: Thu Mar 26, 2015 5:57 pm
by AHF
Enrico Maria Giordano wrote:AHF wrote:Please note that the I'm using SELECT * FROM ... in adordd thats its really not good SQL practice but compatibility much be achieved.
???
SELECT * FROM is the base of all SQL queries. What else would you want to use?
EMG
I mean avoid loading all the fields instead of just the ones we need for the task.
Re: ADO RDD xHarbour
Posted: Thu Mar 26, 2015 6:02 pm
by Enrico Maria Giordano
Antonio
AHF wrote:I mean avoid loading all the fields instead of just the ones we need for the task.
Ok, but what if I need all the fields?
EMG
Re: ADO RDD xHarbour
Posted: Thu Mar 26, 2015 6:10 pm
by AHF
Enrico Maria Giordano wrote:Antonio
AHF wrote:I mean avoid loading all the fields instead of just the ones we need for the task.
Ok, but what if I need all the fields?
EMG
Then SELECT colname FROM table unfortunatly in adordd its always all the fields! Its the price for no code change.
Re: ADO RDD xHarbour
Posted: Thu Mar 26, 2015 6:28 pm
by Enrico Maria Giordano
Antonio,
AHF wrote:Then SELECT colname FROM table unfortunatly in adordd its always all the fields! Its the price for no code change.
Ok, I see, you're right.
EMG
Re: ADO RDD xHarbour
Posted: Thu Mar 26, 2015 6:58 pm
by AHF
Antonio,
Can you find rdd function for close all ?
Re: ADO RDD xHarbour
Posted: Fri Mar 27, 2015 5:41 am
by Antonio Linares
Do you mean DbCloseAll() ?
I think you have to implement UR_CLOSE