This website requires Javascript to function properly. Please go to the setting of your web browser and enable Javascript for this website.

Ad by

最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。

Ad by

最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。

How come the join is so slow in microsoft access database? It's such a simple join. Thanks.

tongcd(Rock Your Body)

Using select idrecord from defects where defectnum=3200
I got 3231 as idrecord. Then runing a query
select * from custmval where iddefrec=3231, allreturns instantly.

But If I put them together in either one of ways below, the query hangs, which I take it as extremely slow. Anyone has any idea why and how to solve this issue? Thanks.

1)select d.summary, d.idrecord from
(select defects.summary, defects.idrecord
from defects
where defects.defectnum=3200) d, custmval
where custmval.iddefrec=d.idrecord
2)select defects.summary, defects.idrecord
from defects ,custmval
where defects.defectnum=3200 and custmval.iddefrec=defects.idrecord

(#2070711@0)
Last Updated: 2005-1-12
This post has been archived. It cannot be replied.

Report

Replies, comments and Discussions:

工作学习 / IT技术讨论 / How come the join is so slow in microsoft access database? It's such a simple join. Thanks. -tongcd(Rock Your Body); 2005-1-12 {661} (#2070711@0)
Using select idrecord from defects where defectnum=3200
I got 3231 as idrecord. Then runing a query
select * from custmval where iddefrec=3231, allreturns instantly.

But If I put them together in either one of ways below, the query hangs, which I take it as extremely slow. Anyone has any idea why and how to solve this issue? Thanks.

1)select d.summary, d.idrecord from
(select defects.summary, defects.idrecord
from defects
where defects.defectnum=3200) d, custmval
where custmval.iddefrec=d.idrecord
2)select defects.summary, defects.idrecord
from defects ,custmval
where defects.defectnum=3200 and custmval.iddefrec=defects.idrecord

do u have index(uniqe, non-uniq) on defects.defctnum, custmval.idderec, and defects.idrecord? -647i(盲人协会); 2005-1-12 {330} (#2070725@0)
BTW, my 2 cents, the second query may have better performance, if the DB engine doesn't have ablility to convert the sub-query to inner join.

use sub-query unless u must use "exist, in", otherwise use sub-query instead. Just for better performace.

Above is all based on database general sense. I don't use M$ DB at all.

Thanks for prompt reply. I was wrong about using access, actually I was just using linking to table via odbc from access. So it must be something wrong with odbc driver I was using. -tongcd(Rock Your Body); 2005-1-12 (#2070790@0)

看了半天才看懂:prompt -> hints,trick. 你以为我是机器呀，给你prompt. -647i(盲人协会); 2005-1-12 (#2071749@0)

@Florida

How come the join is so slow in microsoft access database? It's such a simple join. Thanks.

Replies, comments and Discussions:

More Topics