I have a table that has grown to over 1 million records... today (all valid)
我有一张表已经发展到超过100万条记录......今天(全部有效)
I need to speed it up... would Table Partitioning be the answer? If so can i get some help on building the query?
我需要加快速度......表格分区会成为答案吗?如果是这样,我可以在构建查询时获得一些帮助吗?
The table has 4 bigint value keys and thats all with a primary key indexed and a index desc on userid the other values are at max 139 (there is just over 10,000 users now)
该表有4个bigint值键,所有这些键都有索引的主键和用户ID上的索引desc,其他值最大为139(现在只有10,000多个用户)
Any help or direction would be appreciated :)
任何帮助或方向将不胜感激:)
4 个解决方案
#1
you probably just need to tune your queries and/or indexes. 1 million records shouldn't be causing you problems. I have a table with several hundred million records & am able to maintain pretty high performance. I have found the SQL Server profiler to be pretty helpful with this stuff. It's available in SQL Server Management Studio (but not the express version, unfortunately). You can also do Query > Include Actual Execution Plan to see a diagram of where time is being spent during the query.
您可能只需要调整查询和/或索引。 100万条记录不应该导致你的问题。我有一张拥有数亿记录的表,并且能够保持相当高的性能。我发现SQL Server探查器对这些东西非常有帮助。它可以在SQL Server Management Studio中使用(但不幸的是,不是快速版本)。您还可以执行查询>包括实际执行计划,以查看查询期间花费时间的图表。
#2
You should investigate your indexes and query workload before thinking about partitioning. If you have done a large number of inserts, your clustered index may be fragmented.
在考虑分区之前,您应该调查索引和查询工作负载。如果已完成大量插入操作,则聚簇索引可能会碎片化。
Even though you are using SQL Server Express you can still profile using this free tool: Profiler for Microsoft SQL Server 2005/2008 Express Edition
即使您使用的是SQL Server Express,您仍然可以使用此免费工具进行配置:适用于Microsoft SQL Server 2005/2008 Express Edition的Profiler
#3
I agree with the other comments. With a reasonably small database (largest table 1MM records) it's unlikely that any activity in the database should provide a noticeable load if queries are optimized and the rest of the code isn't abusing the database with redundant queries. It's a good opportunity to get a feeling for the interplay between database queries and the rest of the code.
我同意其他意见。使用相当小的数据库(最大的表1MM记录),如果优化查询并且其余代码不使用冗余查询滥用数据库,则数据库中的任何活动都不太可能提供明显的负载。这是一个了解数据库查询和其他代码之间相互作用的好机会。
#4
See my experiments on sql table partitioning here [http://faiz.kera.la/2009/08/02/does-partitioning-improve-performance-for-sql-tables/]. Hope this is helpful for you... And for your case, 1M is not a considerable figure. May be you need to fine tune the queries than going for partitioning.
请参阅我在sql table partitioning上的实验[http://faiz.kera.la/2009/08/02/does-partitioning-improve-performance-for-sql-tables/]。希望这对你有所帮助......对于你的情况,1M不是一个相当大的数字。可能需要微调查询而不是分区。
#1
you probably just need to tune your queries and/or indexes. 1 million records shouldn't be causing you problems. I have a table with several hundred million records & am able to maintain pretty high performance. I have found the SQL Server profiler to be pretty helpful with this stuff. It's available in SQL Server Management Studio (but not the express version, unfortunately). You can also do Query > Include Actual Execution Plan to see a diagram of where time is being spent during the query.
您可能只需要调整查询和/或索引。 100万条记录不应该导致你的问题。我有一张拥有数亿记录的表,并且能够保持相当高的性能。我发现SQL Server探查器对这些东西非常有帮助。它可以在SQL Server Management Studio中使用(但不幸的是,不是快速版本)。您还可以执行查询>包括实际执行计划,以查看查询期间花费时间的图表。
#2
You should investigate your indexes and query workload before thinking about partitioning. If you have done a large number of inserts, your clustered index may be fragmented.
在考虑分区之前,您应该调查索引和查询工作负载。如果已完成大量插入操作,则聚簇索引可能会碎片化。
Even though you are using SQL Server Express you can still profile using this free tool: Profiler for Microsoft SQL Server 2005/2008 Express Edition
即使您使用的是SQL Server Express,您仍然可以使用此免费工具进行配置:适用于Microsoft SQL Server 2005/2008 Express Edition的Profiler
#3
I agree with the other comments. With a reasonably small database (largest table 1MM records) it's unlikely that any activity in the database should provide a noticeable load if queries are optimized and the rest of the code isn't abusing the database with redundant queries. It's a good opportunity to get a feeling for the interplay between database queries and the rest of the code.
我同意其他意见。使用相当小的数据库(最大的表1MM记录),如果优化查询并且其余代码不使用冗余查询滥用数据库,则数据库中的任何活动都不太可能提供明显的负载。这是一个了解数据库查询和其他代码之间相互作用的好机会。
#4
See my experiments on sql table partitioning here [http://faiz.kera.la/2009/08/02/does-partitioning-improve-performance-for-sql-tables/]. Hope this is helpful for you... And for your case, 1M is not a considerable figure. May be you need to fine tune the queries than going for partitioning.
请参阅我在sql table partitioning上的实验[http://faiz.kera.la/2009/08/02/does-partitioning-improve-performance-for-sql-tables/]。希望这对你有所帮助......对于你的情况,1M不是一个相当大的数字。可能需要微调查询而不是分区。