Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    3

    Question Answered: Quick Execution / Slow Fetching

    Hi,

    I have a query which executes in 64ms (according to the "EXPLAIN ANALYSE" execution plan). When I run the query sure enough it executes all of the code incredibly quickly (I can see it running through the procedure using DataGrip). However, once the code has executed it takes a further 12 seconds to return the actual result set.

    The result set contains around 20k lines and around 30 columns. It is large.

    My questions are:

    • How long is the load on the server? Is it 64ms, or is it 12 seconds + 64ms?
    • Is it true to state that there is little point in tuning the query as it is executing in 64ms?
    • Is there anyway of being able to improve the 12 seconds return time without having to reduce the sheer amount of data that the query is returning?


    Thank you in advance for improving my understanding

    VL

  2. Best Answer
    Posted by shammat

    "The runtime shown by explain analyze is the runtime on the server. So the server needs 64ms to parse, prepare and execute the statement, retrieving all data. The 12 seconds you see are caused by transferring the data from the server to the client and probably also by the client to process and display those 30.000 rows. That means, even if you could tune the statement itself (to run e.g. in 10ms instead), you'd still have 12 seconds as the total elapsed time.

    You have two options for tuning: don't display that much data or improve your application to be faster in processing and displaying the data."


  3. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    The runtime shown by explain analyze is the runtime on the server. So the server needs 64ms to parse, prepare and execute the statement, retrieving all data. The 12 seconds you see are caused by transferring the data from the server to the client and probably also by the client to process and display those 30.000 rows. That means, even if you could tune the statement itself (to run e.g. in 10ms instead), you'd still have 12 seconds as the total elapsed time.

    You have two options for tuning: don't display that much data or improve your application to be faster in processing and displaying the data.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #3
    Join Date
    Jul 2009
    Posts
    3
    Thank you Shammat - that is really useful to know.

    Would the server be experiencing high processor usage or memory usage during the 12 second transfer from server to client? (please excuse my ignorance on this).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •