RE: Query result times in WO and mysql
RE: Query result times in WO and mysql
- Subject: RE: Query result times in WO and mysql
- From: Lon Baker <email@hidden>
- Date: Tue, 20 Jan 2004 21:06:57 -0500
Kib,
I would add "profileSql=true" to the connection string to see what is
acually being executed by MySQL in the console while running.The
current version of Connector/J if 3.0.9 which has some major
optimizations mentioned in the release notes, as well as MySQL 4.0.17,
which is performing well for me. I would definitely recommend updating
to the latest versions of Connector/J and MySQL first and see how that
affects your performance.
The LIMIT clause was something I relied on also, coming from a PHP
background, but have opted out of using to maintain portability and
take advantage of EOF. As I was educated when I started using WO, is to
consider how much information can be displayed to a user at one time.
Do you need 5500 rows? Can the user use 5500 rows?
On my first major project I instinctually dove into using raw rows for
"browser listings" of records, however recently on new projects I moved
away from that tactic. I finally relaxed and started allowing EOF and
WO to do what they do so well, maintain a cache of data, share it
between sessions, and refresh it as needed. This really made a
difference in the "perceived" responsiveness of my projects as well as
reducing the amount of code.
That being said the lack of support for LIMIT clause under MySQL can
cause memory issues that only raw rows and/or more memory will solve.
In a fetch specification you can set the fetch limit to a value (ie.
99), when the fetch is called it will still pull all the objects
possible from MySQL filling up memory, and then return 99 of those
objects to the actual fetch request. This can be troublesome to say the
least.
I discussed this with other developers recently and was educated that
it is possible to override part of EOF to have it add the LIMIT clause.
But I have avoided doing that, and have focused on being very careful
with how I model relationships, build the interface and optimize
indexes in MySQL. This has allowed me to get reasonable performance so
far.
One last comment on using raw rows to much, and I am sure someone here
will correct me if I am wrong, raw rows are never cached. This means as
you try to scale your application you will see the bottleneck develop
quickly on the MySQL side. My initial overuse of raw rows saw MySQL peg
out the CPU and cause big delays as I added more users. In my case a
single page that needed to refresh every 90 seconds made a number of
queries for lists of raw rows.
My other early mistake was to model relationships for a lot of things I
did not need to, such as Countries, States, Status Indicators, etc., I
later found that it was more effective to use dictionaries/arrays of
static values such as these and story the value in the record instead
of a related record. This made the records easier to read in raw
format, less resource intensive to cache or display, and reduced the
load database fetches can have on both the application and the database
server.
The guys over at Openbase though unable to provide any real data, have
assured me that their solution has a number of optimizations, as well
as better WO integration that allows them to out perform MySQL in
significant ways. So you might consider them if you find this too big a
hurdle for your application.
Anyway sorry I was so long winded just wanted to share what I have
found.
My 2".
Lon Baker
_______________________________________________
webobjects-dev mailing list | email@hidden
Help/Unsubscribe/Archives: http://www.lists.apple.com/mailman/listinfo/webobjects-dev
Do not post admin requests to the list. They will be ignored.