慢MDB链接视图 [英] Slow MDB Linked View
问题描述
我之前发过了。这个，但我想再次尝试使用
事实的摘要。
我有一个带有SQL Server 7后端的Access 2000 MDB。有一种观点认为
通过ODBC链接到数据库并且已经存在了好几年
而没有任何性能问题。
最近我在视图的输出中添加了几个字段，滚动时它变得非常慢。刚刚在数据库窗口中打开时，
链接视图大约需要一秒钟向下滚动一个屏幕。在
表格中打开（在连续表格视图中），大约需要2-3秒。它过去常常滚动
。
我尝试删除我添加的几个字段以恢复之前的视图
形式，但没有效果。视图仍然比以前慢得多。
从视图返回的记录总数大约是1300，所以它是
没有大量的记录。该视图有大约25个字段。
我发现当我在MDB中链接视图而没有指定唯一的
索引时，它会非常快速地滚动 - 几乎是瞬间完成的。但是当我指定
唯一索引时，它很慢。由于需要编辑视图，因此需要定义唯一索引
。
如上所述，它已经存在多年了，唯一索引定义，但没有缓慢的
。任何关于可能导致这个和什么
可能会完成的想法将不胜感激。我已将视图中的SQL包含在下面的
。
谢谢，
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。状态，
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 of
facts.
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))
推荐答案
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 of
facts.
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))
感谢您的注释。是的，没有效果。还尝试了索引
向导，看看我是否错过了什么，但它没有任何
推荐。
Tom van Stiphout <无************* @ cox.net>在留言中写道
新闻：3s ******************************** @ 4ax.com ...
Thanks for the note. Yeah, didn''t have an effect. Also tried the index
wizard to see if I had missed something, but it didn''t have any
recommendations.
"Tom van Stiphout" <no*************@cox.net> wrote in message
news: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 of
facts.
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))
问：如果你在视图上打开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 does
it 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.aspx
the recordset should be filled at the rate of 100 records
every 10 seconds (!) That seems wrong! but you might
like to try changing it.
You can force form to load an entire recordset before
display. This means that there is a long pause before
the display is filled, but scrolling is then immediate.
The only way I /know/ to force this behaviour is to
put combo-boxes on the form. You haven''t gotten confused
by this kind of behaviour have you?
(david)
"Neil" <no****@nospam.net> wrote in message
news: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 of
facts.
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链接视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
