What is the purpose of your query? To Summarize numeric data? A VLDB (Very Large Database) or DW (Data Warehouse) requires a different set of rules. And most the rules affect the database. Perl can only do so much until the database processing time overwhelms it. One trick to help with summary data is to pre-aggregate the summarized data in another table. Then you only query this smaller table. But your requirements determine which tables and columns are pre-aggregated. You cannot pre-aggregate all columns, so you must decide which ones are the important ones to report or query on.

Example: 1 Billion rows in a Master table of customer data called CUST. CUST contains the following important columns: zip code, state, year to date purchases.

Requirement: To summarize all the year to date sales for each state for each month.

Solution: Create a table called SUMSTATE that contains the fields that you wish to query on from the CUST table. Add a column PERIOD that is defined as date time. PERIOD will contain the month and year.

Processing Option One: On the fly, create the SUMSTATE table and populate it using a stored procedure that summarizes the data from CUST. This can be done incrementally so that the server or cluster is not impacted.

Processing Option Two: Each time a row is added or updated in CUST, trigger a stored procedure that updates SUMSTATE.

Processing Option Two is the preferred method, but each situation can be different.

Richard

There are three types of people in this world, those that can count and those that cannot. Anon

In reply to Re: Big database queries by richardX
in thread Big database queries by ezekiel

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.