[mythtv] sql code

[Andy Davidoff]

Well, I'm trying to figure out if there's method to this madness. :-)

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.

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").

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.

Okay, that stuff is nit-picky.  The schema stuff is more of an issue
long-term.  I'd like to improve things, but I need to know how we
can normalize the data and what logical relations exist in the data-
base.  There are many uses of variable-length records which we /may/
be able to eliminate.  This would equate to faster searches and
updates.  Some such records, like the playlist in MythMusic, need to
be replaced by fixed-length relation maps which can grow to arbitrary
length while offering more structure and using less disk space.

I don't even have a TV tuner yet, so my database is a poor source for
testing.  I'd /greatly/ appreciate it if someone with many records,
ie. lots of music/recorded video/program data, could  send me a dump
(with data and schema) so that I could test my patches against it.

There.  Is that about what you expected? ;-)


#if Tarek Loubani /* Feb 20, 00:44 */
> Why?? Any conversation you guys could have would be wasted in private.. Instead, let's see it on the list so that those who are interested in SQL work later may be able to use this as an archived reference..
> 
> Unless it's about security holes.. then maybe : )
#endif /* tarek at tarek.2y.net */