Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Latency on big table / arraysize variable (Read 7683 times)
elshaa
YaBB Newbies
*


I Love SQL*XL

Posts: 2
Latency on big table / arraysize variable
17.04.09 at 19:12:09
 
Hi all,
I'm not an SQL*XL user, just a sysadmin. Users from office abroad report problems fetching lots of rows returned by a query. (around 200.000).
The problem is well explained here : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:366342600346 905366
 
The way sqlplus (so I guess SQL*XL) works generates lots of packets round trip while Oracle returns the rows to sqlplus, resulting in latency.
I was looking for a way to configure the "arraysize" variable in SQL*XL but couldn't find it. I though the option "Optimize for large rows +1000" would be likely similar, but choosing it generates an error when executing the query.
 
Is there an other way to setup this option ?
I didn't try to execute the sql statement "alter session set arraysize 5000" or just "set arraysize 5000" would work... ?
 
Thanks!
Back to top
 
« Last Edit: 17.04.09 at 22:34:21 by Gerrit-Jan Linker »  
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Latency on big table / arraysize variable
Reply #1 - 20.04.09 at 13:48:18
 
Hi,
 
The default is a buffer size of 10 records. I have changed this for you in version  4.3.16.
 
I have added 2 program parameters. You can change them in the program preferences, in the Resultset options dialog settings:

  • Record buffer:
    Specifies how many records should be retrieved from the database in each fetch. Default is 50.
  • Large  recordset record buffer  
    The same but then for the large recordset option. Default is 1000.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
elshaa
YaBB Newbies
*


I Love SQL*XL

Posts: 2
Re: Latency on big table / arraysize variable
Reply #2 - 20.04.09 at 14:23:23
 
Thanks a lot for that !
Do I have to wait for the stable release of 4.3.16 (the one I could get from the download page is 4.3.15), or is it a "beta versions" link ?
 
Cheers Smiley
Back to top
 
 
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Latency on big table / arraysize variable
Reply #3 - 20.04.09 at 16:13:15
 
I will contact you privately to arrange for the "beta test". Other users will need to wait for the stable release unless they also indicate they wish to help in the testing.
 
If I do not contact you shortly please use the enquiries form to request this version.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Latency on big table / arraysize variable
Reply #4 - 04.05.09 at 10:32:37
 
Feedback received after the testing:
 
Quote:
After a few days testing, I come with positive feedbacks. The
retrieving of Oracle output is much faster (I would say 2 or 3 times
faster).

Thanks a lot for having taken my request into consideration. If well
documentated, I'm sure it will help lots of people.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1