Does recording SQL queries improve performance?

Question

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? do not accept indexes

matches LINQ?

Answer 1

Since your qualifiers are essentially the same (it doesn't matter in which order the where clauses are placed), then no, there is no difference between them.

As for LINQ, you need to know what LINQ to SQL query really emits (you can use SQL Profiler to find out). Sometimes a query will be the simplest query you can think of, sometimes it will be a complex set if you don’t understand it, due to such things as dependencies on FK or other similar restrictions. LINQ will also not use * to select.

The only real way to find out is to find out the SQL Server Query Execution plan for both queries. To learn more about the topic, go here:

SQL Server Execution Plan Analysis

Answer 2

Should it? No. SQL is relational algebra, and the DBMS must optimize regardless of the order inside the statement.

It? Maybe. Some DBMSs can store data in a certain order (for example, support a key), despite what they were told. But, here is the point: you cannot rely on it.

You may need to switch the DBMS at some point in the future. Even a later version of the same DBMS can change its behavior. The only thing you should rely on is that of the SQL standard.

Regarding a given query: without indexes or a primary key in the two areas under consideration, you should assume that you will need a full table scan for both cases. Therefore, they should work at the same speed.

Answer 3

I do not recommend *, because before executing the query, the engine should look for the table schema. Instead, use the table fields that you want to avoid unnecessary overhead.

And yes, the engine optimizes your queries, but help him with that.

Best wishes!

Answer 4

For simple queries, there is probably little or no difference, but yes, indeed, the way you write the query can have a huge impact on performance.

In SQL Server (performance problems are very specific for a particular database), the correlated subquery typically has poor performance compared to the same as in the connection to the view.

Other things in the query that may affect performance include using SARGable ¹ where clauses are instead of non-SARGable clauses, selecting only the fields you need and never using select * (especially when you are not connecting because at least one field is repeated) using a set-based query instead of a cursor, avoiding the use of a wildcard as the first character in aa like clause and incl. and further. There are very large books that devote chapters to more efficient ways of writing queries.

¹ "SARGable", for those who do not know, are predicates of stage 1 in the DB2 language (and, possibly, in other DBMSs). Stage 1 predicates are more efficient because they are part of indexes, and DB2 uses them first.