Help a DB2 guy write good SQL for Informix
A DB2 developer asked how to write well-tuned SQL against Informix: query plan tools, system catalog, optimizer hints and join-order rules, and monitoring. Replies answered everything: use SET EXPLAIN ON for plans; systables/syscolumns/sysindexes/sysfragments/sysdistrib (system catalog rows have tabid < 100); run UPDATE STATISTICS (MEDIUM/HIGH for distributions); onstat/onperf/xtree for monitoring. For plan control, 7.3 optimizer directives ({+INDEX}, {+ORDERED}, USE_NL/USE_HASH) or, pre-7.3, OPTCOMPIND plus tricks like "f1 + 0 = 7" to suppress index use. Poster was satisfied.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all I'm getting charged with writing some reports which will run against a new informix server at our company. I've been reporting against DB2 tables for 5 years or so and have a good grasp of how DB2's RDBMS decides to retrieve data. So on DB2, I write some pretty good, optimized queries. I no a poorly written query can run several times longer than one written by someone who understands the guts of the DBMS. So I want to be sure that I'm doing the right things to get quick results back from informix. Specifically I'm hoping you all might be able to fill me in on a few things I do with DB2 that I'd like to know about on informix. 1. Is there something akin to DB2's EXPLAIN facility? This is a utility that comes with DB2 that you can use to see the order in which tables will be accessed to satisfy your query, it list the type of join being done and shows any index pages that are being used (among other things). I'm looking for something that can tell me how much work I'm asking Informix to do. 2. I know nothing of the Informix system catalog yet. Do you know of any place online that I can get list of the system tables / columns? I'll be looking for ways to tell the the table statistics are current, what the keys are to tables and what indexes are available and so on. 3. A long time ago, DB2 used to have some little tricks you could do to fool the RDBMS into starting with a particular table (you could include a 'where' test over 10 times on a field in the table you wanted it to hit first and other odd things you might know of). In recent releases, all the little tricks I knew of are gone. Are there any tricks like this that I might want to use in Informix? 4. When writing a multi table query for DB2, it doesn't really matter which table you list first in your SQL; The RDBMS will use whichever one it thinks is best as the first place to start. In Oracle on the other hand, if the optimizer isn't really sure which table it should start with first (if it thinks there is no performance benefit to using one over the other), it will default to using the one your coded first in your FROM statement. What are the rules for Informix on this subject? 5. I can use DB2AM or Platinum or Mainview to watch a query run when I'm hitting DB2. Is there a performance monitor of some sort that I could use with our Informix server (it's on an NT server machine). Lastly, I don't have much faith in our DBA's as they are contractors from outside our company and this is their first attempt at using Informix... Can you think of anything I should keep an eye out for that might hurt our performance from a DBA's point of view? Oh yes and really lastly, can you think of any good books I might look into on the query performance front? I don't really need all the admin / installation stuff, but that's fine if a good book you know of covers it as well. Thanks for any input, sorry I've dumped so many questions on you at once - I just hate poorly written code and don't want to do a shoddy job. -Pete
Postman Pete wrote:
> ...skipped
> Specifically I'm hoping you all might be able to fill me in on a few
> things I do with DB2 that I'd like to know about on informix.
>
> 1. Is there something akin to DB2's EXPLAIN facility? This is a
> utility that comes with DB2 that you can use to see the order in which
> tables will be accessed to satisfy your query, it list the type of
> join being done and shows any index pages that are being used (among
> other things). I'm looking for something that can tell me how much
> work I'm asking Informix to do.
use "SET EXPLAIN ON" SQL command before.
>
>
> 2. I know nothing of the Informix system catalog yet. Do you know of
> any place online that I can get list of the system tables / columns?
> I'll be looking for ways to tell the the table statistics are current,
> what the keys are to tables and what indexes are available and so on.
SELECT * FROM systables;
SELECT * FROM syscolumns;
>
>
> 3. A long time ago, DB2 used to have some little tricks you could do
> to fool the RDBMS into starting with a particular table (you could
> include a 'where' test over 10 times on a field in the table you
> wanted it to hit first and other odd things you might know of). In
> recent releases, all the little tricks I knew of are gone. Are there
> any tricks like this that I might want to use in Informix?
>
> 4. When writing a multi table query for DB2, it doesn't really matter
> which table you list first in your SQL; The RDBMS will use whichever
> one it thinks is best as the first place to start. In Oracle on the
> other hand, if the optimizer isn't really sure which table it should
> start with first (if it thinks there is no performance benefit to
> using one over the other), it will default to using the one your coded
> first in your FROM statement. What are the rules for Informix on this
> subject?
Informix optimizer does not matter wich is the order the tables are in the
FROM clause. It takes the rules from their internal query plan. In IDS
7.30 there are an extension of SQL syntaxis to force the optimizer to act
as we want.
>
>
> 5. I can use DB2AM or Platinum or Mainview to watch a query run when
> I'm hitting DB2. Is there a performance monitor of some sort that I
> could use with our Informix server (it's on an NT server machine).
in Unix: try xtree and onperf
>
>
> Lastly, I don't have much faith in our DBA's as they are contractors
> from outside our company and this is their first attempt at using
> Informix... Can you think of anything I should keep an eye out for
> that might hurt our performance from a DBA's point of view
all database engiles (like IDS) are a complex programs. You should monitor
the engine via onstat command and change the appropiate
onconfig.%INFORMIXSERVER% file as appropiate.
>
>
> Oh yes and really lastly, can you think of any good books I might look
> into on the query performance front? I don't really need all the
> admin / installation stuff, but that's fine if a good book you know of
> covers it as well.
Look also at Informix documentation like performance book and
Administrators guide. Informix has a good documentation. :-)
>
>
> Thanks for any input, sorry I've dumped so many questions on you at
> once - I just hate poorly written code and don't want to do a shoddy
> job.
>
> -Pete
Vicente Salvador
DEISTER, S.A. (Spain)
On Sun, 17 Jan 1999 14:09:27 +0100, "V.Salvador"
<nospam_vsalvador@deistersoft.com> wrote:
>> skipped...
>>
>> 2. I know nothing of the Informix system catalog yet. Do you know of
>> any place online that I can get list of the system tables / columns?
>> I'll be looking for ways to tell the the table statistics are current,
>> what the keys are to tables and what indexes are available and so on.
>
>SELECT * FROM systables;
>SELECT * FROM syscolumns;>
Is there a creator name to retrieve just the system catalog tables
(like in DB2 I'd say SELECT * FROM SYSIBM.SYSTABLES WHERE
CREATOR='SYSIBM')?
In article <36a33466.434847347@news.swbell.net>, Postman Pete <PostmanPe
te@mail.notreally.com> writes
>On Sun, 17 Jan 1999 14:09:27 +0100, "V.Salvador"
><nospam_vsalvador@deistersoft.com> wrote:
>
>>> skipped...
>>>
>>> 2. I know nothing of the Informix system catalog yet. Do you know of
>>> any place online that I can get list of the system tables / columns?
>>> I'll be looking for ways to tell the the table statistics are current,
>>> what the keys are to tables and what indexes are available and so on.
>>
>>SELECT * FROM systables;
>>SELECT * FROM syscolumns;>>
>
>Is there a creator name to retrieve just the system catalog tables
>(like in DB2 I'd say SELECT * FROM SYSIBM.SYSTABLES WHERE
>CREATOR='SYSIBM')?
>
where tabid < 100
the first 100 are reserved for system catalogues
tabid = the unique table identifier...
>
--
David Williams
Thanks for the tabid stuff David. This will get me started!
On Sun, 17 Jan 1999 22:28:40 +0000, David Williams
<djw@smooth1.demon.co.uk> wrote:
>In article <36a33466.434847347@news.swbell.net>, Postman Pete <PostmanPe
>te@mail.notreally.com> writes
>>On Sun, 17 Jan 1999 14:09:27 +0100, "V.Salvador"
>><nospam_vsalvador@deistersoft.com> wrote:
>>
>>>> skipped...
>>>>
>>>> 2. I know nothing of the Informix system catalog yet. Do you know of
>>>> any place online that I can get list of the system tables / columns?
>>>> I'll be looking for ways to tell the the table statistics are current,
>>>> what the keys are to tables and what indexes are available and so on.
>>>
>>>SELECT * FROM systables;
>>>SELECT * FROM syscolumns;>>>
>>
>>Is there a creator name to retrieve just the system catalog tables
>>(like in DB2 I'd say SELECT * FROM SYSIBM.SYSTABLES WHERE
>>CREATOR='SYSIBM')?
>>
>
> where tabid < 100
>
> the first 100 are reserved for system catalogues
>
> tabid = the unique table identifier...
>
>>
Hi,
I don't want to repeat, what V.Salvador wrote. I fully agree.
I'll just fill in a few additional comments:
Postman Pete wrote:
>
> Hi all
>
> I'm getting charged with writing some reports which will run against a
> new informix server at our company.
>
> I've been reporting against DB2 tables for 5 years or so and have a
> good grasp of how DB2's RDBMS decides to retrieve data. So on DB2, I
> write some pretty good, optimized queries.
>
> I no a poorly written query can run several times longer than one
> written by someone who understands the guts of the DBMS. So I want to
> be sure that I'm doing the right things to get quick results back from
> informix.
>
> Specifically I'm hoping you all might be able to fill me in on a few
> things I do with DB2 that I'd like to know about on informix.
>
> 1. Is there something akin to DB2's EXPLAIN facility? This is a
> utility that comes with DB2 that you can use to see the order in which
> tables will be accessed to satisfy your query, it list the type of
> join being done and shows any index pages that are being used (among
> other things). I'm looking for something that can tell me how much
> work I'm asking Informix to do.
>
> 2. I know nothing of the Informix system catalog yet. Do you know of
> any place online that I can get list of the system tables / columns?
> I'll be looking for ways to tell the the table statistics are current,
> what the keys are to tables and what indexes are available and so on.
systables, syscolumns, sysfragments and sysindexes contain the data
dictionary catalog and statistics information as well as the sysdistrib
table, a special table that contains detailed data distribution information.
Statistics must be updated manually by the "UPDATE STATISTICS" command.
Data distribution must be generated explizit by "UPDATE STATISTICS MEDIUM/HIGH"
if neccessary.
You cannot fill the statistics with other data than the current,
that is, you cannot ask the optimizer how it would work, if you would
have larger tables.
> 3. A long time ago, DB2 used to have some little tricks you could do
> to fool the RDBMS into starting with a particular table (you could
> include a 'where' test over 10 times on a field in the table you
> wanted it to hit first and other odd things you might know of). In
> recent releases, all the little tricks I knew of are gone. Are there
> any tricks like this that I might want to use in Informix?
Most of the tricks you know will also work for Informix. For instance
your trick with the 'where' clause. There are a lot of other tricks.
If you will use version 7.3, use the official directives. The directive
starts after the SELECT and is embedded in braces {}, or C-comments /* */
or double-dash --
SELECT {+INDEX(idxname,tabname)} * FROM tabname WHERE f1 = 9;
There are more directives available than at Oracle. USE_NL,AVOID_NL,
USE_HASH,AVOID_HASH, ...
If you are using any version pre 7.3, you must use other tricks.
First of all you should set the Informix configuration parameter
OPTCOMPIND to 0. This is just to force the optimizer to use an
index if there is an index available. You can also use the
environment variable OPTCOMPIND before starting your application.
Some tricks ( Force the optimizer (not) to use an index )
select * from table where f1 = 7 and f2 = 3;
If you have 2 indexes, one on f1 and one on f2, you can force the
optimizer to use the index on f2 if you place the f1 condition in
an expression:
select * from table where f1 + 0 = 7 and f2 = 3;
The comparison "<>" or "!=" will never use an index.
select * from tabname where f1 != 3;
It might use an index, if you would use the following:
select * form tabname where f1 < 3 or f1 > 3;
To force the optimizer to use nested loop joins, you must prevent
a hash join.
select * form t1, t2 where t1.x = t2.x; -- might cause a hash join
select * from t1, t2 where t1.x <= t2.x and t1.x >= t2.x; -- uses nested loop join
To force the optimizer to use a hash join:
select * from t1, t2 where t1.x + 0 = t2.x + 0;
( if t1.x is an alphanumeric column )
select * from t1, t2 where t1.x || "" = t2.x || "";
----------------------------------------------------------------------------
MOST IMPORTANT
----------------------------------------------------------------------------
Update statisticsCombine cursors
Avoid correlated queries
Avoid client/server communication ( use cursor communication,
use stored procedures, don't select unneccessary columns, ... )
> 4. When writing a multi table query for DB2, it doesn't really matter
> which table you list first in your SQL; The RDBMS will use whichever
> one it thinks is best as the first place to start. In Oracle on the
> other hand, if the optimizer isn't really sure which table it should
> start with first (if it thinks there is no performance benefit to
> using one over the other), it will default to using the one your coded
> first in your FROM statement. What are the rules for Informix on this
> subject?
If both table-filters will result in the same number of rows returned
and the same I/O, Informix will always use order you used in the from
clause. ( This would happen if you would never update your statistics ).
Starting with version 7.3 we have an optimiter directive to force the
optimizer to use the order of the from clause.
SELECT {+ORDERED} ... FROM tab1, tab2 WHERE ...
> 5. I can use DB2AM or Platinum or Mainview to watch a query run when
> I'm hitting DB2. Is there a performance monitor of some sort that I
> could use with our Informix server (it's on an NT server machine).
>
> Lastly, I don't have much faith in our DBA's as they are contractors
> from outside our company and this is their first attempt at using
> Informix... Can you think of anything I should keep an eye out for
> that might hurt our performance from a DBA's point of view?
Keep the physical I/O as small as possible ( normalize your tables )
Spread the I/O across multiple disks
Make a few tests with an INSERT or UPDATE statement and BUFFERED
transaction logging and UNBUFFERED logging.
Have a look at the SQL statement BEGIN WORK.
> Oh yes and really lastly, can you think of any good books I might look
> into on the query performance front? I don't really need all the
> admin / installation stuff, but that's fine if a good book you know of
> covers it as well.
>
> Thanks for any input, sorry I've dumped so many questions on you at
> once - I just hate poorly written code and don't want to do a shoddy
> job.
>
> -Pete
--
Stefan Weideneder
----------------------------------------------------------------
----------------------------------------------------------------
Perfect Stefan - thats just the sort of thing I'm looking for!
On Mon, 18 Jan 1999 10:37:40 +0100, Stefan Weideneder
<stefan@weideneder.de> wrote:
>Hi,
>
>I don't want to repeat, what V.Salvador wrote. I fully agree.
>I'll just fill in a few additional comments:
>
>Postman Pete wrote:
>>
>> Hi all
>>
>> I'm getting charged with writing some reports which will run against a
>> new informix server at our company.
>>
>> I've been reporting against DB2 tables for 5 years or so and have a
>> good grasp of how DB2's RDBMS decides to retrieve data. So on DB2, I
>> write some pretty good, optimized queries.
>>
>> I no a poorly written query can run several times longer than one
>> written by someone who understands the guts of the DBMS. So I want to
>> be sure that I'm doing the right things to get quick results back from
>> informix.
>>
>> Specifically I'm hoping you all might be able to fill me in on a few
>> things I do with DB2 that I'd like to know about on informix.
>>
>> 1. Is there something akin to DB2's EXPLAIN facility? This is a
>> utility that comes with DB2 that you can use to see the order in which
>> tables will be accessed to satisfy your query, it list the type of
>> join being done and shows any index pages that are being used (among
>> other things). I'm looking for something that can tell me how much
>> work I'm asking Informix to do.
>>
>> 2. I know nothing of the Informix system catalog yet. Do you know of
>> any place online that I can get list of the system tables / columns?
>> I'll be looking for ways to tell the the table statistics are current,
>> what the keys are to tables and what indexes are available and so on.
>
>systables, syscolumns, sysfragments and sysindexes contain the data
>dictionary catalog and statistics information as well as the sysdistrib
>table, a special table that contains detailed data distribution information.
>Statistics must be updated manually by the "UPDATE STATISTICS" command.
>Data distribution must be generated explizit by "UPDATE STATISTICS MEDIUM/HIGH"
>if neccessary.
>You cannot fill the statistics with other data than the current,
>that is, you cannot ask the optimizer how it would work, if you would
>have larger tables.
>
>> 3. A long time ago, DB2 used to have some little tricks you could do
>> to fool the RDBMS into starting with a particular table (you could
>> include a 'where' test over 10 times on a field in the table you
>> wanted it to hit first and other odd things you might know of). In
>> recent releases, all the little tricks I knew of are gone. Are there
>> any tricks like this that I might want to use in Informix?
>
>Most of the tricks you know will also work for Informix. For instance
>your trick with the 'where' clause. There are a lot of other tricks.
>
>If you will use version 7.3, use the official directives. The directive
>starts after the SELECT and is embedded in braces {}, or C-comments /* */
>or double-dash --
>
>SELECT {+INDEX(idxname,tabname)} * FROM tabname WHERE f1 = 9;
>
>There are more directives available than at Oracle. USE_NL,AVOID_NL,
>USE_HASH,AVOID_HASH, ...
>
>If you are using any version pre 7.3, you must use other tricks.
>First of all you should set the Informix configuration parameter
>OPTCOMPIND to 0. This is just to force the optimizer to use an
>index if there is an index available. You can also use the
>environment variable OPTCOMPIND before starting your application.
>
>Some tricks ( Force the optimizer (not) to use an index )
>
>select * from table where f1 = 7 and f2 = 3;>
>If you have 2 indexes, one on f1 and one on f2, you can force the
>optimizer to use the index on f2 if you place the f1 condition in
>an expression:
>
>select * from table where f1 + 0 = 7 and f2 = 3;>
>The comparison "<>" or "!=" will never use an index.
>
>select * from tabname where f1 != 3;>
>It might use an index, if you would use the following:
>
>select * form tabname where f1 < 3 or f1 > 3;>
>
>To force the optimizer to use nested loop joins, you must prevent
>a hash join.
>
>select * form t1, t2 where t1.x = t2.x; -- might cause a hash join>
>select * from t1, t2 where t1.x <= t2.x and t1.x >= t2.x; -- uses nested loop join>
>To force the optimizer to use a hash join:
>
>select * from t1, t2 where t1.x + 0 = t2.x + 0;>
>( if t1.x is an alphanumeric column )
>
>select * from t1, t2 where t1.x || "" = t2.x || "";>
>----------------------------------------------------------------------------
>MOST IMPORTANT
>----------------------------------------------------------------------------
>Update statistics>Combine cursors
>Avoid correlated queries
>Avoid client/server communication ( use cursor communication,
>use stored procedures, don't select unneccessary columns, ... )
>
>
>
>> 4. When writing a multi table query for DB2, it doesn't really matter
>> which table you list first in your SQL; The RDBMS will use whichever
>> one it thinks is best as the first place to start. In Oracle on the
>> other hand, if the optimizer isn't really sure which table it should
>> start with first (if it thinks there is no performance benefit to
>> using one over the other), it will default to using the one your coded
>> first in your FROM statement. What are the rules for Informix on this
>> subject?
>
>If both table-filters will result in the same number of rows returned
>and the same I/O, Informix will always use order you used in the from
>clause. ( This would happen if you would never update your statistics ).
>
>Starting with version 7.3 we have an optimiter directive to force the
>optimizer to use the order of the from clause.
>
>SELECT {+ORDERED} ... FROM tab1, tab2 WHERE ...
>
>> 5. I can use DB2AM or Platinum or Mainview to watch a query run when
>> I'm hitting DB2. Is there a performance monitor of some sort that I
>> could use with our Informix server (it's on an NT server machine).
>>
>> Lastly, I don't have much faith in our DBA's as they are contractors
>> from outside our company and this is their first attempt at using
>> Informix... Can you think of anything I should keep an eye out for
>> that might hurt our performance from a DBA's point of view?
>
>Keep the physical I/O as small as possible ( normalize your tables )
>Spread the I/O across multiple disks
>Make a few tests with an INSERT or UPDATE statement and BUFFERED
>transaction logging and UNBUFFERED logging.
>Have a look at the SQL statement BEGIN WORK.
>
>> Oh yes and really lastly, can you think of any good books I might look
>> into on the query performance front? I don't really need all the
>> admin / installation stuff, but that's fine if a good book you know of
>> covers it as well.
>>
>> Thanks for any input, sorry I've dumped so many questions on you at
>> once - I just hate poorly written code and don't want to do a shoddy
>> job.
>>
>> -Pete