[IQUG] IQUG Digest, Vol 41, Issue 49

Mumy, Mark mark.mumy at sap.com
Mon Jun 13 16:40:40 MST 2016

It could possibly affect shorter queries, yes.  When the result set is done, though, the packet goes.  I don’t think we will send a full 64k packet if we just filled it with 1k of data.  That’s my understanding anyway.  We want a larger packet and will fill it with data, but if there’s no data it goes to the client or back to the server.

Mark Mumy
Enterprise Architect, Customer Innovation and Enterprise Platform Team 
M +1 347 820-2136, E mark.mumy at sap.com
Join me online: http://scn.sap.com/people/markmumy/blog <http://scn.sap.com/people/markmumy/blog::1n::>
Please consider the impact on the environment before printing this e-mail.

On 6/8/16, 15:01, "Steve Shen" <sshen at sscinc.com> wrote:

Hi Mark,

If the default packet size is set to 64K at version 16.x, won't the performances for some SQL statements, sending small amount of data to Server or receiving small amounts of data from Server, be suffering?

Won't it more granular to let the client machines to specify "CommBufferSize" in the ODBC configuration files?

Please advise. Thank you.


Steve Shen

t: (646) 827-2102

-----Original Message-----
From: iqug-bounces at iqug.org [mailto:iqug-bounces at iqug.org] On Behalf Of iqug-request at iqug.org
Sent: Friday, May 27, 2016 11:09 AM
To: iqug at iqug.org
Subject: IQUG Digest, Vol 41, Issue 49

Send IQUG mailing list submissions to
        iqug at iqug.org

To subscribe or unsubscribe via the World Wide Web, visit
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: SELECT is faster using the "Temp_Extract_" features
      (Mumy, Mark)


Message: 1
Date: Fri, 27 May 2016 15:08:36 +0000
From: "Mumy, Mark" <mark.mumy at sap.com>
To: "Barton, John" <john.barton at sap.com>, "Baker, Chris"
        <c.baker at sap.com>,      William Kennedy <kennb53 at icloud.com>,
        "iqug at dssolutions.com"  <iqug at dssolutions.com>
Subject: Re: [IQUG] SELECT is faster using the "Temp_Extract_"
Message-ID: <66D6BFE7-0BB1-488B-B399-BDEBC5A8DBAB at sap.com>
Content-Type: text/plain; charset="utf-8"

In IQ 16 all of those parameters were automated and can resize automatically.  No need to set them like we did in IQ 15.

I would also add that we would want to increase the packets size on the IQ server side to the max and not set it at the client.  The client will get it from the server at login.

Mark Mumy
Enterprise Architect, Customer Innovation and Enterprise Platform Team SAP

M +1 347 820-2136, E mark.mumy at sap.com<mailto:mark.mumy at sap.com>

Join me online: http://scn.sap.com/people/markmumy/blog<http://scn.sap.com/people/markmumy/blog::1n::>

Please consider the impact on the environment before printing this e-mail.

From: "iqug-bounces at iqug.org" <iqug-bounces at iqug.org> on behalf of "Barton, John" <john.barton at sap.com>
Date: Friday, May 27, 2016 at 09:43
To: Chris Baker <c.baker at sap.com>, William Kennedy <kennb53 at icloud.com>, IQ Group <iqug at dssolutions.com>
Subject: Re: [IQUG] SELECT is faster using the "Temp_Extract_" features


Any idea what the default settings are for prefetching done by the driver in IQ 15.4 and IQ 16?

It has been a long time since I did any testing with these setting but I do believe it changes the behavior of how data gets fetched by the ODBC driver which is sitting below the application. Not sure if they apply to the IQ JDBC driver used by java based tools that connect.

Parameters for UNIX/Linux ODBC DSN or Connection string:

CommBufferSize = 16000
PreFetchBuffer = 16000
On Windows, you can add these settings via the odbc admin interface.
There?s a check box for the CommBufferSize and the two other settings can be added manually on the box below in that format:

