hi, i've been running some pretty big queries on a few tables (total of 900 million records) on a 64GB windows machine and i'm curious if anyone might provide advice about whether any of these strategies are computationally expensive? i am maxing out my available RAM in my queries a lot, and at the point it hits that ceiling, the computation starts dragging. here's my pseudo-sql that displays the kinds of commands i've been running. thanks!
============
(a) i am conducting a left join where not all records match. the non-matching records need to be zeroes
in general, is this the least computationally demanding way to get an average amount per person, including the zeroes?
SELECT AVG( sum_amount ) AS avg_amt_per_person
FROM
( SELECT person_id , SUM( CASE WHEN amount IS NULL THEN 0 ELSE amount END ) AS sum_amount FROM ( ( SELECT person_id FROM person_table ) AS a LEFT JOIN ( SELECT person_id , amount FROM amount_table ) AS b ON a.person_id = b.person_id ) AS temp_table GROUP BY person_id ) ;
============
(b) i am on windows (so no strptime) and i have dates in either a mm/dd/yy format but some january thru september are m/dd/yy (i.e. the month can either be one or two digits). i am just calculating the month as part of my query on-the-fly. is it foolish of me not to add a new, permanent column to the data set? i don't have a good sense of whether this segment slows down my query by a lot. it is one of my join/merge variables.
CAST( CASE WHEN SUBSTRING( some_date , 2 , 1 ) = '/' THEN SUBSTRING( some_date , 1 , 1 ) ELSE SUBSTRING( some_date , 1 , 2 ) END AS DOUBLE ) AS some_month
============
(c) i have a three tables that have the same structure that get LEFT JOINed onto my main table. so right now i am doing something like--
main_table LEFT JOIN second_table LEFT JOIN third_table LEFT JOIN fourth_table
is it smarter fo me to do this instead? --
CREATE TABLE stacked_table FROM second_table UNION ALL third_table UNION ALL fourth_table
main_table LEFT JOIN stacked_table