Performance Question

[Dan]

Hi,

I am looking for a ORM like tool to use in groovy/java to do some functional testing and came across ActiveJDBC. We are currently using Hibernate but we are not super happy with the load times. 

Running ActiveJDBC with only a few table models created work perfectly and just how we like it. However when I tried adding all of our tables to model objects to the project it takes a long time to do any query. 

According to the logs it registered every model and then starts to fetch the metadata, this results in excessive time to run. We have about 750 tables and I gave up after 10 minutes of runtime. I've read here(http://www.findbestopensource.com/product/activejdbc) that ActiveJDBC supports on demand loading by default but it doesn't seem to work like that for me. 

I've tried a fix where each table was it's 'own database' by using the DbName annotation but this runs into issues of opening it's own connection per table. 

My models currently look like:

import org.javalite.activejdbc.Model

import org.javalite.activejdbc.annotations.Table

@Table(value="Account")

class Account extends Model{

}

The unit test is in spock:

    @Unroll

    def testDbNew(){

        when:

        def time = System.currentTimeMillis()

        Base.open("oracle.jdbc.OracleDriver", "jdbc:oracle:thin:@XXXXX", "XXXXX", "XXXX")

        def end = System.currentTimeMillis()

        println (end - time)

        time = System.currentTimeMillis()

        def account = Account.findAll()

        end = System.currentTimeMillis()

        println (end - time)

        println account

        then:

        assert account!=null

        Base.close()

    }

Thanks,

Dan

[Dan]

The article I referenced mentions:

ActiveJDBC now has ability to load metadata on demand - when a database is first accessed. 

I guess that means the total database. Is it possible to just load a single table's metadata on demand?

[Igor Polevoy]

Dan, loading metadata for only one table is not practical, because ActiveJDBC also infers relationships across tables. 

Now, getting metadata out of a database might take time, and in our largest peoject we had a few hundred tables in Oracle, it took less that 30 seconds. 

Now, this is a one time operation and will not affect the actual speed of queries. Consider this initialization phase. 

DbName annotation is designed to go after completely different databases, say Oracle and MySQL in the same project

Does this help?

Thanks

[Dan]

Yes this does help. I am seeing loading times exceeding 10 minutes for 750 tables. Should I assume something is wrong in this case? Especially since with a few hundred tables it took you less than 30 seconds?

[Igor Polevoy]

Dan, this seems out of whack. On the other hand, 750 tables is a lot. I wonder if you can talk to DBA to see why this is happening. 

However, looking at code here: 

https://github.com/javalite/activejdbc/blob/master/activejdbc/src/main/java/org/javalite/activejdbc/Registry.java#L293

I can see that for each table, it calls 

connection.getMetaData()

and 

con.getMetaData().getDatabaseProductName()

which is not necessary. These objects can be requested one time, and simply passed into method fetchMetaParams(). 

I can make that optimization so you can test if this is making a difference. 

Also, what database are you using?

Let me know if you want to try this

Thanks

igor

[Igor Polevoy]

I went ahead and optimized this code. Please pull a snapshot and see if it helps:

https://oss.sonatype.org/content/repositories/snapshots/org/javalite/activejdbc/1.4.10-SNAPSHOT/

tx

[Dan]

Hi - thanks for taking the time to optimize the code. I added the snap shot and it's still performing the same way. I am actually seeing another performance issue I saw from before.

I created ~170 tables' models and it takes 2-3 seconds to load each table's metadata. I saw this happening on 1.4.8 activejdbc and 1.4.1 instrumentation(They both are now on 1.4.10-SNAPSHOT).

Apr 11, 2014 2:39:12 PM org.javalite.activejdbc.LogFilter log

INFO: Fetched metadata for table: T_X

Apr 11, 2014 2:39:15 PM org.javalite.activejdbc.LogFilter log

INFO: Fetched metadata for table: T_Y

Apr 11, 2014 2:39:17 PM org.javalite.activejdbc.LogFilter log

INFO: Fetched metadata for table: T_A

Apr 11, 2014 2:39:19 PM org.javalite.activejdbc.LogFilter log

INFO: Fetched metadata for table: T_B

Before I have seen it load 170 objects in under 6 seconds but now it's taking 2-3 seconds per table. 

[Dan]

Not sure if I mentioned it before but I am using oracle.

[Dan]

Hi,

I ran it against a local copy of the database and it flies. It loads ~170 in about 13 seconds. 

When the AJ runs does it query each table for it's metadata? Does this result in a round trip/connection per table?

Thanks,

Dan

[Igor Polevoy]

Dan, if it flies locally, than you might be having issues with network latency. 

What abut other queries to the database, something that should be instant. Is this taking 3 seconds, or is instant?

As far as your question, not sure if it executes different statements for each metadata table. I'd assume so, but ultimately it is up to Oracle driver implementation.

Can you run your code in proximity to the database that is slow, so you can tell if this is a network?

THanks

[Dan]

Subsequent queries are taking about 120ms which is great. Sadly I am unable to run the code in proximity to the database. 

[Igor Polevoy]

hm, maybe you can talk to DBA to see how this can be optimized? # seconds for a query seems a long time 

[Igor Polevoy]

Dan, your next message was blocked as spam. I approved it, but apparently it is lost by this forum. Please re-post but ensure you are logged in. 

tx

On Monday, April 14, 2014 8:51:01 AM UTC-5, Dan wrote: