>
>
> 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
