CODEWITHSUNDEEP
sql-serverlinq
Harshit
Harshit

Reputation: 5157

Fastest way to check the records if exists in the SQL table

I have hundreds of thousands of records in my SQL table. If between particular data, if no record is present, it takes so much time to inform us. Currently, I am using this query to check if record exists.

select TOP 1 1 AS getRowCount 
from MYTable 
where ID IN ('3','5','2','4','1') 
AND (
      datewithTime >= '2015-01-01 07:00:00' 
     AND datewithTime < '2016-01-01 07:00:00'
    )

In the above query, I am getting the record of 1 year, but records are not present in this time limit. But it is taking too much time to respond. Is there any other way that can show if data exists in the table for this particular time interval ?

Will LINQ perform better ?

Upvotes: 1

Views: 4333

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

First of all add indexes to your table:

ALTER TABLE TableName ADD  CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)
GO

CREATE NONCLUSTERED INDEX [IX_TableName_datewithTime ] ON TableName
(
    datewithTime ASC
)
GO

Then change your query to this:

if exists(select * from TableName
          where ID in ('3','5','2','4','1') and
                datewithTime >= '2015-01-01 07:00:00' and 
                datewithTime < '2016-01-01 07:00:00')
    select 1 as DataExists
else
    select 0 as DataExists

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82474

First, You should use the EXISTS statement instead of selecting top 1:

SET @getRowCount = EXISTS(select 1
       from MYTable 
       where ID IN ('3','5','2','4','1') 
       AND datewithTime >= '2015-01-01 07:00:00' 
       AND datewithTime < '2016-01-01 07:00:00'
)

Second, you should check the execution plan to see if you can improve performance by adding indices or altering existing indices.

update

Sorry, I wasn't paying enough attention to what I'm writing. Exists returns a boolean value, but sql server does not have a boolean data type, this is why you get the incorrect syntax error.

Here is the correct syntax:

DECLARE  @getRowCount bit = 0

IF EXISTS(select 1
       from MYTable 
       where ID IN ('3','5','2','4','1') 
       AND datewithTime >= '2015-01-01 07:00:00' 
       AND datewithTime < '2016-01-01 07:00:00'
) SET @getRowCount = 1

SELECT @getRowCount

Upvotes: 2

Related Questions