Mark, this looks very strange indeed. My system is a Vaio Intel i5-2520M
2.5GHz with 6GB RAM and an SSD drive so it's fast! And I still get these
high values, I can't understand it. How many columns are there in your test
database? It's the large number of columns that causes the performance
problem I think, my test db has 149 columns and around 20,000 rows.
Staffan
On Tue, Oct 16, 2012 at 6:19 PM, Mark Miesfeld <miesf...@gmail.com> wrote:
> 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
>
>
------------------------------------------------------------------------------
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
