Need a summary by department

This sql is slow. (one minute) It show the department name. There are only 10 departments.
If I take out the department name it run fast(less than a second)
The order clientnumber is index so is the orderDate
Any suggestions?

select O.ClientNum, sum(OL.Total), OL.Dep, d.name
from Orders as O, OrderLines as OL, Department as D
where
OL.Dep=D.num
and
OL.Num=O.num
and
O.date >='2005-01-01' and o.date <='2008-01-01'
and
O.ClientNum =  2000
group by OL.Dep

Carlos

> select O.ClientNum, sum(OL.Total), OL.Dep, d.name
> from Orders as O, OrderLines as OL, Department as D
> where
> OL.Dep=D.num
> and
> OL.Num=O.num
> and
> O.date >='2005-01-01' and o.date <='2008-01-01'
> and
> O.ClientNum =  2000
> group by OL.Dep

Could you at least send us some informations:

a) What DBISAM version ?
b) Query plan or table/indexes structure

BTW
On table Orders you should have indexes on fields "num", "date", "clientnum"
(3 indexes starting with these fields)
On the table OrderLines you should have indexes on fields "dep", "num" (2
indexes starting with these fields)
On the table Department you should have index on field "num" (1 index
starting with this field)
You can change
O.date >='2005-01-01' and o.date <='2008-01-01'
by
O.date between '2005-01-01' and '2008-01-01'

The most apropriated answer could be given if you send us a query plan.

Eduardo

carlosam


If you using V4 then the first thing to do is check out the query plan. Generally this will tell you where things are slow.

The standard rule for DBISAM is to get speed you want an index on all fields in the where / join / group / order by clauses of a query so try adding indices on

OL.Dep
D.num
OL.Num
O.num

Roy Lambert

"carlosam" <carlos.aneses@gmail.com> wrote in message
news:07F438C9-C3E4-4921-BE1B-B5BAC566A40A@news.elevatesoft.com...
> OL.Dep=D.num

This has to be the problem. Each line item is having to hit the department
table. Is this really necessary? And are OL.Dep and D.Num indexes?

Or try something like this


select sum(OL.Total) SUMTOT, OL.Dep
INTO MEMORY\TEMP
from Orders as O, OrderLines as OL
where
OL.Num=O.num
and
O.date >='2005-01-01' and o.date <='2008-01-01'
and
O.ClientNum =  2000
group by OL.Dep;
SELECT  D.NAME, M.SUMTOT
FROM MEMORY\TEMP M
JOIN DEPARTMENT D ON D.NUM = M.DEP;

Robert

> OL.Dep=D.num

This has to be the problem. Each line item is having to hit the department
table. Is this really necessary? And are OL.Dep and D.Num indexes?

Or try something like this


select sum(OL.Total) SUMTOT, OL.Dep
INTO MEMORY\TEMP
from Orders as O, OrderLines as OL
where
OL.Num=O.num
and
O.date >='2005-01-01' and o.date <='2008-01-01'
and
O.ClientNum =  2000
group by OL.Dep;
SELECT  D.NAME, M.SUMTOT
FROM MEMORY\TEMP M
JOIN DEPARTMENT D ON D.NUM = M.DEP;

Robert


OL.Dep is not index dep.num is.
Using dbisam 3
I need the department name on the sql result. Is there a way to make it on the same
select statment without this delay?
Can I use 2 select statments on the same sql on DBISAM 3 as you show here?

Carlos

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote:


"carlosam" <carlos.aneses@gmail.com> wrote in message
news:07F438C9-C3E4-4921-BE1B-B5BAC566A40A@news.elevatesoft.com...
> OL.Dep=D.num

This has to be the problem. Each line item is having to hit the department
table. Is this really necessary? And are OL.Dep and D.Num indexes?

Or try something like this


select sum(OL.Total) SUMTOT, OL.Dep
INTO MEMORY\TEMP
from Orders as O, OrderLines as OL
where
OL.Num=O.num
and
O.date >='2005-01-01' and o.date <='2008-01-01'
and
O.ClientNum =  2000
group by OL.Dep;
SELECT  D.NAME, M.SUMTOT
FROM MEMORY\TEMP M
JOIN DEPARTMENT D ON D.NUM = M.DEP;

Robert


Worked fast Thanks.

Carlos