2 Replies Latest reply: Jun 23, 2011 6:45 AM by Razor Ramon RSS

    Join problem using qvds

      Hi guys,

       

      Looking for some major help here. I have an application that loads data from multiple csv files. The csv files take too much time to load as our data volumes have grown and now we have converted all the files to qvd files.

       

      At the end of my load script, I do a join which joins all the tables together into one central table with a few reference tables for better performance.

       

      In my script, I have a major join at the end between 2 tables that have 25 million and 50 million rows. The app is successful when loading from csvs, even though the join is huge and it takes over 6 hours for the entire reload.

       

      I have now done the exact same thing, but loading from qvd files instead. Every time it gets to the join that loads two of the biggest tables together, it fails with a General Script error when I try to load multiple months.

       

      The strangest thing that happens is that with qvds, the app is successful for one to three months of data, but for anything more than three months of data, it always fails with the General Script Error during the final join.

       

       

      To give additional information, my QlikView server has 256GB of RAM, so memory is not an issue.

       

      Part of my Final Join Script

      LEFT JOIN (Table1)

      LOAD * RESIDENT Table2;

      DROP TABLE Table2;

       

      LEFT JOIN (Table1)

      LOAD * RESIDENT Table3_;

      DROP TABLE Table3;

       

      LEFT JOIN (Table4)

      LOAD * RESIDENT Table5;

      DROP TABLE Table5;

       

      LEFT JOIN (Table4)

      LOAD * RESIDENT Table6;

      DROP TABLE Table6;

       

      LEFT JOIN (Table4)

      LOAD * RESIDENT Table1;

      DROP TABLE Table1;

       

      I read somewhere that we should avoid using resident tables, but I am using them because my final join requires that I use a couple of tables that have already been joined together.

       

      Any pointers as to what could be happening and why my app is failing and how I can fix it?

       

      Thanks

        • Re: Join problem using qvds
          Rahul Gupta

          Hey hi,

           

          Please check the unique fields or keys on which you are joining the tables.

          These kinds of issues occur when the keys are not defined between the two tables that have to be joined.

          In the case more than 1 field are common to the two tables to be joined, it gives this type of error.

           

          Regards

            • Join problem using qvds

              Hi Rahul,

               

              My application which uses exactly the same code runs successfully when I load all the info from csv files, but not qvd files.

               

              I have checked and I only have one common field between the tables to be joined so I doubt it is the point you have raised.

               

              Kind Regards