[IQUG] IQUG Digest, Vol 59, Issue 3

Khan, Imran imran01.khan at sap.com
Thu Nov 2 01:17:12 MST 2017


Unless detailed query plan is shared, commenting anything would be guessing.

Besides, few check points are as follows: 

Ensure use of columns, caching, wide tables ( projection list) and predicates usage is made most optimal. Since you have "order by" clause ensure that column rd_7_days is indexed !

HTH,
- Imran. 

-----Original Message-----
From: iqug-bounces at iqug.org [mailto:iqug-bounces at iqug.org] On Behalf Of iqug-request at iqug.org
Sent: Thursday, November 2, 2017 1:41 PM
To: iqug at iqug.org
Subject: IQUG Digest, Vol 59, Issue 3

Send IQUG mailing list submissions to
	iqug at iqug.org

To subscribe or unsubscribe via the World Wide Web, visit
	http://iqug.org/mailman/listinfo/iqug
or, via email, send a message with subject or body 'help' to
	iqug-request at iqug.org

You can reach the person managing the list at
	iqug-owner at iqug.org

When replying, please edit your Subject line so it is more specific
than "Re: Contents of IQUG digest..."


Today's Topics:

   1. Re: long time for first fetch return (Kutlu, Mustafa)


----------------------------------------------------------------------

Message: 1
Date: Thu, 2 Nov 2017 08:10:00 +0000
From: "Kutlu, Mustafa" <mustafa.kutlu at sap.com>
To: "Baker, Chris" <c.baker at sap.com>, Julie Nishimura
	<jnishimura at connexity.com>
Cc: IQ Users Group <iqug at iqug.org>
Subject: Re: [IQUG] long time for first fetch return
Message-ID: <31ff08bd37f9470d8e093440216725aa at sap.com>
Content-Type: text/plain; charset="utf-8"

If there are wide columns in the projection list, then it definitely takes long time to build rows from those columns. If below query works much faster, you could blame those fat columns.

select 1,1,1,1,1,1,1,1,1,1,1,1
from iqe_main_data
where mid = 271517
and deleted_status = 0
and indexed = 0
order by rd_7_days desc

Best

Mustafa Kutlu
Solution Manager | Database and Data Management
SAP T?RK?YE, Tepe Prime Eski?ehir Devlet Yolu (Dumlup?nar Bulvar?) 9.km B Blok No:15 06800 ?ankaya \ANKARA

T +90 312 2180706, F +90 312 2180799, M +90 530 1730414, mailto: mustafa.kutlu at sap.com<mailto:mustafa.kutlu at sap.com>

Join us online: www.sap.com/turkey<http://www.sap.com/turkey>, Twitter<http://twitter.com/SAPTurkiye>, Facebook<http://www.facebook.com/#!/sapturkiye>, Linkedln<http://www.linkedin.com/groups?gid=3151476&mostPopular=>, Flickr<http://flickr.com/SAPTurkiye>, FriendFeed<http://friendfeed.com/SAPTurkiye>



From: iqug-bounces at iqug.org [mailto:iqug-bounces at iqug.org] On Behalf Of Baker, Chris
Sent: Thursday, November 2, 2017 12:59 AM
To: Julie Nishimura <jnishimura at connexity.com>
Cc: IQ Users Group <iqug at iqug.org>
Subject: Re: [IQUG] long time for first fetch return

Sorry. Set the option ?on?. Default is ?off?. (I was walking and typing at the same time - next time I?ll just chew gum:-)

Chris
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 4:09 PM, Julie Nishimura <jnishimura at connexity.com<mailto:jnishimura at connexity.com>> wrote:
Thank you Chris, I will try that.

Mark, I?ve emailed you the query plan. Thanks again!

From: Baker, Chris [mailto:c.baker at sap.com]
Sent: Wednesday, November 01, 2017 1:00 PM
To: Mark Mumy
Cc: Julie Nishimura; IQ Users Group
Subject: Re: [IQUG] long time for first fetch return

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
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.

Mark



On Nov 1, 2017, at 13:15, Julie Nishimura <jnishimura at connexity.com<mailto:jnishimura at connexity.com>> wrote:

Hello,
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 *
from iqe_main_data
where mid = 271517
and deleted_status = 0
and indexed = 0
order by rd_7_days desc


<image001.png>



JULIE NISHIMURA | Senior Database Administrator | Connexity.com<http://connexity.com/>

P: 310.903.4152 | jnishimura at connexity.com<mailto:jnishimura at connexity.com>
<image002.jpg><http://about.shopzilla.com/>

_______________________________________________
IQUG mailing list
IQUG at iqug.org<mailto:IQUG at iqug.org>
http://iqug.org/mailman/listinfo/iqug
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20171102/1a128418/attachment.html>

------------------------------

_______________________________________________
IQUG mailing list
IQUG at iqug.org
http://iqug.org/mailman/listinfo/iqug

End of IQUG Digest, Vol 59, Issue 3
***********************************


More information about the IQUG mailing list