4

I've analyzed the code of SqlCommand and see that it works fine (see CompleteExecuteScalar method). But I'm not sure should I use top 1 in the query. For example I have select name from Person and table Person contains a lot of records. Should I modify qry to select top 1 name from Person? Or I can leave qry as is and it will not affect performance/memory etc? I'm afraid that server can buffer some data before output.

Serg046
  • 1,043
  • 1
  • 13
  • 42
  • Well, if you want single value you need to apply a `WHERE` condition that returns a single row. Otherwise your result would be arbitrary. If you need to use TOP 1 (with addtional ORDER BY) to get the correct value it isn't more efficient than using the correct WHERE condition in the first place. – Tim Schmelter Feb 17 '17 at 10:03
  • The query is filtered but it can output 0-30 rows. Based on an answer I will filter it by top 1 as well – Serg046 Feb 17 '17 at 10:08
  • 2
    It's always best practices to give the database hints, because it can optimize the query plan then. `SELECT TOP 1` is such a hint which might give you better performance than omitting the `TOP n` – Tim Schmelter Feb 17 '17 at 10:10

2 Answers2

8

You should definitely put TOP 1 in the query if you want to fetch only one row from the database, regardless of whether you are delivering the data through ExecuteScalar or ExecuteReader for instance.

You should not read source code to come to such conclusion. In fact, the way command is implemented is subject to encapsulation and calling classes should not make decisions based on exact implementation of the command.

Instead, my thinking is that the database is scarce resource that should be used optimally in terms of data throughput and related measures. That includes indicating to the database that exactly one row is wanted and be done with it. Let lower layers, such as command implementation and database itself see how to use that information for the best performance.

Zoran Horvat
  • 10,924
  • 3
  • 31
  • 43
  • 1
    If i want one record/value i write a query that returns one record/value. So normally instead of using `TOP 1 ... ORDER BY` you should use a `WHERE` condition, f.e. `WHERE ID = @ID`. – Tim Schmelter Feb 17 '17 at 10:04
  • @TimSchmelter Me too. The question is interesting. I never used `ExecuteScalar` to isolate just one field from one record. – Zoran Horvat Feb 17 '17 at 10:06
  • Thanks a lot, just wanted to get more points. I will always use `top 1` (will write extension to not forget top 1) – Serg046 Feb 17 '17 at 10:10
  • Another case is to obtain the *best*, *worst*, *top* and the like results which normally is implemented via aggregate functions: `select max(...) from ... ` not with `order by ... top 1` – Dmitry Bychenko Feb 17 '17 at 10:12
2

ExecuteScalar fetches the first column value of the first row. So, it won't make a difference if you add TOP 1 to your query or not. However, it is recommended that TOP 1 be added if you are expecting duplicate values and you need that one particular value (for the sake of speed). One such plausible scenario is that you are reusing some script that returns a set or rows containing Customer Information about a specific customer, provided, your first column is the CustomerID or any primary key.

Prashant Tiwari
  • 346
  • 1
  • 3
  • 17