Performance effect of using TOP 1 in a SELECT query
You may get some performance difference from just using top
, but the real performance you get by using indexes.
If you have an index for the UserName
and Application
fields, the database doesn't even have to touch the table until it has isolated the single record. Also, it will already know from the table statistics that the values are unique, so using top
makes no difference.
Why is doing a top(1) on an indexed column in SQL Server slow?
Due to the statistics, you should explicitly ask the optimizer to use the index you've created instead of the clustered one.
SELECT TOP (1) connectionid
FROM outgoing_messages WITH (NOLOCK, index(idx_connectionid))
WHERE (campaignid_int = 3835)
I hope it will solve the issue.
Regards,
Enrique
select top 1 * vs select top 1 1
SQL Server detects EXISTS
predicate relatively early in the query compilation / optimisation process, and eliminates actual data retrieval for such clauses, replacing them with existence checks. So your assumption:
I now see that the first is 80% of the execution time (relative to the batch of 2) whilst the second is only 20%.
is wrong, because in the preceding comparison you have actually retrieved some data, which doesn't happen if the query is put into the (not) exists
predicate.
Most of the time, there is no difference how to test for the existence of rows, except for a single yet important catch. Suppose you say:
if exists (select * from dbo.SomeTable)
...
somewhere in the code module (view, stored procedure, function etc.). Then, later, when someone else will decide to put WITH SCHEMABINDING
clause into this code module, SQL Server will not allow it and instead of possibly binding to the current list of columns it will throw an error:
Msg 1054, Level 15, State 7, Procedure BoundView, Line 6
Syntax '*' is not allowed in schema-bound objects.
So, in short:
if exists (select 0 from ...)
is a safest, fastest and one-size-fits-all way for existence checks.
Why use Select Top 100 Percent?
It was used for "intermediate materialization (Google search)"
Good article: Adam Machanic: Exploring the secrets of intermediate materialization
He even raised an MS Connect so it can be done in a cleaner fashion
My view is "not inherently bad", but don't use it unless 100% sure. The problem is, it works only at the time you do it and probably not later (patch level, schema, index, row counts etc)...
Worked example
This may fail because you don't know in which order things are evaluated
SELECT foo From MyTable WHERE ISNUMERIC (foo) = 1 AND CAST(foo AS int) > 100
And this may also fail because
SELECT foo
FROM
(SELECT foo From MyTable WHERE ISNUMERIC (foo) = 1) bar
WHERE
CAST(foo AS int) > 100
However, this did not in SQL Server 2000. The inner query is evaluated and spooled:
SELECT foo
FROM
(SELECT TOP 100 PERCENT foo From MyTable WHERE ISNUMERIC (foo) = 1 ORDER BY foo) bar
WHERE
CAST(foo AS int) > 100
Note, this still works in SQL Server 2005
SELECT TOP 2000000000 ... ORDER BY...
SELECT TOP 1 1 VS IF EXISTS(SELECT 1
I'd recommend IF EXISTS(SELECT * ...)
, unless this is actually causing a performance issue. It expresses the intent of the query in a much better understood fashion than alternatives.
I'd avoid COUNT(*)
(as in the current answers) unless you actually need the count of rows from the table.
If you want the "efficiency" of checking the rowcount from the result, I'd probably go for:
select 1 where exists(select * from BigTable where SomeColumn=200)
Which produces the same result set as your second query (either 0 or 1 row)
Related Topics
How to Correctly Do Upsert in Postgres 9.5
Update Multiple Tables in SQL Server Using Inner Join
Similarity Function in Postgres with Pg_Trgm
Truncate Multiple Tables in One MySQL Statement
Finding Simultaneous Events in a Database Between Times
How to Create a Conditional Where Clause
Postgres Recursive Query with Row_To_JSON
Sql: How to Find Duplicates Based on Two Fields
How to Get Current Datetime in SQL
Add Row Number to This T-SQL Query
How to Document Your Database Structure
Oracle SQL: How to Use More Than 1000 Items Inside an in Clause
Oracle Select for Update Behaviour
How to Check If Identity_Insert Is Set to on or Off in SQL Server
Retrieve Id of Record Just Inserted into a Java Db (Derby) Database
How to Use Null or Empty String in SQL