SP takes forever to run in SSIS

Question

Hi there,

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

Answer 1

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.

Answer 2

Have you tried running the query in the sp as the source rather than the sp? Is that just as slow?

Answer 3

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