Hi all.

Help me please. I want to understand how to make queries with indirect hints for
engine. I'm trying to keep portability with other SQL servers. That is why Im
not using direct plans inside queries. Select from a small table that contains
less than 100 records causes 10 min of waiting. Why the engine chooses the worst
scenario to do that? The oddest way to get over the problem is using subselects.
Details are:


/*Simplified data structures */

/*manufacturers, customers, etc, about 10000 records*/
create table firms (
ID integer not null primary key,
name varchar(80)
);

/*Product catalog, about 10000 records*/
create table Products (
ID integer not null primary key,
manufacturer integer not null references firms(id),
partnumber varchar(32),
name varchar(100),
price float
);

/*orders, about 70000 records*/
create table Orders(
ID integer not null primary key,
CreateDate timestamp,
CustomerID integer references firms(id)
);

/* order items, about 200000 records */
/*one item can contain another, but contained items can't*/
create table OrderItems(
ID integer not null primary key,
OrderID integer not null references Orders(ID),
ProdID integer not null references Products(ID),
Qty integer not null,
Price float,
PartOf integer references OrderItems(ID) /* 2 level hierarchy */
);

/*So I avoid to use UDFs in application because it is not portable. That is why
I'm using views here*/

declare external function inullz integer rerurns integer by value entry_point
'inullz' module_name 'my_udfs';
int inullz(int* i) { if (i) return *i; else return 0; }

create view vOrderItems (ID, OrderID, ProdID, Qty, SetQty, Price, PartOf) as
select I.ID, I.OrderID, I.ProdID, I.Qty,
cast((1+inullz(P.Qty-1)) as integer), /*quantity in set, or 1*/
I.Price, I.PartOf
from OrderItems I left join OrderItems P on I.PartOf = P.ID;

/* Actual reserves. A small table that contains less than 100 records */
create table Reserves (
RowID integer not null primary key, /* see below */
Qty integer not null,
Term timestamp,
foreign key (RowID) references OrderItems(ID)
);

select I.OrderID, O.CreateDate as OrderDate,
I.ProdID, P.Name as ProdName, M.Name as Manufacturer
O.CustomerID, C.Name as Customer,
I.Qty as OrderQty, I.Qty*I.SetQty as TotalQty,
R.Qty as Reserved, R.Qty*I.SetQty as TotalReserved,
I.Price, R.Term
from Reserves R, vOrderItems I, Orders O, Products P, Firms M, Firms C
where R.RowID = I.ID
and I.OrderID = O.ID
and I.ProdID = P.ID
and O.CustomerID = C.ID
and P.Manufacturer = M.ID

About 100 rows and takes about 10 min. Only raw subselect helps:

select I.OrderID, O.CreateDate as OrderDate,
I.ProdID, P.Name as ProdName, M.Name as Manufacturer
O.CustomerID, C.Name as Customer,
I.Qty as OrderQty,
I.Qty*(select II.SetQty from vOrderItems II where II.ID = I.PartOf) as
TotalQty,
R.Qty as Reserved,
R.Qty*(select II.SetQty from vOrderItems II where II.ID = I.PartOf) as
TotalReserved,
I.Price, R.Term
from Reserves R, OrderItems I, Orders O, Products P, Firms M, Firms C
where R.RowID = I.ID
and I.OrderID = O.ID
and I.ProdID = P.ID
and O.CustomerID = C.ID
and P.Manufacturer = M.ID

That's it. Im tired.

Thanks for your patience...

Andrew