ansaurus

Question

Using multiple UNION ALLs to build a view: is there a better way?

Answer 1

+2 A:

Depends on what you are trying to do with the view.
The view query has to execute before whatever operations you perform on it. So if you are doing things that only deal with 10% of the results returned by the view, there's a waste of processing. It's likely the operations performed could be done at the same time as the logic already in the view.

Can you add indexes if necessary? That would help...

A materialized view, called an indexed view in SQL Server terminology, is a viable possibility but notoriously unaccommodating.

OMG Ponies 2010-02-10 21:21:47

Technically, views do not necessarily "execute before" other operations. In fact, I believe views are expanded in the context of an enclosing query and then the whole business gets optimized as one plan. Exceptions would be when using the "noexpand" hint.

onupdatecascade 2010-02-10 23:55:38

@onupdatecascade: In order for operations to occur on the resultset provided by a view, the view has to execute first. Effectively, a subquery/derived table. `SELECT LEFT(...) FROM v_view` has to run the view query first - the view query is not interrupted to add the additional criteria.

OMG Ponies 2010-02-11 01:55:43

Curious, I've run select statements on views that run faster if I omit a choice column from the view (This column is based on a poorly designed UDF.). If the view is required to run first, they would perform the same?

Jeff O 2010-02-11 02:45:56

It's plausible that the query runs faster because you omitted the column afterwards - the query plan would already exist. The first time would have been the hard parse, subsequent ones are soft parses.

OMG Ponies 2010-02-11 03:05:20

@OMGPonies - it depends on the content of the view, and the rules for optimization. If there's a function in there (like your left() example) then it might be necessary to process all the rows in the view, but that's because of the function, not the presence of the view. The rules for optimization would be the same if you copied and pasted the view definition into the outer query as a derived table.

onupdatecascade 2010-02-11 16:12:15

See: http://technet.microsoft.com/en-us/library/cc917715.aspx "Using the NOEXPAND view hint" This can be proven with some query plan examination

onupdatecascade 2010-02-11 16:16:02

Answer 2

+1 A:

Have you considered the UNPIVOT operator? It does the same thing, but might be less painful (maybe! :-). Requires 2005 or later.

http://blogs.msdn.com/craigfr/archive/2007/07/17/the-unpivot-operator.aspx

Example:

declare @names table ( personid int, 
    firstname1 varchar(50), 
    lastname1 varchar(50),
    firstname2 varchar(50), 
    lastname2 varchar(50),
    firstname3 varchar(50), 
    lastname3 varchar(50)

    -- <etc.>
    )

Insert @names values ( 1, 'Fred', 'Flintstone', 'Barney', 'Rubble', 'Wilma', 'Flintstone' )
Insert @names values ( 2, 'Super', 'Man', 'Aqua', 'Man', 'Wonder', 'Woman' )

select * from @names

select personid, firstnamecol, firstname, lastnamecol, lastname
from @names
unpivot( firstname for firstnamecol in ( firstname1, firstname2, firstname3 ) ) firstnames
unpivot( lastname for lastnamecol in ( lastname1, lastname2, lastname3 ) ) lastnames
where right(firstnamecol, 1) = right( lastnamecol, 1 ) -- This is the tricky bit

Using multiple unpivots in one select is tricky; I got the above notion from these guys:

http://weblogs.sqlteam.com/jeffs/archive/2008/04/23/unpivot.aspx http://mangalpardeshi.blogspot.com/2009/04/unpivot-multiple-columns.html

It's important to note that the part marked "tricky" will break with varying numbers of digits in the names of the repeating columns (e.g. FirstName11 and FirstName1 will be a problem). You could work around that with some substring() tricks. Might not be the best idea, but...

Addition, Feb 22: This is a PHENOMENAL article on unpivot: http://bradsruminations.blogspot.com/2010/02/spotlight-on-unpivot-part-1.html

onupdatecascade 2010-02-11 00:39:27

Wow, I didn't know about the unpivot operator. Conceptually, this is exactly what I need.

DanielMason 2010-02-11 19:46:17

Just be careful - I don't know what sort of performance impact you can expect. Could be terrible :-)

onupdatecascade 2010-02-11 21:35:32

Answer 3

A:

DanielMason 2010-02-11 20:12:39

ansaurus

tags:

views:

answers:

Using multiple UNION ALLs to build a view: is there a better way?

related questions