SQL FOR MAIN VIEW: SELECT INVTRY。[Index],INVTRY.TITLE,INVTRY.AUTHILL1, INVTRY .attFirstEdition,INVTRY.attSigned, ISNULL(INVTRY.attSignedPD,'''')SignedCond,INVTRY.YRPUB, INVTRY.PRICE,INVTRY.Web,INVTRY。状态, INVTRY.WebStatusPending,INVTRY.ActivateDate, INVTRY.DeactivateDate,INVTRY.WebAddedBatchID, INVTRY.AllowDuplicate,INVTRY.WebAction, INVTRY.WebActionPending,INVTRY.DateModified, INVTRY.DateWebActionApplied,INVTRY.JIT,INVTRY.MImage, INVTRY.HImage,INVTRY。 AdCode, CASE WHEN INVURN.WebAddedBatchID IS NOT NULL THEN - 1 ELSE 0 END as OnWeb FROM vwInventory_Dupes INNER JOIN (WebStatus INNER JOIN (INVOND INNER JOIN tabStatus ON INVTRY.Status = tabStatus.Status)ON WebStatus.WebStatus = INVTRY.Web)ON (vwInventory_Dupes.YearPub = INVTRY.YRPUB)AND (vwInventory_Dupes.SignedCond = ISNULL(INVTRY。 attSignedPD, '''')和(vwInventory_Dupes.Signed = INVTRY.attSigned)和 (vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition)和 (vwInventory_Dupes.Author = INVTRY.AUTHILL1)和 (vwInventory_Dupes.TITLE = INVTRY.TITLE) WHERE(((tabStatus.ForWeb)= 1)AND ((WebStatus.IncludeDupe) = 1)) SQL FOR vwInventory_Dupes: SELECT INVTRY.TITLE ,INVTRY.AUTHILL1作者, 演员(attFirstEdition AS tinyint)FirstEd, 演员(attSigned AS tinyint)签名, ISNULL(参赛作品。 attSignedPD,'''')SignedCond, INVTRY.YRPUB YearPub 来自WebStatus INNER JOIN (INVOND INNER JOIN tabStatus ON INVTRY.Status = tabStatus.Status)ON WebStatus.WebStatus = INVTRY.Web WHERE(((tabStatus.ForWeb)= 1)AND((WebStatus.IncludeDupe) = 1)) GROUP BY INVTRY.TITLE ,INVTRY.AUTHILL1, Cast(attFirstEdition AS tinyint),Cast(attSigned AS tinyint), ISNULL(INVTRY.attSignedPD,''''),INVTRY.YRPUB HAVING(((COUNT(INVTRY。[INDEX]))> 1)) I previously posted re. this, but thought I''d try again with a summary offacts. I have an Access 2000 MDB with a SQL Server 7 back end. There is a view thatis linked to the database via ODBC and has been in place for several yearswithout any performance problems. Recently I added a couple of fields to the output of the view, and it becamevery slow when scrolling. When just opened in the database window, thelinked view takes about a second to scroll down one screen. When opened inthe form (in Continuous Form view), it takes about 2-3 seconds. It used toscroll just about instantaneously. I tried removing the few fields I added to restore the view to its previousform, but it had no effect. The view was still much slower than it had been. The total number of records returned from the view is about 1300, so it''snot a large number of records. The view has about 25 fields. I found that when I link the view in the MDB without specifying a uniqueindex, it scrolls very quickly -- almost instantaneously. But when I specifythe unique index, it is slow. Since the view needs to be edited, it needsthe unique index defined. As noted, it''s been in place for years, with a unique index defined, yetwithout the slowness. Any ideas as to what might have caused this and whatmight be done would be appreciated. I''ve included the SQL for the viewbelow. Thanks, Neil SQL FOR MAIN VIEW: SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,INVTRY.attFirstEdition, INVTRY.attSigned,ISNULL(INVTRY.attSignedPD, '' '') SignedCond, INVTRY.YRPUB,INVTRY.PRICE, INVTRY.Web, INVTRY.Status,INVTRY.WebStatusPending, INVTRY.ActivateDate,INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,INVTRY.AllowDuplicate, INVTRY.WebAction,INVTRY.WebActionPending, INVTRY.DateModified,INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,INVTRY.HImage, INVTRY.AdCode,CASE WHEN INVTRY.WebAddedBatchID IS NOT NULLTHEN - 1 ELSE 0 END AS OnWebFROM vwInventory_Dupes INNER JOIN(WebStatus INNER JOIN(INVTRY INNER JOINtabStatus ON INVTRY.Status = tabStatus.Status) ONWebStatus.WebStatus = INVTRY.Web) ON(vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND(vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,'' '')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND(vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND(vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND(vwInventory_Dupes.TITLE = INVTRY.TITLE)WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)= 1)) SQL FOR vwInventory_Dupes: SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,Cast(attFirstEdition AS tinyint) FirstEd,Cast(attSigned AS tinyint) Signed,ISNULL(INVTRY.attSignedPD, '' '') SignedCond,INVTRY.YRPUB YearPubFROM WebStatus INNER JOIN(INVTRY INNER JOINtabStatus ON INVTRY.Status = tabStatus.Status) ONWebStatus.WebStatus = INVTRY.WebWHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)= 1))GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),ISNULL(INVTRY.attSignedPD, '' ''), INVTRY.YRPUBHAVING (((COUNT(INVTRY.[INDEX])) > 1))推荐答案 2005年5月10日星期二08:46:39 GMT,Neil <无**** @ nospam.net>写道: 尝试一些相当长的镜头: *重新链接视图。在设计更改后,Access中的旧表示 已过时。 * sp_recompile视图。 -Tom。 On Tue, 10 May 2005 08:46:39 GMT, "Neil" <no****@nospam.net> wrote: Some fairly long shots to try:* Re-link the view. After its design changes, the old representationin Access is obsolete.* sp_recompile the view. -Tom. 我之前发布过re。这个,但我想再次尝试使用事实的摘要。 我有一个带有SQL Server 7后端的Access 2000 MDB。有一种观点认为通过ODBC链接到数据库并且已经存在了好几年没有任何性能问题。 最近我添加了几个字段到视图的输出,滚动时变得非常慢。刚刚在数据库窗口中打开时,链接视图大约需要一秒钟向下滚动一个屏幕。当以表格形式打开时(在连续表格视图中),大约需要2-3秒。它过去常常滚动。 我尝试删除我添加的几个字段以将视图恢复到之前的形式,但它没有任何效果。视图仍然比以前慢得多。 从视图返回的记录总数大约是1300,所以它不是大量的记录。该视图有大约25个字段。 我发现当我在MDB中链接视图而没有指定唯一的索引时,它会非常快速地滚动 - 几乎是即时的。但是当我指定唯一索引时,它很慢。由于需要对视图进行编辑,因此需要定义唯一索引。 如上所述,它已经存在多年,并且定义了唯一索引,但没有缓慢。任何关于可能导致这个以及可能做什么的想法都将受到赞赏。我已经在下面的视图中包含了SQL。 谢谢, Neil SQL主要视图: SELECT INVTRY。[Index],INVTRY.TITLE,INVTRY.AUTHILL1, INVTRY.attFirstEdition,INVTRY.attSigned, ISNULL(INVTRY.attSignedPD,'''')SignedCond, INVTRY.YRPUB, INVTRY.PRICE,INVTRY.Web,INVTRY.Status, INVTRY.WebStatusPending,INVTRY.ActivateDate, INVTRY.DeactivateDate,INVTRY.WebAddedBatchID, INVTRY。 AllowDuplicate,INVTRY.WebAction, INVTRY.WebActionPending,INVTRY.DateModified, INVTRY.DateWebActionApplied,INVTRY.JIT,INVTRY.MImage, INVTRY.HImage,INVTRY.AdCode,例如,当INVTRY.WebAddedBatchID不为空时然后 - 1结束0结束OnWeb 来自vwInventory_Dupes内部加入(WebStatus INNER JOIN (INVTRY INNER JOIN tabStatus ON INVTRY.Status = tabStatus.Status)ON WebStatus.WebStatus = INVTRY.Web)ON (vwInventory_Dup es.YearPub = INVTRY.YRPUB)AND (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,'''))和(vwInventory_Dupes.Signed = INVTRY.attSigned)AND ( vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition)AND (vwInventory_Dupes.Author = INVTRY.AUTHILL1)AND (vwInventory_Dupes.TITLE = INVTRY.TITLE) WHERE(((tabStatus.ForWeb)= 1 )和((WebStatus.IncludeDupe) = 1)) SQL FOR vwInventory_Dupes: SELECT INVTRY.TITLE,INVTRY.AUTHILL1作者, Cast(attFirstEdition AS tinyint)FirstEd, Cast(attSigned AS tinyint)签名, ISNULL(INVTRY.attSignedPD,''')SignedCond, INVTRY.YRPUB YearPub FROM WebStatus INNER JOIN (INVTRY INNER JOIN tabStatus ON INVTRY.Status = tabStatus.Status)ON WebStatus.WebStatus = INVTRY.Web WHERE(((tabStatus.ForWeb)= 1)AND((WebStatus.IncludeDupe) = 1)) GROUP BY INVTRY.TITLE,INVTRY.AUTHILL1 ,演员(attFirstEdition AS tinyint),演员(attSigned AS tinyint), ISNULL(INVTRY.attSignedPD,''''),INVTRY.YRPUB HAVING((COUNT(INVTRY。 [INDEX]))> 1))I previously posted re. this, but thought I''d try again with a summary offacts.I have an Access 2000 MDB with a SQL Server 7 back end. There is a view thatis linked to the database via ODBC and has been in place for several yearswithout any performance problems.Recently I added a couple of fields to the output of the view, and it becamevery slow when scrolling. When just opened in the database window, thelinked view takes about a second to scroll down one screen. When opened inthe form (in Continuous Form view), it takes about 2-3 seconds. It used toscroll just about instantaneously.I tried removing the few fields I added to restore the view to its previousform, but it had no effect. The view was still much slower than it had been.The total number of records returned from the view is about 1300, so it''snot a large number of records. The view has about 25 fields.I found that when I link the view in the MDB without specifying a uniqueindex, it scrolls very quickly -- almost instantaneously. But when I specifythe unique index, it is slow. Since the view needs to be edited, it needsthe unique index defined.As noted, it''s been in place for years, with a unique index defined, yetwithout the slowness. Any ideas as to what might have caused this and whatmight be done would be appreciated. I''ve included the SQL for the viewbelow.Thanks,NeilSQL FOR MAIN VIEW:SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1, INVTRY.attFirstEdition, INVTRY.attSigned, ISNULL(INVTRY.attSignedPD, '' '') SignedCond, INVTRY.YRPUB, INVTRY.PRICE, INVTRY.Web, INVTRY.Status, INVTRY.WebStatusPending, INVTRY.ActivateDate, INVTRY.DeactivateDate, INVTRY.WebAddedBatchID, INVTRY.AllowDuplicate, INVTRY.WebAction, INVTRY.WebActionPending, INVTRY.DateModified, INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage, INVTRY.HImage, INVTRY.AdCode, CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL THEN - 1 ELSE 0 END AS OnWebFROM vwInventory_Dupes INNER JOIN (WebStatus INNER JOIN (INVTRY INNER JOIN tabStatus ON INVTRY.Status = tabStatus.Status) ON WebStatus.WebStatus = INVTRY.Web) ON (vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD, '' '')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND (vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND (vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND (vwInventory_Dupes.TITLE = INVTRY.TITLE)WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) = 1))SQL FOR vwInventory_Dupes:SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author, Cast(attFirstEdition AS tinyint) FirstEd, Cast(attSigned AS tinyint) Signed, ISNULL(INVTRY.attSignedPD, '' '') SignedCond, INVTRY.YRPUB YearPubFROM WebStatus INNER JOIN (INVTRY INNER JOIN tabStatus ON INVTRY.Status = tabStatus.Status) ON WebStatus.WebStatus = INVTRY.WebWHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) = 1))GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1, Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint), ISNULL(INVTRY.attSignedPD, '' ''), INVTRY.YRPUBHAVING (((COUNT(INVTRY.[INDEX])) > 1)) 感谢您的注释。是的,没有效果。还尝试了索引 向导,看看我是否错过了什么,但它没有任何 推荐。 Tom van Stiphout <无************* @ cox.net>在留言中写道 新闻:3s ******************************** @ 4ax.com ... Thanks for the note. Yeah, didn''t have an effect. Also tried the indexwizard to see if I had missed something, but it didn''t have anyrecommendations. "Tom van Stiphout" <no*************@cox.net> wrote in messagenews:3s********************************@4ax.com... 2005年5月10日星期二08:46:39 GMT,Neil <无**** @ nospam.net>写道: 尝试一些相当长的镜头: *重新链接视图。在设计更改后,Access中的旧表示已过时。 * sp_recompile视图。 -Tom。 On Tue, 10 May 2005 08:46:39 GMT, "Neil" <no****@nospam.net> wrote: Some fairly long shots to try: * Re-link the view. After its design changes, the old representation in Access is obsolete. * sp_recompile the view. -Tom.我以前发过re。这个,但我想再次尝试使用事实的摘要。 我有一个带有SQL Server 7后端的Access 2000 MDB。有一种观点通过ODBC链接到数据库并且已经存在了好几年没有任何性能问题。 最近我添加了一对对于视图输出的字段,滚动时它变得非常慢。刚刚在数据库窗口中打开时,链接视图大约需要一秒钟向下滚动一个屏幕。当以表格形式打开时(在连续表格视图中),大约需要2-3秒。它过去只是即时滚动。 我尝试删除我添加的几个字段以将视图恢复到其以前的形式,但它没有效果。视图仍然比它已经慢得多。 从视图返回的记录总数大约是1300,所以它的数量不是很多记录。该视图有大约25个字段。 我发现当我在MDB中链接视图而没有指定唯一的索引时,它会非常快速地滚动 - 几乎是即时的。但是当我指定唯一索引时,它很慢。由于需要对视图进行编辑,因此需要定义唯一索引。 如上所述,它已经存在多年,并且定义了唯一索引,但没有缓慢。任何关于可能导致这个以及可能做什么的想法都将受到赞赏。我已经在下面的视图中包含了SQL。 谢谢, Neil SQL主要视图: SELECT INVTRY。[Index],INVTRY.TITLE,INVTRY.AUTHILL1, INVTRY.attFirstEdition,INVTRY.attSigned, ISNULL(INVTRY.attSignedPD,'''')SignedCond, INVTRY.YRPUB, INVTRY.PRICE,INVTRY.Web,INVTRY.Status, INVTRY.WebStatusPending,INVTRY.ActivateDate, INVTRY.DeactivateDate,INVTRY.WebAddedBatchID, INVTRY。 AllowDuplicate,INVTRY.WebAction, INVTRY.WebActionPending,INVTRY.DateModified, INVTRY.DateWebActionApplied,INVTRY.JIT,INVTRY.MImage, INVTRY.HImage,INVTRY.AdCode,例如,当INVTRY.WebAddedBatchID不为空时然后 - 1结束0结束OnWeb 来自vwInventory_Dupes内部加入(WebStatus INNER JOIN (INVTRY INNER JOIN tabStatus ON INVTRY.Status = tabStatus.Status)ON WebStatus.WebStatus = INVTRY.Web)ON (vwInventory_Dup es.YearPub = INVTRY.YRPUB)AND (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,'''))和(vwInventory_Dupes.Signed = INVTRY.attSigned)AND ( vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition)AND (vwInventory_Dupes.Author = INVTRY.AUTHILL1)AND (vwInventory_Dupes.TITLE = INVTRY.TITLE) WHERE(((tabStatus.ForWeb)= 1 )和((WebStatus.IncludeDupe) = 1)) SQL FOR vwInventory_Dupes: SELECT INVTRY.TITLE,INVTRY.AUTHILL1作者, Cast(attFirstEdition AS tinyint)FirstEd, Cast(attSigned AS tinyint)签名, ISNULL(INVTRY.attSignedPD,''')SignedCond, INVTRY.YRPUB YearPub FROM WebStatus INNER JOIN (INVTRY INNER JOIN tabStatus ON INVTRY.Status = tabStatus.Status)ON WebStatus.WebStatus = INVTRY.Web WHERE(((tabStatus.ForWeb)= 1)AND((WebStatus.IncludeDupe) = 1)) GROUP BY INVTRY.TITLE,INVTRY.AUTHILL1 ,演员(attFirstEdition AS tinyint),演员(attSigned AS tinyint), ISNULL(INVTRY.attSignedPD,''''),INVTRY.YRPUB HAVING((COUNT(INVTRY。 [INDEX]))> 1))I previously posted re. this, but thought I''d try again with a summary offacts.I have an Access 2000 MDB with a SQL Server 7 back end. There is a viewthatis linked to the database via ODBC and has been in place for several yearswithout any performance problems.Recently I added a couple of fields to the output of the view, and itbecamevery slow when scrolling. When just opened in the database window, thelinked view takes about a second to scroll down one screen. When opened inthe form (in Continuous Form view), it takes about 2-3 seconds. It used toscroll just about instantaneously.I tried removing the few fields I added to restore the view to itspreviousform, but it had no effect. The view was still much slower than it hadbeen.The total number of records returned from the view is about 1300, so it''snot a large number of records. The view has about 25 fields.I found that when I link the view in the MDB without specifying a uniqueindex, it scrolls very quickly -- almost instantaneously. But when Ispecifythe unique index, it is slow. Since the view needs to be edited, it needsthe unique index defined.As noted, it''s been in place for years, with a unique index defined, yetwithout the slowness. Any ideas as to what might have caused this and whatmight be done would be appreciated. I''ve included the SQL for the viewbelow.Thanks,NeilSQL FOR MAIN VIEW:SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1, INVTRY.attFirstEdition, INVTRY.attSigned, ISNULL(INVTRY.attSignedPD, '' '') SignedCond, INVTRY.YRPUB, INVTRY.PRICE, INVTRY.Web, INVTRY.Status, INVTRY.WebStatusPending, INVTRY.ActivateDate, INVTRY.DeactivateDate, INVTRY.WebAddedBatchID, INVTRY.AllowDuplicate, INVTRY.WebAction, INVTRY.WebActionPending, INVTRY.DateModified, INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage, INVTRY.HImage, INVTRY.AdCode, CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL THEN - 1 ELSE 0 END AS OnWebFROM vwInventory_Dupes INNER JOIN (WebStatus INNER JOIN (INVTRY INNER JOIN tabStatus ON INVTRY.Status = tabStatus.Status) ON WebStatus.WebStatus = INVTRY.Web) ON (vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD, '' '')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND (vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND (vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND (vwInventory_Dupes.TITLE = INVTRY.TITLE)WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) = 1))SQL FOR vwInventory_Dupes:SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author, Cast(attFirstEdition AS tinyint) FirstEd, Cast(attSigned AS tinyint) Signed, ISNULL(INVTRY.attSignedPD, '' '') SignedCond, INVTRY.YRPUB YearPubFROM WebStatus INNER JOIN (INVTRY INNER JOIN tabStatus ON INVTRY.Status = tabStatus.Status) ON WebStatus.WebStatus = INVTRY.WebWHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) = 1))GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1, Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint), ISNULL(INVTRY.attSignedPD, '' ''), INVTRY.YRPUBHAVING (((COUNT(INVTRY.[INDEX])) > 1)) 问:如果你在视图上打开DAO记录集, 它会持续多长时间拿(作为快照?作为动态的动态集?) 通常,填写数据表是后台任务。 根据文档, http://office.microsoft.com/en- gb / as ... 876211033.aspx 记录集应以100条记录的速度填充 $ 10 $ b每10秒(!)这似乎是错误的!但你可能会想要尝试更改它。 你可以在 显示之前强制表格加载整个记录集。这意味着在显示填充之前有一段很长的停顿时间,但是立即滚动。 我/知道/强迫这种行为的唯一方法是 在表单上放置组合框。你有没有因为这种行为而感到困惑 你呢? (大卫) "尼尔" <无**** @ nospam.net>在消息中写道 新闻:Px *************** @ newsread3.news.pas.earthlin k.net ... Q: If you open a DAO recordset on the view, how long doesit take (as a snapshot? as a dynaset with movelast?) Normally, filling a datasheet is a background task.According to the documentation, http://office.microsoft.com/en-gb/as...876211033.aspxthe recordset should be filled at the rate of 100 recordsevery 10 seconds (!) That seems wrong! but you mightlike to try changing it. You can force form to load an entire recordset beforedisplay. This means that there is a long pause beforethe display is filled, but scrolling is then immediate.The only way I /know/ to force this behaviour is toput combo-boxes on the form. You haven''t gotten confusedby this kind of behaviour have you? (david) "Neil" <no****@nospam.net> wrote in messagenews:Px***************@newsread3.news.pas.earthlin k.net...我以前发过重新。这个,但我想再次尝试使用事实的摘要。 我有一个带有SQL Server 7后端的Access 2000 MDB。有一个视图通过ODBC链接到数据库,并且已经存在了好几年没有任何性能问题。 最近我添加了几个字段到视图的输出,滚动时它变得非常慢。刚刚在数据库窗口中打开时,链接的视图大约需要一秒钟向下滚动一个屏幕。 在表格中打开时(在连续表格视图中),大约需要2-3秒。它曾经瞬间滚动。 我尝试删除几个字段我添加了将视图恢复到其以前的格式,但它没有任何效果。视图仍然比它过得慢得多。 从视图返回的记录总数大约是1300,所以它的数量不是很多记录。该视图有大约25个字段。 我发现当我在MDB中链接视图而没有指定唯一的索引时,它会非常快速地滚动 - 几乎是即时的。但是当我指定唯一索引时,它很慢。由于需要对视图进行编辑,因此需要定义唯一的索引。 如上所述,它已经存在多年,并且定义了唯一的索引,但没有缓慢。任何关于可能导致这个以及可能做什么的想法都将受到赞赏。我已经在下面的视图中包含了SQL。 谢谢, Neil SQL主要视图: SELECT INVTRY。[Index],INVTRY.TITLE,INVTRY.AUTHILL1, INVTRY.attFirstEdition,INVTRY.attSigned, ISNULL(INVTRY.attSignedPD,'''')SignedCond, INVTRY.YRPUB, INVTRY.PRICE,INVTRY.Web,INVTRY.Status, INVTRY.WebStatusPending,INVTRY.ActivateDate, INVTRY.DeactivateDate,INVTRY.WebAddedBatchID, INVTRY。 AllowDuplicate,INVTRY.WebAction, INVTRY.WebActionPending,INVTRY.DateModified, INVTRY.DateWebActionApplied,INVTRY.JIT,INVTRY.MImage, INVTRY.HImage,INVTRY.AdCode,例如,当INVTRY.WebAddedBatchID不为空时然后 - 1结束0结束OnWeb 来自vwInventory_Dupes内部加入(WebStatus INNER JOIN (INVTRY INNER JOIN tabStatus ON INVTRY.Status = tabStatus.Status)ON WebStatus.WebStatus = INVTRY.Web)ON (vwInvento ry_Dupes.YearPub = INVTRY.YRPUB)AND (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,'''))和(vwInventory_Dupes.Signed = INVTRY.attSigned)AND ( vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition)AND (vwInventory_Dupes.Author = INVTRY.AUTHILL1)AND (vwInventory_Dupes.TITLE = INVTRY.TITLE) WHERE(((tabStatus.ForWeb)= 1 )和((WebStatus.IncludeDupe) = 1)) SQL FOR vwInventory_Dupes: SELECT INVTRY.TITLE,INVTRY.AUTHILL1作者, Cast(attFirstEdition AS tinyint)FirstEd, Cast(attSigned AS tinyint)签名, ISNULL(INVTRY.attSignedPD,''')SignedCond, INVTRY.YRPUB YearPub FROM WebStatus INNER JOIN (INVTRY INNER JOIN tabStatus ON INVTRY.Status = tabStatus.Status)ON WebStatus.WebStatus = INVTRY.Web WHERE(((tabStatus.ForWeb)= 1)AND((WebStatus.IncludeDupe) = 1)) GROUP BY INVTRY.TITLE,INV TRY.AUTHILL1,演员(attFirstEdition AS tinyint),演员(attSigned AS tinyint), ISNULL(INVTRY.attSignedPD,''''),INVTRY.YRPUB HAVING((COUNT (INVTRY。[INDEX]))> 1))I previously posted re. this, but thought I''d try again with a summary offacts. I have an Access 2000 MDB with a SQL Server 7 back end. There is a view that is linked to the database via ODBC and has been in place for several years without any performance problems. Recently I added a couple of fields to the output of the view, and it became very slow when scrolling. When just opened in the database window, the linked view takes about a second to scroll down one screen. When opened in the form (in Continuous Form view), it takes about 2-3 seconds. It used to scroll just about instantaneously. I tried removing the few fields I added to restore the view to its previous form, but it had no effect. The view was still much slower than it had been. The total number of records returned from the view is about 1300, so it''s not a large number of records. The view has about 25 fields. I found that when I link the view in the MDB without specifying a unique index, it scrolls very quickly -- almost instantaneously. But when I specify the unique index, it is slow. Since the view needs to be edited, it needs the unique index defined. As noted, it''s been in place for years, with a unique index defined, yet without the slowness. Any ideas as to what might have caused this and what might be done would be appreciated. I''ve included the SQL for the view below. Thanks, Neil SQL FOR MAIN VIEW: SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1, INVTRY.attFirstEdition, INVTRY.attSigned, ISNULL(INVTRY.attSignedPD, '' '') SignedCond, INVTRY.YRPUB, INVTRY.PRICE, INVTRY.Web, INVTRY.Status, INVTRY.WebStatusPending, INVTRY.ActivateDate, INVTRY.DeactivateDate, INVTRY.WebAddedBatchID, INVTRY.AllowDuplicate, INVTRY.WebAction, INVTRY.WebActionPending, INVTRY.DateModified, INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage, INVTRY.HImage, INVTRY.AdCode, CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL THEN - 1 ELSE 0 END AS OnWeb FROM vwInventory_Dupes INNER JOIN (WebStatus INNER JOIN (INVTRY INNER JOIN tabStatus ON INVTRY.Status = tabStatus.Status) ON WebStatus.WebStatus = INVTRY.Web) ON (vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD, '' '')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND (vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND (vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND (vwInventory_Dupes.TITLE = INVTRY.TITLE) WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) = 1)) SQL FOR vwInventory_Dupes: SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author, Cast(attFirstEdition AS tinyint) FirstEd, Cast(attSigned AS tinyint) Signed, ISNULL(INVTRY.attSignedPD, '' '') SignedCond, INVTRY.YRPUB YearPub FROM WebStatus INNER JOIN (INVTRY INNER JOIN tabStatus ON INVTRY.Status = tabStatus.Status) ON WebStatus.WebStatus = INVTRY.Web WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe) = 1)) GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1, Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint), ISNULL(INVTRY.attSignedPD, '' ''), INVTRY.YRPUB HAVING (((COUNT(INVTRY.[INDEX])) > 1)) 这篇关于慢MDB链接视图的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!
10-28 17:10