访问97，从sql server插入慢

本文介绍了访问97，从sql server插入慢的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我有一个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 asql server table into a local table to run reportsif the local table is part of the reporting MDB, the insert statement(16 fields) takes less than 30secs, but because of db-bloat, I movedthe local table to a 2nd MDBand per postings, this 2nd MDB is copied into a folder and linked as a''temp'' MDB every time I run my reporting mdbalso, per postings, my main form opens a recordset to a table in the''temp'' MDB to prevent continuous LDB activitybut 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 terminalserver via a 1-GB networkupdating to the ''temp'' MDB is slow with / without any indexing on thelocal tablethe ''temp'' MDB has been compacted, no differenceI created a new temp MDB and imported the tables, no differencewhat am I missing ?推荐答案 这真的不是那么多记录。为什么不让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 runreports against linked tables or pass through queries? It wouldeliminate 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 这真的不是那么多记录。为什么不让mdb / e运行 报告链接表或通过查询？它会消除对数据副本的维护。 - Tim http://www.ucs.mun.ca/~tmarshal/ ^ o< /＃）Burp-beep，burp-beep，burp-beep？ - Quaker Jake / ^^Whatcha doin？ - 同上TIM-MAY !! - MeThat''s really not that many records. Why not just have the mdb/e runreports against linked tables or pass through queries? It wouldeliminate 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 几乎可以肯定的是，一个包含77,000行数据的报告，从可用性的角度来看，需要工作。考虑在SQL上创建一个视图 服务器以确保提取和操作全部完成 服务器端，或传递查询9（但是，我的经验访问，Jet， ODBC和SQL Server就是这样，除非您的连接是Jet认为的，否则组合生成的SQL通常是高效的。如果 你只能通过网络将实际数据显示在 人类可用上。报告，我敢打赌你不会遇到性能问题 （除非桌面设计出现严重错误）。 * Jet'' 复杂的标准版本之间有所不同，但如果它认为过于复杂，则需要。它将带来或试图将所有基础数​​据带到用户的机器上，这将是什么 似乎是更高效的假设 比在服务器上执行它。 Larry Linson Microsoft Access MVPAlmost certainly, a report with 77,000 rows of data is, from a usabilitypoint of view, something that needs work. Consider creating a View on SQLServer to ensure that the extraction and manipulation is all doneserver-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 considerscomplex*, the SQL generated by the combination is generally efficient). Ifyou 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, butif it deems "too complex" it will bring, or try to bring,all the underlying data to the user''s machine on whatseems to be the assumption that will be more efficientthan doing it on the server.Larry LinsonMicrosoft Access MVP 这篇关于访问97，从sql server插入慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！