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 <staffan.ty...@gmail.com>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
> 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
