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
Improve data retrieval speed (Read 11510 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Improve data retrieval speed
20.12.05 at 09:18:01
 
Venku from the USA asked the following question:
 
Question:
Data retrieval of my 25,000 row table is slow. Is there a way to increase the performance or speed of data retrieval in SQL*XL?
 
Answer:
Slow is always relative and things need always go be faster. You can do many things to speed up performance and you can achieve a 90% reduction in query time when you chose the correct options. Please consider the following performance tips. I listed them in order of speed of data retrieval with the fastest method first and the slowest at the end.

  • Use the XLS output target:
    Using this option a new xls file will be written completely without the help of Excel.  This is very very fast and my estimation is that you cut the retrieval time 90% but it could be more. It is just very fast. You will get a simpel spreadsheet where no formatting is done whatsoever. It is a dump.
  • Switch off the formatting:
    In the resultset options dialog, switch off the formatting option. SQL*XL will still look in detail at the data and tries to put the data correctly into the spreadsheet but it will not do any formatting in terms of fonts and colors, column widths etc.
  • Use the very large recordset option:
    If you specify in the resultset options dialog that you will execute a large query SQL*XL will not try to cache all the data. Normally the data is stored in memory too in case you are going to make updates. If you use the large recordset option the results are not kept on the PC. It is very memory friendly but you loose the ability to update the records later. Initially this method is slower than the normal retrieval method but if you run out of physical memory during the retrieval process, in the later stage of the retrieval it may go quicker.
  • Use the overwrite method:
    In the resultset options dialog specify that the new rows are not to be inserted but that new rows will be written in existing cells. This is the overwrite option. This is quicker than the insert method.
Back to top
 
« Last Edit: 02.02.06 at 12:03:26 by Gerrit-Jan Linker »  

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




Posts: 75
Re: Improve data retrieval speed
Reply #1 - 20.04.06 at 08:48:16
 
SQL*XL user Raimonds from Latvia sent in the following question.
 
Question:
I retrieved 25,000 rows from Firebird into Excel with SQL*XL's large dataset 1000+ rows function on. It took 181 seconds. Is there a way to speed this up?
 
Answer:
I don't think this is very bad performance actually. I do agree that if you want to get a lot of data it is a long time to wait for it. Why don't you use the XLS output target and let SQL*XL load the file when it is ready? Most of the time SQL*XL needs to put the data into Excel. Putting it directly into a XLS will be much faster as Excel does not slow that process down.
Back to top
 
 

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




Posts: 75
Re: Improve data retrieval speed
Reply #2 - 26.10.06 at 08:28:04
 
Some further info on the difference between the "normal" optimisation and the optimisation "for large resultsets".
 
Memory usage can also be a factor in the performance of SQL*XL. When you use the normal optimisation all the data is first collected in memory and then the processing to Excel begins. When you chose the optimisation for large resultsets the memory usage is minimised and the processing in Excel starts immediately. Drawback with the large resultset option is that not all the services are available for the resultset. It will not be updatable for example.
 
Test scenario:
Test table my_table with 140.000 rows.
Initial memory usage on PC 201 MB
After executing query but before data processing starts memory usage is 310 MB
When setting the large resultset option the memory usage was 204 MB when the processing in Excel started.
 
This does point to using the large resultset option whenever you query a large set of records from the database.
Back to top
 
 

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




Posts: 75
Re: Improve data retrieval speed
Reply #3 - 31.10.06 at 10:35:36
 
Gabriel from the USA wrote:
 
I was able to download and connect to our database.  However, when I tried to download 20,000+ rows from a wide table, my machine locked up.  This is the issue we are trying to solve.  Our users are currently downloading their Cognos web reports to Excel and it is taking forever.  They usually download a lot of data.  I was hoping that this may be another way to do it.  But it seems like it is the same issue; too much data to be downloaded to Excel.  
 
Answer
You need to use the Large Resultset support in SQL*XL. Using that the machine will not lock up as memory usage will not increase. As you can read above, SQL*XL can also output to Excel it other ways which can have a significant performance improvement. The fastest is the direct output into XLS which creates a new file. You will not get formatting of the results however. I am sure that with the large resultset optimisation things will run much smoother.
 
You can set the optimisation for large resultsets in the Resultset Options dialog. To permanently set this option you can use the SQL*XL program perferences screen.
Back to top
 
 

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




Posts: 75
Improve network speed setting the Record buffer
Reply #4 - 02.05.09 at 08:35:03
 
Improve network speed setting the Record buffer
 
On networks with high ping times, speed can be improved setting the record buffer program preference. See the following topic for details:
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1239988330#1
Back to top
 
 

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