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. The results talk for
> themselves:
>
Steffan,
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. ;-)
>
> 2 *-* db1=.oosqliteconnection~new("h1.db")
> >>> "an ooSQLiteConnection"
> 3 *-* db1~recordformat = .oosqlite~OO_ARRAY_OF_ARRAYS
> 4 *-* x = time("E")
> >>> "0"
> 5 *-* rows1 = db1~exec("SELECT * FROM TBL1", .TRUE)
> >>> "an Array"
> 6 *-* say time("E")
> >>> "2.855000"
> 2.855000
> 8 *-* db2=.oosqliteconnection~new("h1.db")
> >>> "an ooSQLiteConnection"
> 9 *-* db2~recordformat = .oosqlite~OO_ARRAY_OF_DIRECTORIES
> 10 *-* rows2 = db2~exec("SELECT * FROM TBL1", .TRUE)
> >>> "an Array"
> 11 *-* say time("E")
> >>> "1466.093000"
> 1466.093000
> 13 *-* db3=.oosqliteconnection~new("h1.db")
> >>> "an ooSQLiteConnection"
> 14 *-* db3~recordformat = .oosqlite~OO_STEM_OF_STEMS
> 15 *-* rows3 = db3~exec("SELECT * FROM TBL1", .TRUE)
> >>> "records"
> 16 *-* say time("E")
> >>> "2932.653000"
> 2932.653000
>
> As I've recently converted from Rexx/SQL I decided to run the same test
> using that product, with the result shown below. The output from Rexx/SQL
> corresponds to record format OO_STEM_OF_STEMS in ooSQLite.
>
> 2 *-* Call RXFuncAdd "SQLLoadFuncs", "rexxsql", "SQLLoadFuncs"
> >>> "0"
> 3 *-* Call SQLLoadFuncs
> >>> "0"
> 4 *-* db = "H1DB"
> >>> "H1DB"
> 5 *-* say SQLCONNECT(db,"","","h1.db","")
> >>> "0"
> 0
> 6 *-* x = time("E")
> >>> "0"
> 7 *-* say SQLCOMMAND(s1,"SELECT * FROM TBL1")
> >>> "0"
> 0
> 8 *-* say time("E")
> >>> "14.258000"
> 14.258000
> 9 *-* say s1.col1.0
> >>> "20158"
> 20158
>
> It certainly looks like something is seriously wrong in the handling of
> two of the record formats in ooSQLite. Grateful for any comments.
Well, my response above was a little bit tongue in cheek. Here are some
other comments.
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.
Your tests probably indicate that an array of arrays will always be the
fastest way to use exec().
2.) I personally would probably never use exec() for large databases. I
would use a prepared statement and step through it.
There is an example of how to do this under the testing directory, the
execTestNoExec.rex example. I would be interested in knowing what time you
get if you enhance your test program to also include that method of running
the select statement.
3.) There is an obvious optimization I see for the directory and stem
record sets. I'll implement that and you can see how much of an
improvement it makes. It may take me awhile to put a new build with the
changes up on SourceForge. It might not be until this weekend.
4.) I implemented ooSQLite because I wanted a simple way to use a database
with some example ooDialog programs. But, I have little to no previous
experience with databases and SQL.
This is the main reason I made ooSQLite available as a downloadable
preview. I was hoping that some users of ooRexx that do have experience
with databases and SQL would be willing to contribute a little bit of their
time to use and test ooSQLite. To help me find these types of issues and
to refine the implementation.
So, thanks for contributing to this effort! ;-)
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.
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.
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.
--
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
