×

Loading...
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.

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
Report

Replies, comments and Discussions:

  • 工作学习 / IT技术讨论 / How come the join is so slow in microsoft access database? It's such a simple join. Thanks.
    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?
      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.
        • 看了半天才看懂:prompt -> hints,trick. 你以为我是机器呀,给你prompt.