Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Is derived column better for performance of a package ? Expand / Collapse
Author
Message
Posted Tuesday, November 13, 2012 7:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 28, 2014 12:58 PM
Points: 24, Visits: 149
rhythm.varshney (11/13/2012)

Yes, actually if you see my comments I mentioned that I am performing UPINSERT logic on the basis of staging table which hold around 50 K records daily basis.So was thinking to handle the split on the same.As was having the staging table and did not want to insert derived columns additionally.But not sure about the performance.


I would still use the data flow for the inserts. Do a conditional split to a staging table with just the rows that need to be updated and go ahead and do your inserts in the pipeline. Then you can use an Execute SQL Task to do a set-based update. The alternative would be an OLE DB Command to do your updates but that will be RBAR. So this is one case where a staging table makes a lot of sense.

Post #1384370
Posted Tuesday, November 13, 2012 11:53 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 19, 2013 4:30 AM
Points: 78, Visits: 195
Randy Knight (11/13/2012)
rhythm.varshney (11/13/2012)

Yes, actually if you see my comments I mentioned that I am performing UPINSERT logic on the basis of staging table which hold around 50 K records daily basis.So was thinking to handle the split on the same.As was having the staging table and did not want to insert derived columns additionally.But not sure about the performance.


I would still use the data flow for the inserts. Do a conditional split to a staging table with just the rows that need to be updated and go ahead and do your inserts in the pipeline. Then you can use an Execute SQL Task to do a set-based update. The alternative would be an OLE DB Command to do your updates but that will be RBAR. So this is one case where a staging table makes a lot of sense.



That is the case I can not use set based approach as updates may be on different columns and with different pattern and so.Hence will have to use RBAR. So what would be the recommendation for my requirement i.e. I need to process 50K records daily basis from an excel to database table and I need to use UPINSERT logic along with few splits on certain columns.
So far I used a staging table for this and handling both on the same.However looking for better approach like derived column transformation or else but at the same time do not want to increase overhead on SQL engine.
Post #1384406
Posted Wednesday, November 14, 2012 9:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 28, 2014 12:58 PM
Points: 24, Visits: 149
You can do everthing you want to in the data flow. Conditional split to separate the inserts and updates, and if you truly do need to do your updates RBAR then an OLE DB Command for those.
Post #1384714
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse