Skip to main content
Question

Exec time/ Fetch time JDBC

  • November 6, 2018
  • 8 replies
  • 28 views
chaima
Forum|alt.badge.img+2
  • chaima
  • Participating Frequently

Hi team,

Can anyone please help me understand why fetch time may be long even if a query returns a small number of results?
Sorry if this is a basic question, but I have a query that takes 8 seconds to execute and 4 seconds to fetch just 12 records

Thank you
Chaima

    8 replies

    Vertiguy
    Forum|alt.badge.img+2
    • Vertiguy
    • Participating Frequently
    • November 6, 2018

    Are you querying internal tables?

    chaima
    Forum|alt.badge.img+2
    • chaima
    • Author
    • Participating Frequently
    • November 6, 2018

    No regular tables

    mosheg
    Forum|alt.badge.img+2
    • mosheg
    • Participating Frequently
    • November 6, 2018
    Hi Chaima,

    Try to run the same query directly on one of the cluster nodes in vsql with: \timing ON
    and compare with your remote client times.
    The difference usually relates to client settings, for example Tableau.

    BTW, sometimes when you have timing turned on and see results using MORE, if you wait sometime, this time will be added to the "All formatted rows" value.

    Kind Regards,
    Moshe
    chaima
    Forum|alt.badge.img+2
    • chaima
    • Author
    • Participating Frequently
    • November 6, 2018
    Hi Moshe, thank you.
    Unfortunately I can't do the test directly on one of the nodes... I don't have the access to the machines

    What is even more confusing to me is that on simple queries that return thousands of results the fetch time is less than a second (I am using DBviz) My first instinct was that there might be a network problem

    One follow up question please, is the fetch time included in query_duration column of the system table query_requests?
    Thanks again

    Chaima
    Vertica_Curtis
    Forum|alt.badge.img+1
    • Vertica_Curtis
    • Participating Frequently
    • November 6, 2018

    At Intuit a couple of years ago, we found a table that took a couple of seconds to return 7 rows. We rebuilt the table, and it fixed the issue. No idea what caused it.

    Maurizio
    Forum|alt.badge.img
    • Maurizio
    • Participating Frequently
    • November 6, 2018

    Hi Chaima. You say that fetch time is long with a small reault set.

    Question... How did you measure the fetch time? Did you profile the Q and got the value associated with the "Send data to client" counter or what?

    Sometimes certain "click-click interfaces" send monster query against the catalog just to see how to align the columns in output...

    MaciejPaliwoda
    Forum|alt.badge.img+2
    • MaciejPaliwoda
    • Participating Frequently
    • November 6, 2018

    The another issue that can cause such behaviour is pool configuration or long queue of queries (= high concurrency in the point of time that You are executing your query). I observed such behaviour on my local VM with VERTICA and Tableau (installed on my machine as well).

    If it is not the faulty query (=high cost of execution - check the "query plan") i would check this option as well.

    chaima
    Forum|alt.badge.img+2
    • chaima
    • Author
    • Participating Frequently
    • November 9, 2018

    @Maurizio I'm using DBVisualizer, and trusting the timings it's displaying, Is there a reason I shouldn't?
    I'll check if this matches the counter Send data to client
    Thanks

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement