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.



        DateKey as PostedDt


      CalendarTraditional.qvd (qvd)

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




      LOAD PaymentID,



      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.


      Load JournalID,



      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?