--- In firebird-support@yahoogroups.com, "phil_hhn" <time_lord@h...>
wrote:

>
> [Firebird 1.5.1, JDBC 1.5.4]
>
> I've found a strange performance problem which appears to be firebird
> related (not in the jdbc driver). Simplifying our system, we have:
>
> 1) table 'Resources', key is resPk, and contains a char(80) column
> called 'title'.
> 2) table 'Wordstore', key is wsPk, and contains a char(80) column
> called 'text'.
> 3) a cross-reference many-many table 'xref', linking the two, with
> pk/fk's to the pks of the first 2 tables.
>
> There are 18,000 rows in the Wordstore (each contains one or more
> words in the text column). Listing all words is relatively quick (as
> fast as I'd expect for 18,000 records).
>
> We want to list all Resources where there is an associated word in the
> Wordstore. For example, list all resource titles where there is an
> associated word in the wordstore starting with 'magic':
>
> select r.title from resources r join xref x on x.resPk = r.resPk join
> Wordstore w on w.wsPk = x.wsPk where w.text like 'magic%'
>
> So far so good... I run the query with IBexpert against a database on
> a windows XP box, no problem; Run it within a java program, no
> problem... both return 48 rows which is the correct result.
> However this is where it gets strange.
> A) I put the same database on a MAC (a G3 I think, sorry not my forte
> - it's a colleagues secondary system) - and the query takes 9 seconds!
> Looking closer at IBexpert, the query tool switches to the results
> window as soon as you execute it (and immediately shows the first
> 20-something), but if you drag the scrollbar to the bottom (to see the
> 48th row), it takes 9 seconds.
> B) When running the query in Java, the query returns immediately, but
> the first time you call ResultSet.next(), it takes 9 seconds!
> Subsequent calls to next() have no such delay.
>
> Now I'd heard that MACs can be slow with database app's, but this is
> crazy ;-) The G3 'possibly' has a 5200rpm 2.5" drive, but it couldn't
> degrade things that much, surely. The MACs are a significant part of
> our market so this is important to us.
>
> So I added an index to the Wordstore text column, thinking that maybe
> we're being hit badly by "where w.text like 'magic%'". But this made
> no noticeable improvement. But I didn't expect it to - we have written
> MUCH more complex queries with lots of joins, and involving the
> Wordstore, which do not have bad performance... so I cannot figure out
> why the MAC has problems with such a simple one.
> And here's a different angle on things: I generated some hibernate
> mappings with middlegen, ran the query with hibernate (against the MAC
> still) and there were no noticeable delays. So the MAC /is/ capable of
> good performance....
>
> So can anyone tell me if they've encountered similar issues with the
> MAC, or point out more efficient or 'correct' ways to write that SQL?
> While the hibernate solution is encouraging, we are not going down
> that path quite yet...
>
> Thanks

A slight correction, found when running the hibernate query that I was
pointing at the wrong database... corrected that, pointed to the MAC
and found the query now takes 4.5 seconds. Good, it's in the same
ballpark as the other results, but suprisingly only half the time of
the IBexpert or Java test. :)
But it's still unacceptably slow...