What is the most common and easy to implement solution to improve speed for SQL Server 2008R2 database & .Net 3.5 application.
最常见、最容易实现的解决方案是提高SQL Server 2008R2数据库和。net 3.5应用程序的速度。
We have an application with the following attributes:
- small number of simultaneous clients (~200 at MOST).
- complex math operations on SQL server side
- we are imitating something to oracle's row-level security (Thus using tvf's and storedprocs instead of directly querying tables) -The main problem is that users perform high amount of updates/inserts/deletes/calculations, and they freak out because they need to wait for pages to reload while those actions are done.
我们有一个具有以下属性的应用程序:-少量同时存在的客户端(至多200个)。——复杂的数学操作SQL服务器端——我们是模仿一些甲骨文级安全(因此使用tvf storedprocs而不是直接查询表)——主要问题是用户执行大量的更新/插入/删除/计算,他们发疯,因为需要等待页面重新加载而行动。
The questions I need clarification on are as follows:
我需要澄清的问题如下:
- What is faster: returning whole dataset from sql server and performing math functions on C# side, or performing calculation functions on sql side (thus, not returning extra columns). Or is it only hardware dependant?
- 更快的方法是:从sql server返回整个数据集，在c#端执行数学函数，或者在sql端执行计算函数(因此，不返回额外的列)。还是仅仅依赖于硬件?
- Will caching improve performance (For example if we add redis cache). Or caching solutions only feasible for large number of clients?
- 缓存将提高性能(例如，如果我们添加redis缓存)。或者缓存解决方案只适用于大量客户机?
- Is it a bad practice to pre-calculate some of the data and store somewhere in the database (so, when user will request, it will already be calculated). Or this is what caching suppose to do? If this is not a bad practice, how do you configure SQL server to do calculations when there are available resources?
- 预先计算一些数据并将其存储在数据库中的某个位置(因此，当用户请求时，它将已经被计算)，这是不是一种不好的做法?或者这就是缓存的作用?如果这不是一个坏习惯，那么当有可用资源时，如何配置SQL server进行计算?
- How caching can improve performance if it still needs to go to the database and see if any records were updated?
- 如果缓存仍然需要访问数据库并查看是否更新了任何记录，那么如何改进性能呢?
general suggestions and comments are also welcome.
欢迎提出一般性建议和意见。
1 个解决方案
#1
7
Let's separate the answer to two parts, performance of your query execution and caching to improve that performance. I believe you should start with addressing the load on your SQL server and try to optimize process running on it to the maximum, this should resolve most of the need to implement any caching.
让我们将答案分为两个部分，执行查询执行和缓存，以提高性能。我认为，您应该从解决SQL服务器上的负载开始，并尝试优化运行到最大的进程，这应该解决了实现任何缓存的大部分需求。
From your question it appears that you have a system that is used for both transactional processing and also for aggregations/calculations, this will often result in conflicts when these two tasks lock each other resources. A long query performing math operations may lock/hold an object required by the UI. Optimizing these systems to work side-by-side and improving the query efficiency is the key for having increased performance.
从您的问题来看，您似乎有一个既用于事务处理又用于聚合/计算的系统，这常常导致在这两个任务锁定彼此资源时发生冲突。执行数学操作的长查询可以锁定/保存UI所需的对象。优化这些系统并行工作并提高查询效率是提高性能的关键。
To start, I'll use your questions. What is faster? depends on the actual aggregation you are performing, if you're dealing with a set operations, i.e. SUM/AVG of a column, keep it in SQL, on the other hand if you find yourself having a cursor in the procedure, move it to C#. Cursors will kill your performance! You asked if it's bad-practice to aggregate data aside and later query that repository, this is the best practice :). You'll end up with having one database catering the transactional, high-paced clients and another database storing the aggregated info, this will be quickly and easily available for your other needs. Taking it to the next step will result with you having a data warehouse, so this is definitely where you want to be heading when you have a lot information and calculations.
首先，我将用你们的问题。速度是什么呢?依赖于您正在执行的实际聚合，如果您正在处理一个集合操作，即一个列的SUM/AVG，将它保存在SQL中，另一方面，如果您发现自己在过程中有游标，那么将它移到c#。游标会破坏您的性能!您会问，将数据聚合到一边并在稍后查询该存储库是否存在不良实践，这是最佳实践:)。最终，您将拥有一个数据库来满足事务性、高速度的客户端和另一个存储聚合信息的数据库，这将快速且容易地满足您的其他需求。将它带到下一个步骤将会导致您拥有一个数据仓库，所以当您有大量的信息和计算时，这绝对是您想要前进的方向。
Lastly, caching, this is tricky and really depends on the specific nature of your needs, I'd say take the above approach, spend the time in improving the processes and I expect the end result will make caching redundant.
最后，缓存，这很棘手，实际上取决于您的需求的特定性质，我认为采用上述方法，花时间改进进程，我预计最终结果将使缓存变得多余。
One of your best friends for the task is SQL Profiler, run a trace on stmt:completed to see what are the highest duration/io/cpu and pick on them first.
您最好的朋友之一是SQL Profiler，在stmt:complete上运行一个跟踪，看看什么是最高持续时间/io/cpu，并首先选择它们。
Good luck!
好运！
#1
7
Let's separate the answer to two parts, performance of your query execution and caching to improve that performance. I believe you should start with addressing the load on your SQL server and try to optimize process running on it to the maximum, this should resolve most of the need to implement any caching.
让我们将答案分为两个部分，执行查询执行和缓存，以提高性能。我认为，您应该从解决SQL服务器上的负载开始，并尝试优化运行到最大的进程，这应该解决了实现任何缓存的大部分需求。
From your question it appears that you have a system that is used for both transactional processing and also for aggregations/calculations, this will often result in conflicts when these two tasks lock each other resources. A long query performing math operations may lock/hold an object required by the UI. Optimizing these systems to work side-by-side and improving the query efficiency is the key for having increased performance.
从您的问题来看，您似乎有一个既用于事务处理又用于聚合/计算的系统，这常常导致在这两个任务锁定彼此资源时发生冲突。执行数学操作的长查询可以锁定/保存UI所需的对象。优化这些系统并行工作并提高查询效率是提高性能的关键。
To start, I'll use your questions. What is faster? depends on the actual aggregation you are performing, if you're dealing with a set operations, i.e. SUM/AVG of a column, keep it in SQL, on the other hand if you find yourself having a cursor in the procedure, move it to C#. Cursors will kill your performance! You asked if it's bad-practice to aggregate data aside and later query that repository, this is the best practice :). You'll end up with having one database catering the transactional, high-paced clients and another database storing the aggregated info, this will be quickly and easily available for your other needs. Taking it to the next step will result with you having a data warehouse, so this is definitely where you want to be heading when you have a lot information and calculations.
首先，我将用你们的问题。速度是什么呢?依赖于您正在执行的实际聚合，如果您正在处理一个集合操作，即一个列的SUM/AVG，将它保存在SQL中，另一方面，如果您发现自己在过程中有游标，那么将它移到c#。游标会破坏您的性能!您会问，将数据聚合到一边并在稍后查询该存储库是否存在不良实践，这是最佳实践:)。最终，您将拥有一个数据库来满足事务性、高速度的客户端和另一个存储聚合信息的数据库，这将快速且容易地满足您的其他需求。将它带到下一个步骤将会导致您拥有一个数据仓库，所以当您有大量的信息和计算时，这绝对是您想要前进的方向。
Lastly, caching, this is tricky and really depends on the specific nature of your needs, I'd say take the above approach, spend the time in improving the processes and I expect the end result will make caching redundant.
最后，缓存，这很棘手，实际上取决于您的需求的特定性质，我认为采用上述方法，花时间改进进程，我预计最终结果将使缓存变得多余。
One of your best friends for the task is SQL Profiler, run a trace on stmt:completed to see what are the highest duration/io/cpu and pick on them first.
您最好的朋友之一是SQL Profiler，在stmt:complete上运行一个跟踪，看看什么是最高持续时间/io/cpu，并首先选择它们。
Good luck!
好运！