title: How to optimize multiple left-joins SQL SELECT-query?
body: <p>Situation:</p>
  
  <p>We have a database "base1" ~ 6 million lines of data, which shows the actual customer purchases and the day of purchase + the parameters of this purchase.</p>
  
  <pre><code>CREATE TABLE base1 (
  User_id NOT NULL PRIMARY KEY ,
  PurchaseDate date,
  Parameter1 int,
  Parameter2 int,
  ...
  ParameterK int );
  </code></pre>
  
  <p>And also another database "base2" ~ 90 million lines of data, which actually shows the same thing, but instead of the day of purchase, a weekly section is used (for example: all weeks for 4 years for each client - if there was no purchase for N week, the client is still shown).</p>
  
  <pre><code>CREATE TABLE base2 (
  Users_id NOT NULL PRIMARY KEY ,
  Week_start date ,
  Week_end date,
  Parameter1 int,
  Parameter2 int,
  ...
  ParameterN int );
  </code></pre>
  
  <p>The task to do the following query:</p>
  
  <pre><code>-- a = base1 , b , wb%% = base2
  --create index idx_uid_purch_date on base1(Users_ID,Purchasedate);
  SELECT a.Users_id
  -- Checking whether the client will make a purchase in next week and the purchase will be bought on condition
  ,iif(b.Users_id is not null,1,0) as User_will_buy_next_week
  ,iif(b.Users_id is not null and b.Parameter1 = 1,1,0) as User_will_buy_on_Condition1
  --   about 12 similar iif-conditions
  ,iif(b.Users_id is not null and (b.Parameter1 = 1 and b.Parameter12 = 1),1,0) 
  as User_will_buy_on_Condition13
  
  -- checking on the fact of purchase in the past month, 2 months ago, 2.5 months, etc.
  ,iif(wb1m.Users_id is null,0,1) as was_buy_1_month_ago
  ,iif(wb2m.Users_id is null,0,1) as was_buy_2_month_ago
  ,iif(wb25m.Users_id is null,0,1) as was_buy_25_month_ago
  ,iif(wb3m.Users_id is null,0,1) as was_buy_3_month_ago
  ,iif(wb6m.Users_id is null,0,1) as was_buy_6_month_ago
  ,iif(wb1y.Users_id is null,0,1) as was_buy_1_year_ago
  
   ,a.[Week_start]
   ,a.[Week_end]
  
   into base3
   FROM base2 a 
  
   -- Join for User_will_buy
   left join base1 b
   on a.Users_id =b.Users_id and 
   cast(b.[PurchaseDate] as date)&gt;=DATEADD(dd,7,cast(a.[Week_end] as date)) 
   and cast(b.[PurchaseDate] as date)&lt;=DATEADD(dd,14,cast(a.[Week_end] as date))
  
   -- Joins for was_buy
   left join base1  wb1m
   on a.Users_id =wb1m.Users_id 
   and cast(wb1m.[PurchaseDate] as date)&gt;=DATEADD(dd,-30-4,cast(a.[Week_end] as date)) 
   and cast(wb1m.[PurchaseDate] as date)&lt;=DATEADD(dd,-30+4,cast(a.[Week_end] as date))
  
  /* 4 more similar joins where different values are added in 
  DATEADD (dd, %%, cast (a. [Week_end] as date))
  to check on the fact of purchase for a certain period */
  
   left outer join base1 wb1y
   on a.Users_id =wb1y.Users_id and 
   cast(wb1y.[PurchaseDate] as date)&gt;=DATEADD(dd,-365-4,cast(a.[Week_end] as date)) 
   and cast(wb1y.[PurchaseDate] as date)&lt;=DATEADD(dd,-365+5,cast(a.[Week_end] as date))
  </code></pre>
  
  <p>Because of the huge number of Joins and rather big databases - this script runs for about <strong>24 hours</strong>, which is incredibly long.</p>
  
  <p>Main time, as the execution plan shows, is spent on "Merge Join" and view the rows of the table from base1 and base2, and insert the data into another base3 table.</p>
  
  <p>Question: Is it possible to optimize this query so it works faster?</p>
  
  <p>Perhaps using one Join instead or something.</p>
  
  <p>Help please, I'm not that smart enough :(</p>
  
  <p>Thanx everybody for your answers!</p>
  
  <p>UPD: Maybe use of different type of joins (merge, loop, or hash) may help me, but can't really check this theory. Maybe someone can tell me whether it's right or wrong ;)</p>