Hi everyone,

I have two tables A and B. A has about 1.6 million records and B has about one million records. Both tables have two fields only.

I have built two queries as below. The weird thing is that the first query which doesn't use index takes 10 minutes with almost million records in the result. The second one which uses the primary index takes more than a day (I'm still waiting for the result)

Can anyone explain to me why is this happenning? And how can I make it faster?

First query,

select B.fldb
from A, B
where A.flda=B.dsca
selectdo
endselect

Second query,

select B.fldb
from A, B
where A._index1={B.dsca}
selectdo
endselect

Thanks in advance
Hiba

Have you tried also:

select B.fldb
from A, B
where B._index1={A.dsca}
selectdo
endselect

Can't do this, A.dsca doesn't exist.

- Hiba

Can you post exactly the field names and the indexes of the tables, so can it be clear the structure?

It doesn't matter which tables we are talking about. There's only one index in each table and only two fields in each one.

The only way to compare between these tables is by comparing fields A.flda and B.dsca when A.flda is A._index1.

- Hiba

This must be equivalent to the first query:

select B.fldb
from A, B
where B.dsca refers to A
selectdo
endselect

you can try the following options..

1) while running the sql query, use sql trace, this will give you the exact breakup of time fetching each records.
2) compare the table definitions of both the tables, whether the domain types are the same
3) try reorganizing and reconfiguring the table.. may be the table index needs some repairing to be done.
4) Try using order by and sort it in ascending mode...

hope it helps you to find solution for your problem

Thanks for your suggestions, I'll try them all and let you know..

I have some questions to you raviraj,

1) How can I do the sql trace, can you explain a bit more about it...
2) Both fields that are being compared are of type string, but each one has a different length, will this affect the query performance?

- Hiba

Zardoz, I can't use your suggestion since it'll output a different result than the one I need.

Hiba

Hi Siba,

Sorry for the delay, had got entangled in some production issues.

Regarding SQL trace
Just -- -keeplog -logfile mylogfile -set TT_SQL_TRACE=0200
add this line in Bw configuration.
Log in to Baan, Run your session, a file with name mylogfile will get created in home directory.

Just view the file..
It should give output in the following format
================================================
Fetch times of Query (QID : 12) in SqlBreak :
select ttdsk360.*
where
(ttdsk360.user = :logname$ or ttdsk360.user = :1)
order by ttdsk360.user desc, ttdsk360.cpac desc,
ttdsk360.rsst desc, ttdsk360.rsid desc

Nr Rows Fetched : 39
Fetch Time for 1st Row : 0.060 sec
Max Fetch Time : 0.060 sec
Average Fetch Time : 0.000 sec
Average Fetch Time (except Max) : 0.000 sec


Analyse it and you will probable be able to know the reason of performance deterioration.