John Barton
SAP Digital Enterprise Platform Group
Concept Engineering Team
Database Specialist
Phone: 602-418-4308
E-Mail: john.barton at sap.com<mailto:john.barton at sap.com>
Twitter: @johnsbarton<http://twitter.com/johnsbarton>

From: iqug-bounces at iqug.org [mailto:iqug-bounces at iqug.org] On Behalf Of Baker, Chris
Sent: Friday, May 27, 2016 4:18 AM
To: William Kennedy <kennb53 at icloud.com>; iqug at dssolutions.com
Subject: Re: [IQUG] SELECT is faster using the "Temp_Extract_" features

You are seeing the difference between IQ?s true speed of query and the network transfer time back to a client.

How are you connecting to IQ with dbisql?  If you are connecting ?to a running database on this computer? then the shared memory driver would be used and should be the fastest transfer.  Otherwise, if you are using the ?connect to a running database on another computer (-x tcpip?), then try setting the ?-p? server option to its largest value 65535 (IQ 16) in the .cfg file and restart the IQ server.  This will allow the server to use the largest network packet size in the transfer.

Regardless of the above, also check the setting of the ?Force_No_Scroll_Cursors? option.  This defaults to ?Off? but by setting it ?On? - e.g. set option public.Force_No_Scroll_Cursors=?On?; for all users or set temporary option Force_No_Scroll_Cursors=?On?; for you connection can reduce the use of temp space and begin transferring the results immediately.  I usually leave it on but for some BI applications that use scrollable cursors to retrieve data, it needs to be set off.

Keep in mind that when retrieving data to dbisql, any query plan timing will be affected - the number of rows returned in block fetches will be interrupted if you quit the result set transfer and this will be shown in your query plan (not all expected rows flow up to the root node).  As you have observed, to get true query-only timings on IQ queries and avoid network transfer, use the temp_extract options or select into a #temp table to eliminate the network transfer.  You also need to issue a ?commit? at the end of the query, even if you cancel the result set in dbisql, in order to get the HTML query plan output.


Chris Baker | Platform Architect | STIG | Customer Innovation & Enterprise Platform | 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. 4120 Yonge Street, Suite 600, Toronto, M2P 2B8<x-apple-data-detectors://17/1>
c.baker at sap.com<mailto:c.baker at sap.com> | www.sap.com<http://www.sap.com/>

Conference tel: 1-866-312-7353,,9648565377#<tel:1-866-312-7353,,9648565377%23>

From: iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org> [mailto:iqug-bounces at iqug.org] On Behalf Of William Kennedy
Sent: Friday, May 27, 2016 5:38 AM
To: iqug at dssolutions.com<mailto:iqug at dssolutions.com>
Subject: Re: [IQUG] SELECT is faster using the "Temp_Extract_" features

I may have found the answer in the manuals:

Data extraction options
The data extraction facility improves performance dramatically for queries with a large result set.

However, any advices is very welcome and worth recording here in the user group emails I think.

Sybase IQ DownUnder

Sent from my iPhone

On 27 May 2016, at 10:51, William Kennedy <kennb53 at icloud.com<mailto:kennb53 at icloud.com>> wrote:
I have a SELECT statement in a stored procedure which returns about 3,000,000 rows.

The rows are returned to the client application (dbisql) running on the same machine (the data server host).

The SELECT takes about 2 minutes to return the 3,000,000 rows.

If I use the "Temp_Extract_? features, inside the stored procedure, and export the data to a file instead, the entire process takes only 3 seconds rather than 2 minutes.

Is there some way I can get the SELECT to return the rows this quickly without resorting to redirecting the return set to a file using the "Temp_Extract_? features.

Any suggestions, comments, ideas, sincerely appreciated.
IQUG mailing list
IQUG at iqug.org<mailto:IQUG at iqug.org>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20160527/1d0bcd10/attachment.html>


IQUG mailing list
IQUG at iqug.org

End of IQUG Digest, Vol 41, Issue 49
This email with all information contained herein or attached hereto may contain confidential and/or privileged information intended for the addressee(s) only. If you have received this email in error, please contact the sender and immediately delete this email in its entirety and any attachments thereto.

More information about the IQUG mailing list