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
SQL*XL query speed test (Read 2310 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
SQL*XL query speed test
30.10.09 at 15:23:21
 
SQL*XL query speed test
 
After a question about performance today I ran a benchmark test. Perhaps it is useful to compare your results with this.
 
Factors that influence the performance:
-processor speed
-network speed (if the database resides elsewhere on the network)
-available memory (if no large recordset option is used, memory usage can be large)
-if formatting is turned on
-large recordset support (in combination with available memory)
 
My environment is not the fastest you can get. Perhaps that makes the tests more realistic.  
 
Environment:
I ran the test in a vmware virtual machine. The host is a 2GHz dual core pentium with sufficient memory. I used 500 MB memory for the virtual machine.  
The operating system is windows XP. I used Office 2000. For both no service packs were applied.
Database: SQL Server 2008 Express.
 
Test SQL:
set timing on;
select * from sys.all_parameters;
set timing off;

 
Tests:

  • All default options. Target Excel.
    7088 rows returned in 29 seconds. Speed: 244 records/sec.
  • Large recordset support enabled.
    7088 rows returned in 28 seconds. Speed 253 records/sec.
  • No application of formatting.
    7088 rows returned in 13 seconds. Speed 545 records/sec.
  • No formatting and large recordset support enabled.
    7088 rows returned in 14 seconds. Speed 506 records/sec.
  • Output target XLS (writes a binary XLS file that can be opened in Excel)
    7088 rows returned in 2 seconds. Speed 3544 records/sec.

 
The last option is a bit cheating. If the recordset gets larger this option will slow down more than linearly. Recent performance enhances of the litXLS software component made it a lot quicker!i
Back to top
 
« Last Edit: 30.10.09 at 16:57:53 by Gerrit-Jan Linker »  

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