Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
miamikk
Starting Member
19 Posts |
 Posted - 2006-10-17 : 17:28:53
|
| 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, CountryA 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 ??ORCan I write a script that creates a html files for each table for all input combinations save them ?ORIs there exists any other solution ? |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-10-18 : 12:04:52
|
| Sure, create a table for each combination, write a stored procedure that truncates each table and then does an insert select into each table. Then create a job to execute the stored procedure at regular intervals.Now you'll always have those small fast tables with your various query results.Another option would be to create a single table to hold the various query results and key it with your query params, so it would have extra columns for month and country for example, then you just query that table. The stored proc would then delete and insert rows or possibly update rows that table when it does the refresh. |
 |
|
|
miamikk
Starting Member
19 Posts |
Posted - 2006-10-18 : 12:56:03
|
| snSQL said....." Sure, create a table for each combination, write a stored procedure that truncates each table and then does an insert select into each table. Then create a job to execute the stored procedure at regular intervals.Now you'll always have those small fast tables with your various query results"Can you be more specific what this exactly means. I am newbie to programming. |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-10-18 : 13:56:02
|
| Which of the following do you and don't you know how to do (statements in parens are the T-SQL statements to do these things)? 1. Create a table (CREATE TABLE)2. Insert rows into a table based on another table (INSERT...SELECT)3. Truncate a table (TRUNCATE TABLE)4. Delete rows (DELETE)5. Update rows (UPDATE)6. Create jobs (various T-SQL, but pretty simple in Enterprise Manager) |
|
|
|
|
|
|
|
|
saving tables that are generated by queries as HTM