UNION vs OR --> NP-Complete Problem

Question

UNION vs OR --> NP-Complete Problem

karthik M

One Orange Chip

Points: 29139
More actions
February 5, 2013 at 2:53 am

#270285

I have recently read about this in the below link.
http://www.sql-server-performance.com/2013/tsql-incorrect-union-operator/
I just heard about "NP-Complete Problem" first time 🙂
From Joe Celko...
This UNION vs OR problem has been in SQL for along time, not just T-SQL. The one-table VIEW with the OR is updatable; the UNION version counts as two tables, so the VIEW is not updateable, even tho it is logically equivalent.
When we wrote the Standards, we knew that VIEW updatign is an NP-Complete problem, so we went with the easiest definition for the Standards.
The conclusion is to use 'OR' instead UNION. Right?
karthik
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

karthik M (2/5/2013)
I have recently read about this in the below link.
http://www.sql-server-performance.com/2013/tsql-incorrect-union-operator/
I just heard about "NP-Complete Problem" first time 🙂
From Joe Celko...
This UNION vs OR problem has been in SQL for along time, not just T-SQL. The one-table VIEW with the OR is updatable; the UNION version counts as two tables, so the VIEW is not updateable, even tho it is logically equivalent.
When we wrote the Standards, we knew that VIEW updatign is an NP-Complete problem, so we went with the easiest definition for the Standards.
The conclusion is to use 'OR' instead UNION. Right?

I wouldn't say so. As not-updateable VIEW can be made updateable with INSTEAD OF triggers help...

I also think that use 'OR' over 'UNION' should be decided case-by-case based on best performance.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 3

karthik M

One Orange Chip

Points: 29139

February 6, 2013 at 5:56 am

#1584899

few more thoughts ?

karthik

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

karthik M (2/6/2013)
few more thoughts ?

It depends.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

ScottPletcher SSC Guru Points: 101105 More actions · Answer 5

No, I wouldn't use OR just because that makes a view updateable -- UPDATEs aren't typically done using views like that anyway.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

ScottPletcher (2/6/2013)
No, I wouldn't use OR just because that makes a view updateable -- UPDATEs aren't typically done using views like that anyway.

What is the problem with updateable VIEW? I agree, usually views are not subjects of modification queries, but I don't think that "updateability" of a VIEW is evil enough to stop creating such views.

What about VIEW which doesn't even need OR or UNION, it can be updateable, will you not have such views at all?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

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

GilaMonster (2/5/2013)
Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.

^{“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

ScottPletcher SSC Guru Points: 101105 More actions · Answer 8

Eugene Elutin (2/7/2013)
ScottPletcher (2/6/2013)
No, I wouldn't use OR just because that makes a view updateable -- UPDATEs aren't typically done using views like that anyway.
What is the problem with updateable VIEW? I agree, usually views are not subjects of modification queries, but I don't think that "updateability" of a VIEW is evil enough to stop creating such views.
What about VIEW which doesn't even need OR or UNION, it can be updateable, will you not have such views at all?

I think you misread what I said. I don't object to updateable views per se. I meant I wouldn't change the way I coded a view just to make the view updateable (unless I already intended to UPDATE via that view).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Paul White SSC Guru Points: 150467 More actions · Answer 9

karthik M (2/5/2013)
The conclusion is to use 'OR' instead UNION. Right?

No.

Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.

SQL is a declarative language: you specify the results you want, not how to get them; that's the optimizer's job.

Regarding the original article, it's interesting that the author chose not to make the clustered index on the table UNIQUE, even though the clustering key is an IDENTITY column. Had he done so, the example query would have produced this execution plan:

The example was also carefully crafted to use a very small number of rows and unhelpful nonclustered indexes. Joe Celko's comment about updatable views is mildly interesting if you want to understand why the SQL standard is the mess it is, but it has little practical consequence.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

SQL Kiwi (2/9/2013)
The example was also carefully crafted to use a very small number of rows and unhelpful nonclustered indexes.

I've seen a few articles on that SQL site like that, examples specifically crafted to have a very specific (and non-optimal) behaviour and the author then producing a general solution and general conclusion from that.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

If you can withstand the PITA associated with finding the correct key, you could use a partitioed view which is updateable. Just kiss your IDENTITY columns goodbye, though.

I do like the idea of an "Instead of Trigger" on the view, though.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 12

ScottPletcher (2/7/2013)
Eugene Elutin (2/7/2013)
ScottPletcher (2/6/2013)
No, I wouldn't use OR just because that makes a view updateable -- UPDATEs aren't typically done using views like that anyway.
What is the problem with updateable VIEW? I agree, usually views are not subjects of modification queries, but I don't think that "updateability" of a VIEW is evil enough to stop creating such views.
What about VIEW which doesn't even need OR or UNION, it can be updateable, will you not have such views at all?
I think you misread what I said. I don't object to updateable views per se. I meant I wouldn't change the way I coded a view just to make the view updateable (unless I already intended to UPDATE via that view).

Ough, Sorry! I did really misread your post then.

I do agree with you on this subject completely - there is no needs to make sure view is updateable, until you really need it to be so 🙂

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]