6 Replies Latest reply: Mar 4, 2013 2:30 AM by CP RSS

    Performacne Query - General

    CP
      Which one performs better in the below 2 SQLs
      SQL 1
      Insert into Table1
      Select col1, col2, col3 from table2
      union all
      Select col1, col2, col3 from table3
      union all
      Select col1, col2, col3 from table4
      SQL 2
      if condition 1 
      then
      
      Insert into Table1
      Select col1, col2, col3 from table2;
      
      elsif condition 2 then
      Select col1, col2, col3 from table3;
      
      elsif condition 3 then
      Select col1, col2, col3 from table4;
      
      end if;
      Both will Insert the data in Table1. Checking the condition and Inserting in Table separately is better or Inserting with UNIONALL irrespective of the condition is better?
        • 1. Re: Performacne Query - General
          SomeoneElse
          The two aren't the same at all.

          What are these mysterious "conditions"?
          • 2. Re: Performacne Query - General
            sb92075
            post EXPLAIN PLAN for both alternatives



            How do I ask a question on the forums?
            SQL and PL/SQL FAQ
            • 3. Re: Performacne Query - General
              CP
              if condition 1 
              then
               
              Insert into Table1
              Select col1, col2, col3 from table2;
               
              elsif condition 2 then
              Insert into Table1
              Select col1, col2, col3 from table3;
               
              elsif condition 3 then
              Insert into Table1
              Select col1, col2, col3 from table4;
               
              end if;
              Missed 'Insert into Table1' in elsif condition 2 and 3.

              Conditions are simple. If data exists in table2, table3, table4 then insert into table1.
              • 4. Re: Performacne Query - General
                Frank Kulash
                Hi,
                CP wrote:
                Which one performs better in the below 2 SQLs
                SQL 1
                Insert into Table1
                Select col1, col2, col3 from table2
                union all
                Select col1, col2, col3 from table3
                union all
                Select col1, col2, col3 from table4
                SQL 2
                if condition 1 
                then
                
                Insert into Table1
                Select col1, col2, col3 from table2;
                
                elsif condition 2 then
                Select col1, col2, col3 from table3;
                
                elsif condition 3 then
                Select col1, col2, col3 from table4;
                
                end if;
                Those are not equivalnt.
                SQL 1 inserts data from all 3 tables, and always does the same thing regardless of conditions 1, 2 and 3.
                SQL 2 inserts data from (at most) one of those tables, depending on the conditions.
                Both will Insert the data in Table1.
                Both may insert some data into Table1. In general, it won;t be the same data.
                Checking the condition and Inserting in Table separately is better or Inserting with UNIONALL irrespective of the condition is better?
                Usually we say that if approach A produces the right results, and approach B does not, then A is better than B. If both A and B produce the same results, then we might look at performance as a tie-breaker. Once again, in the example you posted, if the two approaches produce the same results it is purely coincidence.

                When you ask
                "which one performs better?", I assume what you're really interested in is
                "which one performs better on my system with my data and my indexes?" You're in a better position to answer that than strangers who don't have access to your system, your data or your indexes. If you'd like help, post your exact SQL code, and the execution plans. See the forum FAQ {message:id=9360003}

                UNION tends to be slow. I've seen several cases where there were N possible cases, depnding on conditions being handled by one big SQL statement (using UNION and/or CASE and or joins), and performace was improved by testing the conditions first, in PL/SQL, and then performing 1 of N much simpler SQL statements. I have also seen some cases where just the opposite was true, but not nearly so many.
                • 5. Re: Performacne Query - General
                  Nikolay Savvinov
                  Hi,

                  if you meant to ask whether it is better to do one INSERT / union'ed SELECT rather than N independent INSERT / SELECT statements, then yes, generally this would be the case, although I think that for reasonable values of N the difference in performance won't be very significant.

                  Best regards,
                  Nikolay
                  • 6. Re: Performacne Query - General
                    CP
                    if condition 1 
                    then
                    Insert into Table1
                    Select col1, col2, col3 from table2;
                    end if;
                    if condition 2 then
                    Select col1, col2, col3 from table3;
                    end if; 
                    if condition 3 then
                    Select col1, col2, col3 from table4;
                    end if;
                    This is what I was supposed to Post. Posted Wrong.

                    >
                    Frank Kulash wrote:
                    +When you ask
                    "which one performs better?", I assume what you're really interested in is
                    "which one performs better on my system with my data and my indexes?" You're in a better position to answer that than strangers who don't have access to your system, your data or your indexes. If you'd like help, post your exact SQL code, and the execution plans. See the forum FAQ 3. How to improve the performance of my query? / My query is running slow.+
                    >

                    I didnt start writing anything yet. I was about to start and got a doubt which way is better. Hence I was unable to post this.

                    >
                    UNION tends to be slow. I've seen several cases where there were N possible cases, depnding on conditions being handled by one big SQL statement (using UNION and/or CASE and or joins), and performace was improved by testing the conditions first, in PL/SQL, and then performing 1 of N much simpler SQL statements. I have also seen some cases where just the opposite was true, but not nearly so many.
                    >

                    This is what I expected. Whether to use UNION ALL or not. I got the solution though we cannot be very sure on this. I will follow the majority path.
                    Thanks Frank.