访问97，从sql server插入慢 [英] access 97, insert from sql server slow
问题描述
我有一个access97报告mdb，它将数据（77,000行）从
sql server表中提取到本地表中以运行报告
如果本地表是报告MDB的一部分，则插入语句
（16个字段）不到30秒，但由于db-bloat，我移动了
本地表到第二个MDB
和每个帖子，这个第二个MDB被复制到一个文件夹中并且每次链接为一个
''temp''MDB我运行我的报告mdb
，每个贴子，我的主窗体打开一个记录集到
''临时''MDB中的表，以防止连续LDB活动
但是插入语句现在需要20分钟....
报告MDB和临时MDB都是相同的终端服务器
（在不同的驱动器上）和sql server通过1 GB网络连接到终端
服务器
更新到''temp''MDB很慢/没有任何索引
本地表
''临时''MDB已经压缩，没有区别
我创建了一个新的临时MDB并导入了表格，没有区别
我错过了什么？
I''ve got an access97 reporting mdb that pulls data (77,000 rows) from a
sql server table into a local table to run reports
if the local table is part of the reporting MDB, the insert statement
(16 fields) takes less than 30secs, but because of db-bloat, I moved
the local table to a 2nd MDB
and per postings, this 2nd MDB is copied into a folder and linked as a
''temp'' MDB every time I run my reporting mdb
also, per postings, my main form opens a recordset to a table in the
''temp'' MDB to prevent continuous LDB activity
but the insert statement now takes 20 mins....
both the reporting MDB and the temp MDB are on the same terminal server
(on different drives) and the sql server is connected to the terminal
server via a 1-GB network
updating to the ''temp'' MDB is slow with / without any indexing on the
local table
the ''temp'' MDB has been compacted, no difference
I created a new temp MDB and imported the tables, no difference
what am I missing ?
推荐答案
le*********@natpro.com 写道：
le*********@natpro.com wrote:
我有一个access97报告mdb，它将数据（77,000行）从
sql server表中提取到本地表中运行报告
I''ve got an access97 reporting mdb that pulls data (77,000 rows) from a
sql server table into a local table to run reports
这真的不是那么多记录。为什么不让mdb / e运行
报告链接表或通过查询？它会消除对数据副本的维护。
-
Tim http://www.ucs.mun.ca/~tmarshal/
^ o<
/＃）Burp-beep，burp-beep，burp-beep？ - Quaker Jake
/ ^^Whatcha doin？ - 同上TIM-MAY !! - 我
That''s really not that many records. Why not just have the mdb/e run
reports against linked tables or pass through queries? It would
eliminate maintenance of a copy of your data.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
最终报告基于许多表的查询（包括
sql server和access）
用户选择的过滤
和当前的sql-server应用程序不允许对其
表进行索引
和.....
自发布以来，我更改了插入查询以仅插入一个字段
（6-char文本）它仍然很慢
如果我打开临时MDB并查看表中的内容，它将是空的，直到
为空到了20多分钟......所以这不是数据转移/
运球问题......
就像整个事情暂停在sql server等待
查询运行
我会尝试插入而不使用终端服务器
Tim Marshall写道：
the final report is based on a query of many tables (consisting of both
sql server and access)
with user-selected filtering
and the current sql-server app does not allowing for indexing on its
tables
and .....
Since the posting, I changed the insert query to insert just one field
(6-char text) and it''s still slow
And if I open the temp MDB and look at the contents of the table, it''s
empty until to 20-odd minutes are up... so it''s not a data transfer /
dribble issue...
It''s like the whole thing is suspended on the sql server waiting for
the query to run
I''ll try the insert without using the terminal server
Tim Marshall wrote:
le * ********@natpro.c om 写道：
le*********@natpro.com wrote:
我有一个access97报告mdb从
sql中提取数据（77,000行）服务器表进入本地表运行报告
I''ve got an access97 reporting mdb that pulls data (77,000 rows) from a
sql server table into a local table to run reports
这真的不是那么多记录。为什么不让mdb / e运行
报告链接表或通过查询？它会消除对数据副本的维护。
-
Tim http://www.ucs.mun.ca/~tmarshal/
^ o<
/＃）Burp-beep，burp-beep，burp-beep？ - Quaker Jake
/ ^^Whatcha doin？ - 同上TIM-MAY !! - Me
That''s really not that many records. Why not just have the mdb/e run
reports against linked tables or pass through queries? It would
eliminate maintenance of a copy of your data.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
< le ********* @ natpro.comwrote
<le*********@natpro.comwrote
我有一个access97报告mdb，它将数据从一个sql server表中拉出
（77,000行）到一个本地
表来运行报告
I''ve got an access97 reporting mdb that pulls data
(77,000 rows) from a sql server table into a local
table to run reports
几乎可以肯定的是，一个包含77,000行数据的报告，从可用性的角度来看，需要工作。考虑在SQL上创建一个视图
服务器以确保提取和操作全部完成
服务器端，或传递查询9（但是，我的经验访问，Jet，
ODBC和SQL Server就是这样，除非您的连接是Jet认为的，否则组合生成的SQL通常是高效的。如果
你只能通过网络将实际数据显示在
人类可用上。报告，我敢打赌你不会遇到性能问题
（除非桌面设计出现严重错误）。
* Jet'' 复杂的标准版本之间有所不同，但如果它认为过于复杂，则需要
。它将带来或试图将所有基础数据带到用户的机器上，这将是什么
似乎是更高效的假设
比在服务器上执行它。
Larry Linson
Microsoft Access MVP
Almost certainly, a report with 77,000 rows of data is, from a usability
point of view, something that needs work. Consider creating a View on SQL
Server to ensure that the extraction and manipulation is all done
server-side, or a pass-through Query 9 (but, my experience with Access, Jet,
ODBC, and SQL Server has been that, unless your joins are what Jet considers
complex*, the SQL generated by the combination is generally efficient). If
you only bring across the network the actual data to appear on a
"human-usable" report, I''ll bet you would not have performance problems
(unless there is something seriously wrong with the Table design).
* Jet''s criteria for "complex" vary between versions, but
if it deems "too complex" it will bring, or try to bring,
all the underlying data to the user''s machine on what
seems to be the assumption that will be more efficient
than doing it on the server.
Larry Linson
Microsoft Access MVP
这篇关于访问97，从sql server插入慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
