| Author |
Topic |
|
8022421
Starting Member
45 Posts |
 Posted - 2008-12-07 : 22:54:27
|
| Hi,I am designing a ssis package which extracts the output from the stored procedure to flat file. The output from the stored procedure has 12 million records. I wrote the stored Procedure using #table. I have selected the result set from the #table. When I execute the procedure in the SQL server 2005 it took around 20 minutes. I have created a package with OLEDB source and the output of the OLEDB source is inputted to a flat file. When I build the package and execute it is taking around 1:30 minutes to execute the package. Is there any way that I can optimize the package. ThanksDhana |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-07 : 23:24:32
|
| are you using fast load option in oledb source? that will make sure transfertakes place faster. |
 |
|
|
8022421
Starting Member
45 Posts |
Posted - 2008-12-08 : 08:45:54
|
| No i am using SQL command in the access mode. In the OLEDB source Editor Text I have given the below like this.SET FMTONLY OFFSET NOCOUNT ONEXEC [dbo].[GetExtractCMP02].Is SETFMTONLY OFF affects the performance here? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-08 : 08:52:12
|
| what the purpose of setting SET FMTONLY OFF here? are you setting it ON otherwise? I guess, that may be because you want to take metadata info in ssis? for that, you could have simply converted procedure to a table values function, in which case, it takes the metadata info automatically. |
|
|
|
8022421
Starting Member
45 Posts |
Posted - 2008-12-08 : 09:11:17
|
| Vishak,I am using Hashtable in the procedure. More Over I am having 100 coulumns in the #table.I will do some calualtions in the prcoedure and update the respective columns in the #table. Finally i will make a select in the #table. In OLEDB Source the columns are not exposed , so I have set it as SET FMTONLY OFF. Which exposes the columns. Can you give me a example of a Table Value Function? Will the Table Valued Function can be used in the OLEDB source? Whether using the Table valued Function will give some perfomrance Imprpovement. I Fine tunned the query in almost all the possible ways. So I need to do something with the Package to get the peformance. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-08 : 09:24:55
|
quote:
Originally posted by 8022421
Vishak,I am using Hashtable in the procedure. More Over I am having 100 coulumns in the #table.I will do some calualtions in the prcoedure and update the respective columns in the #table. Finally i will make a select in the #table. In OLEDB Source the columns are not exposed , so I have set it as SET FMTONLY OFF. Which exposes the columns. Can you give me a example of a Table Value Function?see link below Will the Table Valued Function can be used in the OLEDB source? yes, it can be used Whether using the Table valued Function will give some perfomrance Imprpovementthat depends on your query used inside it. I Fine tunned the query in almost all the possible ways. So I need to do something with the Package to get the peformance.
see 2 nd and 3rd optionshttp://www.sqlteam.com/article/user-defined-functions |
|
|
|
8022421
Starting Member
45 Posts |
Posted - 2008-12-08 : 10:07:45
|
| I will go with the thrid option in this article. So In OLEDB source Editor can I give the Access mode as SQLCOMMAND and in the editor can I give it as "SELECT COLUMNNAMES FROM FUNCTION()". will this will work. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-09 : 03:17:24
|
quote:
Originally posted by 8022421
I will go with the thrid option in this article. So In OLEDB source Editor can I give the Access mode as SQLCOMMAND and in the editor can I give it as "SELECT COLUMNNAMES FROM FUNCTION()". will this will work.
yup it will..i have used it a lot. |
|
|
|
8022421
Starting Member
45 Posts |
Posted - 2008-12-09 : 08:30:17
|
| Vishak,When I Impelemented the table valued,it is taking more time than the procedure. I think this is beacuse of the Huge amount of Return table columns from the function. If I populate the values to staging table instead of a hash table in the procedure whether this will replace the SET FMTOFF ONLY?. More Over In the Procedure I am having a Select staement from the populated #table which is taking around 10 - 15 min.If I comment this portion in the procedure and try to replace this with a new task like OLE DB source by giving the populated staging table name whether this will have any impact on the performance?.so my task can be like thisExecute SQL task - > populates the staging tables alone no select statement.OLE DB source -> Access Mode as the Populated Table name.Flat File Destination - >Out put from the OLE Db source is given as Input.Do you think that this rearrangement of the task will give any improvement in my performance. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-09 : 13:34:37
|
| how many columns are you trying to return from UDF? how much time does it takes while excuting on mgmnt studio? |
|
|
|
|
Slow Running SSIS Package