Query Performance of a VIEW containing UNION

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query Performance of a VIEW containing UNION

Author

Topic

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-13 : 04:34:54

Query Performance on a VIEW containing UNION

Hello everyone,

I have a problem with the performance of a query selecting from a view.

If I run the queries outside of the view, they return in 2 ms, doing 6 reads against 5 rows with index seeks.
When selecting from the view, results return in 263 ms, doing over 300 reads against 96,000 rows with index scans.

Please take a look at the code and stats below and let me know if you have any suggestions on how to make the view perform like the individual queries.

The actual production version of this view has 7 more columns and unions 4 additional tables, but this example demonstrates the problem.

The view:


	CREATE VIEW v_nd_ds_1 as 
	SELECT ds_doc_po po FROM ds_document 
	UNION ALL
	SELECT po FROM ns_document

Select from the view. (This po exists only in the ds_document table.)


	SELECT po FROM v_nd_ds_1 WHERE po = 'IV203757-0'

po                             
------------------------------ 
IV203757-0

Table 'NS_DOCUMENT'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
Table 'DS_DOCUMENT'. Scan count 1, logical reads 320, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 263 ms,  elapsed time = 263 ms.
Rows        Executes    StmtText
----------- ----------- ---------------------------------------------------------
1           1           SELECT [po]=[po] FROM [v_nd_ds_1] WHERE [po]=@1
1           1             |--Concatenation
1           1                  |--Filter(WHERE:([Expr1002]='IV203757-0'))
96084       1                  |    |--Compute Scalar(DEFINE:([Expr1002]=Convert([DS_DOCUMENT].[ds_doc_po])))
96084       1                  |         |--Index Scan(OBJECT:([APDMS].[dbo].[DS_DOCUMENT].[ds_doc_po_idx]))
0           1                  |--Index Seek(OBJECT:([APDMS].[dbo].[NS_DOCUMENT].[NS_D_po_idx]), SEEK:([NS_DOCUMENT].[po]='IV203757-0') ORDERED FORWARD)

Selecting directly from the tables in QA with the same WHERE clause:


	select ds_doc_po po from ds_document where ds_doc_po = 'IV203757-0'
	union all
	select po from ns_document where po = 'IV203757-0'

po                             
------------------------------ 
IV203757-0

Table 'NS_DOCUMENT'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
Table 'DS_DOCUMENT'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 2 ms,  elapsed time = 2 ms.
Rows        Executes    StmtText                                                                                                                                        
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------- 
1           1           select ds_doc_po po from ds_document where ds_doc_po = 'IV203757-0'
union all
select po from ns_document where po = 'IV203757-0'              78          1           0           NULL                           NULL                           NULL                                                                                                                 NULL                                            17.962494                NULL                     NULL                     NULL        6.5855621E-3             NULL                       NULL     SELECT                         0        NULL
1           1             |--Concatenation                                                                                                                              
1           1                  |--Compute Scalar(DEFINE:([Expr1002]=Convert([DS_DOCUMENT].[ds_doc_po])))                                                                
1           1                  |    |--Index Seek(OBJECT:([APDMS].[dbo].[DS_DOCUMENT].[ds_doc_po_idx]), SEEK:([DS_DOCUMENT].[ds_doc_po]='IV203757-0') ORDERED FORWARD)  
0           1                  |--Index Seek(OBJECT:([APDMS].[dbo].[NS_DOCUMENT].[NS_D_po_idx]), SEEK:([NS_DOCUMENT].[po]='IV203757-0') ORDERED FORWARD)

Any suggestions are appreciated.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-06-13 : 10:31:12

Conjecture:

ds_doc_po and po are of different types.
The optimizer will not duplicate the single string literal 'IV203757-0',
so the filter on one side of the UNION ALL gets a Cast in its comparison,
preventing that side from using the index.
The VIEW is not part of the problem, per se.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-13 : 14:04:22

U da man Arnold!

Dropped and recreated the column as varchar(30) instead of varchar(26) and bang! Works like a charm.

Very interesting to know. I have never heard of this optimizer suggestion.

FYI:

SELECT po FROM v_nd_ds_1 WHERE po = 'IV203757-0'

po
------------------------------
IV203757-0

Table 'NS_DOCUMENT'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
Table 'DS_DOCUMENT'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
Rows Executes StmtText
----------- ----------- ---------------------------------------------------------------------------------------------------------------------
1 1 SELECT [po]=[po] FROM [v_nd_ds_1] WHERE [po]=@1
1 1 |--Concatenation
1 1 |--Index Seek(OBJECT:([APDMS].[dbo].[DS_DOCUMENT].[ds_doc_po_idx]), SEEK:([DS_DOCUMENT].[ds_doc_po]='IV203757-0') ORDERED FORWARD)
0 1 |--Index Seek(OBJECT:([APDMS].[dbo].[NS_DOCUMENT].[NS_D_po_idx]), SEEK:([NS_DOCUMENT].[po]='IV203757-0') ORDERED FORWARD)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources