2 Replies Latest reply: Nov 19, 2012 11:12 AM by user9542267 RSS

    compating two select statements execution time

    user9542267
      Hi All,
      This is a curious question. I have a query with very long and complicate calculations and some total calculations depend from sub calculations.
      What I did is just query the sub calculations and using WITH finish the total calculations but the query using WITH is taking almost double the time of inline query.
      I have the idea that using WITH it will be easy and take less effort, anyone know WHY takes more time?
      Can I do this in a different way for better performance?
      Thanks!

      I did some test using the table from scott user, first query takes 0.031 seconds and the second query 0.047. Of course my query have long calculations using a lot or rows.
      select empno, ename, sal + comm as subTotal1 
      ,sal * 1.20 as subTotal2
      ,(sal+comm) + (sal * 1.20) as Total1
      from emp;
      
      With cal_scott as
      (
      select empno, ename, sal + comm as subTotal1 
      ,sal * 1.20 as subTotal2
      from emp
      )
      select empno, ename, subTotal1, subTotal2, subTotal1 + subTotal2 as Total1
      from cal_scott;
        • 1. Re: compating two select statements execution time
          BluShadow
          user9542267 wrote:
          Hi All,
          This is a curious question. I have a query with very long and complicate calculations and some total calculations depend from sub calculations.
          What I did is just query the sub calculations and using WITH finish the total calculations but the query using WITH is taking almost double the time of inline query.
          I have the idea that using WITH it will be easy and take less effort, anyone know WHY takes more time?
          Can I do this in a different way for better performance?
          Thanks!

          I did some test using the table from scott user, first query takes 0.031 seconds and the second query 0.047. Of course my query have long calculations using a lot or rows.
          That is not an accurate test for performance. 1/10th of a second could be taken by network traffic or other reasons unrelated to your actual query.

          I suspect your second query would actually have been faster to run due to data being cached at the operating system level, but the amount of data you're querying isn't enough to be indicative of performance anyway, as the amount of data on the emp table likely is all read by a single disk I/O anyway.

          Read the following two threads linked to in the FAQ: {message:id=9360003}

          to get an idea about performance issues and how to identify them (and ask questions about them)
          • 2. Re: compating two select statements execution time
            user9542267
            Hi
            Thanks for you reply and help !
            I am glad to hear that my issue is somewhere else, let me go the threads and see if I can come up with more questions.
            Best,