Best way to write the query for given Requirement

Question

Hi All, 

      I am trying to write a query for the following requirement. 

SELECT 
	code,
	key,
	ItemValue
FROM XYZ
WHERE
	If key in (1,2,3) then pick only records with column [code] having 2 Pipes 
	If key not in (1,2,3) then pick only records with column [itemValue] like '%|%'

      I know that we can write the query as follows but I am not sure if it is the best approach. 

SELECT 
	code,
	key,
	ItemValue
FROM XYZ
WHERE key in (1,2,3)
and LEN(code) - LEN(REPLACE(code, '|', '')) = 2

UNION

SELECT 
	code,
	key,
	ItemValue
FROM XYZ
WHERE key NOT in (1,2,3)
and itemValue like '%|%'

Can you please help in writing the query in a better way?

Thanks and Regards,

Vijeth

---

Vijeth Sankethi

Answer 1

this?

SELECT 
	code,
	key,
	ItemValue
FROM XYZ
WHERE (key in (1,2,3)
and LEN(code) - LEN(REPLACE(code, '|', '')) = 2)
OR itemValue like '%|%'

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 2

Thanks for the Reply.

I had written this earlier and I had dismissed this as a bad query. 

Please note that the table XYZ is very huge and it has rows in millions. So the above query didn't complete. The column [code] doesn't have an index and I cannot create it as I don't have permissions for it. 

Is there any way that I can improve this query? 

Thanks and Regards,

Vijeth

---

Vijeth Sankethi

Answer 3

Look, both predicates  are not sargable , means you will get probably index scan for both queries in your example... BTW, replace UNION with UNION ALL , do you really need UNION?  

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 4

Thanks for the Reply.

I had written this earlier and I had dismissed this as a bad query. 

Please note that the table XYZ is very huge and it has rows in millions. So the above query didn't complete. The column [code] doesn't have an index and I cannot create it as I don't have permissions for it. 

Is there any way that I can improve this query? 

Thanks and Regards,

Vijeth

---

Vijeth Sankethi

You cant do much on it so far as you're searching for %% using LIKE ie pattern within

It cant make use of an available index in this case

 

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 5

Thanks for your reply. 

I will see what else I can do. 

Thanks and Regards,

Vijeth

---

Vijeth Sankethi

Answer 6

I can use UNION ALL as there will not be any duplicates in the result set. 

I just wanted a query earlier. Didn't think much about Performance implication of UNION. 

Thanks for pointing it out.

Regards,

---

Vijeth Sankethi