Alex,
> > Furthermore, despite the everyone's instinct, it's a good deal faster in
> > the general case to read a table in an optimal order and sort the data
> > in memory that to read the data in index order - random order relative
> > to storage.
> >
>
> Ann, from server POV you are definitely right. But when talking about
> ways to get first record from server as fast as possible we care about
> not server-only performance, but about performance of overall
> client-server system.
When the server is bogged down running queries inefficiently, the overall
system performance does suffer. However, if all you want is the first few
records, reading hundreds of thousands and sorting them is wasteful - and
hard on the client. That's why Firebird's optimizer handles a restriction
on
the number of rows to be returned as an optimizer hint to navigate through
the index, rather than doing a natural read and sorting the results.
> We have very simple table:
>
> create table MOVIES
> (NAME varchar(255) primary key,
> COMMENTS varchar(8192),
> ISO_IMAGE blob);
>
(A good example of the problems with natural primary keys.)
And want to run such a query:
>
> select * from MOVIES where COMMENTS like '%yacht%' order by NAME;
>
> Two plans are possible:
> PLAN SORT (MOVIES NATURAL)
> PLAN (MOVIES ORDER RDB$PRIMARY1)
>
Right. And if you want to give the optimizer a hint that it should choose
the second plan, change the query like this:
select first 1000000 * from MOVIES where COMMENTS like '%yacht% order by
NAME;
If you think your client may want more than a million rows, increase that
number.
As far as I know, Firebird's optimizer doesn't do anything clever like
trying to guess
what part of the table the "first <n>" represents - ask it for the first 1
or the first
ten million, it will still choose the navigational path. So ask it for ten
times as many
records as you expect to find. Probably a bad idea to exceed the size of
an int64.
> Cause we need to scan all the table first plan appears to be better - it
> requires less disk operations even taking into an account sort after
> scanning all the table. But overall throughput depends upon what are we
> going to do with ISO_IMAGE at client side. Imagine we want to burn DVDs
> with all images (and have the set of DVDs sorted after burning).
Overall throughput also depends on what else is going on with the system -
if you've got lots of read queries that are hitting the disk hard, making
your
burn program wait a few seconds for its first results could improve the
performance of the system overall. But that's moot. You have the ability
to ask for navigational access through the index, if that's what you think
you need.
>
> Certainly this does not mean that for all queries, containing blobs,
> natural scan should not be used :-)
>
And again, for the naive, the blobs aren't accessed until the records are
read, qualified by the comment containing 'yacht' - in this case, the non-
standard CONTAINING would be better because it is not case sensitive.
> Therefore I agree with Dmitry - such a hint to
> optimizer is required part of SELECT statement if we want to have
> optimal performance for all the system, not for server only.
And I guess I agree also, but think that the hint is already part of
Firebird - as either FIRST or LIMIT or ROWS or whatever other silly
syntax exists for restricting the number of records returned. Remember,
the hint is needed only when the query is sorted and there is an index
with the same keys in the same order and direction as the sort and
that index can be used.
> (Telling
> true I do not understand why _THIS_ kind of hint is not part of SQL
> standard - may be people who deal with standard look at the world from
> server-only POV?)
No, the standards organization attempts to restrict SQL to the logical
description of database structure and manipulation. That's why CREATE
INDEX is not a standard statement.
Cheers,
Ann
>
>
------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
