2007/1/29, Becker, Holger <[EMAIL PROTECTED]>:
Eduard M wrote:
> I have a query that does a simple join from 2 tables, on a
> foreign key. Both
> tables are about 1,000,000 rows each. The query takes about
> 11 seconds. It
> seems like way too long.
>
> select kar.id from per_karte kar, per_nodarbosanas nod where nod.id =
> kar.per_nodarbosanas_id
>
> The query is executed from SQL Studio.
> If I limit the query to rowno < 500, it executes very fast.
>
> I wonder why the big query takes so long. Is it somehow related to
> transaction isolation level? Is database copying aside any
> temporary data,
> or looking into rollback segment while making that join?
>
> Eduard
Hi,
join selects always creates a result set in MaxDB.
So if in your case each row from one table hits one in the other
you gets a result set with 1 million rows and this might take 11
seconds.
In fact the result of the join might be much larger, depending on the
cardinality of the key columns. You could do
select count(*) "result size"
from per_karte kar, per_nodarbosanas nod
where nod.id = kar.per_nodarbosanas_id
do get the actual size of the result.
Another reason for the long runtime might be indexing. Eduard, you do
not mention what indexes you have on those tables. DDL might probably
help as well.
Finally you could look at the execution plan to find out what the DB is doing.
Kind regards
robert
--
Have a look: http://www.flickr.com/photos/fussel-foto/
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
