Set up OLE DB source to read from View efficiently

  • Article

 

Introduction

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.

Use “SQL Command” to pull data from a view

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.

Conclusion

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

Comments

  • Anonymous
    May 01, 2007
    SQL Server Integration Services provide a set of performance counters. Among them the following few are

  • Anonymous
    May 07, 2007
    Runying Mao and Len Wyatt did a great study of SSIS performance, and posted some results: Getting Optimal

  • Anonymous
    April 09, 2008
    This makes a massive difference in performance. Rather than using the "Table or View" option

  • Anonymous
    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.