[Opensource] SEC: UNCLASSIFIED - RE: Urgent Help! Horrendous Expresso DBObject per formance problem.

[larry hamel]

ok, I'm glad you're one step ahead of me, tracking actual SQL.

like Mike said, consider an explicit, custom cache for your values.  design well with concurrency in mind--you're in a servlet env.

also, consider stepping back with an architect's eye toward redesign.  is there something you can do with a wholesale change to minimize the query load?  intermediate tables?  a "push" system to the middleware (to invalidate items in that custom cache)?  a stored procedure in the DB to return just changed items? a modify date in tables to determine when items change?

deadlines motivate, but it is part of the normal cycle to discover bottlenecks after an initial design gets implemented.  remind your bosses of this, and see if you can 'release' the slow version while you are revving the next.

good luck,

larry

At 10:54 PM 4/2/2003, you wrote:
>Yeah (the subject line) we're in the middle of a project with a deadline
>looming, and we can't release with the performance as it is.
>
>We actually logged the calls from the database side, and aside from some
>miscellaneous stuff (I'm assuming expresso setup), there is only one actual
>call to the database, so its actually using the cache for the other 9999,
>but its still taking 2ms (oops I said 4ms before, I meant 2ms).
>
>
>I'll try setting up logging, though, and reply
>
>Rob
>
>                -----Original Message-----
>                From:   larry hamel [mailto:expresso at codeguild.com]
>                Sent:   Thursday, 3 April 2003 14:46
>                To:     opensource at jcorporate.com
>                Subject:        Re: [Opensource] SEC: UNCLASSIFIED - Urgent
>Help! Horrendous Expresso DBObject per formance problem.
>
>                hi Rob,
>
>                quite a subject line :-)
>
>                it would be instructive to figure out is what DB calls are
>being made.
>
>                can you turn on logging for SQL calls? 
>
>                do this by going to expressoLogging.xml and find these lines
>
>                        <category name="expresso.core.db.SQL">
>                                <!-- turn value="debug" will trace each and
>every SQL statement
>                            issued to the database -->
>                        </category>
>
>
>                and add this
>                                <priority value="debug"/>
>
>                where the comment is.  then we'll know what queries are
>happening.
>
>                BTW, the expresso forums are faster for replies than this
>mailing list.
>
>                larry
>
>                At 04:11 PM 4/2/2003, you wrote:
>
>                >We're using Expresso DBObjects as our data access for our
>web based
>                >application.  Because of the kind of application, in one
>web request, we are
>                >making 4000 requests to expresso for data.  We need this to
>take under 1s
>                >(approx under 250ns each).  These requests are all simple
>'get this field
>                >from the record with this key' type requests, and there are
>only 140 unique
>                >accesses, so theoretically, caching should eliminate any
>performance
>                >problems.  However, we're getting horrendous performance.
>                >
>                >We setup a test class that hit expresso for a single field
>(of the same
>                >record), based on the primary key 10000 times, and got an
>average response
>                >of 4ms (startup costs were removed from the timings, and
>expresso was
>                >'warmed up' with 2000 hits'.   This results in a total time
>of 16s for our
>                >response, which is well above our 1s expectation.  We have
>caching enabled
>                >('y' in the config file) and we have an entry in the
>DBObjLimit table that
>                >gives ample room for entries, but this doesn't seem to
>improve the
>                >performance enough (we got an initial 1/3 performance
>improvement through
>                >this).
>                > 
>                >Is this an expected time (4ms for a cached retrieval), and
>has anyone else
>                >had any experiences in this area/know of a solution?
>                >
>                >Rob 
>                >
>                >PS the database is in the other room, so network should be
>almost ideal.
>                >_______________________________________________
>                >Opensource mailing list
>                >Opensource at jcorporate.com
>                >http://mail.jcorporate.com/mailman/listinfo/opensource
>                >Archives: http://mail.jcorporate.com/pipermail/opensource/
>
>                _______________________________________________
>                Opensource mailing list
>                Opensource at jcorporate.com
>                http://mail.jcorporate.com/mailman/listinfo/opensource
>                Archives: http://mail.jcorporate.com/pipermail/opensource/
>_______________________________________________
>Opensource mailing list
>Opensource at jcorporate.com
>http://mail.jcorporate.com/mailman/listinfo/opensource
>Archives: http://mail.jcorporate.com/pipermail/opensource/