Re: [Info-Ingres] Insert performance (was "Sun buys MySQL")
- From: withdefault@xxxxxxxxx
- Date: Mon, 11 Feb 2008 00:12:25 -0800 (PST)
Queries are presented at the DBMS server via different GCA messages
depending
on the mode of execution; entry into the sequencer differs for static
REPEAT
queries, dynamic SQL and cursors defined in dynamic SQL. Static
repeat
queries currently use an identifier from the client application to
store a
query plan and discards the query source. Using a computed checksum
of the
query text as the object name used to save the query text and plan
allows them to be retrieved from cache via syntax alone. Once located
an
additional comparison of query text with saved query text should
ensure the
identical query is being executed. If the query contains parameters
another
safeguard would be type checking of the input parameters with those in
the
execution plan as the plan is executed.
For developers using Java, PHP, Perl, Python and other drivers queries
are
typically executed via a cursor defined in dynamic SQL; in
applications where
queries are generated and not easily modified this feature would be
useful,
especially where the queries require greedy heuristics to optimize.
In order to find the likely benefit try writing the queries as
database procedures
and comparing the results with those executed directly from the
application.
Betty & Karl Schendel wrote:
At 10:01 PM +0000 2/10/08, On Web wrote:.
In Java I used to trap front-end queries en-route to the database (via jdbc)
and check them against queries that had already been performed...
This was only possible because only that particular application would be
manipulating the data in question, so there was no danger of data changing
behind the scenes. But it's not really that hard and I'm sure that a similar
mechanism could be embedded in the ingres query mechanism.
The standard truism amongst Ingres developers is that
*nothing* is as easy as you would expect it to be.
I don't pretend that query matching is wildly difficult, but
there will be some interesting little glitches. For instance,
the table list (including view decomposition) will be attached
to a qtree, but you'll be matching the qtext. You'll have to
watch out for parameterized queries, and a design decision to
make is whether to attempt to match parameterized queries
with non-parameterized. Most qsf objects are "unnamed" objects,
which won't work if one intends to save them for potential
caching. (and the name for qsf named objects is
supplied by the client, which won't be supplying the names
in this case, so a new algorithm will be needed that won't
interfere with traditional named objects). Etc, etc.
Anyway, it definitely can be done, and if someone is working
on this project, so much the better for Ingres.
Karl
- References:
- Re: [Info-Ingres] Insert performance (was "Sun buys MySQL")
- From: Karl Schendel
- Re: [Info-Ingres] Insert performance (was "Sun buys MySQL")
- From: On Web
- Re: [Info-Ingres] Insert performance (was "Sun buys MySQL")
- From: Betty & Karl Schendel
- Re: [Info-Ingres] Insert performance (was "Sun buys MySQL")
- Prev by Date: Re: [Info-Ingres] Insert performance (was "Sun buys MySQL")
- Next by Date: Re: [Info-Ingres] Insert performance (was "Sun buys MySQL")
- Previous by thread: Re: [Info-Ingres] Insert performance (was "Sun buys MySQL")
- Next by thread: Re: [Info-Ingres] Insert performance (was "Sun buys MySQL")
- Index(es):
Relevant Pages
|
