Post reply

saving tables that are generated by queries as HTML file or sub-tables

  • October 17, 2006 at 3:29 pm

    #91797

    I have a trade data tables (about 10) and I need to retrieve information based on input parameters. Each table has about 3-4 million rows.

    The table has columns like Commodity, Unit, Quantity, Value, Month, Country

    A typical query I use to select data is "Select top 10 commodity , sum(value), sum(quantity) , column4, column5, column6 from table where month=xx and country=xxxx"

    The column4 = (column2)/(total sum of value) and column 5=(column3)/(total sum of quantity). Column6=column5/column4.

    It takes about 3-4 minutes for the query to complete and its a lot of time specially since I need to pull this information from a webpage.

    I wanted to know if there is an alternate way to pull the data from server ?

    I mean can I write a script that creates tables for all the input combinations i.e month x country (12x228) and save them in table (subtable-table) with a naming convention so from the web I can just pull the table with input parameters mapped to name convention and not running any runtime queries on database ??

    OR

    Can I write a script that creates a html files for each table for all input combinations save them ?

    OR

    Is there exists any other solution ?

  • October 17, 2006 at 10:55 pm

    #666398

    O.L.A.P.  That's what it's all about - aggregating loads of data for this kind of stuff.

    Not sure what you're using this for, and OLAP (a.k.a. SQL Server Analysis Services) is a pretty big hammer to go swinging at all nails.  If this is big-time decision-support or data slice-and-dice stuff, then you should consider building a cube or two.

    Cubes don't get built cheaply, quickly, or without some decent planning, so let's look at some other options.

    You may try creating an indexed view (plenty of rules involved, but not too difficult) on each table.  An indexed view is a view that SQL Server will physically turn into a table, and then maintain it automagically for you when the underlying data changes.  You can pre-calculate the per-Country/Month/Commodity sums, something like:

    SET NUMBERIC_ROUNDABORT OFF;
SET ANSI_NULLS, [etc., see BOL] ON;
GO
--<existance check and drop go here>
GO
CREATE VIEW dbo.MyIdxView
WITH SCHEMABINDING
AS
  SELECT Country, Month, Commodity, SUM(value) AS Value, SUM(Quantity) AS Quantity
    FROM <table>
   GROUP BY Country, Month, Commodity
GO
  CREATE CLUSTERED INDEX IX_MyView
      ON dbo.MyIdxView(Country, Month, Commodity)
GO

    Now, admittedly, all the caveats, warnings, and gotchas with indexed views would fill another four or five long-winded posts.  Read the topics full oin BOL, and then give yourself some time to experiment and test.

    In SQL Server 2000, indexed views work best on Enterprise (and Developer) Edition.  On Standard Edition, the indexed view will not be considered by the optimizer unless the NOEXPAND hint is used (SELECT * FROM dbo.MyIdxView WITH (NOEXPAND)...).  I don't have a SQL 2005 featuer matrix in front of me.  Check BOL or the SQL website if you're using that.

    Once you knock the kinks out of building indexed views, they become a really handy tool for pre-aggregating queries like this.

    Eddie Wuerch
    MCM: SQL

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

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