[IQUG] long time for first fetch return
c.baker at sap.com
Wed Nov 1 13:00:14 MST 2017
Set the ‘force_no_scroll_cursors’ option ‘off’ to avoid materializing the results in temp first.
You will get first row faster, but be aware that some query tools (e.g. MicroStrategy) do not allow this.
Also you must retrieve all results when generating query plans, otherwise the timing will only reflect what has been retrieved to the root node to query cancellation or ‘commit’ when using dbisql.
Chris Baker | Database Engineering Evangelist | CERT | PI HANA Platform Data Management | SAP
T +1 416-226-7033<tel:+1%20416-226-7033> | M +1 647-224-2033<tel:+1%20647-224-2033> | TF +1 866-716-8860<tel:+1%20866-716-8860>
SAP Canada Inc. 445 Wes Graham Way, Waterloo, N2L 6R2<x-apple-data-detectors://17/1>
c.baker at sap.com<mailto:c.baker at sap.com> | www.sap.com<http://www.sap.com/>
On Nov 1, 2017, at 2:24 PM, Mark Mumy <markdmumy at gmail.com<mailto:markdmumy at gmail.com>> wrote:
IQ 12.7? What’s that? :)
In this case, this is the time difference between when the parent node called the order by node and when the child node (#01) returned the first value. Think of this like dialing a phone. You called someone at 10:45:30 but they didn’t pick up and talk to you until 10:55:57.
Can you send the full HTML plan? If so, that will help diagnose what the real issue is. It is something in the child node or lower. For instance, if you didn’t have any indexes, then the 10 minutes could simply be the time it takes to run the query and return the first row. Some of this is the width of the data as well. The row being sent up to node #2 is 3383 bytes wide. The wider the data, the more work to stitch it together lower in the plan.
Seeing the full plan will help explain a lot of this, so share if you can.
On Nov 1, 2017, at 13:15, Julie Nishimura <jnishimura at connexity.com<mailto:jnishimura at connexity.com>> wrote:
While investigating one slow query and looking at its query plan, I am trying to recall what I can tune to reduce the time of first fetch return?
We are still using IQ 12.7, if anyone still remembers it… Thanks a lot!
select top 10 *
where mid = 271517
and deleted_status = 0
and indexed = 0
order by rd_7_days desc
JULIE NISHIMURA | Senior Database Administrator | Connexity.com<http://connexity.com/>
P: 310.903.4152 | jnishimura at connexity.com<mailto:jnishimura at connexity.com>
IQUG mailing list
IQUG at iqug.org<mailto:IQUG at iqug.org>
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the IQUG