[NTLUG:Discuss] Re: OT Perl vs C question

[tr_data1]

> ... the SQL has been tested and it runs for over 24 hours to acquire a
> 30 days range of data (that test run being on a weekend, which is 
> non-peak hours for us).  Our final run must collect a 365 days range of
> data - estimated running time for the SQL would therefore be about 
> 304 hours (12.67 days) 

I also think it is in your best interest to firstly concentrate on the SQL
optimization. Those are insane times!

1) Identify the SQL pieces that consume the majority of the time. I'm
sure there are several tools (even free ones) that can do the analysis.

2) Index the frequently-accessed keys (yes, with caution to how often
they are updated)

3) Look into timesavers like pre-compiled stored procedures, etc

4) Identify the duplicate query pieces and create a view or sub-result
table(s) of some kind. 

5) Break down the query timeframes into smaller chunks, saving the
smaller results. For example, if you have a query that does "past 4wks",
instead of nabbing week 31-34 and then the following week nab 32-35,
have another table with weekly summary data that can be hit.

6) Is there a time of day where you can lock out writes? Or, is there
enough resources to make a dup into a read-only ?

There has got to be something seriously wrong with the SQL queries
you are using right now. I bet you can cut the time down to much
less than half.