[IQUG] Slow fetching between two identical IQ servers

Mark Mumy markdmumy at gmail.com
Thu Nov 16 07:37:39 MST 2017


Do a fetch with Open Client and compare the times.  dbisql has more overhead than isql.  If your SQL file is just 1 statement (select), these options won’t affect it.

Have you compared the cfg file to make sure those are the same?

Where are you streaming the result to?  Is it traversing your LAN/WAN?  Or are you using the client on the IQ host and streaming to disk?

You have a significant number of variables, quite a lot of which are out of your control (like network topology).

I would run an isolated test.  One that isolates just IQ so that you can determine if IQ is the issue or if it is environmental.  

On each IQ host, put your select statement into a file.  IQ_test.sql.

Then run that on the iq host:

	time isql -u dba -p sql -S locahost:2638 -i IQ_test.sql | grep -i “affected”

This will throw all the data away.  It is still returned from IQ to isql, but then thrown away unless the row has the word affected (which is what you get at the end of the SELECT OUTPUT).

This eliminates anything related to networking as the issue.  This eliminates anything related to the machine that is running the query.  This eliminates all disk activity except for the read that IQ must do.  If the times are still different than you have to look to IQ for a solution.  If the times are roughly the same, then the issue is not IQ, but rather something else in the data flow chain once the data leaves IQ.

Mark

> On Nov 16, 2017, at 08:22, Louie, David <David.Louie at blackrock.com> wrote:
> 
> Thanks Mark.
>   
> However the packet sizes are identical on both Dev and QA servers which does not explain the slow fetching.
>  
> I don’t think these options are the issues but saw them set in the slow QA server and not in the DEV.
>  
> I opened a case with SAP.
>  
>  
> From: Mark Mumy [mailto:markdmumy at gmail.com] 
> Sent: Thursday, November 16, 2017 9:10 AM
> To: Louie, David <David.Louie at blackrock.com>
> Cc: IQ Users Group <iqug at iqug.org>
> Subject: Re: [IQUG] Slow fetching between two identical IQ servers
>  
> The max packet size should be 64k now.  Thought that was in 15.4 too.  
>  
> The options and errors make perfect sense.  Those are not IQ options but rather for dbisql and it’s behavior.  
> 
> Mark
>  
> ========================
>  
> Sent from my mobile device
> 
> On Nov 16, 2017, at 07:17, Louie, David <David.Louie at blackrock.com <mailto:David.Louie at blackrock.com>> wrote:
> 
> <image001.png>
>  
>  
> It takes 16X longer to fetch data from IQ to a flat file in our QA server vs our DEV server.
>  
> Hardware same, nic card, same, RAM and CPU same.  Table is the same.
>  
> My packet size is 16000 ( max).
>  
> However I see that these two isql configs are set in the slower server.
>  
> Suspecting this may be the issue I want to turn them off or rest to default however cannot
>  
>  
> QTREWD_QA_IQ.dlouie..2010.1> set option public.ISQL_SHOW_MULTIPLE_RESULT_SETS='OFF'
> QTREWD_QA_IQ.dlouie..2010.2> go
> SQL Anywhere Warning 120: 'ISQL_SHOW_MULTIPLE_RESULT_SETS' is an unknown option
>  
>  
> QTREWD_QA_IQ.dlouie..2012.1> set option public.ISQL_PRINT_RESULT_SET='LAST';
> SQL Anywhere Warning 120: 'ISQL_PRINT_RESULT_SET' is an unknown option
> QTREWD_QA_IQ.dlouie..2013.1>
>  
> This is IQ 15.4.
>  
> Thanks
> David
>  
>  
>  
> This message may contain information that is confidential or privileged. If you are not the intended recipient, please advise the sender immediately and delete this message. See http://www.blackrock.com/corporate/en-us/compliance/email-disclaimers <http://www.blackrock.com/corporate/en-us/compliance/email-disclaimers> for further information.  Please refer tohttp://www.blackrock.com/corporate/en-us/compliance/privacy-policy <http://www.blackrock.com/corporate/en-us/compliance/privacy-policy> for more information about BlackRock’s Privacy Policy.
> For a list of BlackRock's office addresses worldwide, see http://www.blackrock.com/corporate/en-us/about-us/contacts-locations <http://www.blackrock.com/corporate/en-us/about-us/contacts-locations>.
> 
> © 2017 BlackRock, Inc. All rights reserved.
> _______________________________________________
> IQUG mailing list
> IQUG at iqug.org <mailto:IQUG at iqug.org>
> http://iqug.org/mailman/listinfo/iqug <https://urldefense.proofpoint.com/v2/url?u=http-3A__iqug.org_mailman_listinfo_iqug&d=DwMFaQ&c=zUO0BtkCe66yJvAZ4cAvZg&r=hvVkP5y30w3_fHBwXsoZ2pqbwQXietniE01Z666b0tE&m=JK4-1yujrvHzaEGMP6nvOWLrZmT7atlaKS7m7voGi0M&s=W2XMzus00NyOwaAGZ7C4Jo1ZOWMJBrBoG93sn1rqpKE&e=>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20171116/bdfafbc7/attachment-0001.html>


More information about the IQUG mailing list