[mythtv] sql code

[Matt Zimmerman]

On Thu, Feb 20, 2003 at 01:22:42AM -0500, Andy Davidoff wrote:

> Some of the SQL communication is sub-optimal, but "acceptable", like the
> insertions during mythfilldatabase -- it'd be faster to group them
> together and make a few insertions of several hundred rows, but it could
> be argued that this could impose CPU load spikes which could impact a
> front-end.

I run mythfilldatabase on a diskless (NFS root) box, talking to a remote
MySQL server, which is probably fairly close to a worst-case scenario, and
it takes a negligible amount of time to do these inserts.  Additionally, 99%
of the time is spent waiting for the grabber anyway.  Is there really a
problem to be fixed here?

> Then there's the queries for settings in the database.  Every such query
> is actually three queries -- one to check for a key/value for the current
> host, one to check for a default value (if the first has failed), and then
> a query that does nothing at all ("SELECT NULL").

The per-host settings stuff went in only in the past few days; I didn't
realize that it added an extra query.  Feel free to provide a patch for
that.

The SELECT NULL query is a workaround for the fact that the mysql client
library does not recover gracefully if a TCP connection is lost (for
example, a timeout, or a mysqld restart).  This query is a canary which will
fail, but allow the client library to clean itself up so that the next one
will succeed.

This sucks, but it works for now.

> Alpha code, I know, but in some cases these groups of queries are executed
> for the same settings values several times per second; in the case of
> GuiHeight/GuiWidth, 15-20 times during MythMusic initialization.  This
> results in sluggish load times -- several seconds on my Athlon 2k.  It
> should be trivial to cache settings and/or load them all at once in a
> single query during initialization.  By my estimation, the
> mythfilldatabase code queries for the value of NULL (!) and to check for a
> possible TimeOffset several thousand times per session.  These should be
> static values in mid-session.

Caching these settings would be easy; feel free to provide a patch.
However, I doubt this is truly the source of your slowdown, on my system
(which I described above), mythfrontend starts in 2-3 seconds.

-- 
 - mdz