CODEWITHSUNDEEP
c#sql-servercollections
Mahdi Tahsildari
Mahdi Tahsildari

Reputation: 13582

Fastest way to determine if a collection (or db table) has two records at the same time or not

I want to check a collection (Huge collection let's say), to see if it has two records, each with a specific criteria. What is the fastest way?

For example I would say I have a People table with billion records, I want to get a True answer if there is a person with name equal to JACK and there is a person with Last name equal to SMITH. It may be one record, like "Jack Smith" or two records "Jack some-family" and "some-name Smith".

Please tell me what is the fastest way in C# (collections and lists) and what would be the fastest way in Sql-Server.

My opinion: Checking Exists (C#) or Any (sql) is faster. Someones opinion: put a where resulting a smaller collection (than the whole table) and then distinct it and count it (confused me too) is faster.

Your opinion goes in the answers

Upvotes: 0

Views: 78

Answers (3)

Neeraj Prasad Sharma
Neeraj Prasad Sharma

Reputation: 1675

In sql server we can find the jump to the matching records directly using B tree architecture , so sql server engine no need to scan all the pages containing matching rows, its quite easy to implement

create index IX_People_name on People( FirstName)
create index IX_People_Surname on People( Surname)

Having the perfect indexes with having Sargable query makes works perfectly

for your query:

if exists (
select 1 from People  WHERE FirstName = 'Jack' 
union all
SELECT  1 Surname FROM People
 WHERE Surname = 'Smith'

 )
 BEGIN

 print 'first Condition '

  END

  ELSE

  BEGIN 

  print 'Second Condition '

  END

This should return the result with in the second you can test it with

Set statistics time on

Upvotes: 0

Pankaj
Pankaj

Reputation: 3

C#- we can give a try to generic hashset and use Contain method. SQL- we can create index/cover indes on search field.

Upvotes: 0

Steve Harris
Steve Harris

Reputation: 5109

I think using distinct would exclude the 'Jack Smith' result as it would return 1 record rather than 2.

In sql you probably won't get much better than:

SELECT COUNT(*) FROM 
(
 SELECT TOP 1 Surname FROM People
 WHERE FirstName = 'Jack'
 UNION ALL
 SELECT TOP 1 Surname FROM People
 WHERE Surname = 'Smith'
)

Upvotes: 0

Related Questions