Referencing ADO Recordset elements

[krudler]

Ok, this is probably an unbelievably simple question but for some reason I cant seem to dig up a strightforward answer:

I'm using ADO/ODBC to connect to a db (Sybase) and pull some stuff out, which I then have to reformat and put in a text file and send off somewhere else. Easy enough. The data in question is acutally some fields from 2 or 3 different tables. I grab this stuff with a simple SQL statement:

SELECT cvs.ARCHIVE_PROCEDURES.ProcIDExt, cvs.ARCHIVE_EPISODES.MRN, cvs.ARCHIVE_PROCEDURES.ProcTime, cvs.ARCHIVE_PROCEDURES.ProcCodeID, cvs.ARCHIVE_EPISODES.CancelFlag, cvs.ARCHIVE_EPISODES.CustArriveTime, cvs.ARCHIVE_PROCEDURES.CancelFlag, cvs.ARCHIVE_PROCEDURES.ExpireTime, cvs.ARCHIVE_PROCEDURES.ProcState

FROM cvs.ARCHIVE_EPISODES INNER JOIN cvs.ARCHIVE_PROCEDURES ON cvs.ARCHIVE_EPISODES.EpisodeID = cvs.ARCHIVE_PROCEDURES.EpisodeID

via an oRS.OPEN command with the above SQL included as a parameter. I then get a bunch of nice oRS!fields that I can do what I need to with.

PROBLEM: This has always worked well enough before, but now for the first time I have 2 fields that have the same name but are in 2 different tables (cvs.ARCHIVE_PROCEDURES.CancelFlag and cvs.ARCHIVE_EPISODES.CancelFlag). Using the normal syntax I could reference such a field as oRS!CancelFlag and it works fine....but now there are 2 oRS!CancelFlag fields. How do I reference them or tell the difference? Is the only way to do this to build a separate recordset for each table and then somehow cross-link that info when moving through the recordsets? Or is there some easier way?

I know this is probably ADO 101, but it's just never come up before....

 

[Foada]

Did you try to use an alias

cvs.ARCHIVE_EPISODES.CancelFlag AS [red]MyCancelFlag1[/red], cvs.ARCHIVE_EPISODES.CustArriveTime

If you choose to battle wits with the witless be prepared to lose.

 

[krudler]

WOO HOOOOOO!

Awesome tip - thanks much!!

 

[Foada]

No Problem.

If you choose to battle wits with the witless be prepared to lose.

 

[BobRodes]

Also:

SELECT p.ProcIDExt, e.MRN, p.ProcTime, p.ProcCodes, e.CancelFlag as CancelEpisode, e.CustArriveTime, p.CancelFlag as CancelProc, p.ExpireTime, p.ProcState

FROM cvs.ARCHIVE_EPISODES e INNER JOIN cvs.ARCHIVE_PROCEDURES p ON e.EpisodeID = p.EpisodeID

fixes things up some more.

HTH

Bob

 

[strongm]

Other alternatives for referencing the fields if for some reason you don't like field and table aliasing:

oRS![cvs.ARCHIVE_PROCEDURES.CancelFlag]

or

oRS("cvs.ARCHIVE_PROCEDURES.CancelFlag")

 

[gmmastros]

strongm, I've never been able to get that to work for me. Does that only work for certain database? I just tested it, and it doesn't seem to work with SQL Server.

As far as I know, you can use a field alias, or reference the field by it's ordinal value.

Assuming the query is...

SELECT p.ProcIDExt, 
       e.MRN, 
       p.ProcTime, 
       p.ProcCodes, 
       e.CancelFlag, 
       e.CustArriveTime, 
       p.CancelFlag, 
       p.ExpireTime, 
       p.ProcState

FROM cvs.ARCHIVE_EPISODES e INNER JOIN cvs.ARCHIVE_PROCEDURES p ON e.EpisodeID = p.EpisodeID

You can reference the ProcIdExt field with RS(0)
Since the first cancelFlag field is the 5th field, it can be retrieved with RS(4) (Think 0 based array). The next CancelFlag field is the seventh field, so RS(6).

