使用OLE DB更快地提取数据 [英] Extract data with an OLE DB faster
问题描述
大家好，我正在尝试使用SSIS(OLE DB SOURCE)从许多联接的表和视图中提取许多记录，但这需要花费大量时间！问题是由于查询造成的，因为当我在sql server上解析它时，花费了一个多小时！她是我的sis包装设计
Hi everyone I'm trying to extract a lot of records from a lot of joined tables and views using SSIS (OLE DB SOURCE) but it takes a huge time! the problem is due to the query because when I parsed it on sql server it takes more than hour ! Her's my ssis package design
我想到了使用两个OLE DB源和合并联接进行并行提取，但是不建议使用它！除了需要更多的时间！有什么办法可以帮助我吗?
I thought of paralleled extraction using two OLE DB source and merge join but it isn't recommended using it! besides it takes more time! Is there any way to help me please?
推荐答案
使用OLEDB source中的所有joins编写T-sql查询总是比使用different source然后使用Merge Join恕我直言的快. .原因是SSIS是memory Oriented architecture，它必须将来自N不同表的所有数据放入其缓冲区中，然后使用Merge join对其进行过滤，而Merge Join是一个
Writing the T-sql query with all the joins in the OLEDB source will always be faster than using different source and then using Merge Join IMHO. The reason is SSIS is memory Oriented architecture .It has to bring all the data from N different tables into its buffers and then filter it using Merge join and more over Merge Join is an asynchronous component(Semi Blocking) therefore it cannot use the same input buffer for its output .A new buffer is created and you may run out of memory if there are large number of rows extracted from the table.
已经说过，有几种方法可以使用OLEDB源提高提取性能
Having said that there are few ways you can enhance the extraction performance using OLEDB source
1.调整SQL查询.避免使用Select *
1.Tune your SQL Query .Avoid using Select *
2.检查网络带宽.您的吞吐量不能超过带宽支持.
2.Check network bandwidth .You just cannot have faster throughput than your bandwidth supports.
3.All source adapters are asynchronous .The speed of an SSIS Source is not about how fast your query runs .It's about how fast the data is retrieved .
正如上面其他人所建议的那样，您应该向我们显示查询以及收回数据所花费的时间，否则这些只是少数可以使提取速度更快的优化技术
As others have suggested above ,you should show us the query and also the time it is taking to retireve the data else these are just few optimization technique which can make the extraction faster
这篇关于使用OLE DB更快地提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
