Vipul123


Hi

I have a made a simple mapping connecting source and destination on SQL server on local box. I am getting ~36K rows/min as the thru put. I only want to use ole db destination data access mode as SQL query (dnt want to use fast load).

I am doing this test in order to set a bench mark for a custom component which i have developed. With this result i can figure out how much time my custom component is taking.

Experts, please let me know your views on the thru put which i am getting is it good bad or ok with the scenario i am testing and also if there are some ways to improve it.

Thanks,

Vipul




Re: SSIS Perfromance issue using oledb destination

JayH


600 rows/second seems pretty good for not using fastload.

However, if you're trying to benchmark performance of a component, you probably shouldn't be pushing the rows into a database. Your component will probably be an order of magnitude faster than the database and thus you won't observe any slowdown.

If you want to see how much your component adds to a Data Flow, you want the Data Flow to be as fast as possible. I'd suggest you use a custom script source component to generate data (faster than reading data from somewhere else and removes any vagaries introduced by the source) and a Union All for a destination. If you really want to use a source, I would load your data into a recordset in memory using a separate task or data flow and then shred it in the data flow you want to benchmark.





Re: SSIS Perfromance issue using oledb destination

Phil Brammer

Not using fast load performs a costly write to the transaction log for every record.








Re: SSIS Perfromance issue using oledb destination

Vipul123

Phil:

Can you also tell your view is 600 rows/sec is good

Thanks,

Vipul





Re: SSIS Perfromance issue using oledb destination

Vipul123

Also, the custom component which i have developed is doing things like resolving keys of the dimension in the fact, resloving date keys, doing currency conversion, checking for Type 1, Type 2 in the rows. And this is a destination component, so the component is forming the insert/update/delete query for a row set.

I am getting a thru put on 100 rows/sec using all these things. I am bit concerend abt the performance here. I am also doing code optimization but still lot needs to be done in this i guess looking at the overall thru put.





Re: SSIS Perfromance issue using oledb destination

Phil Brammer

You have to keep in mind that performing row by row inserts/updates is not the most efficient way to do things.

Your custom component shouldn't write/update changes to the database, rather it should write to a staging table the changes (updates) and try to perform a bulk insert so as to avoid that performance hit. Then you can issue a batch update using the staging table as the source for the changes.

I question what it is that you are doing though, that's different than using the predefined components of SSIS.





Re: SSIS Perfromance issue using oledb destination

JayH

Vipul123 wrote:

Also, the custom component which i have developed is doing things like resolving keys of the dimension in the fact, resloving date keys, doing currency conversion, checking for Type 1, Type 2 in the rows. And this is a destination component, so the component is forming the insert/update/delete query for a row set.

I am getting a thru put on 100 rows/sec using all these things. I am bit concerend abt the performance here. I am also doing code optimization but still lot needs to be done in this i guess looking at the overall thru put.



Well, that makes a little more sense why you want to benchmark against writing to a database. Question is, what type of activity were you doing to get that 600 rows/sec Likely it didn't have the randomness or concurrency your component generates.

I don't think you should concern yourself too much about the database performance, nothing your component can do about that. That's just the nature of trying to manage dimension changes in SSIS, unless you want to drop the changes back to staging tables.




Re: SSIS Perfromance issue using oledb destination

Vipul123





Re: SSIS Perfromance issue using oledb destination

JayH

Vipul123 wrote:

Phil:

There were many issues when we first started to develop a package in SSIS. Like in order to resolve dimension i have to use lookups, some of my fact tables have arnd 10 and more dimensions in that case i have to make 10 lookups on different tables. This will increase the overhead of the 10 more components in the package. Is there a disconnected lookup in SSIS Previously i was using Informatica power center, that has this great concept of lookups which you can call as functions from an expression transform(Derived column in SSIS).

I have aroung 60 facts and 20 dimensions. Now doing the same this things for all the packages would have taken lot of time and effort during development. Instead i developed a custom component which is going to do the same for me so the dev time is decreased a lot. But yes there are many concerns like performance now.

Do you have any test results for such kind of warehouse, Also if you have any suggestion reg the design please let me know.

Thanks,

Vipul



I probably would have advised you to focus your development effort on a tool to generate packages, rather than rewrite SSIS functionality inside a component. They way you're approaching it, why even use SSIS





Re: SSIS Perfromance issue using oledb destination

jwelch

JayH wrote:
Vipul123 wrote:

Phil:

There were many issues when we first started to develop a package in SSIS. Like in order to resolve dimension i have to use lookups, some of my fact tables have arnd 10 and more dimensions in that case i have to make 10 lookups on different tables. This will increase the overhead of the 10 more components in the package. Is there a disconnected lookup in SSIS Previously i was using Informatica power center, that has this great concept of lookups which you can call as functions from an expression transform(Derived column in SSIS).

I have aroung 60 facts and 20 dimensions. Now doing the same this things for all the packages would have taken lot of time and effort during development. Instead i developed a custom component which is going to do the same for me so the dev time is decreased a lot. But yes there are many concerns like performance now.

Do you have any test results for such kind of warehouse, Also if you have any suggestion reg the design please let me know.

Thanks,

Vipul


Having 10 lookups in a data flow isn't necessarily a lot of overhead. It really depends on how much data is included in each lookup. Is your concern just the number of components






Re: SSIS Perfromance issue using oledb destination

Vipul123

The way you think JayH, microsoft shouldnt have exposed all these API to develop the custom components. They have and then it makes more sense to use it for the purporse if its reducing the development time. Anyways, thanks for all your suggestions. Using my component i am able to achieve 80% of the performance achieved by OLEDB destination.



Re: SSIS Perfromance issue using oledb destination

JayH

Vipul123 wrote:
The way you think JayH, microsoft shouldnt have exposed all these API to develop the custom components. They have and then it makes more sense to use it for the purporse if its reducing the development time. Anyways, thanks for all your suggestions. Using my component i am able to achieve 80% of the performance achieved by OLEDB destination.


I certainly wasn't criticizing the use of the API. I use it all the time and have developed my share of custom components. I was just wondering if you used it to your best advantage. In particular I was responding to your comments:

Vipul123 wrote:
I have aroung 60 facts and 20 dimensions. Now doing the same this things for all the packages would have taken lot of time and effort during development. Instead i developed a custom component which is going to do the same for me so the dev time is decreased a lot. But yes there are many concerns like performance now.


You make it clear that you want a data flow that is flexible enough for differing metadata in order to reduce the time of developing packages for each dimension. You also indicate in other posts that you are doing lookups, detecting changes, and issuing statements to the destination from inside your component. If that is the case (and since you didn't correct me, I assume it is), then your custom component sounds like a whole data flow.

I understand your desire to reduce development time. I was merely remarking that if I were fully informed about you're scenario, which I'm not, I think I would have come down on the side of using the API to generate multiple native SSIS packages (as I believe most people in your situation do), rather than create one flexible custom component that extensively reinvents existing functionality.

Anyway, I'm glad you got acceptable performance and that we were able to help.





Re: SSIS Perfromance issue using oledb destination

Vipul123

thanks dude, appreciate your help.