.EOF takes a really long time!

[April15Hater]

Hi Guys-

I know most of you are access experts, but I have a problem with an oracle database. I'm using Excel VBA to tap an Oracle DB via ADO. My problem is that .EOF takes a really long time. Wondering if there are any suggestions on how to speed this up...? Code is below, and my problem begins at the line- "If .EOF Then NoErrors = True"

Public Function OpenADOConnection()
Dim cnProductionDatabase As ADODB.Connection
Dim dbConnection As String
'DEFINE LOCATION OF THE DATABASE
dbConnection = "Provider=MSDAORA.1; Password=focus; User ID=cis; Data Source=" & Left(Worksheets("Main Sheet").Range("C6"), 4)
'Open the DB connection
If cnProductionDatabase Is Nothing Then
    Set cnProductionDatabase = New ADODB.Connection
    cnProductionDatabase.Open dbConnection
End If
Set OpenADOConnection = cnProductionDatabase
End Function
 
Function StrandAddVsHouse(MapNumber As String, RecordNumber As Long) As Variant
Dim rsOracle As ADODB.Recordset
Dim IntX As Long
Dim pRecordNumber As Integer
Dim NoErrors As Boolean
Dim rsArray(0 To 4)
Set rsOracle = New ADODB.Recordset
With rsOracle
    .ActiveConnection = OpenADOConnection
    .CursorType = adOpenDynamic
    .LockType = adLockReadOnly
    .Source = "SELECT hscount.mapname, hscount.mslink, count(address.hcmslink) AS CountHC, hscount.act_count, poleped.cell_name " _
        & "FROM Address, hscount, poleped " _
        & "WHERE address.hcmslink= hscount.mslink(+) and poleped.mslink= hscount.pole_link and hscount.mapname = '" & UCase(MapNumber) & "' " _
        & "GROUP BY hscount.mapname, hscount.mslink, hscount.act_count, poleped.cell_name " _
        & "HAVING count(address.hcmslink)<> hscount.act_count and poleped.cell_name<> 'PLENUL' "
    .Open
    For IntX = 1 To RecordNumber
        If .EOF Then NoErrors = True
        If Not .EOF Then .MoveNext
    Next IntX
    .Close
    .Open
    Do Until .EOF Or pRecordNumber = RecordNumber
        rsArray(0) = !MapName
        rsArray(1) = !mslink
        rsArray(2) = !CountHC
        rsArray(3) = !Act_Count
        rsArray(4) = !Cell_Name
        pRecordNumber = pRecordNumber + 1
        .MoveNext
    Loop
    If NoErrors Then rsArray(0) = "END"
    StrandAddVsHouse = rsArray
End With
End Function

 

[Banana]

Just to clarify... It takes long to process single line where .EOF is checked or is it the loop that takes long?

 

[April15Hater]

Hey buddy!

No, the loop is fine, in fact nine times out of ten that 'RecordNumber' variable is going to be 1 anyways. It just hangs on the single line when I'm stepping through it.

I also just made another observation that the very next line (If Not .EOF then .MoveNext) does not hang....hmm.

Thanks,

Joe

 

[Banana]

Curious.

Just to be complete...

1) Does it hang if you do this in the immediate windows:

?.EOF

Note: we have to be in break mode and at that bad line or the immediate window will crap out with an error

2) If it doesn't hang, test each separately:

Debug.Print .EOF
Debug.Print NoErrors

See what results are.

3) Reset everything and a fresh point but set a breakpoint prior to the bad line. Hit that breakpoint, then skip directly to the next line, and see if it hangs this time. (to rule out whether .EOF hangs only for the first time it's called)

 

[April15Hater]

1) Yes, it hung
2) Did this anyways, and it hung. That line is 2 steps, but the EOF is where it's hanging.
3) I'm a little confused here....so breakpoint at the loop, skip "If .EOF...." and run "If Not .EOF"? If so, then yes, it hung.

I'm starting to think it is the SQL I am running, but I figured if that were the case, it would hang on .open... I ran the SQL in our oracle interface, and it hung there too. I know you need to know the table structure etc. to analyze the SQL, but I posted it anyways in case you might see something off the top of your head.


Thanks!

SELECT hscount.mapname, hscount.mslink, count(address.hcmslink) AS CountHC, hscount.act_count, poleped.cell_name 
FROM Address, hscount, poleped 
WHERE address.hcmslink= hscount.mslink(+) and poleped.mslink= hscount.pole_link and hscount.mapname = '471-480' 
GROUP BY hscount.mapname, hscount.mslink, hscount.act_count, poleped.cell_name 
HAVING count(address.hcmslink)<> hscount.act_count and poleped.cell_name<> 'PLENUL'

 

[Banana]

Forgot to ask if 2nd and subsequent calls to .EOF would hang as well... But at least we can rule out the problem being with the line.

Now, let's see... We're doing a outer join with a inner join and a count. I would say that 2nd criteria in HAVING (poleped.cell_name<>...) can be moved into WHERE but don't know if that's the silver bullet we want.

If you omit the aggregrating/grouping (and keeping the criteria except for the first criteria in HAVING), does the SQL execute fast in the Oracle?

 

[DCrake]

    For IntX = 1 To RecordNumber
        If .EOF Then NoErrors = True
        If Not .EOF Then .MoveNext
    Next IntX

What is this actually doing? What is its purpose?

It seams to me that all you need to do is check the .RecordCount

You don't seem to be using IntX or RecordNumber anywhere

David

 

[April15Hater]

Banana- If I take out the aggregation, I get the error: 'not a GROUP BY expression'

