Having huge number of condition in a single query will screw the performance ?

Question

Having huge number of condition in a single query will screw the performance ?

squvi.87

Default port

Points: 1450
More actions
September 10, 2015 at 1:27 am

#399018

Having huge number of condition in a single query will screw the performance ?
I have a staging table which has 100 columns. I need to validate data before I insert those into destination table. All the data errors should be updated into one column called ValidationMessage.
Almost all the columns have some criteria to check, Now I'm using separate update query for each validation
EG :
Update StagingTable Set ValidationMessage = Isnull(ValidationMessage,'') + 'column 1 can contain max 100 chars'
where len(column1 ) > 100
Update StagingTable Set ValidationMessage = Isnull(ValidationMessage,'') + 'column 2 can contain value 1,2 or 3'
where column2 not in (1,2,3)
I'm thinking to have single update by using case,
Eg:
Update StagingTable
Set ValidationMessage =
case when len(column1 ) > 100 then Isnull(ValidationMessage,'') + 'column 1 can contain max 100 chars' end +
Case when column2 not in (1,2,3) then Isnull(ValidationMessage,'') + 'column 2 can contain value 1,2 or 3' end
where len(column1 ) > 100 or column2 not in (1,2,3)
So this will reduce the multiple but I'm doubting about "Having huge number of condition in a single query will screw the performance ?"
sql-server-2008 tsql sql-update case sqlperformance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Since you're going to get a table scan with the WHERE clause containing non-SARGable predicates, remove it altogether. Also, include a coded alternative in your CASEs as the default is NULL. Otherwise, you're nearly there:

UPDATE StagingTable

SET ValidationMessage = ISNULL(ValidationMessage,'') +

CASE WHEN LEN(column1 ) > 100 THEN 'column 1 can contain max 100 chars; ' ELSE '' END +

CASE WHEN column2 NOT IN (1,2,3) THEN 'column 2 can contain values 1, 2 or 3; ' ELSE '' END

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

squvi.87 Default port Points: 1450 More actions · Answer 2

ChrisM@Work (9/10/2015)
Since you're going to get a table scan with the WHERE clause containing non-SARGable predicates, remove it altogether. Also, include a coded alternative in your CASEs as the default is NULL. Otherwise, you're nearly there:
UPDATE StagingTable
SET ValidationMessage = ISNULL(ValidationMessage,'') +
CASE WHEN LEN(column1 ) > 100 THEN 'column 1 can contain max 100 chars; ' ELSE '' END +
CASE WHEN column2 NOT IN (1,2,3) THEN 'column 2 can contain values 1, 2 or 3; ' ELSE '' END

Ok, but if I remove where clause query will update all the rows (with some message or empty). Even if I validate all the rows, it is not meaning that all rows will be updated with the error message. In an average only 2% of the data come as bad record.

In such case will this work efficient?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

squvi.87 (9/10/2015)
ChrisM@Work (9/10/2015)
Since you're going to get a table scan with the WHERE clause containing non-SARGable predicates, remove it altogether. Also, include a coded alternative in your CASEs as the default is NULL. Otherwise, you're nearly there:
UPDATE StagingTable
SET ValidationMessage = ISNULL(ValidationMessage,'') +
CASE WHEN LEN(column1 ) > 100 THEN 'column 1 can contain max 100 chars; ' ELSE '' END +
CASE WHEN column2 NOT IN (1,2,3) THEN 'column 2 can contain values 1, 2 or 3; ' ELSE '' END
Ok, but if I remove where clause query will update all the rows (with some message or empty). Even if I validate all the rows, it is not meaning that all rows will be updated with the error message. In an average only 2% of the data come as bad record.
In such case will this work efficient?

It's little effort to try both cases, the code change is small. Why not test them and then you can tell us?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

squvi.87 Default port Points: 1450 More actions · Answer 4

ChrisM@Work (9/10/2015)
squvi.87 (9/10/2015)
ChrisM@Work (9/10/2015)
Since you're going to get a table scan with the WHERE clause containing non-SARGable predicates, remove it altogether. Also, include a coded alternative in your CASEs as the default is NULL. Otherwise, you're nearly there:
UPDATE StagingTable
SET ValidationMessage = ISNULL(ValidationMessage,'') +
CASE WHEN LEN(column1 ) > 100 THEN 'column 1 can contain max 100 chars; ' ELSE '' END +
CASE WHEN column2 NOT IN (1,2,3) THEN 'column 2 can contain values 1, 2 or 3; ' ELSE '' END
Ok, but if I remove where clause query will update all the rows (with some message or empty). Even if I validate all the rows, it is not meaning that all rows will be updated with the error message. In an average only 2% of the data come as bad record.
In such case will this work efficient?
It's little effort to try both cases, the code change is small. Why not test them and then you can tell us?

sure will test and share you the result