什么是Access应用程序的最大合理大小？ [英] Whats the maximum sensible size for an Access application?
问题描述
大家好，
我开发了一个很好的小数据库来分析设备日志文件。
项目证明非常有用，人们开始向我发送越来越多的数据。
事实证明，我很快就遇到了2GB的限制，所以我拆分数据库并使用带有链接表的前端数据库为每个设备启动数据库。生活很美好。
目前我在链接表中有12GB的数据和通过前端运行3000万条记录的查询。
查询运行得非常慢（至少几小时）并且显然只会因为更多的数据而变得更糟...
我想知道Access现在是一个合理的架构还是我会得到SQL服务器实现的更好性能。我们的公司网络非常慢，所以我很想在我的电脑上托管数据，如果我理解正确的话，我不能在没有服务器的情况下运行SQL吗？
另一种想法是处理数据离线进入OLAP多维数据集，然后查询多维数据集而不是表格。
我在这里超出了我的深度，使用Access / VBA仅用了3个月就会非常感谢一些有经验的人用户观点什么是明智的。
谢谢，
Mark。
通过转移到SQL服务器肯定会更好，它当然可以在PC上运行SQL Server，我可以在我的网站上运行。
但是你不会获得SQL的全部好处服务器，如果你不在propper服务器上运行它。服务器规范在从SQL Server获得的任何性能提升中发挥着重要作用。确实规格将根据您将数据库的用途而改变。
如果您想在做出决定之前尝试一段时间，那么我建议您安装SQL将Server Express与SQL Server Management Studio Express一起安装到您的PC上。
这些都可以从microsoft站点免费获得
如果你的运行查询在3000万条记录中，也可能值得花些时间正确设计索引，并研究是否正确设置了所有字段类型。
3个月内12 gb对我来说听起来很多（其他人可能会有不同看法）。
你有没有试过做一个紧凑的&不时修理？如果您运行了很多插入，特别是在索引表中，您的数据库可以快速增长。
另一种选择可能是在单独的后端中拆分旧数据（每月一个，或者每个设备一个），取决于你需要的东西。
你存储什么信息来获得3000万条记录？
Hey Delerna ，
感谢您的提示。我以为SQL Server必须在服务器操作系统上运行。我会在我的电脑上安装快速版本，看看SQL中的dbs会发生什么。
谢谢，
马克。
Hi All,
I developed a nice little database for analysis of equipment log files.
The project proved very useful and people started sending me more and more data.
It turned out I ran up against the 2GB limit quickly so I split the db and started a db for each piece of equipment using a front end db with linked table. Life was good.
Currently I have 12GB of data in linked tables and queries running through the front end on 30 million records.
Queries run really slowly (couple hours at least) and clearly will only get worse with more data ...
I''m wondering if Access is now a sensible architecture or if I would get better performance from an SQL server implementation. Our corporate network is really slow so I''m keen to host the data on my PC and if I understand correctly I cant run SQL without a server?
Another thought is to process data into OLAP cubes offline and then query the cubes instead of the tables.
I''m way out of my depth here, only 3 months using Access/VBA and would really appreciate some experienced users perspective of whats sensible.
Thanks,
Mark.
You will definitely be better off by moving over to SQL server, it is certainly possible to run SQL Server on a PC, I do it on mine.
However you will not gain the full benefit of SQL server if you don''t run it on a propper server. The server specs play a large role in any performance gain you will get from SQL Server. Indeed the specs will change according to the use you will put the database to.
If you want to try it out for a while before making a decision then I would suggest that you install SQL Server Express onto your PC along with SQL Server Management Studio Express.
Both of these are freely available from the microsoft site
If your running queries on 30 million records, it may also be worth spending some time on designing your index''s properly, and looking into whether you have set all your field types correctly.
12 gb in 3 months sounds like a lot to me (others may think differently).
Have you tried doing a compact & repair from time to time? If your running alot of inserts, specifically in indexed tables, your database can grow quite fast.
Another option could be to split out old data in seperate backends (one for each month, or one for each piece of equipment), depending on what you need.
What information are you storing to get 30 million records?
Hey Delerna,
Thanks for the tips. I''d thought SQL Server had to run on a server OS. I''ll install the express edition on my PC and see what happens with the dbs in SQL.
Thanks,
Mark.
这篇关于什么是Access应用程序的最大合理大小？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
