Cache Split Memory Reader...Job is running very very long...

dataservices performance
1

Hi,

I have a Data Services job , where it is reading the data from a input file(around 900K records) & after validations it is writing into MS SQL table.

When I run the job it took 33 hours .

I went into Performance Monitor log, & in Data Flow it spend 112,132 seconds. & it clicked into data flow log , at one step it spend 110,808 seconds.

That step is
Begin*
CacheSplitMemoryReader Cache read (non-indexed) 2011-02-10 10:27:59 2011-02-11 17:14:47 110,808 825669

CEII520_STG Target (MS SQL Server) 2011-02-10 10:27:59 2011-02-11 17:16:10 110,891 825669
End*****

Can anybody tell me what is this ‘CacheSplitMemoryReader’ why the job spend so many hours just before writing the data into Database table ‘CEII520_STG’. Is it because of memory issue OR because of DB table?
I don’t have any joins for the target table. It is single stright forward table.

All other jobs with more input file size are finished in 1-2 hours.

Not sure why this is happening.

Pls help me in this regard.

Thanks,
Sarma

dormarthi (BOB member since 2011-01-26)

2

http://wiki.sdn.sap.com/wiki/x/8gDyAg

more details on how to find out what is going on
https://wiki.sdn.sap.com/wiki/display/BOBJ/Joining+tables+in+the+engine

Werner Daehn :de: (BOB member since 2004-12-17)

3

Hi wdaehn,

Thanks for the reply.

But in my job I don’t have any joins. A input file is coming & some validations & move the good records into target table.

1 File , 1DB table. So I’m not sure where to analyze further on ‘Cache SPlit Memory Reader’.

Pls help.

Thanks,

dormarthi (BOB member since 2011-01-26)

4

There has to be more than that, if no join all I can think of is a Table Comparison. Comparing source with target is technically speaking, something very close to an outer join.
If I am right with this TC, it depends on its settings, e.g. detect-deletes?

Werner Daehn :de: (BOB member since 2004-12-17)

5

Even the transform TC is not using in my job. 2 days back again I ran the same job it took around 40 hours…Spending on the same step ‘Cache Split Memory Readre’. This time it spends even more time(144,070 sec) …

Begin
CacheSplitMemoryReader Cache read (non-indexed) 2011-02-23 15:34:06 2011-02-25 07:33:29 143,963 825669 0

CEII520_STG Target (MS SQL Server) 2011-02-23 15:34:06 2011-02-25 07:35:16 144,070 825669 0

End**

Now I have changed some settings with Bulk load/Degree of parallalism/Network Packet size… to see if there is any improvement.

Thanks,

dormarthi (BOB member since 2011-01-26)

6

A screenshot of the dataflow please…

Werner Daehn :de: (BOB member since 2004-12-17)