Needed: ADO guru 1

[mike777]

Hello,
I have the following code:
Sub MySub()
Dim ADORst as ADODB.Recordset, strCn as string
DimCnLive as ADODB.Connection, cmdADO as ADODB.Command
Set ADORSt = New ADODB.Recordset
Set CnLive = New ADODB.Connection
strCn="DSN=MyDSN; UID=MyID; PWD=MyPwd; " & _
"DBQ=MyDb; DBQ=W; APA=T; PFC=1; TLO=0"
CnLive.ConnectionTimeout=0
Set cmdADO=New ADODB.Command
strSQL="select * from myTable_in_oracle;"
cmdADO.CommandText=strSQL
With ADORst
.CursorType=adOpenStatic
.LockType=adLockPessimistic
.ActiveConnection=CnLive
End With
ADORst.Open cmdADO.CommandText
call
End Sub
==============================================
Sub MyNextSub(ADORst as ADODB.Recordset)
ADORst.MoveFirst
'more code...
End Sub

I'm new to ADO, and all this is kind of freakin' me out, but the first procedure seems to work with pretty good efficiency. The only thing I have problems with is in the next procedure, I pass the ADORst object, and do an ADORst.MoveFirst on it. The MoveFirst takes FOREVER. Not only that, but if the server is already being hit by a lot of activity when my code reaches the "MoveFirst" part, the ODBC driver times out, and I get an error message. I don't know what the deal is with respect to why an ADO recordset requires so many resources just to move to the first recordset, do you have any suggestions?
Also, I believe it is true that when the ADO recordset opens, it defaults to the first record. Is this correct?
Thanking you in advance for your help.
-Mike

 

[scking]

With ADO your second function is unnecessary since a MoveFirst command is not required.

----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[cmmrfrds]

I hope this is a very small table, since you are requesting and locking all the records and all the fields from the records.
strSQL="select * from myTable_in_oracle;"

Do you want to lock the entire table????
.LockType=adLockPessimistic

Explain what you are trying to do, I am sure there is a much more efficient way to handle your requirements. How many records in the table? Number of fields? etc...

 

[mike777]

Aha...cmmrfrds...thanks for pointing that out. Like I said...new to ADO. Didn't even know what that did. Ahem...

OK, so now I'm thinking that this lock setting might have something to do with the deplorable performance (actually, it's worse than deplorable...it doesn't work at all) of my query when the server is under a normal work load (average CPU [or processor, can't remember which] is around 80%). Is it true that my query has to wait until it CAN lock the table? If this is true, not making this a requirement would seem to help. Would you agree?

What I WAS trying to do was run four different queries against the table, and at the end of each one, transfer the data to an array. This way, at the end of the fourth query and array transfer, I would have three columns of data stacked from the four queries (identical columns, data the result of different criteria). NOW, however, I've learned how to create views in Oracle by replacing my SQL string "select ..." with "create or replace view MyView as select...". So I create the four views (passing the parameters as part of the SQL string), and then use the UNION ALL (via a string passed from my app, just like all the rest of the views) function in Oracle to "stack" the data.

