Wierd ADODB.Recordset problem

[Ruairi]

I have been chasing this bug for a week and i can't seem to get anywhere. Maybe someone has some ideas?

    Dim YTrs As ADODB.Recordset
    Dim LogsRs As ADODB.Recordset
    Dim sql As String
    Dim Rec As Long
    Rec = -1
    If Not rescflag Then
        If Val(yardticket) <> 0 Then
            StartTime = Time
            Open &quot;c:\queries.txt&quot; For Append As #55
            Print #55, &quot;STARTING: &quot; &amp; Format(Time, &quot;Long Time&quot;)
            Close #55
            sql = &quot;select rec from loads where yardticket = '&quot; &amp; yardticket &amp; &quot;' and destinationrec = &quot; &amp; destinationrec &amp; &quot; and (c) and (d)&quot;
            OpenSubs.OpenForwardRecordset YTrs, sql
            Open &quot;c:\queries.txt&quot; For Append As #55
            Print #55, sql
            Print #55, &quot;FINISHING: &quot; &amp; Format(Time, &quot;Long Time&quot;)
            
******** This Line ********            
            If YTrs.BOF And YTrs.EOF Then
******** This Line ********        
               Rec = -1
            Else
                Rec = YTrs(&quot;rec&quot;)
            End If
            Print #55, &quot;VALUE OF REC SET: &quot; &amp; Format(Time, &quot;Long Time&quot;)
            OpenSubs.CloseRecordset YTrs
            Print #55, &quot;RECORDSET CLOSED : &quot; &amp; Format(Time, &quot;Long Time&quot;)
            Close #55
            EndTime = Time
            Print #100, &quot;FINDLOAD:yardtic

This is part of a quite involved process of converting and importing data to a SQL Server 6.5 table. Right now each load (one record worth of data) takes about 60 secs. to import. 45 secs. of that is spent evaluating the line between the ***** This Line ****** markers. This doesnt make any sense. This recordset is opened as a firehose cursor and so far in my testing it has returned no records. How can it take 45 seconds to see that .EOF and .BOF are true? This problem only occurs on one of my test systems, but i cant find a cause for it. Any ideas? I'm about ready to set this computer on fire, maybe that will help. This exact same type of operation is executed probably 20 other times in the course of importing a load and none of those take longer than a couple of milliseconds. Please help if you can think of anything.
TIA
Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[Vraylle]

Maybe I'm just dense (and I work with this sort of thing daily!), but how can BOF *AND* EOF be true at the same time?

 

[Ruairi]

It's a standard way of checking whether any records were returned. If .BOF and .EOF are true the recordset is empty. With a forward-scrollable recordset you doont have a recordcount property to check. The fields in the where clause of this querey comprise a unique constraint in the table so either .BOF and .EOF will be true, or one record will be returned, which is the value i set the Rec variable to. Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[Antzz]

Ok, have you tried dynamic and static cursors too? It might help to check them out before we come to any conclusion.

 

[Ruairi]

Antzz,
Thanks for your response but i don't think cursors will help. The only place i have used them with success is in stored procedures. Client side cursors have been super slow when i have used them(as they are known to be. every piece of documentation i have seen on writing SQL Server clients advises against using cursors except as a last resort.) I have found a work around that seems to be doing OK. (so far anyway). I am using a "select count(*)" querey with the same where clause so i know whether there will be records returned or not. It seems like it should be inefficient but so far is much better than the .BOF .EOF method(in this particular case anyway.) Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[MatsHulten]

How is the recordset opened?
When you open a recordset you have the option to open it asyncronysly (sorry for the spelling). IE, to start the download of, say 300 records, then get the referense to the recordset back, while ADO continues to populate the RS in the background.

If you have got a problem with this, it may be that you have gotten the control back from the open method before the download is complete, and so you will have to wait until the download is commplete before you can get a result to BOF and EOF.

-Mats

 

[Vraylle]

Neat trick (the BOF and EOF bit), by the way. I have no idea for a solution now...but will watch this thread with interest.

 

[Ruairi]

Mats,
Here is the code for 2 of my open recordset methods (from one of my data access DLL's). I was oroiginally using the OpenForwardRecordset method, which should be the fastest way to open a recordset. The where clause i am using in this case stipulates all the fields of a unique constraint, so there will only ever be one record returned. In the course of trying to fix this bug i tried loading all the necessary information needed by the procedure in the first post into an array prior to processing. This speeded it up overall but i still saw the same 40 second delay when testing if there were any records in the recordset used to populate the array. The next thing i tried was to use a count(*) querey before issuing the querey to fill the array. That eliminated the need to test for BOF or EOF on the array recordset. Then the delay started happening as i read the records from the array recordset (maybe on the .movenext method). I am totally mystified by this. I tried using the OpenStaticRecordset procedure below instead of OpenForwardRecordset (using a snapshot-type recordset instead of a forward scrolling) even though it should not be as fast. That seemed to help somewhat. Does this code answer your question about an asynchronous or synchronous recordset?

Public Sub OpenForwardRecordset(ByRef rs As ADODB.Recordset, ByVal sql As String)
    Set rs = New ADODB.Recordset
    AdjustQuery sql
    rs.Open sql, m_cn, adOpenForwardOnly, adLockReadOnly, adCmdText
End Sub

'**************************************
'Opens a scrollable read only recordset
'This is read only, move forward and backward
'**************************************
Public Sub OpenStaticRecordset(ByRef rs As ADODB.Recordset, ByVal sql As String, Optional ByVal CacheSize As Long = 350)
    Set rs = New ADODB.Recordset
    AdjustQuery sql
    If DatabaseType = enuDatabaseType.sql Then
        rs.Open sql, m_cn, adOpenStatic, adLockReadOnly, adCmdText
    Else ' DatabaseType = enuDatabaseType.access
        rs.Open sql, m_cn, adOpenStatic, adLockPessimistic, adCmdText
    End If
End Sub

Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[adio]

Did you ever find a solution to this? Or does anyone know of a solution. I am having the same problem now.

TIA

Adio