@Nasser_DRMCP wrote:
hello
I a struggling with a proc sql . it takes too much time, too much cpu.
I have two tables. table of mails (500 milions rows / 10 columns ) with the n° mail and the model of the mail and the send date . tables of client/mails (3 billions) with n° mail + n° client (but without the model of the mail, sans la date).
I would like to get the list of the client that are received the model mail '503106' in september.
should I make a join between the two big vertica tables ? or should I , first, create a sas table filtered on model and the sent month. and join this sas table on the second vertica table ?
many thanks in advance
Nasser
It is also a good idea to include an example of the code. For one thing then specific variable names can be used for clarity in discussion. Small example data sets of the tables or at least a clear description of variables also helps, especially with dates involved.
There may be ways to change the existing code to run faster. For example, a common issue is doing a Cartesian join and then filtering the result with a Where afterwards. That approach combines every single observation from both tables.
Dummy example of Cartesian join and where
Proc sql;
create table junk as
select <vars from first table>
,<vars from second table>
from firsttable, secondtable
where firsttable.var = secondtable.var
;
quit;
Usually runs faster:
Proc sql;
create table junk as
select <vars from first table>
,<vars from second table>
from firsttable join secondtable
on firsttable.var = secondtable.var
;
quit;
Not to mention perhaps the specific type of join used might be important for efficiency or other conditions added to the ON may help as many joins can produce duplicate output.
Or perhaps the data from one of the tables should be filtered BEFORE the join
Proc sql;
create table junk as
select <vars from first table>
,<vars from second table>
from (select * from firsttable where somevariable="some value" and month(date)=9)
join secondtable
on firsttable.var = secondtable.var
;
quit;
... View more
