改善SQL Server视图的读取时间 [英] Improving the reading time of an sql server view

问题描述

我有一个正在获取大量数据的视图.我正在通过Web方法调用此视图.所以我需要使此视图的读取速度更快，因为它在jquery映射上显示数据并且我需要它是快速的.所以我本来想将其存储在会话中而不是每次都访问数据库，但是将其作为会话存储对于大量用户来说不是一个好主意.我也考虑过将其存储在缓存中，但用户可能会禁用缓存或清除缓存.缓存.因此，有什么建议可以快速读取此视图吗?

谢谢.

Hi ,

I have a view that is getting a large data.And i am calling this view through a web method.So i need to make reading this view faster since it is displaying data on a jquery map and i need it to be fast.So i had the idea of storing it in a session instead of hitting the database everytime but storing it in as session is not a good idea for large number of users.Also i thought about storing it in the cache but the user might disable caching or clear the cache.So is there any suggestion to read this view in a fast way ?

Thanks.

推荐答案

使用SQL Management Studio，您可以查看执行计划.这向您展示了SQL如何构建结果集.

有些人通过阅读这些内容并从中挤出最后几毫秒来赚取非常丰厚的收入.

以下是一个代码项目文章，其中包括一个非常简单的示例.

SQL优化教程-了解数据库执行计划(1) [^ ]

这一切都与拥有正确的索引有关，以便SQL可以以最小的努力连接表并过滤行.执行计划显示您在查询的每个点花费了多长时间，因此您可以查看哪些零件花费了您最多的处理时间.

如果您在该位置获得了正确的索引，而又无法再挤压查询了.许多人需要考虑改用Analysis Services.如果您的查询具有复杂的聚合，则可以使用Analysis Services获得巨大的报表改进.概括地说，它预先汇总了所有可能的结果.以下是分步介绍.

http://www.scribd.com/doc/88610541/SQL-Server-Analysis- Services-tutorial [ ^ ]

尽管如此，也有一些可行的应用方法.我同意在会话中放入大量数据不是一个好主意.尤其是如果您已经实现了网络园艺，并且必须将项目序列化进出会话状态.

如果您想采用应用程序方法，我将编写一个自托管的WCF服务.这是WCF服务的简介.我总是选择自托管，因为您不会遇到与Web园艺相关的多进程问题，也不会启动与IIS工作进程相关的延迟问题.

http://invalidcast.com/2010/04/a-gentle-introduction-to-wcf [^ ]

创建此类用于缓存数据的服务时，了解并发性和实例化非常重要，以确保后续调用可以访问相同的数据.以下是一篇出色的文章，解释了论文的行为.

WCF并发(单，多和可重入)和限制 [ ^ ]

Using SQL Management Studio you can view the execution plan. This shows you how SQL builds your result set.

There are people who earn very good money reading these and squeezing the last few milliseconds out of them.

The following is a code project article with covers doing this with a very simple example.

SQL Tuning Tutorial - Understanding a Database Execution Plan (1)[^]

It''s all about having the right indexes so that SQL can join the tables and filter the rows with the least effort. The execution plan shows you how long was spent at each point of the query so you can see which parts cost you the most processing time.

If when you get the correct indexes in the place and you can''t squeeze any more out your query. You many need to consider using Analysis Services instead. Where you have queries with complex aggregations, you can get a huge reporting improvement by using Analysis Services. In a nutshell it pre-aggregates all the possible results. The following is a step by step introduction.

http://www.scribd.com/doc/88610541/SQL-Server-Analysis-Services-tutorial[^]

Despite this there are also application approaches to this that would also work. I agree putting large quantities of data into the session isn''t a good idea. Especially if you''ve implemented web gardening and are having to serialise items in and out of your session state.

If you want to take the application approach, I''d write a self hosted WCF service. This is an introduction to WCF services. I''d always choose self hosted as you don''t get multi-process issues associated with web gardening or start up latency issues associated with IIS worker processes.

http://invalidcast.com/2010/04/a-gentle-introduction-to-wcf[^]

When creating this kind service which is caching data it''s important to understand Concurrency and Instancing to ensure subsequent calls have access to the same data. The following is an excellent article which explains the behaviour of theses.

WCF Concurrency (Single, Multiple, and Reentrant) and Throttling[^]

这篇关于改善SQL Server视图的读取时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！