say i have a table
说我有一张桌子
Id int
Region int
Name nvarchar
select * from table1 where region = 1 and name = 'test'
select * from table1 where name = 'test' and region = 1
will there be a difference in performance? assume no indexes
性能会有差异吗?假设没有索引
is it the same with LINQ?
与LINQ一样吗?
4 个解决方案
#1
Because your qualifiers are, in essence, actually the same (it doesn't matter what order the where clauses are put in), then no, there's no difference between those.
因为你的限定符本质上实际上是相同的(无论where子句的顺序是什么并不重要),那么不,这些之间没有区别。
As for LINQ, you will need to know what query LINQ to SQL actually emits (you can use a SQL Profiler to find out). Sometimes the query will be the simplest query you can think of, sometimes it will be a convoluted variety of such without you realizing it, because of things like dependencies on FKs or other such constraints. LINQ also wouldn't use an * for select.
对于LINQ,您需要知道LINQ to SQL实际发出的查询(您可以使用SQL事件探查器来查找)。有时查询将是您能想到的最简单的查询,有时候如果没有您意识到它将会是一个复杂的查询,因为像FK或其他此类约束的依赖性。 LINQ也不会使用*来进行选择。
The only real way to know is to find out the SQL Server Query Execution plan of both queries. To read more on the topic, go here:
唯一真正的方法是找出两个查询的SQL Server查询执行计划。要阅读有关该主题的更多信息,请访问:
SQL Server Query Execution Plan Analysis
SQL Server查询执行计划分析
#2
Should it? No. SQL is a relational algebra and the DBMS should optimize irrespective of order within the statement.
应该是?不是.SQL是一种关系代数,DBMS应该优化而不管语句中的顺序如何。
Does it? Possibly. Some DBMS' may store data in a certain order (e.g., maintain a key of some sort) despite what they've been told. But, and here's the crux: you cannot rely on it.
可以?有可能。一些DBMS可以按照特定顺序存储数据(例如,保持某种键),尽管他们被告知了。但是,这就是关键所在:你不能依赖它。
You may need to switch DBMS' at some point in the future. Even a later version of the same DBMS may change its behavior. The only thing you should be relying on is what's in the SQL standard.
您可能需要在将来某个时候切换DBMS。即使是相同DBMS的更高版本也可能会改变其行为。您唯一应该依赖的是SQL标准中的内容。
Regarding the query given: with no indexes or primary key on the two fields in question, you should assume that you'll need a full table scan for both cases. Hence they should run at the same speed.
关于给出的查询:在所讨论的两个字段上没有索引或主键,您应该假设您需要对两种情况进行全表扫描。因此他们应该以相同的速度运行。
#3
I don't recommend the *, because the engine should look for the table scheme before executing the query. Instead use the table fields you want to avoid unnecessary overhead.
我不建议使用*,因为引擎应该在执行查询之前查找表方案。而是使用您想要的表字段来避免不必要的开销。
And yes, the engine optimizes your queries, but help him :) with that.
是的,引擎优化了您的查询,但帮助他:)。
Best Regards!
#4
For simple queries, likely there is little or no difference, but yes indeed the way you write a query can have a huge impact on performance.
对于简单查询,可能存在很少或没有区别,但是确实,编写查询的方式可能会对性能产生巨大影响。
In SQL Server (performance issues are very database specific), a correlated subquery will usually have poor performance compared to doing the same thing in a join to a derived table.
在SQL Server中(性能问题是特定于数据库的),与在派生表的连接中执行相同操作相比,相关子查询通常具有较差的性能。
Other things in a query that can affect performance include using SARGable1 where clauses instead of non-SARGable ones, selecting only the fields you need and never using select * (especially not when doing a join as at least one field is repeated), using a set-bases query instead of a cursor, avoiding using a wildcard as the first character in a a like clause and on and on. There are very large books that devote chapters to more efficient ways to write queries.
查询中可能影响性能的其他内容包括使用SARGable1 where子句而不是非SARGable子句,只选择您需要的字段并且从不使用select *(特别是在进行连接时不重复至少一个字段),使用基于set-bases的查询而不是游标,避免使用通配符作为aa like子句中的第一个字符以及on和on。有非常大的书籍将章节用于更有效的方式来编写查询。
1 "SARGable", for those that don't know, are stage 1 predicates in DB2 parlance (and possibly other DBMS'). Stage 1 predicates are more efficient since they're parts of indexes and DB2 uses those first.
1“SARGable”,对于那些不知道的人来说,是DB2用语中的第1阶段谓词(可能还有其他DBMS')。阶段1谓词更有效,因为它们是索引的一部分,DB2首先使用它们。
#1
Because your qualifiers are, in essence, actually the same (it doesn't matter what order the where clauses are put in), then no, there's no difference between those.
因为你的限定符本质上实际上是相同的(无论where子句的顺序是什么并不重要),那么不,这些之间没有区别。
As for LINQ, you will need to know what query LINQ to SQL actually emits (you can use a SQL Profiler to find out). Sometimes the query will be the simplest query you can think of, sometimes it will be a convoluted variety of such without you realizing it, because of things like dependencies on FKs or other such constraints. LINQ also wouldn't use an * for select.
对于LINQ,您需要知道LINQ to SQL实际发出的查询(您可以使用SQL事件探查器来查找)。有时查询将是您能想到的最简单的查询,有时候如果没有您意识到它将会是一个复杂的查询,因为像FK或其他此类约束的依赖性。 LINQ也不会使用*来进行选择。
The only real way to know is to find out the SQL Server Query Execution plan of both queries. To read more on the topic, go here:
唯一真正的方法是找出两个查询的SQL Server查询执行计划。要阅读有关该主题的更多信息,请访问:
SQL Server Query Execution Plan Analysis
SQL Server查询执行计划分析
#2
Should it? No. SQL is a relational algebra and the DBMS should optimize irrespective of order within the statement.
应该是?不是.SQL是一种关系代数,DBMS应该优化而不管语句中的顺序如何。
Does it? Possibly. Some DBMS' may store data in a certain order (e.g., maintain a key of some sort) despite what they've been told. But, and here's the crux: you cannot rely on it.
可以?有可能。一些DBMS可以按照特定顺序存储数据(例如,保持某种键),尽管他们被告知了。但是,这就是关键所在:你不能依赖它。
You may need to switch DBMS' at some point in the future. Even a later version of the same DBMS may change its behavior. The only thing you should be relying on is what's in the SQL standard.
您可能需要在将来某个时候切换DBMS。即使是相同DBMS的更高版本也可能会改变其行为。您唯一应该依赖的是SQL标准中的内容。
Regarding the query given: with no indexes or primary key on the two fields in question, you should assume that you'll need a full table scan for both cases. Hence they should run at the same speed.
关于给出的查询:在所讨论的两个字段上没有索引或主键,您应该假设您需要对两种情况进行全表扫描。因此他们应该以相同的速度运行。
#3
I don't recommend the *, because the engine should look for the table scheme before executing the query. Instead use the table fields you want to avoid unnecessary overhead.
我不建议使用*,因为引擎应该在执行查询之前查找表方案。而是使用您想要的表字段来避免不必要的开销。
And yes, the engine optimizes your queries, but help him :) with that.
是的,引擎优化了您的查询,但帮助他:)。
Best Regards!
#4
For simple queries, likely there is little or no difference, but yes indeed the way you write a query can have a huge impact on performance.
对于简单查询,可能存在很少或没有区别,但是确实,编写查询的方式可能会对性能产生巨大影响。
In SQL Server (performance issues are very database specific), a correlated subquery will usually have poor performance compared to doing the same thing in a join to a derived table.
在SQL Server中(性能问题是特定于数据库的),与在派生表的连接中执行相同操作相比,相关子查询通常具有较差的性能。
Other things in a query that can affect performance include using SARGable1 where clauses instead of non-SARGable ones, selecting only the fields you need and never using select * (especially not when doing a join as at least one field is repeated), using a set-bases query instead of a cursor, avoiding using a wildcard as the first character in a a like clause and on and on. There are very large books that devote chapters to more efficient ways to write queries.
查询中可能影响性能的其他内容包括使用SARGable1 where子句而不是非SARGable子句,只选择您需要的字段并且从不使用select *(特别是在进行连接时不重复至少一个字段),使用基于set-bases的查询而不是游标,避免使用通配符作为aa like子句中的第一个字符以及on和on。有非常大的书籍将章节用于更有效的方式来编写查询。
1 "SARGable", for those that don't know, are stage 1 predicates in DB2 parlance (and possibly other DBMS'). Stage 1 predicates are more efficient since they're parts of indexes and DB2 uses those first.
1“SARGable”,对于那些不知道的人来说,是DB2用语中的第1阶段谓词(可能还有其他DBMS')。阶段1谓词更有效,因为它们是索引的一部分,DB2首先使用它们。