David- What's happening here is that we are checking oracle for user input errors. If there are no errors then no records will appear. If there are records, then there are errors and it needs to record them (done in another function). I suppose I could use .recordcount but I've always have trouble with that function's consistency. I recall it wanting to return a -1. The RecordNumber variable however is passed into the function. The IntX variable is not use anywhere else.

Thanks!

 

[Atomic Shrimp]

It looks like it might be a way of seeking a specific numbered record. Not sure why this would be desirable, as opposed to finding that record by its primary key.

 

[Banana]

When you take out the aggregation, you have to take out the group by clause as well

 

[April15Hater]

I did take that out...or am I way off here:

SELECT hscount.mapname, hscount.mslink, hscount.act_count, poleped.cell_name 
FROM Address, hscount, poleped 
WHERE  poleped.mslink= hscount.pole_link and hscount.mapname = '471-480' 
HAVING poleped.cell_name<> 'PLENUL'

Atomic Shrimp- I'm not looking for a specific record number, I'm looking for a record that meets certain critereon. The RecordNumber variable is there because if the query returns more than one record then it needs to know what record number to come back to when it recalls the query. I suppose it is faulted in that it has to recall the same query numerous times if there are multiple errors.

Here is the calling code:

'Begin Error Reporting
''Add vs. HouseCount Error Report
RowCount = 1: ColumnCount = 0: RecordCount = 1
ErrorList1 = StrandAddVsHouse(Worksheets("Main Sheet").Range("C8"), RecordCount)
Do Until ErrorList1(0) = "END"
    If RecordCount = 1 Then 'Write headers
        With Worksheets("ERROR REPORT")
            .Cells(RowCount, 1) = "Add vs. House Count": RowCount = RowCount + 1
            .Cells(RowCount, 1) = "Map"
            .Cells(RowCount, 2) = "MSLink"
            .Cells(RowCount, 3) = "Count"
            .Cells(RowCount, 4) = "Act_Count"
            .Cells(RowCount, 5) = "Cell_Name"
        End With
        RowCount = RowCount + 1
    End If
    ErrorList1 = StrandAddVsHouse(Worksheets("Main Sheet").Range("C8"), RecordCount)
    If ErrorList1(0) <> "END" Then
        With Worksheets("ERROR REPORT")
            For ColumnCount = 1 To 5
                .Cells(RowCount, ColumnCount) = ErrorList1(ColumnCount - 1)
            Next ColumnCount
        End With
        'Count Errors and write to Strand Coversheet
        With Worksheets("STRAND COVERSHEET")
            .Cells(8, 11) = .Cells(8, 11) + 1
        End With
    End If
    RecordCount = RecordCount + 1: RowCount = RowCount + 1
Loop
''_____

 

[Banana]

HAVING is a part of aggregate process so it also have to be taken out as well. Move that criteria into WHERE because it doesn't have to be in the HAVING. HAVING should be reserved only for evaluations that would be otherwise impossible *before* aggregating (e.g. counting how many counts each group had would be a good candidate for HAVING, but examining whether a name matches a criteria should be in WHERE)

Hope that helps clarifies things.

 

[April15Hater]

Sure does....it is still lagging though

 

[Banana]

Okay, good. We now know it's not aggregating but simply selecting the records. Furthermore, you did that in Oracle interface, no?

If so, then you are now sure that the problem does not exist in your VBA code or how you are querying the data and thus need to pass it to the Oracle DBA. If you're also the Oracle DBA, then you would probably want to check if any index exists for all important columns that's used in JOINs, WHEREs, HAVING & GROUP BY criteria. Maybe index exists, but needs to be rebuilt as well.

Hope that helps you get somewhere.


EDIT: Though, it still bother me that it hangs on a simple .EOF check. If it was a .MoveLast, then well, that's sensible. But EOF? How odd.

 

[April15Hater]

That's what is bothering me too, how could an EOF cause this much drama? But I would put a crater in my desk about the size of my forehead if my problem boiled down to indexing again. On a quick side note, that access database is screamin now that I fixed the indexes there.

So now to the Oracle DBA problem....There is no DBA. I guess you could call our production manager the DBA, but he knows about as much about SQL as I do. Reason being is that the Oracle db is just the backbone to an Autocad based software that we use for 75% of our production. I would assume that the software would have some provisions to reindex when need be and take care of all the upkeep, but I could be wrong.

Correct me if I'm wrong, but aren't indexes in Oracle handled through SQL commands rather than a nice little drop down in access?

Thanks,

Joe

 

[Banana]

I've not used Oracle but I wouldn't be all surprised that there wasn't any nice little drop down a la Access and you need to use Oracle SQL to rebuild. I would probably look in Oracle's manual or maybe ask at a Oracle-forums.

One more thing to test because of that .EOF oddity bothering me. Does it hang if you do something else other than .EOF (e.g. Movenext? Reading a field?) as the very first thing you do right after opening the recordset. (e.g. skip over any lines that deals with .EOF and go directly to unrelated methods)

 

[Banana]

Some more info/questions:

1) It should be realized that with ADO, they get to write their own provider and specify as much as features they want the provider to support. I don't know know much about ADO provider architecture, but it may be possible that the Oracle's provider is written in such way that Open is instantaneous but does not actually load the recordset until a command is issued, which may be why Open doesn't hang. I hope that the test I recently asked would bear this out.

2) You mentioned indexing Access database. Are you also joining Oracle table to Access table?

 

[April15Hater]

1) Yes, we have some hanging on a '.movenext', maybe it doesn't 'open' per se until a command is thrown. Interesting.....

2) Nope... Not that good yet

Glad we have some kind of closure on that EOF thing though.