如何测试我的数据库速度? (学习)
我设计了一个数据库。没有带有索引的列,也没有任何用于优化的代码。我确信我应该为某些列建立索引,因为我经常搜索它们。
我的问题是如何测试数据库的任何部分是否会变慢? ATM 我正在使用 sqlite,我将根据我的主机提供商切换到 MS Sql 或 MySql。在每个表中创建 100,000 条记录就足够了吗?或者在 sqlite 中总是很快,而我需要做 100 万次?在数据库变慢之前我需要 1000 万吗?
还有我该如何计时?我正在使用 C#,所以我应该使用 StopWatch 还是应该使用 ADO.NET/Sqlite 函数?
I have design a database. Theres no columns with indexing, nor any code for optimizing. I am positive i should index certain columns since i search them a lot.
My question is HOW do i test if any part of my database will be slow? ATM I am using sqlite and i will be switching to either MS Sql or MySql based on my host provider. Will creating 100,000 records in each table be enough? Or will that always be fast in sqlite and i need to do 1mil? Do i need 10mil before a database will become slow?
Also how do i time it? I am using C# so should i use StopWatch or is there a ADO.NET/Sqlite function i should use?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
这实际上是一个随着时间的推移监控事物的问题,因为最佳执行计划会随着您添加更多数据而改变。如果您想进行测试,那么 1000 万行应该足以使大多数性能问题变得可见,但您还需要尝试使用与真实数据相同的基数特征来填充测试数据。 SQL Server 具有许多内置的性能监控功能(动态管理视图、XEvents、SQL Trace/Profiler),但我不确定如果您使用托管解决方案,您将能够访问多少功能。为了监视数据库外部的性能,ADO.NET 支持跟踪,但我自己从未使用过它。
Its really a question of monitoring things over time as optimal execution plans will change as you add more data. If you want to test then 10 million rows should be adequate to make most performance issues visible but you will also need to try and populate the test data with the same cardinality characteristics as the real data will have. SQL Server has lots of performance monitoring functionality built in (Dynamic Management Views, XEvents, SQL Trace/Profiler) but I'm not sure how much you would be able to access if you are on a hosted solution. For monitoring performance outside the database ADO.NET supports tracing but I've never used it myself.
关于你的第二个问题 SqlLite 几乎总是比 MsSql 更快,因为 SqlLite 中的数据库位于内存中,就像 MsSql 中的数据库位于硬盘上一样
About your second question SqlLite is almost always faster than MsSql since your database in SqlLite is in memory as in MsSql your database is on a hard disk