3

We are using SQL Server and this is a performance related question.

We have a relatively big table and intend to perform an action in case of having more than 1 record with the same ID. Consider the simple queries below:

select top 2 [TenantID] from [dbo].[tblTenant] where TenantID = 1

vs

select COUNT ([TenantID]) from [dbo].[tblTenant] where TenantID = 1

I would guess in the first query, SQL Server stops scanning the entire table after finding the first possible two TenantIDs. But in the second query, operation is done over all the records in the table.

Is that a correct assumption? I mean, could we say the first query is better in terms of performance?

2

I would guess in the first query, SQL Server stops scanning the entire table after finding the first possible two TenantIDs.

Correct, it will stop after finding two matching rows. But it doesn't necessarily scan the whole table. If there is a suitable index, on (TenantID), it will only need an index seek and a sequential scan on the index - and stop at the second row, whether it is a match or not. The index guarantees that the rows with same TenantID will be one after the other in the index structure.

But in the second query, operation is done over all the records in the table.

Yes again. With the same note, if there is an index, it only has to do an index seek and a scan of the index, until it finds the first row with a different (than 1) TeanantID. If there is no index, it will have to do a full scan on the table.

Is that a correct assumption? I mean, could we say the first query is better in terms of performance?

Yes, that is a correct assumption. The first query will be (slightly or more) efficient than the second. The difference will probably be negligible though in most cases. Reading 2, 3 or 10 values from an index will not make any noticeable difference. If however, there are many rows with the same TenantID value, say tens of thousands or millions, then you'll start noticing.

To summarize, if you need to find whether there are N or more rows that match a condition, I suggest you always use the TOP (N) method, and then count:

... 
WHERE (SELECT COUNT(*) FROM
        (select top (2) * from [dbo].[tblTenant] where TenantID = 1) AS t
      ) = 2 ;
0

I can suggest another approach to this problem. Create a new table, let's say tblTenantConsolidated, with minimum 2 columns: TenantID and currentCount. When a new record is added to tblTenant, insert trigger on the table is creating/updating count in tblTenantConsolidated. This way you avoid querying your big table.

  • Number of columns is less an issue here and we are more concerned about the number of rows. As a matter of fact, this is a race condition case that we are trying to handle. Keeping the consolidated table in sync will also affect our SLA. – Mori Jan 16 '17 at 10:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.