SP 在 SSIS 中运行需要很长时间
这是我的第一个问题，所以请耐心等待，如果可以的话，我也会尝试在某个地方回答一个问题作为付款，尽管不确定我的技能是否能够胜任。
我在 SQL 2008 中有一个存储过程，目前执行的结果约为 600 奇数行，它内部有联合，并且有一个多次使用的用户定义函数。没什么离谱的。通过 Management studio 中的查询窗口，查询在 7 秒内完成。
在 BIDS 中，编写 SSIS 时，我使用与 OLEDB 源相同的 SP 作为数据流的一部分。结果集输出到 Excel。
在完成之前，相同的查询会在预执行阶段中挂起 40 多分钟。
我尝试重新创建相同的数据流，以防出现一些奇怪的问题，我尝试替换查询中的 UDF 以查看是否存在问题，但无济于事。
有谁知道问题可能是什么，或者我可以做什么来进一步调查。
亲切的问候，
马特 H
This is my first question so bear with me, i will also attempt to answer one somewhere if i can as payment, though not sure my skills are up to it yet.
I have a stored procedure in SQL 2008 that at the moment executes with a result of about 600 odd rows, it has unions within it and a user defined function used multiple times. Nothing outrageous. Query completes in 7 seconds via a query window in the Management studio.
Within BIDS, writing a SSIS i use the same SP as an OLEDB source as part of a Data flow. The result set is output to Excel.
That same query hangs within the Pre-execute phas for 40 odd minutes before completing.
I have tried re-creating the same data flow incase there was some strange quirk, i have tried replacing the UDF within the query to see if that was the problem but to no avail.
Does anyone have any ideas what the problem maybe or what i might do to further some investigation.
Kind regards,
Matt H
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您是否尝试过在 sp 作为源而不是 sp 中运行查询？就这么慢吗？
Have you tried running the query in the sp as the source rather than the sp? Is that just as slow?
为所有有帮助的人干杯。
更新......
我昨天遇到了类似的问题，这让我觉得昨天找到的解决方案有点简单。
事实证明，这个问题在其他地方已经提到过——它基本上是我的客户数据不好的结果。
从包中的数据源运行的存储过程必须命中索引。
我在这些数据源中进行查询，这些查询与具有主键的表进行通信，但由于这些特定查询中的客户数据错误，联接必须引用其他字段，我对表数据运行了一些清理任务并创建了一个唯一的数据表来自他们。
从包中的数据源运行相同的查询（现在带有引用主键的联接）现在只需几秒而不是几小时。
因此，我读到的有关其他人类似问题的所有内容，其中的猜测是由于整个表被查询而导致的，并且预执行阶段的类似情况可能接近事实......对我来说，解决方案是查看底层数据并得到它的吱吱声。
希望对未来的人有所帮助
Matt H
Cheers to all that helped.
Update.......
I encountered a similar problem yesterday that made me think the solution found yesterday was a little to easy.
The problem has, it turns out, been mentioned elsewhere - its a product of my customers data being bad basically.
The stored procedures running from the data sources in the packages must hit an index.
I have queries in these data sources that talk to tables that have primary keys but because of bad customer data in these particular queries the joins are having to refer to other fields, i ran some cleanliness tasks over the table data and created a unique data table from them.
Running the same queries from the data sources in the package (now with joins refering to primary keys) now run in seconds rather than hours.
So all the things i have read about other peoples similar issues where the surmising is that is due to the whole tables being queried and things like that in the pre-execute phases is probably close to the truth..........solution for me has been to look at the underlying data and get it squeaky.
Hope that helps someone in the future
Matt H
我不知道问题是什么。您可以尝试将结果输出到临时表，然后将这些结果移动到电子表格中。
I don't know what the problem is. You might try outputting the results to a temporary table and then move those results into the spreadsheet.