保证＆QUOT;合理＆QUOT;只有查询 [英] Ensure "Reasonable" queries only

问题描述

在我们的组织，我们有必要通过提供WHERE子句，让我们的Web应用程序的员工过滤器的数据。这是很长一段时间伟大的工作，但我们偶尔也会碰到用户提供需要的大表全表扫描或低效率的连接，查询等。

In our organization we have the need to let employees filter data in our web application by supplying WHERE clauses. It's worked great for a long time, but we occasionally run into users providing queries that require full table scans on large tables or inefficient joins, etc.

有些小丑可能会写这样的：

Some clown might write something like:

select * from big_table where
Name in (select name from some_table where name like '%search everything%')
or name in ('a', 'b', 'c')
or price < 20
or price > 40
or exists (select 1 from some_other_table where col1 + col2 + col3 = 4)
or exists (select 1 from table_a, table+b)

显然，这不是要查询这些表与计算出的值，非索引列，大量或年代和表-A和表-B无限制加入一个伟大的方式。

Obviously, this is not a great way to query these tables with computed values, non-indexed columns, lots of OR's and an unrestricted join on table_a and table_b.

但对于一个用户来说，这可能使总的感觉。

But for a user, this may make total sense.

那么，什么是最好的方式，如果有的话，允许内部用户的查询提供给数据库，同时确保它不会锁定十几张桌子，挂Web服务器5分钟？

So what's the best way, if any, to allow internal users to supply a query to the database while ensuring that it won't lock a dozen tables and hang the webserver for 5 minutes?

我猜测，在C＃/ SQL服务器编程的方式来获取执行计划的查询才能运行。如果是这样，是什么因素导致成本？估计I / O成本是多少？估计的CPU成本是多少？这将是在该地告诉他查询的用户在合理的范围不好吗？

I'm guessing that's a programmatic way in c#/sql-server to get the execution plan for a query before it runs. And if so, what factors contribute to cost? Estimated I/O cost? Estimated CPU cost? What would be reasonable limits at which to tell the user that his query's no good?

编辑：我们是一家市场研究公司。我们有数以千计的调查，每个都有自己的数据。我们有很多希望切片以任意方式的数据的研究人员。我们有工具，让他们构建使用GUI有效过滤器，但一些超级用户希望提供自己的查询。我知道这是不是标准或最佳实践，但我还能怎么让几十个用户的查询表使用任意复杂的情况，他们想要的行不断变化的条件是什么？

We're a market research company. We have thousands of surveys, each with their own data. We have dozens of researchers that want to slice that data in arbitrary ways. We have tools to let them construct "valid" filters using a GUI, but some "power users" want to supply their own queries. I realize this isn't standard or best practice, but how else can I let dozens of users query tables for the rows they want using arbitrarily complex conditions and ever-changing conditions?

推荐答案

您可以尝试使用以下内容：

You can try using the following:

SET SHOWPLAN_ALL ON
GO
SET FMTONLY ON
GO
<<< Your SQL code here >>>
GO
SET FMTONLY OFF
GO
SET SHOWPLAN_ALL OFF
GO

然后你就可以通过你有什么分析。至于在哪里画上各种事物的线，那将需要一些经验。有一些看点，但没有什么是切段，晒干。它通常更多的是艺术的检查查询计划，而不是科学。

Then you can parse through what you've got. As to where to draw the line on various things, that's going to take some experience. There are some things to watch for, but nothing that is cut and dried. It's often more of an art to examine the query plans than a science.

正如其他人所指出的，虽然，我认为你的问题比技术的影响更深。你让不合格的人以这样的方式访问数据库的事实是根本问题。从过去的经验，我经常看到这个在他们太懒或太缺乏经验，正确捕捉他们的应用程序的要求的公司。我不是说这是必然与企业环境的情况下，但是这是我所看到的。

As others have pointed out though, I think that your problem goes deeper than the technology implications. The fact that you let unqualified people access your database in such a way is the underlying problem. From past experience, I often see this in companies where they are too lazy or too inexperienced to properly capture their application's requirements. I'm not saying that this is necessarily the case with your corporate environment, but that's what I've seen.

这篇关于保证＆QUOT;合理＆QUOT;只有查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！