I'm seeing differences in performance for the same SQL being executed
on two slightly different databases. Using Set Explain, the difference
is when database1 exectues part of the WHERE, it goes down the Index
Path for that particular table and it's fast. On database2, the query
plan does a Sequenial Scan of that particular table instead of the
Index Path. This is IDS 9.4.

The differences in schema for the table in question are minimal:
(1) In database1, there is no Index declared, but as I understand the
primary key is an index implicitly. In database2, the index is
declared and named on the primary key. Could those differences,
explicitly declaring and naming the PK and Index, cause a differnce in
the Optimizer's approach and query plan?
(2) In database2, the Primary Key, and Index are declared outside of
the Create Table block. Could declaring the PK, Index, etc OUTSIDE of
the Create Table cause it?

Below is the basic schema:

--database1
create table tablename (
....
primary key (col1, col2, col3)
constraint tablename_key
) lock mode row;


--database2
create table tablename (
....
) lock mode row;

REVOKE ALL PRIVILEGES ON tablename FROM PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON tablename TO PUBLIC;

CREATE DISTINCT INDEX tablename_key_idx ON tablename
(col1, col2, col3);
ALTER TABLE tablename ADD CONSTRAINT PRIMARY KEY
( col1, col2, col3) CONSTRAINT tablename_key;


Thanks for any help,
Bob

NCC1701BOB (AT) gmail (DOT) com wrote:
When and how have you run update statistics on either database?

--
Clive

On Jun 15, 8:50 am, Clive Eisen <c... (AT) serendipita (DOT) com> wrote:
I'm not sure what you mean by "how", it was run yesterday. I'm a
developer and our DBA is assisting in this and he ran the update
statistics through SQL Editor.

Do you have the same data in both tables ? Do both tables have good statistics in sysdistrib ?


-----Original Message-----
From: NCC1701BOB (AT) gmail (DOT) com [mailto:NCC1701BOB (AT) gmail (DOT) com]
Sent: Friday, June 15, 2007 09:44 AM
To: informix-list (AT) iiug (DOT) org
Subject: IDS Optimizer Query Plan, declaring and naming indexes

I'm seeing differences in performance for the same SQL being executed
on two slightly different databases. Using Set Explain, the difference
is when database1 exectues part of the WHERE, it goes down the Index
Path for that particular table and it's fast. On database2, the query
plan does a Sequenial Scan of that particular table instead of the
Index Path. This is IDS 9.4.

The differences in schema for the table in question are minimal:
(1) In database1, there is no Index declared, but as I understand the
primary key is an index implicitly. In database2, the index is
declared and named on the primary key. Could those differences,
explicitly declaring and naming the PK and Index, cause a differnce in
the Optimizer's approach and query plan?
(2) In database2, the Primary Key, and Index are declared outside of
the Create Table block. Could declaring the PK, Index, etc OUTSIDE of
the Create Table cause it?

Below is the basic schema:

--database1
create table tablename (
....
primary key (col1, col2, col3)
constraint tablename_key
) lock mode row;


--database2
create table tablename (
....
) lock mode row;

REVOKE ALL PRIVILEGES ON tablename FROM PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON tablename TO PUBLIC;

CREATE DISTINCT INDEX tablename_key_idx ON tablename
(col1, col2, col3);
ALTER TABLE tablename ADD CONSTRAINT PRIMARY KEY
( col1, col2, col3) CONSTRAINT tablename_key;


Thanks for any help,
Bob

_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list

NCC1701BOB (AT) gmail (DOT) com said:
I think he means "which type of statistics did he get: high, medium or low?
--
Bye now,
Obnoxio

"I'm astonished anyone pays real money for this crap."
-- Cosmo

--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.

On Jun 15, 9:00 am, "Obnoxio The Clown" <obno... (AT) serendipita (DOT) com>
wrote:
"Do you have the same data in both tables ? Do both tables have good
statistics in sysdistrib ?"
Both tables have about the same number of rows, but not the same data
exactly. I don't know about the sysdistrib. I'll try to look and have
our DBA look when he gets in.

It was update statistics on HIGH.

Yea, because depending on the data distribution, the optimizer may decideit is easier to scan the table than to use the index. You have update stats high, so you'd think the optimizer would make the correct decision, but if the query is slower, I guess it didn't.
You can force the use of the index with an optimizer directive.


-----Original Message-----
From: NCC1701BOB (AT) gmail (DOT) com [mailto:NCC1701BOB (AT) gmail (DOT) com]
Sent: Friday, June 15, 2007 10:44 AM
To: informix-list (AT) iiug (DOT) org
Subject: Re: IDS Optimizer Query Plan, declaring and naming indexes

On Jun 15, 9:00 am, "Obnoxio The Clown" <obno... (AT) serendipita (DOT) com>
wrote:
"Do you have the same data in both tables ? Do both tables have good
statistics in sysdistrib ?"
Both tables have about the same number of rows, but not the same data
exactly. I don't know about the sysdistrib. I'll try to look and have
our DBA look when he gets in.

It was update statistics on HIGH.


_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list