 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / December 2005Multiple recordsets continued
I was inspired by the recent discussion of returning multiple recordsets to ADO from a stored procedure. (Amazed is probably more accurate). I asked about how to accomplish same with Oracle and got a nudge in the right direction from Mr. Kreft. I promised to provide details once working, so here it is. The code is shown below. My next step is to build this technique into my application. I'm hoping for substantial performance gain. This is totally new to me, I'd welcome any constructive criticism. By the same token, I got it working, so I'll be happy to attempt to answer any questions anyone might have. Randy Harris In Oracle I created this package and stored procedure: /* A PL/SQL book said that the cursor object must be declard in a package */ CREATE OR REPLACE PACKAGE OraOLEDB AS TYPE m_refcur IS REF CURSOR; END OraOLEDB; / /* =================================== */ /* This procedure will return two REF cursors */ /* I added a couple of IN parameters to prove I could */ CREATE OR REPLACE PROCEDURE getrecs( firstweek IN NUMBER, lastweek IN NUMBER, firstrs OUT OraOLEDB.m_refcur, secondrs OUT OraOLEDB.m_refcur) IS BEGIN /* Define the first cursor that will become a recordset */ OPEN firstrs FOR SELECT weekid, weekstart FROM weeks_tbl WHERE weekid BETWEEN firstweek AND lastweek ; /* And now a second */ OPEN secondrs FOR SELECT tablename, updateseq FROM concurrence ; END getrecs; / Here's the VBA procedure that calls the SP: Private Sub MultiRecs() Dim conn As New ADODB.Connection Dim rst1 As New ADODB.Recordset Dim rst2 As New ADODB.Recordset Dim cmd As New ADODB.Command With conn .Provider = "OraOLEDB.Oracle" .Open "data source=" & OracleDB & ";", dbUser, dbPass End With cmd.ActiveConnection = conn cmd.Properties("PLSQLRSet") = True cmd.CommandType = adCmdText cmd.CommandText = "{Call getrecs (420, 430) }" ' this calls the procedure and retrieves the cursors ' the first recordset is created Set rst1 = cmd.Execute While Not rst1.EOF Debug.Print rst1(0), rst1(1) rst1.MoveNext Wend ' Now call the NextRecordset Method Set rst2 = rst1.NextRecordset While Not rst2.EOF Debug.Print rst2(0), rst2(1) rst2.MoveNext Wend rst1.Close rst2.Close End Sub > The code is shown below. My next step is to build this technique into my > application. I'm hoping for substantial performance gain. Why would getting one recordset composed of two recordsets result in a substantial performance gain as compared with just getting two recordsets? 1. It's true that you will send one command instead of two, but will you not get back the same data in the same form (form the noun not form the Access Object) and is the transport of this data not likely to be the chief time-consuming activity of the whole procedure; 2. In MS-SQL we can create and use the cursor in the stored procedure and get only the second recordset (assuming the first is used only as fodder for the second); can this be done in Oracle; 3. How often will we write Procedures that will return multiple recordsets, which might be rewritten as Joins to return the required data; 4. How efficient is it to hold two recordsets in memory if we are using them consecutively, that is, if we use the first, then use the second and never return to the first; 4. In cases where field structure is the same will Unions or multiple recordsets be more efficient? These are idle thoughts of my own that I am pondering; I'm currently at this stage: 1. I don't have anything running so slowly or inefficiently that I'm going to rewrite it using multiple recordsets; 2. I'll keep multiples in mind when I program ADO recordsets in the future; 3. I already use Unions when I have two recordsets with identical fields that I want to use, using an absolute field defined something like "A" as WhichGroup for the first and "B" as WhichGroup for the second; this troubles me and maybe I'll replace it with multiples recordsets; 4. With SQL 2000 and 2005 VBA is almost redundant; there is little I can do in a VBA module that I can't do in a SPROC; but do I really wanna have the server do all the work sharing it's CPU time with a bunch of clients, rather than having the client machine do all the work, sharing its CPU time with nothing? As an aside T-SQL can even act as an adjunct to VBA. Don't want the hassle of API functions to get Zulu time? Have access to an MS-SQL sever? Use its functions then: CurrentProject.Connection.Execute("SELECT GETUTCDATE()").Collect(0) 2005-12-10 03:24:26 If one extends this to UDFs then one could do everything in the server and UDFs would be the repository for ones code. And of course, this doesn't have to have anything at all to do with your data or your type of database Your data could be an mdb or oracle or whatever and you could access functions from some central business rules/ function ms-sql code repository on the other side of the earth, or on your local hard drive. That is we could all share the same functions and have access to instant updates and additions. I'm digressing, sorry. Lyle > > The code is shown below. My next step is to build this technique into my > > application. I'm hoping for substantial performance gain. [quoted text clipped - 59 lines] > > Lyle Good questions, and food for thought. Perhaps my judgment might be clouded by my enthusiasm for the novelty of this approach. Regarding question #1: The particular areas where I'm hoping this might be beneficial involve consecutively passing multiple SQL statements to Oracle then retrieving or updating recordset data, then closing. In several places there are 4 to 6 operations. My thinking is that there is "handshake" time involved in each of these operations. Perhaps the "stack up" of that time can be reduced by use of the single SP. The quantity of data passed should be unchanged from the technique used currently. #3: I think there are some places where properly defined joins could reduce the number of retrieval recordsets. #5: The tables involved have dissimilar structure, but unions would be more efficient if usable. Perhaps wishful thinking. I don't know. I can think of only one way to find out for certain. Randy I had to test. If you see something I didn't optimize correctly, please, let me know. The current connection is to an internet enabled MS-SQL server in California (I am near Toronto about 4000 km away). I am using a cable modem (Your download speed : 3133 kbps or 391.6 KB/sec.Your upload speed : 597 kbps or 74.6 KB/sec.). FFDBATransactions has 296 rows; 4060148Transactions has 867 rows. I interpret the results to show that multiple recordset retrieval has about a 0.01 second advantage over the retrieval of two separate recordsets under these conditions. - code is left aligned to try to prevent news client mangling. Private Declare Function GetTickCount Lib "kernel32" () As Long Private Const Iterations As Long = 10 Private Const SQL1 As String = "SELECT * FROM FFDBATransactions" Private Const SQL2 As String = "SELECT * FROM [4060148Transactions]" Private Sub TwoRecordSets() Dim r(2) As ADODB.Recordset Dim t As Long Dim z As Long t = GetTickCount() For z = 1 To Iterations Set r(0) = CurrentProject.AccessConnection.Execute(SQL1) With r(0) While .State <> adStateOpen Wend .Close End With Set r(1) = CurrentProject.AccessConnection.Execute(SQL2) With r(1) While .State <> adStateOpen Wend .Close End With Next z Debug.Print GetTickCount - t '8201,8162,8131,8171 End Sub Private Sub OneMultipleRecordSetofTwoRecordsets() Dim r(2) As ADODB.Recordset Dim s As String Dim t As Long Dim z As Long s = "SET NOCOUNT ON;" & SQL1 & ";" & SQL2 & ";SET NOCOUNT OFF" t = GetTickCount() For z = 1 To Iterations Set r(0) = CurrentProject.Connection.Execute(s) With r(0) While .State <> adStateOpen Wend Set r(1) = .NextRecordset .Close r(1).Close End With Next z Debug.Print GetTickCount - t '8101,8071,8042,8062 End Sub You've deflated my enthusiasm for this... somewhat. I think there might still be the possibility of some advantage, despite the negligible difference you've demonstrated. As Terry pointed out, a significant portion of the time involved would be the compile and plan time at the server. Perhaps the server has cached these as you submit the same SQL repeatedly? If you were to set Iterations to 1 and run the same test, might the results reflect less time actually passing data across the network and a higher percentage of "overhead" time? The other reason that I still hold out some hope that this technique might have promise is this. When dynamic SQL is passed to the server it must always be parsed and compiled at run time. By using a stored procedure, I'm hoping that the SQL will be kept precompiled. Randy > I had to test. If you see something I didn't optimize correctly, > please, let me know. [quoted text clipped - 55 lines] > Debug.Print GetTickCount - t '8101,8071,8042,8062 > End Sub Running them 6 times each but not repeatedly I get: TwoRecordSets 0901 0831 1001 0901 0821 0821 OneMultipleRecordSetofTwoRecordsets 0801 0821 0802 0801 0801 0812 BTW, older people like me may not get over being astonished at getting 2 recordsets and more than 1000 records from another computer situated more than 4000 km away in less than one second using a $1000 computer and a $130 program and a $48 monthly (all amounts CAD) internet connection. If a teacher had demonstrated this in my high school days (the early fifties) he/she would probably have been checked out for witchcraft! > BTW, older people like me may not get over being astonished at getting > 2 recordsets and more than 1000 records from another computer situated [quoted text clipped - 3 lines] > If a teacher had demonstrated this in my high school days (the early > fifties) he/she would probably have been checked out for witchcraft! LOL! Technology is a wonder. Imagine what it will likely be 50 years from now. No reason to think there will be less change in the next 50 years than there were in the last 50. You and I are unlikely to witness it, but it's still something to ponder. > The other reason that I still hold out some hope that this > technique might have promise is this. When dynamic SQL is passed > to the server it must always be parsed and compiled at run time. > By using a stored procedure, I'm hoping that the SQL will be kept > precompiled. That's not true for MS SQL, if I'm not mistaken. If it's sent the same SQL a second time, it uses a cached query plan for it. I don't know how much flexibility there is there, e.g., if all that's changed is the *value* searched for in a WHERE clause, but the query is otherwise identical, the query plan might be cached. You'd have to look at the documentation for your particular db engine, though. It seems to me that this technique would be most useful to reduce load on a server and to control timeing. If, for instance, you have a process that needs to retrieve both recordsets before it can make the UI available to the user, it might make more sense to retrieve those two recordsets in a single request, since the user can't doing anything with the first one by itself. If you send two requests, another request could end up tying up the server before it has a chance to get to the second request, leading to lag time in your application as it waits for the second result set. With multiple recordsets returned, if the server queues the queries them together (as opposed to treating them as independent and perhaps interleaving someone else's requests between them), then you potentially improve the performance of your application. You're also consuming fewer resources on the server, so you're allowing others to work better. But I'd think these issues would be significant only in an app that runs against a server that is under very heavy load. On the other hand, if there's potential for an app to grow in that direction, you might want to implement this approach at the beginning to insure maximum scalability.  SignatureDavid W. Fenton http://www.bway.net/~dfenton dfenton at bway dot net http://www.bway.net/~dfassoc > You've deflated my enthusiasm for this... somewhat. My experience is that if you can use a ServerSide recordset (Connection.CursorLocation = adUseServer or Recordset.CursorLocation=adUseServer) then you can reduce time to get recordset by about 37.5 per cent; times of 8100 I quited before were reduced to 4600. This works for two discrete recordsets. With minimal tries, I have not succeeded in using Multiple Recordsets with Server Side; that is .NextRecordSet seems to work only with Client Side. >From time to time I find CurrentProject.Connection can be very slow. Michka explained why this was once but I've lost the reference. So generally I roll my own ADO Connection. I just a have a public function that uses a string I create creates and returns the connection object. There are other attributes of the recordset that may affect time. For big recordsets I experiment trying to find the minimal (and I hope fastst) configuration that meets my needs. Which returns us to doing things on the Server with Sprocs and UDFs. If one doesn't need the results immediately and one does the work on the server then the time spent on the client will be zero or almost zero, just enough to send our command. > > You've deflated my enthusiasm for this... somewhat. > [quoted text clipped - 3 lines] > recordset by about 37.5 per cent; times of 8100 I quited before were > reduced to 4600. Ah jeez Lyle, you're making this even more complicated. ;- {) I haven't even tried using server side cursors. 37.5% is pretty dramatic. Guess I've got more testing to do. Would you happen to know, can the CursorLocation be changed after the connection has been created, or only when the connection is made? In other words, can I switch an active connection back and forth? Seriously, thanks for the tip. Randy > This works for two discrete recordsets. > [quoted text clipped - 15 lines] > server then the time spent on the client will be zero or almost zero, > just enough to send our command. One of these cursor locations is good for some things and the other is good for other things; I don't want to lead you into a lot of testing and then have you find that can't use the faster anyway: from help files ******* adUseClient 3 Uses client-side cursors supplied by a local cursor library. Local cursor services often will allow many features that driver-supplied cursors may not, so using this setting may provide an advantage with respect to features that will be enabled adUseServer 2 (Default) Uses data-provider or driver-supplied cursors. These cursors are sometimes very flexible and allow for additional sensitivity to changes others make to the data source. However, some features of the Microsoft Cursor Service for OLE DB (such as disassociated Recordset objects) cannot be simulated with server-side cursors and these features will be unavailable with this setting. Cursors returned by the Execute method inherit this setting. Recordset objects will automatically inherit this setting from their associated connections. ******* I think the documentation isn't complete here; they inherit if not set themselves: that is we can have ADOConnection.CursorLocation = CursorLocationEnum Recordsets using ADOConnection will inherit .CursorLocation = CursorLocationEnum BUT this property can be changed with ADORecordset.CursorLocation = OtherCursorLocationEnum before opening ADORecordset ****** I think one can go on with this forever (and I'm going to try to stop now!) BUT: Upon further review the speed of "getting" the recordset seems not to be influenced by the cursor location for the smaller recordset (296 records). That is ten opens of the smaller took about 200 milleseconds with adUseClient and about 200 milliseconds with adUseServer. But the speed for the larger recordset (867 records) is reduced not by 37.5 per cent as I perviously reported but by 66.6 per cent with adUseServer. That is ten opens of the larger took about 600 milleseconds for adUseClient and about 200 milliseconds for asUseServer. BTW, the structure, indexes, relationships of the two tables are identical. >> > You've deflated my enthusiasm for this... somewhat. >> [quoted text clipped - 8 lines] > I haven't even tried using server side cursors. 37.5% is pretty > dramatic. Guess I've got more testing to do. It's not necessarily dramatic -- only if 100% is significant to begin with. If 100% is 10 milliseconds, then a user is going to notice no difference at all. If 100% is 60 seconds, the difference will be noticeable. Anywhere in between is going to require a consideration of how hard it is to re-implement existing code to use the new method. If you're writing new code, though, the cost will be lower, unless the amount of additional code for server-side cursors is significant and not re-usable. I think people often take percentages as proof of the superior performance of different methods, and ignore the real-world implications of it, especially when there are outside dependencies over which you have no control at all (such as sending data to a printer or retrieving data across the Internet or a slow WAN). SignatureDavid W. Fenton http://www.bway.net/~dfenton dfenton at bway dot net http://www.bway.net/~dfassoc > >> > You've deflated my enthusiasm for this... somewhat. > >> [quoted text clipped - 28 lines] > over which you have no control at all (such as sending data to a > printer or retrieving data across the Internet or a slow WAN). I understand what you are saying and agree. My objective here has two parts. I'm hoping to address some current performance issues, but also to develop optimal coding practices for future use. The old, ounce of prevention strategy. Randy Harris Knowledge is like redheads; it requires no raison d'être. Lyle, Some answers. 1) But there is more to it than ths, when you send a command to a server database (SQL anyway) there is time spent compiling that command and doing things like resolving execution plans. Sending one command to retrieve multiple recordsets will impact on these and should show a performance increase in certain cases. Of course (as with anything db) this is not an absolute and would need to be tested for a particular situation to see if a performance increase occurs. 2) outside my area of expertise so I'll leave this to Randy. 3) I would use this technique for situations where I wanted either disparate recordsets or ones where I need to navigate through the recordsets independently. In situations where previously I would have used joins I tend to use shaped recordsets as these can show a performance improvement over standard joined recordsets. 4) This is one of theose situation where you would have to try it and see. BTW, I know that Randy's example shows him consuming the recordsets in sequence you don't have to do this though. You can do this Function xxxx() Dim loCon As ADODB.Connection Dim rs1 As ADODB.Recordset Dim rs2 As ADODB.Recordset Dim rs3 As ADODB.Recordset Set rs1 = New ADODB.Recordset Const CONN_STRING = "File Name=c:\a.udl" Set loCon = New ADODB.Connection With loCon .CursorLocation = adUseClient .ConnectionString = CONN_STRING .Open Set rs1 = .Execute("SELECT * FROM scheme.plsuppm SELECT * FROM scheme.slcustm") End With ' NOTE .clone Set rs2 = rs1.Clone ' Now .NextRecordset Set rs3 = rs1.NextRecordset ' At this point ' rs2 contains the first recordset ' rs3 contains the second recordset End Function SignatureTerry Kreft >> The code is shown below. My next step is to build this technique into my >> application. I'm hoping for substantial performance gain. [quoted text clipped - 59 lines] > > Lyle |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.