In fact, using the ordinal position is faster than referencing fields by their names. (Feel free to test it if you don't believe me. You'll need a large recordset to notice a difference) It's only slightly faster, and requires that you don't modify the query in such a way that would cause the order of the column to change. I absolutely do not recommend using ordinal value, but it is an alternative.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BobRodes]

<In fact, using the ordinal position is faster than referencing fields by their names.
That's interesting, George. Of course, it only makes sense that parsing a string at runtime would create performance overhead. So I did believe you, but I tested it anyway as follows:

Public Sub DoSQLStuff()
Dim i As Long
Dim x As Double
Dim z As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "Provider=SQLOLEDB;Data Source=RRODES2K;Initial Catalog=pubs;user id=sa"
With rs
    .Open "select * from authors", cn
    x = Timer
    For i = 0 To 100000
        z = .Fields(1)
    Next i
    Debug.Print Timer - x
    x = Timer
    For i = 0 To 100000
        z = .Fields("au_lname")
    Next i
    Debug.Print Timer - x
    x = Timer
    For i = 0 To 100000
        z = !au_lname
    Next i
    Debug.Print Timer - x
    Debug.Print
End With
rs.Close
cn.Close
End Sub

5 calls to the above gave the following output:

 0.154656250000698 
 0.186156249998021 
 0.185656250003376 

 0.139281249998021 
 0.154531250002037 
 0.18603124999936 

 0.123281250002037 
 0.185781250002037 
 0.170656249996682 

 0.124031250001281 
 0.186531250001281 
 0.18603124999936 

 0.17040624999936 
 0.232781250000698 
 0.233156249996682

Conclusion: the ordinal number method is significantly faster, whereas the bang and string notations are about the same.

While I was at it, I did this as well:

    x = Timer
    y = !au_lname  'Where y is a string variable defined above
    For i = 0 To 100000
        z = y
    Next i
    Debug.Print Timer - x
    Debug.Print

and got these data:

 1.59687499981374E-02 

 1.60937499967986E-02 

 1.57187500008149E-02 

 3.43749998137355E-04 

 1.62187500027358E-02

which strongly demonstrates the performance overhead of putting object references in loops.

Bob

 

[gmmastros]

Bob,

I glad someone is checking up on me. I do have some comments.

On average (just looking, not calculating), the difference is approximately 0.05 seconds iterating through the loop 100,000 times. Doing the math here results in 5 micro-seconds difference per iteration. This is not significant (in my opinion), especially with smaller data sets returning only a couple of columns.

The reason I don't use this method is because the time savings (of using ordinal position vs. string field names) is not worth the overhead of making sure the ordinal position does not change. It's all about maintenance, my friend. I am working on a product that I started 7 years ago. There have been many version since then, but it's still the same product. I have over 1200 stored procedures in my database, where probably a quarter of them return data. So, roughly speaking, there are 300 - 400 sp's that return data. I don't have to worry about the ordinal positions of the fields because I don't extract the data from the recordset using ordinal value.

Getting back to the original post, the first response by foada is the exact response I would have given to accomplish this.

If you really wanted to squeeze every last drop of performance from a query, you would be better served to examine indexes and the ADO cursor types since they have a much greater impact on performance.

Bob, if you really want to have some fun, use vb.net and compare the SQLDataReader to the ado.net object.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[krudler]

Thanks guys. foada's suggestion was what I used (simple!) and it did work nicely, but I do appreciate all the interesting tidbits you all busted out with. Needless to say I have learned a few things in the last 24 hours.... thanks!

 

[BobRodes]

<The reason I don't use this method is because the time savings (of using ordinal position vs. string field names) is not worth the overhead of making sure the ordinal position does not change. It's all about maintenance, my friend.

I don't generally use it either! I'm entirely in agreement with you that the investment in maintenance overhead will normally outweigh the savings in performance.

However, it's interesting to know that it performs faster. When I said significantly faster, I meant it in the sense of a theoretical statistical significance, meaning that there likely exist practical situations in which the difference would be significant. (By way of comparison, I found the bang notation to be slightly faster, but not to the point of being what I would consider statistically signficant.)

<Bob, if you really want to have some fun, use vb.net and compare the SQLDataReader to the ado.net object.
Well, I could do that, but to make room for .Net, I'd have to remove "World of Warcraft" from my machine, and that's my entire social life. Except here, of course.

Bob