查询不会从软件运行，而是从查询窗口运行... [英] Query won't run from software, but will run from query window...

问题描述

我有一个奇怪的问题...

我们的数据库中有一个表值函数。它非常复杂，我无法真正告诉你它的作用，除了过滤器甚至排序依赖于输入参数（不使用动态SQL！）。该函数调用一些其他表值函数（所有在一个SELECT语句中）。这一切并不重要。



所以问题，从我们的软件调用时（使用普通的旧ADO.NET（SqlConnection，SqlCommand，SqlDataReader ......）当页面打开时，查询运行正常（在不到一秒的时间内获取其数据）。当我们只更改一个参数（日期过滤器）时，查询超时！通过更改参数，从不到一秒到超过30。 ..

现在当我用SSMS中的相同参数运行相同的函数时，它再次运行一秒钟！所以我们从超时到不到一秒钟。之后它将运行正常该软件也是如此。



我想制作查询计划很复杂，因为查询非常复杂，但为什么它在SSMS中运行良好而不是我们的软件？为什么更改单个参数会使查询超时？想法在哪里查找？要搜索什么？我在这里完全是空白...



谢谢。

解决方案

< blockquote>这通常归结为SSMS和ADO.NET之间的 SET 选项的差异，这导致SQL选择不同的执行计划。 ARITHABORT 是一个常见的 - 它通常在SSMS中，但是关闭ADO.NET连接。



你可以通过查看 @@ OPTIONS 并执行一些位测试来查看当前选项：

http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for -a-current-session-in-sql-server / [ ^ ]



您可以更改数据库属性中的大多数默认选项 - ARITHABORT 是启用算术中止。



涵盖了很多其他可能性本文 [ ^ ]通过 Erland Sommarskog [ ^ ]。

I've got a bit of a strange problem...
We have a table-valued function in our database. It's quite complex and I can't really tell you what it does, except that filters and even ordering is dependent on input parameters (without using dynamic SQL!). The function calls a few other table-valued functions (all in one SELECT statement). It's not really important what it all does.

So the problem, when called from our software (using plain old ADO.NET (SqlConnection, SqlCommand, SqlDataReader...) when a page opens the query runs fine (gets its data in less than a second). When we change just one parameter (a date filter) the query times out! From less than a second to more than 30 by changing a parameter...
Now when I run that same function with the same parameters from SSMS it runs under a second again! So then we go from timeout to less than a second. And after that it will run fine in the software as well.

I guess making a query plan is complicated because the query is quite complicated, but why does it run fine in SSMS and not from our software? And why does changing a single parameter make the query timeout? Ideas on where to look? What to search for? I'm completely at a blank here...

Thanks.

解决方案

This is often down to differences in the SET options between SSMS and ADO.NET, which causes SQL to select a different execution plan. ARITHABORT is a common one - it's usually on in SSMS, but off for ADO.NET connections.

You can see the current options by looking at @@OPTIONS and performing some bit tests:
http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/[^]

You can change most of the default options in the properties of the database - ARITHABORT is "Arithmetic Abort Enabled".

Quite a lot of other possibilities are covered in this article[^] by Erland Sommarskog[^].

这篇关于查询不会从软件运行，而是从查询窗口运行...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！