6 Replies Latest reply: Jul 9, 2012 2:05 PM by Marc Livingston RSS

    What hits the database more, Loading full table once, or restricted 3 times

      I do not have much background in understanding performance when loading from the database.

       

      I currently load 1 table 3 times, but it is restricted for each time with conditions. (The conditions make it so the same data is never loaded more than once.)

       

      I was wondering if it would improve performance by loading the full table once, then resident loading the table the three times with the conditions, then dropping the full table.

       

       

      Thoughts?

       

      Also note that I am note using all the data in the table with the 3 hits I currently use.

        • Re: What hits the database more, Loading full table once, or restricted 3 times
          Niky Rathod

          Hi Marc,

           

          I always though that loading same table more then once may be a issue for loading and so I used to concatenate them with conditions on database. Which did not solve my issue as my qvws use to take time to load.

           

          What I then found is that qvw renders each row to fetch data. Which in return usually take time then joins. Hence, you should make logical/ physical join in the qvw that would simplify the data and would be irrespective of how many times you load the same table with different conditions. (Assume you might have some logical joins for instance time dimesnion, products, etc..) Create structured data model.

          Keeping in consideration which should also simplify your expressions for presentation purpose.

          • Re: What hits the database more, Loading full table once, or restricted 3 times
            Jonathan Dienst

            Hi

             

            As you are not using all the data, I would recommend that you only run the select with the conditions, but I would also avoid making 3 round trips to the server. Rather combine the expressions into a single SQL select. In the code fragments below, replace Condition1 - Condition 3 with your conditions.

             

            I would also suggest marking the records with a group indicator. You  say the conditions are mutually exclusive, so you could use a case statement to indicate to which condition each records belongs. This syntax is for SQL Server. Other DBMS use different syntax - this part is optional, though.

             

            Temp1:

            SQL Select ...

                 ...

                 Case When Condition1 Then 1

                      When Condition2 Then 2

                      When Condition3 Then 3

                      Else 0 End As Group,

                 ...

            From dbo.Table ...

            Where (Condition1) OR (Condition2) OR (Condition3);

             

            Result1:

            NoConcatenate

            LOAD * Resident Temp1

            Where Group = 1;

             

            Result2:

            ...

            ...

             

            DROP Table Temp1;

             

            Hope that helps

            Jonathan

            • Re: What hits the database more, Loading full table once, or restricted 3 times
              Dave Riley

              Hi Marc,

               

               

              The answer to your question largely depends on how the data is stored in the source database and whether there are other connections being made to the source tables. 

               

              If you are specifying WHERE clauses, and the data is ordered on the disk in such a way that the records are scattered throughout the table then you will get a slower return of records than if the records are all together in the same region of the table.  Furthermore if other users are accessing at the same time, then the problem could be much worse.

               

              If you are reading a large volume of records, then you may be best to separate out the required data using the ETL tools in the database system into a table of its own and read from that without any conditions. If you have a database developer or database administrator to advise you, that would help.

               

              flipside

                • Re: What hits the database more, Loading full table once, or restricted 3 times
                  Lee Matthews

                  Hi Marc

                   

                  If the 3 chunks of data combined represent the majority of data in the table then extracting the entire table is probably better. Otherwise using a where clause is best, and Jonathan's suggestion above is also going to further reduce load on the database as only one query gets executed. But you also want to ensure that there are indexes on the table that will be used by the where clause to improve data access. I assume you are talking about the Epicor DB - do you use Progress DB or SQL Server? If the latter you can run the SQL query through query analyser to identify the execution plan that SQL will use, including any indexes it will use.

                • Re: What hits the database more, Loading full table once, or restricted 3 times

                  Thank you everyone for your input. I just converted all of our documents to use QVD's and I ended up going around this issue by storing the entire table in a previously loaded document that needed the entire table. I will keep these ideas in mind if I come across this situation again.