On Mon, Oct 15, 2012 at 4:30 AM, Staffan Tylen <staffan.ty...@gmail.com>wrote:
> During some testing I thought for a moment that my application was looping
> but I soon realised that it was a simple SQL SELECT statement that consumed
> all the time. So I decided to run a few tests. Below is the trace output
> from these tests. Input is an SQL database with 149 columns and 20,158
> rows, which means 3,003,542 fields to handle.
Staffan,
A couple of things here.
1.) I put a new build of ooSQLite on SourceForge. It has a few
optimizations for exec(). From testing I did, it's roughly about 10%
improvement in execution time and about 30% improvement in memory usage.
2.) With my initial testing on the previous version, I did not see any
times that looked like what you reported. Using the database built from
the code you posted.
Here is some typical times that I see now:
C:\work.ooRexx\ooSQLite.1.0.0.8514\performance.testing>testExecTimes.rex
ooSQLite Version 1.0.0.8514 (32 bit)
Array of Arrays - Records: 20000
Elapsed time: 7.610000
Array of Arrays - Records: 20000
Elapsed time: 7.578000
Array of Arrays - Records: 20000
Elapsed time: 7.375000
Array of Arrays - Records: 20000
Elapsed time: 7.328000
Array of Directories - Records: 20000
Elapsed time: 18.031000
Array of Directories - Records: 20000
Elapsed time: 19.375000
Array of Directories - Records: 20000
Elapsed time: 18.641000
Array of Directories - Records: 20000
Elapsed time: 18.484000
Stem of Stems - Records: 20000
Elapsed time: 19.000000
Stem of Stems - Records: 20000
Elapsed time: 19.563000
Stem of Stems - Records: 20000
Elapsed time: 19.671000
Stem of Stems - Records: 20000
Elapsed time: 19.594000
3.) However, the above is on a 32-bit system of mine that has 4 GB of
memory. On a 64-bit laptop I have with 1 GB of memory, I ran across this:
C:\work.ooRexx\ooSQLite.1.0.0.8514\performance.testing>testExecTimes.rex
ooSQLite Version 1.0.0.8514 (64 bit)
Array of Arrays - Records: 20000
Elapsed time: 9.625000
Array of Arrays - Records: 20000
Elapsed time: 7.223000
Array of Arrays - Records: 20000
Elapsed time: 6.879000
Array of Arrays - Records: 20000
Elapsed time: 6.755000
Array of Directories - Records: 20000
Elapsed time: 15.101000
Array of Directories - Records: 20000
Elapsed time: 17.955000
Array of Directories - Records: 20000
Elapsed time: 330.159000
This only happens when I run a single program, that does 12 consecutive
exec() invocations, 4 for each of the 3 record formats. It only happens
when the available memory on the system is about 96% consumed.
If I run individual programs for array of directories and stem of stems,
there is no problem, and the times seems very reasonable to me:
C:\work.ooRexx\ooSQLite.1.0.0.8514\performance.testing>directoryTestLarge.rex
Records: 20000
Elapsed time: 13.598000
C:\work.ooRexx\ooSQLite.1.0.0.8514\performance.testing>directoryTestLarge.rex
Records: 20000
Elapsed time: 13.737000
C:\work.ooRexx\ooSQLite.1.0.0.8514\performance.testing>directoryTestLarge.rex
Records: 20000
Elapsed time: 13.664000
C:\work.ooRexx\ooSQLite.1.0.0.8514\performance.testing>directoryTestLarge.rex
Records: 20000
Elapsed time: 13.663000
C:\work.ooRexx\ooSQLite.1.0.0.8514\performance.testing>stemTestLarge.rex
Records: 20000
Elapsed time: 14.710000
C:\work.ooRexx\ooSQLite.1.0.0.8514\performance.testing>stemTestLarge.rex
Records: 20000
Elapsed time: 14.593000
C:\work.ooRexx\ooSQLite.1.0.0.8514\performance.testing>stemTestLarge.rex
Records: 20000
Elapsed time: 14.578000
C:\work.ooRexx\ooSQLite.1.0.0.8514\performance.testing>stemTestLarge.rex
Records: 20000
Elapsed time: 14.737000
C:\work.ooRexx\ooSQLite.1.0.0.8514\performance.testing>
4.) It's hard to debug, because the system memory is close to exhausted.
But, it appears to me that things get stuck in the garbage collector
trying to find memory to allocate a new object.
I'll talk with Rick about this, but there is probably not much that can be
done about this. On your end, you could use a machine with more memory,
break your processing up into smaller chunks, or switch to using an array
of arrays.
On my system with 4 GB of memory, I've run testExecTimes.rex dozens of
times with no problem. But, if the database was increased in size by a
sufficient amount, I'm sure the same problem would eventually happen.
--
Mark Miesfeld
------------------------------------------------------------------------------
Don't let slow site performance ruin your business. Deploy New Relic APM
Deploy New Relic app performance management and know exactly
what is happening inside your Ruby, Python, PHP, Java, and .NET app
Try New Relic at no cost today and get our sweet Data Nerd shirt too!
http://p.sf.net/sfu/newrelic-dev2dev
_______________________________________________
Oorexx-users mailing list
Oorexx-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/oorexx-users
