Hi Andrew,

> 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:

I looked at your queries and saw that the view is properly the importend
thing here that slows down your query.
Have already tried it without using view ?
You want to be portable to other SQL-servers then be aware they don't have
UDF, but where you use an UDF the most other SQL-servers have an build-in
function COALESCE or ISNULL for that.

Run the query below and see what speed it have on your machine :)
This screames on speed on my machine against the ones you post before.
Mostly the best way of building queries is build them in the order you need
the tables/fields.

---------------------------------------
SELECT
OI.OrderID,
O.CreateDate as OrderDate,
OI.ProdID,
P.Name as ProdName,
M.Name as Manufacturer,
O.CustomerID,
C.Name as Customer,
OI.Qty as OrderQty,
OI.Qty * CAST(( 1 + inullz(POI.Qty - 1)) AS integer) As TotalQty,
R.Qty as Reserved,
R.Qty * CAST(( 1 + inullz(POI.Qty - 1)) AS integer) As TotalReserved,
OI.Price,
R.Term
FROM
Reserves R
JOIN OrderItems OI ON (OI.ID = R.RowID)
JOIN Orders O ON (O.ID = OI.OrderID)
JOIN Products P ON (P.ID = OI.ProdID)
JOIN Firms C ON (C.ID = O.CustomerID)
JOIN Firms M ON (M.ID = P.Manufacturer)
LEFT JOIN OrderItems POI ON (POI.ID = OI.PartOf)

---------------------------------------

Regards,
Arno Brinkman

>
>
> /*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
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>