well, GROUP BY and LEFT JOIN are both pretty heavy operations, especially when applied to some 300M rows - that's a very large database. No matter what, it's going to take time. Here are some of the techniques you might consider:
1. THink of implementing incremental load, to reduce the number of rows that need ot be processed every night.
2. You have many IF() formulas, and those are extremely slow. Typically we neglect the impact of IF() on the load script, but with 300M rows you can't afford it. Consider replacing your IF() formulas with multiple loads using WHERE. For example:
LOAD session_id, count (distinct if (param_name<>'slideUp' and action='initial search', id)) as Total_searches,
count (distinct if (param_name<>'slideUp' and action='initial search', id)) as Total_searches,
...
will be much slower than the following:
I realize that you might have to run several separate loads like this, but my feeling that it will be faster this way than evaluating multiple IF conditions over 300M rows, each row by row.
I believe that your load will go much faster if you can get rid of your IF() conditions.
cheers,
LOAD session_id,
RESIDENT ... WHERE param_name<>'slideUp' and action='initial search'
In the future when posting code, try either cutting and pasting to notepad first, and then to the forum from notepad. Or go into settings -> user preferences -> editor, and remove the checkmark next to "Copy as RTF".
On to the question itself.
First, when loading from a QVD, it is best if you can do an optimized load. Things like date(floor(occurrence_date)) and hour(occurrence_date)should already have been done when creating the QVD itself.
As for day, week, month and year, I'd create a separate calendar table for those. There's no reason to redo that sort of logic for every single row in your table, even during QVD creation.
If I'm interpreting your event_params load correctly, you're both creating a separate row for each parameter AND a separate field for each parameter. Shouldn't you have one or the other? And why, at least if you have separate rows, would you left join onto the sessions table, since that will duplicate every row of the sessions table for every parameter you have. I'd just keep the table separate and/or remove the param_name in favor of specific fields for the parameters.
Down in the group by, if you had separate fields for the parameters of interest, and only one row for each ID instead of multiple, the group by would run more quickly.
But I'm probably totally misunderstanding everything. It's kind of hard to read.
