tailieunhanh - Hands-On Microsoft SQL Server 2008 Integration Services part 43

Hands-On Microsoft SQL Server 2008 Integration Services part 43. Deploy and manage high-performance data transformation solutions across your enterprise using the step-by-step techniques in this fully revised guide. Hands-On Microsoft SQL Server 2008 Integration Services, Second Edition explains the tools and methods necessary to extract conclusive business intelligence from disparate corporate data. Learn how to build and secure packages, load and cleanse data, establish workflow, and optimize performance. Real-world examples, detailed illustrations, and hands-on exercises are included throughout this practical resource. . | 398 Hands-On Microsoft SQL Server 2008 Integration Services specify in the Cache Size fields. Two Cache Size fields have been provided in the Partial caching section one is for a 32-bit system and the other one applies to a 64-bit system. What happens with partial cache mode is that the partial reference data rows are loaded into the cache based on the cache size value and as the lookup operation applies the input data rows are matched against the reference data in the cache. If a match is found the row is directed to the match output and if a match is not found in the cache a query is sent to the OLE DB source to find if a match exists. Again if a match is found the row is sent to the match output however if a match is not found in the reference database the non-matching row is directed to the error output or no-match output depending on how you ve chosen to handle the no-match rows. This trip to an external reference database to find a match or no match could be quite expensive and can hurt the performance of a lookup operation. If your reference data is not changing while the package is running and you ve sufficient free memory available on the server you can choose to store the no-matching rows in the cache using the option Enable cache for rows with no matching entries. You can also apply a limit on the amount of cache no matching rows can use by specifying a percentage value in the Allocation From Cache field. The next time the no-matching row with the same key appears in the input data set the lookup transformation already knows about this as it has it in the cache and doesn t do a trip out to the database. This process goes on till the cache is completely filled up at which time the Lookup transformation removes the least frequently used rows from the cache. You can also improve the query performance by selecting the Modify The SQL Statement check box. Using this option you can use an SQL statement to select a subset of the reference data set. For example .