This fifth and final view contains the data I need in my application, and opening the recordset with this final SQL (as ADO Command.CommandText, etc.) is the part that only works when the server is carrying a very low work load (it works exceptionally well at these times, too, I might add (takes about 8 seconds to query the table on three columns, which contains 250k records; 250 columns as well, as you inquired).

I've been pulling my hair out over this for...too long.

Since you asked......also, this fifth query is then processed by a sixth which uses the decode function to create, effectively, a pivot table, with additional columns added as calculations. That is really it in total. Any suggestions you might have for me in my ORAUQ (over-required-and-under-qualified) state of existence at this point would be very much appreciated.

Molto, molto grazi..

-Mike

 

[cmmrfrds]

Yes, I think the locking is causing the problem. It doesn't look like you need to do any locking since it doesn't appear you want to update the table - usually the reason for locking.


strSQL="select * from myTable_in_oracle;"
cmdADO.CommandText=strSQL
With ADORst
.CursorLocation = adUseClient '- probably best on client side, the default is server side, if you want to move around in the recordset.
.CursorType=adOpenStatic '- okay
.LockType=adLockReadOnly '- usually Optimistic best for updating since the record is only locked when it is about to be updated, in this case no lock is needed.
.ActiveConnection=CnLive
End With
ADORst.Open cmdADO.CommandText

Should only get the fields you need.
Select fld1, fld2, fld3 from myTable '- to reduce network traffic.

 

[mike777]

OK, cmmrfrds...here's what I have (and it's still not working during the day, only at night when the server is more idle:
Sub MySub()
Dim CnLive as ADODB.Connection, strCn as string
Dim cmdADO as ADODB.Command, strSQL as string, ADORst as ADODB.Recordset
Set CnLive = New ADODB.Connection
strCn="DSN=MyDSN; UID=MyID; PWD=MyPwd; " & _
"DBQ=MyDb; DBQ=W; APA=T; PFC=1; TLO=0"
With CnLive
.ConnectionTimeout=0
.Open strCn
End With
Set cmdADO=New ADODB.Command
strSQL="select * from ascreport6of6"
With cmdADO
.CommandText = strSQL
.CommandTimeout = 0
.CommandType = adCmdText
End With
Set ADORSt = New ADODB.Recordset
With ADORst
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockReadOnly
.ActiveConnection = CnLive
.Open cmdADO.CommandText
End With
etc......
End Sub

Still getting the Oracle timeout error "01013 User requested cancel of current operation..." (only during the day...) when it reaches the "adorst.open cmdado.commandtext" line. Any other suggestions? I'm thinking it must be me...'cause ADO's gotta be better than this??

Thanks again for all the help.

-Mike

 

[cmmrfrds]

Lets try this to start with. Give a little more time for the command to execute when the system is busy. This is different than the connection timeout which is waiting on the connection to open. Also, the Command object has a separate timeout which you have set to zero - try an explicit number like 300. The commandtimeout on the connection will further restrict the commandtimeout on the command.

With CnLive
.ConnectionTimeout= 300
.CommandTimeout = 300 '300 seconds - should be plenty
.Open strCn
End With

With cmdADO
.CommandText = strSQL
.CommandTimeout = 300
.CommandType = adCmdText
End With

If this doesn't help, then you probably should check if there are some other processes in Oracle that are locking the table or otherwise affecting response time.

 

[mike777]

cmmrfrds,
I think I have it. Haven't gotten the error message yet.
I used to have:
strCn="DSN=DSNNAME; etc...", where "DSNNAME" is the name of my DSN in the ODBC Data Source Administrator.
I now have strCn="Provider=MSDAORA.1; etc..."
I have no idea why this works so well (I got the idea off of the Oracle support web site; this among several other ideas, none of which worked), but it does.
This is especially curious since the original worked just fine after hours.
I do believe this is it. If you have any insight into why this is working, I'd be very interested.
Also, I've noted in the string "MSDAORA...", there is the substring "DAO". I don't believe this refers to DAO..DAO, because I removed the DAO 3.6 Reference from my VBA project, and the code string still worked. So I believe, that a more accurate observation of that string would be to look at the "ORA" substring portion as a reference to Oracle (since all the Oracle error messages have an ORA ####... syntax. MSDA must stand for something else.)
Whew. What an adventure that was. Thanks again for all your help.
-Mike

 

[cmmrfrds]

Mike, it looks like you switched from using the Oracle ODBC driver to using the Oracle OLE-DB Provider. This is a good idea since there is less overhead involved. The OLE-DB goes directly to Oracle without going through ODBC which is really an extra step for ADO, but it is there for backward compatibility. ADO --> Oracle as opposed to
ADO --> ODBC --> Oracle.

What was probably happening is that the OBDC driver was timing you out since it also has a time out over and above the ADO time outs. So, even expanding the time out in ADO did no good since ODBC probably defaulted to something like 30 seconds (not sure). When the system was not busy it finished within the ODBC default time out.

Good luck.

 

[mike777]

Whew!! I think that rabbit chase is really over. I was afraid you were going to respond with something that caused me to have more questions!
Sometimes I hunt something down and am very happy to have learned something new. In this case, I just stumbled across something that worked, without really knowing why. Better to be lucky than good, I guess. I suppose I did learn something as well, although it's not clear right now exactly what that is.
Take care and thanks again.
-Mike

 

[scking]

Mike777, those who don't search won't stumble across a solution.

Although I knew the solution as I read along I did NOT know of the tremendous hit to performance.


----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------