Re: [Oorexx-users] ooSQLite performance observations

[Mark M. <mie...@gm...>]

Staffan,

Thanks for the code to produce a big database.  That will be handy for me.

Regarding using a table for record sets, someone else has brought that up.
 I'm thinking about adding it to the different record format types.
 Exactly how does that work?  Each individual record is a single table
object with the column names the indexes in the table?  Each record is put
into a ?  Put into a table with the index the record number?

--
Mark Miesfeld

On Mon, Oct 15, 2012 at 8:51 AM, Staffan Tylen <sta...@gm...>wrote:

>
>> So, looking at the results, it seems to say that using the same database
>> with the same select statement takes 14 + seconds to execute under Rexx/SQL
>> and 2+ seconds to execute under ooSQLite using an array of arrays as the
>> result set format.
>>
>> In my mind, that says, if you are concerned about performance then use
>> ooSQLite with an array of arrays as the record format.  ;-)
>>
>>
> :) I couldn't agree more. But unfortunately that doesn't take away the
> fact that ooSQLite takes 209 times longer than RexxSQL to produce the same
> result. ;)
>
>
>>
>> 1.)  My first implementation of exec() returned the result set as an
>> array of arrays.  That is what makes the most sense to me.  I added the
>> ability to specify the other record format types as the result of some of
>> the initial discussions about ooSQLite on the developers forum.
>>
>
> I think the idea is very good, but without knowing the internals of ooRexx
> I would have thought that building a table instead of a directory would be
> quicker, because a directory requires methods in addition to the index,
> n'est pas?
>
>
>> 2.)  I personally would probably never use exec() for large databases.  I
>> would use a prepared statement and step through it.
>>
>
> Well, my thinking is if I step through a large database like this, most of
> the processing will be done by the Rexx program with the obvious overhead
> but if I use exec the result will be built by compiled code, which ought to
> be miles quicker.
>
>
>>
>> In particular, I have no large database to work with and don't know of
>> any simple way to create one to test with.
>>
>> It would be very useful if someone could send me a large database to do
>> testing with.
>>
>>
> Try this:
>
> -- Create a large SQL database
>
> totalColumns = 150
> totalRows = 20000
>
> cols = ""
> do col = 1 to totalColumns
>    cols ||= "COL" || col || ","
> end
> cols = strip(cols,"T",",")
> db = .ooSQLiteConnection~new("large.db")
> db~exec("CREATE TABLE TBL1 (" || cols || ");")
> db~exec("BEGIN;", .true)
> do row = 1 to totalRows
>    values = ""
>    do col = 1 to totalColumns
>       values ||= "'ROW_" || row || "_COL_" || col || "',"
>    end
>    values = strip(values,"T",",")
>    db~exec("INSERT INTO TBL1 VALUES(" || values || ");", .true)
> end
> db~exec("COMMIT;",.true)
>
> ::requires "oosqlite.cls"
>
> This sample creates a 53MB database in a few seconds.
>
>
>> I understand that this could be a problem if the data was private.
>>  Still, if someone could help out with this, I'd appreciate it.
>>
>>
> Please modify the example above as the data is private ;)
>
>
>
>> 5.)  I'm actually excited to see that exec() with an array of arrays
>> seems to perform better than Rexx/SQL.  Although my opening statement was
>> tongue in cheek, I think it is valid.  If you are going to use exec() and
>> are worried about performance, then use an array of arrays as the record
>> set format.
>>
>>
> The reason for choosing array of directories was that when working with
> RexxSQL I was using classes such as sqldatabase and sqltable where I used
> tables to handle column data between those classes and the calling code. It
> worked very will so I thought that as directories are very similar to
> tables I could use that instead but I may have to rethink here.
>
> Staffan
>
>
>
>
> ------------------------------------------------------------------------------
> 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
> Oor...@li...
> https://lists.sourceforge.net/lists/listinfo/oorexx-users
>
>