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

[miamikk]

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 ?

[russell]

might be a good idea to generate the HTML files overnight with an automated script. 2,736 is a lot of files though -- if it takes a minute each... Your query can likely be optimized. What dbms are you using? have you looked at the indexing on the table?

[miamikk]

might be a good idea to generate the HTML files overnight with an automated script. 2,736 is a lot of files though -- if it takes a minute each... Your query can likely be optimized. What dbms are you using? have you looked at the indexing on the table?

Thanks for the reply. I have created an index on the Commodity column and that has decreased query execution time to 15 seconds or so. But I think it still a lot.

I would definitely like to create HTML files offline using an automated script. Do you know it can be done ? or is there an example website or project I can take a look into to get an understanding. I am an newbie to programming.

Thanks
KK

[chazzy]

There's lots of ways it can be done. It all depends on what programming languages you know.

[miamikk]

There's lots of ways it can be done. It all depends on what programming languages you know.

What languages are required ? I have used C programming a lot in past. I am newbie to ASP.NET and SQL. I am still learning them. But I can see an example, I can quickly learn and modify it accordingly.

[chazzy]

no particular language is required. I'd say any language: python, perl, php, java, asp, vb, c#, c, c++ could all equally be used (though with different results in performance). i would say just go with what you know.

i think by using the example approach, you don't learn much about how to actually write a generic program, you only learn how to write this program or that program etc.

the things you need to be able to do:

- open a file, and maybe clear the contents.
- generate the "HTML row" that represents a set of data from the query
- write the row to the opened file.
- do those last 2 steps in a loop, until you've read in all of the data.
- close the file.

there are a lot of different approaches to this, clearly.

[miamikk]

If I look at sample, I will get an Idea on how to approach. Can post a link to samples ? (C#, C or ASP )

no particular language is required. I'd say any language: python, perl, php, java, asp, vb, c#, c, c++ could all equally be used (though with different results in performance). i would say just go with what you know.

i think by using the example approach, you don't learn much about how to actually write a generic program, you only learn how to write this program or that program etc.

the things you need to be able to do:

- open a file, and maybe clear the contents.
- generate the "HTML row" that represents a set of data from the query
- write the row to the opened file.
- do those last 2 steps in a loop, until you've read in all of the data.
- close the file.

there are a lot of different approaches to this, clearly.