optimizing proc sql join

[Nasser_DRMCP]

Hello,

I have a table t1 that holds 3 millions rows (with one datetime) and a table t2 that holds 5 millions rows (with 2 datetime)

In a proc sql, i have a join like this

select ...

FROM t1 AS a LEFT JOIN t2 AS b

ON a.IDT_RSS=b.IDT_RSS

AND a.TSP_CVL BETWEEN b.TSP_TEST_DEB AND b.TSP_TEST_FIN

;

cpu times takes  23 minutes. How coul I optimize ?

thanks a lot in advance

nasser

[Kurt_Bremser]

Tell us a little more about your data.

• are the IDT_RSS unique in t1, or do you have multiple entries per IDT_RSS?
• same for t2?
• if IDT_RSS is non-unique in t2, do the time ranges overlap, or is each TSP_TEST_DEB larger than the preceding TSP_TEST_FIN (if the dataset is sorted by IDT_RSS and TSP_TEST_DEB)?

[Kurt_Bremser]

And some additional info: what variables are contained in the datasets, what are their types and defined lengths?

[Nasser_DRMCP]

Hello Kurt

thanks a lot for your respons and sorry for answering in late.

we succeeded to solve the problem by reducing the scope.

instead of collect and treat all rows each times, we treat only one month each month.

I mean we treat only missing data in target (versus source),  instead of remove and replace.

Thanks a lot

regards

Nasser