Results 1 to 13 of 13
  1. #1
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Efficient calculation of subtotals using ADO (ADO 2.7/SQL Server 2000)

    This is an ASP scenario, but you don't really need to know anything about ASP to give me a reality check.

    My code retrieves a Recordset from SQL Server that contains data somewhat like this:

    Joe Client1 Matter1 2.5 1000 Billed
    Joe Client1 Matter1 4.0 1600 Unbilled
    Joe Client1 Matter6 1.0 400 Unbilled
    Sue Client4 Matter9 2.0 800 Billed
    Sue Client6 Matter71 1.1 440 Unbilled
    etc.

    I am trying to create a collapsible view somewhat like this (with much nicer spacing, of course <img src=/S/smile.gif border=0 alt=smile width=15 height=15> ):

    Joe 7.5 $3,000.00
    Client1 7.5 $3,000.00
    Matter1 6.5 $2,600.00
    Matter6 1.0 $400.00
    Sue 3.1 $1,240.00
    Client4 2.0 $800.00
    Matter9 2.0 $800.00
    Client6 1.1 $440.00
    Matter71 1.1 $440.00

    The difficulty is that I would have to run through and sum the values before I can know the totals that go on the first line. To bypass this problem, I built my test page "upside down." When you expand the Timekeeper, the Client totals appear above the Timekeeper's grand total. Looks completely logical printed on paper (data, then subtotals), but it's lame as an interactive web page.

    So... my question is: Is there some incredibly fancy way to extract subtotals out of a Recordset, or should I read the entire Recordset one row at a time and cumulate the values into an array? This query is a union of two aggregate queries on huge tables, so I don't want to run it multiple times. I'd rather read it in code a few times!

    As for how to do the array, it seems difficult to build it efficiently. If I read the Recordset from end to beginning the first time, and then process the array from end to beginning when I got through my Recordset in the proper sort order, that *might* work. (Difficult to illustrate here.) All time-saving suggestions gratefully accepted.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Efficient calculation of subtotals using ADO (ADO 2.7/SQL Server 2000)

    How about using three recordsets:
    <UL><LI>A view/query grouped by employee
    <LI>A view/query grouped by employee and client
    <LI>A view/query sorted (bit not grouped) by employee and client[/list]That way, the totals would be computed on the server. You could use nested loops to loop through the three recordsets in coordinated fashion.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Efficient calculation of subtotals using ADO (ADO 2.7/SQL Server 2000)

    Have you ever played around with hierarchical recordsets, Jefferson? That might be another way to do it. These are not for the faint of heart, but they are quite slick, and I've used them to populate flexgrids.
    Charlotte

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Efficient calculation of subtotals using ADO (ADO 2.7/SQL Server 2000)

    <P ID="edit" class=small>(Edited by jscher2000 on 11-Apr-03 21:23. I forgot field in the BY clause...)</P>Wow, yet another thing I've never heard of. After about 3 hours of reading up on Data Shaping, Shaped/Hierarchical Recordsets, etc., I've reached the point where I think I might have an approach.

    Unlike all the book examples, I don't want to SELECT * FROM Employees, then SELECT * FROM Clients, etc. That is WAY too much data. At the Employee level, it's easy because I'm using an IN criterion to select members of a hand-picked group. For Clients, though, I only want two fields, and only for the ones to whom time was billed during the specified period. This requires a UNION of joins, each one on at least 3 tables. It seems to create double work for SQL Server. Even then, there might be duplicates across the two queries.

    I think, though, that there is a "slick" way to use "reshaping" to solve my problem. If I pass my existing query through the shaping provider, I get a shaped recordset, which we'll call rsGoryDetail. I then should be able to create a new shaped recordset with client aggregates this way:

    <pre>objRs1.Open "SHAPE rsGoryDetails " & _
    "COMPUTE rsGoryDetails, SUM(rsGoryDetails.Hours) AS CltHrs " & _
    "BY EmployeeNumber, ClientNumber", objConn</pre>

    Now, if this avoids a trip back to SQL Server - 'cause I'm merely aggregating what's in an existing recordset - I'm golden. I do this a few times to create my hierarchy and I'm ready to lay down ASP. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Does this seem about right to you? Or is this a "you'll never know until you try it" situation...

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Efficient calculation of subtotals using ADO (ADO 2.7/SQL Server 2000)

    <hr>a shaped recordset, which we'll call rsGoryDetail<hr>
    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>
    It looks like it should work and one neat thing about shaped recordsets is that they can be shaped differently at different levels, unlike regular queries, depending on how you join recordsets. I can't remember if I ever tried them with disconnected recordsets, but that might be another thing to try in order to reduce trips to the server.
    Charlotte

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Efficient calculation of subtotals using ADO (ADO 2.7/SQL Server 2000)

    I'm not sure exactly how you disconnect a recordset. If you .Close the ActiveConnection, then ADO doesn't let you access your shaped recordsets.

    The shaping method seems to add a considerable amount of time to creating the ASP pages, suggesting that ADO is working very hard, or ADO is working SQL Server hard, when instantiating filtered child recordsets from a column in their parent recordset:

    <table border=0 width=100%><td>Bigger Page</td><td>Smaller Page</td><td>OLD METHOD (seconds):
    Recordset Open Time = 0.3588867
    Response.Write Time = 0.4851074</td><td>OLD METHOD (seconds):
    Recordset Open Time = 0.2651367
    Response.Write Time = 7.910156E-02</td><td>SHAPING (seconds):
    Recordset #1 Open Time = 0.4060059
    Recordset #2 Open Time = 4.699707E-02
    Recordset #3 Open Time = 3.100586E-02
    Recordset #4 Open Time = 0
    Response.Write Time = 4.046021</td><td>SHAPING (seconds):
    Recordset #1 Open Time = 0.2661133
    Recordset #2 Open Time = 0
    Recordset #3 Open Time = 1.489258E-02
    Recordset #4 Open Time = 0
    Response.Write Time = 0.6411133</td></table>
    This is not a completely fair comparison, because in the OLD METHOD there was no attempt to calculate totals before laying out the data. Still, it's too slow for me. I might have to go back to an earlier plan to shorten the processing time. Bummer. (But it was a great educational experience. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> )

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Efficient calculation of subtotals using ADO (ADO 2.7/SQL Server 2000)

    To disconnect a recordset, you set the connection object to nothing after you have opened the recordset. You don't *close* the connection, you just destroy the connection object. Are you saying that a shaped recordset doesn't work after that? As I said, I don't remember trying it, but I would expect it to work.
    Charlotte

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Efficient calculation of subtotals using ADO (ADO 2.7/SQL Server 2000)

    This has nothing to do with the subject at hand, but what is with the Gobbler?

    Pat

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Efficient calculation of subtotals using ADO (ADO 2.7/SQL Server 2000)

    That isn't a "gobbler", Pat, it's a Nene goose, a native of the Hawaiian Islands. Maybe Jefferson just likes sticking his neck out! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Nene geese are argumentative and bad tempered, which isn't like Jefferson at all. <img src=/S/nope.gif border=0 alt=nope width=15 height=15>
    Charlotte

  10. #10
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Efficient calculation of subtotals using ADO (ADO 2.7/SQL Server 2000)

    I set .ActiveConnection = Nothing for each recordset, and the page write times didn't change appreciably (about 3 ms better for the 4 second page).

    I'm working (in my mind) on a new approach using bookmarks to avoid having to build dynamic arrays. It just means leaving the relative comfort of forward only recordsets.

  11. #11
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Efficient calculation of subtotals using ADO (ADO 2.7/SQL Server 2000)

    Okay, new timings are in, and I think the "third way" gives the best combination of functionality and performance. As you recall, in the original scenario, I proceeded in an orderly fashion through my ADO Recordset, cumulating values and laying down totals after showing the detail data. This did not make for a nice expanding display, as the detail records expanded upwards from the totals.

    The second strategy was to use data shaping to create hierarchical recordsets: the "outermost" recordset contained the highest level totals, and one of its fields was the next level down recordset. This created very clean-looking code, but ran slowly.

    The third strategy is to proceed through the recordset and every time a change is detected in one of the three values used to form the hierarchy, stop writing rows, set a bookmark, and roll forward cumulating totals, return to the bookmark, and write out the row of totals.

    In the first two methods I used Forward-Only recordsets, and in the third a Static recordset. All three used Read-Only locking and a CacheSize of 20. And now, the results:

    <table border=0 width=80% align=center><td>Bigger Page</td><td>Smaller Page</td><td>TOTAL AFTER (seconds):
    Recordset Open Time = 0.984375
    Response Write Time = 0.484375</td><td>TOTAL AFTER (seconds):
    Recordset Open Time = 0.890625
    Response Write Time = 0.0625</td><td>SHAPING (seconds):
    Recordset #1 Open Time = 1.03125
    Recordset #2 Open Time = 0.046875
    Recordset #3 Open Time = 0.0390625
    Recordset #4 Open Time = 0
    Response Write Time = 4</td><td>SHAPING (seconds):
    Recordset #1 Open Time = 0.890625
    Recordset #2 Open Time = 0
    Recordset #3 Open Time = 0.015625
    Recordset #4 Open Time = 0
    Response Write Time = 0.625</td><td>STOP & SUM (seconds):
    Recordset Open Time = 1.015625
    Response Write Time = 1.453125</td><td>STOP & SUM (seconds):
    Recordset Open Time = 0.875
    Response Write Time = 0.25</td></table>
    Maybe someone will read this post some day and find it useful. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Are shaped recordsets still supported by Microsoft? Because I find content disclaimers in How To Use the ADO SHAPE Command and How To Extract Child Recordsets from Shaped Recordset.
    If so, what are the alternatives offered?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't think shaped recordsets are about to be retired. The MSKB articles you mention are about older versions of DAO; if you search Google for ado shape site:microsoft.com you'll also find articles that don't have a "Retired KB Content Disclaimer".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •