7 Replies Latest reply: Oct 17, 2013 5:46 AM by Михаил Стерлигов RSS

    Exists question for load optimization

    Darrin Pilkington

      Since most where statements other than Exists do not keep a load optimized I have created a temp table storing the dates I wish to pull using the Exists(DateKey) to keep my load optimized but I have ran into a problem that makes what I think is an ugly solution when I have multiple tables to load with different Date names.

       

      We already have a calendar table and the where allows me to only grab the dates I want.  I have named the date according to the Payments table I will later load.

      tDate:

      LOAD

        DateKey as PostedDt

      FROM

      CalendarTraditional.qvd (qvd)

      where DateKey >= YearStart(today(), -1) and DateKey <= MonthEnd(today());

       

       

      Payments:

      LOAD PaymentID,

           PostedDt,

           PaymentCounter

      FROM Payments.qvd (qvd)

      where Exists(PostedDt);

       

       

      This works great and is optimized.  The problem is I am then going to add a second table where the Date field is name CreateDt and this load will not be optimized because Exists(PostedDt, CreateDt) seems to unOptimize it.

      Journals:

      Load JournalID,

           CreateDt

           JournalCounter

      From Journals.qvd (qvd)

      where Exists(PostedDt, CreateDt);

       

       

      The only solution I could think of was to do a Resident load of my tDate table and rename the Date to suit each table I am about to load.  It would be fast but kind of ugly in my opinion.

       

      Any thoughts?

       

      Thanks