Post reply

Back door on Information_Schema

  • April 7, 2008 at 8:32 pm

    #799514

    Jeff Moden (4/7/2008)

    It would be an aggregate query to sum all the CDRs (Call Detail Records for Telephone Billing) for a about 120 thousand customers in a 4-5 million row table. I'm thinking a table variable would fall on the floor and break. Not a problem and I believe I'm all set... I have some experimentation to do with sp_ views.

    I sure do appreciate your help though, Matt. Thanks.

    So - that returns what, a couple of dozen rows? Go for the table var! ...:hehe:

    methinks that would be a tad bit much for a UDF. Maybe a stored proc, perhaps a custom aggregate, but no - definitely not a function.....Especially since it would have to materialize the whole thing in memory (and being CLR - I mean MEMORY, not tempdb).

    Edit: I just thought this out for a sec. What are you doing with the sum? dumping it out somewhere? correlating it further? If you're dumping this out and NOT using this output directly in other queries, it might not be bad at all on second thought (since your record count would be what - 120K rows?).

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • April 7, 2008 at 9:21 pm

    #799524

    Heh... I gotta agree with that...

    Moot point now, though... I just found out that they want to aggregate the aggregates in these views across the databases. Gotta trade in "convenience during maintenance" for blinding speed... they're going to have to be "indexed views". That means they have to live in the same database as the data. Like I said before, no problem with promulgating such views with a bit of dynamic SQL to all the similarly named databases all of which contain a "dbo.Usage" table.

    Once I get them out of the woods on that, the next thing to do is to show them how to do a "summary database" 😉 ... Lots quicker to recalc totals on User-by-month than it is the raw rows... each user can have an average of 0 to 20 calls per day for a month in each Usage table. Each user can appear in a monthly Usage table for the last 360 months... there are 32 such Usage tables per month based on the billing date. That's a pretty big chunk of data that they want to aggregate... indexed views are the only way to go here. And, yes, once created, the User tables are 100% static so no problems with the materialized Indexed Views getting updated.

    Let's see... (4*10^6)*32*360... that's only 46,080,000,000 (46 billion) rows. This is gonna be some fun! Should take me "15 minutes plus parts". 😀 Yeah, I definitely gotta get them to buy the summary database idea (totals to date at end of month) :hehe: Got a similar thing with a payments table and an InvoiceDetail table... the payments table is kinda small (only several tens of millions) so it should be a piece of cake 😛

    Time to hang another 5 gallon bag of coffee and pray no more requirements change...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply