如何在SQL Server 2008中使用标量/表值函数来提高查询的性能 [英] How to improve performance of a query with scalar /table valued function in SQL Server 2008

问题描述

你好朋友，



我有一个提取报告的程序，我使用了两个标量乐趣，还有一个有价值的桌子。



现在因为数据增加太多程序太慢了。



:(



我怎么能解决这个危险的问题。



i做谷歌搜索，但我不满意。



请建议我



Shreeniwas

Hello friends,

I have a procedure to extract report ,i was used two scalar funs,and a table valued fun.

now as data increased too much the procedure too slow .

:(

how i can resolve this dangerous problem .

i did googling but i do not satisfy.

Pls suggest me

Shreeniwas

推荐答案

因为你没有为我们提供DDL或DML审查和评估，这里有一些通用的想法。



如果有 JOIN s，请确保 ON 子句中使用的列已编入索引。



如果在<$中使用了一些非键列c $ c>其中子句，请考虑将它们作为包含列添加到索引中。



使用SQLParameter类将参数传递给SQL语句不是带有嵌入值的串联字符串。



阅读有关提高SQL Server性能的Microsoft文档 [ ^ ]

Since you did not provide DDL or DML for us to review and evaluate, here are a few general purpose ideas.

If there are JOINs, ensure that the columns used in the ON clause are indexed.

If a few non-Key columns are used in Where clauses, consider adding them to the index as an included column.

Use the SQLParameter Class to pass parameters to SQL statements not concatenated strings with embedded values.

Read Microsoft documentation on Improving SQL Server Performance[^]

Mike Meinz向您推荐了一些提示。



您还应该检查一下。



1.

你的sp检查哪个功能的执行时间最长。

执行计划会告诉你功能的成本。



2.

检查执行计划，是执行扫描还是寻找表列？

如果没有几个。从包含大量行的表中获取行，然后在特定列上应用索引。



3.

如果您使用交叉申请使用表函数并且它正在重新获取大量数据然后你应该创建一个视图而不是函数，因为如果对于这个scenerio你正在使用函数那么它将评估函数多次取决于连接列。



希望它能帮助你解决性能问题。

快乐编码！

：）

Mike Meinz has suggested you some tips.

you should also check this.

1.
in your sp check which function is taking highest time for execution.
Execution plan will tell you the cost of function.

2.
check execution plan, is it performing scan or seek for table column?
if a few no. of rows are fetched from table containing lots of rows, then apply index on particular column.

3.
if you have use cross apply with table function and it is retriving a lot data then you should create a view instead of function, because if for this scenerio you are using function then it will evalute function for many time depends on joining columns.

Hope it will help you to solve performance issue.
Happy Coding!
:)

这篇关于如何在SQL Server 2008中使用标量/表值函数来提高查询的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！