Sunday, April 29, 2007

Set up OLE DB source to read from View efficiently

by Data & SQL Storage Performance Team via SQL Server Performance on 4/29/2007 8:27:00 PM

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