4 Replies Latest reply: Apr 5, 2012 8:00 AM by 759648 RSS

    query slow in plsql

    759648
      hi, i have a problem
      i have a query with :

      select table.*, f_function(parameters) from table;

      f_function(parameters)
      is select count(*) from table_2 where id=param;

      why the query is slow with a lot of records???

      if i query with subquery with the same query and with out functions the querys works speedly
      why?? plase help

      Edited by: JaQE on 04-abr-2012 8:06
        • 1. Re: query slow in plsql
          BluShadow
          JaQE wrote:
          hi, i have a problem
          i have a query with :

          select table.*, f_function(parameters) from table;

          f_function(parameters)
          is select count(*) from table_2 where id=param;

          why the query is slow with a lot of records???
          Because you are calling a PL/SQL function for every row you retrieve. That causes a context switch between the SQL and PL/SQL engines each time.

          Don't use PL/SQL when SQL alone will do the job.
          if i query with subquery with the same query and with out functions the querys works speedly
          why?? plase help
          Because PL/SQL and SQL run as seperate processes and it takes time for the two to talk to each other. If you do a lot of switching from one to the other then it will be slow. In SQL you see then when you call PL/SQL functions for each row and in PL/SQL you see this when you perform individual SQL statements for each row of a cursor loop.

          Always aim to use SQL as much as possible.
          • 2. Re: query slow in plsql
            Peter Gjelstrup
            Hi,

            You may be able to reduce the number of times, the function is called. As it is now, you will call for each row returned.

            If you have many same values for parameters, you could change into
            select table.*,
                   (select f_function(parameters) from dual) f_call
            from   table;
            This is known as scalar subquery caching. Tom Kyte has a nice piece on it in oramag:
            http://www.oracle.com/technetwork/issue-archive/2011/11-jan/o11asktom-195084.html

            Regards
            Peter

            Edited by: Peter on Apr 4, 2012 3:02 PM
            - added oramag link
            • 3. Re: query slow in plsql
              Billy~Verreynne
              Peter Gjelstrup wrote:

              You may be able to reduce the number of times, the function is called. As it is now, you will call for each row returned.

              If you have many same values for parameters, you could change into
              select table.*,
              (select f_function(parameters) from dual) f_call
              from   table;
              This is known as scalar subquery caching.
              Is a deterministic PL/SQL function not a similar, and perhaps even a better, option?
              • 4. Re: query slow in plsql
                759648
                thanks you , for the all answers , i have delete the function of query and execute in the other place , out of the query for optimized the application.
                this good know that information :)
                thanks
                Jaqe