×

Loading...
Ad by
  • 技多不压身，工到自然成：安省技工证书特训班，点击咨询报名！
Ad by
  • 技多不压身，工到自然成：安省技工证书特训班，点击咨询报名！

@Calgary

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)

More Topics

  • 老程序员第一次用copilot的一点点体会
  • 现在社区游泳池招救生员巡逻，过一会还要不停的点人头，这工作是否很快就被AI代替了，装一个摄像头后面是AI处理系统，这样只招一个等着救人就行了，人头沉下水一定时间立马强光照射，还可以分析水下人的状况来判断是否溺水等
  • 新冠疫苗引发心肌炎? 最新研究这样看
  • 这道几何题有解吗?
  • SAM 2 Demo
    • 枫下论坛主坛 /  工作学习 /  IT技术讨论