SQL Query performance with if exists

Question

I have this SQL query:

IF NOT EXISTS (SELECT TOP 1 RowId FROM dbo.Cache AS C WHERE StringSearched = @pcpnpi AND colName = 'pcpnpi' AND ModifiedAt > (SELECT ModifiedAt FROM dbo.Patients AS p WHERE P.RowID = C.RowID)) BEGIN SELECT @constVal = FunctionWeight FROM dbo.FunctionWeights WHERE FunctionWeights.FunctionId = 33; INSERT INTO #Temp2 (RowNumber,ValFromUser,ColumnName,ValFromFunc, FuncWeight,percentage) SELECT RowNumber,@pcpnpi,'pcpnpi',PercentMatch, @constVal,PercentMatch * @constVal FROM dbo.Matchpcpnpi (@pcpnpi); END ELSE BEGIN INSERT INTO #Temp2 (RowNumber,ValFromUser,ColumnName,Percentage) SELECT RowId,StringSearched,ColName,PercentMatch FROM dbo.Cache AS C WHERE StringSearched = @pcpnpi AND colName = 'pcpnpi' AND ModifiedAt > (SELECT ModifiedAt FROM dbo.Patients AS p WHERE P.RowID = C.RowID) END

The above if statement is meant to avoid unnecessary look ups for strings that have already been searched earlier and MatchPercent has been calculated. In that case, it is directly retrieved from Cache table.

Above sql query is basically for one particular column and this same kind of query with just columnName and its value changing is repeated for many other columns in the procedure.

The if Exists check was obviously meant so that query performance could improve however the performance has gone down, probably because of extra checks. Cache table which is actually meant to improve the performance, extra checks have ruined it. Is there a way to simplify above query,please ? Any directions on same will help. Thanks

Have you considered deleting rows from cache when the data gets modified? That would reduce the I/O you'll be doing for the checking the time fields now. Also this seems to be at least 3rd question for the same thing for the last 24 hours, maybe you could update the original question... — James Z
– James Z, Commented Mar 3, 2015 at 13:22

Pரதீப் · Accepted Answer · 2015-03-03 13:14:35Z

First insert into #temp2 based on exists condition. If the Insert record count is zero then do the another insert. Try this.

INSERT INTO #Temp2 (RowNumber,ValFromUser,ColumnName,Percentage) SELECT RowId,StringSearched,ColName,PercentMatch FROM dbo.Cache AS C WHERE StringSearched = @pcpnpi AND colName = 'pcpnpi' AND ModifiedAt > (SELECT ModifiedAt FROM dbo.Patients AS p WHERE P.RowID = C.RowID) IF @@ROWCOUNT = 0 BEGIN SELECT @constVal = FunctionWeight FROM dbo.FunctionWeights WHERE FunctionWeights.FunctionId = 33; INSERT INTO #Temp2 (RowNumber,ValFromUser,ColumnName,ValFromFunc, FuncWeight,percentage) SELECT RowNumber,@pcpnpi,'pcpnpi',PercentMatch, @constVal,PercentMatch * @constVal FROM dbo.Matchpcpnpi (@pcpnpi) END

Gordon Linoff · Accepted Answer · 2015-03-03 13:13:31Z

First, consider this query in the exists:

select Top 1 RowId from dbo.Cache as C where StringSearched = @pcpnpi and colName = 'pcpnpi' and ModifiedAt > ( Select ModifiedAt FROM dbo.Patients p WHERE P.RowID = C.RowID))

For performance, you want indexes on cache(StringSearched, colName, ModifiedAt, RowId) and Patients(RowId).

However, you are running this query twice. I would suggest a structure more like:

declare @RowId . . . ; -- I don't know the type select Top 1 @RowId = RowId from dbo.Cache as C where StringSearched = @pcpnpi and colName = 'pcpnpi' and ModifiedAt > ( Select ModifiedAt FROM dbo.Patients p WHERE P.RowID = C.RowID)); if (@RowId) is null . .. else . . .

Thank you. But could you specify if you are suggesting a clustered or a non clustered index on these column ?
@SImran . . . An index is non-clustered unless specified otherwise. The clustered index is usually used for the primary key.

Collectives™ on Stack Overflow

SQL Query performance with if exists

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related