Set up OLE DB source to read from View efficiently
OLE DB source adapter is one of the most commonly used components in SSIS data flow task. In this article, we will discuss a very important performance observation about this adapter.
OLE DB source adapter can be set up to work in either “SQL command” data access mode or “Table or view” data access mode. See the figure below for where to set Data Access Mode in OLE DB Source Editor.
In most cases, whether you use “SQL command” data access mode or “Table or view” data access mode does not make any difference performance wise. But when you are setting up OLE DB source adapter to read data from a view, the performance difference can be huge. In such an occasion, we suggest you set data access mode to “SQL command” and specify the command as, for example, “SELECT * FROM view_name”. In our in-house testing, we have seen “SQL command” data access mode runs about 17 times faster than “Table or view” data access mode in certain scenario.
If you are interested in knowing the cause for such a difference, read on.
In “Table or view” access mode, the OLE DB source adapter calls OpenRowset to get column metadata at Validate phase. The data returned from OpenRowset include more than just column metadata. Thus the adapter issues “SET ROWCOUNT 1” statement to retrieve column metadata information. “SET ROWCOUNT 1” causes an inefficient execution plan (i.e. Nested Loop) to be cached and later used in the subsequent Execute phase.
In “SQL command” access mode, the OLE DB source adapter calls “sp_prepare” to get column metadata at Validate phase, and “sp_execute” at Execute phase. The execution plan used at Execute phase is Hash Match which is more efficient than Nested Loop.
This article has described a performance tip when setting up OLE DB source adapter to read data from a view. We suggest user to set OLE DB source adapter in “SQL command” data access mode for better performance in such a case. We hope you will find this tip useful and helpful in your package design.
- Runying Mao
Anonymous
May 01, 2007
SQL Server Integration Services provide a set of performance counters. Among them the following few areAnonymous
May 07, 2007
Runying Mao and Len Wyatt did a great study of SSIS performance, and posted some results: Getting OptimalAnonymous
April 09, 2008
This makes a massive difference in performance. Rather than using the "Table or View" optionAnonymous
March 19, 2012
Has a huge difference in performance. Wonderful information.Anonymous
July 19, 2013
17 times... that's a huge difference. thanks for sharing.Anonymous
December 08, 2015
Great, It helped us to fix performance issue.