What on Earth is the SFDC Adapter Doing???

dataservices performance
1

A co-worker has a very simple Dataflow:
Source Table (SFDC) --> Query --> Target (Oracle)

The source SFDC table has 6 million rows in it. The Query transform has a WHERE clause of “SFDC_Column IS NULL”. That’s it, nothing else in the WHERE clause, no DISTINCT, no ORDER BY.

The Dataflow is reading every row from the Salesforce table and then applying the WHERE clause at the job server and eventually (hours and hours later!) writes 27,000 rows to the target table.

Is there some trick to getting the WHERE clause pushed down to Salesforce so we don’t read 6 million rows just to get 27,000 rows?

eganjp :us: (BOB member since 2007-09-12)

2

what is the SQL that is getting pushed down to the SFDC ? you can check this in the log

I think IS NULL is not getting pushed down, what else is there in the mapping, if you are using some functions etc in mapping that may also prevent the push down, best is to do all the mappings etc in another Query transform, keep the first query transform for selecting the row and where clause

manoj_d (BOB member since 2009-01-02)

3

I haven’t pulled the syntax that is being sent to SFDC. I’m not sure where to find this or if it is already being generated. Do I need to turn on tracing to see it?

There is nothing special in this Dataflow. The mappings are all straight column to column mappings. The only thing “custom” about the Dataflow is the WHERE clause.

Pulling the data down takes 14 hours. It takes only a few minutes to push the 27,000 rows back up to SFDC as updates.

This is happening on version 12.1 of Data Services.

eganjp :us: (BOB member since 2007-09-12)

4

yes, you will have to enable trace for the adapter instance (you can do this from Management console, from Adapter configuration page), restart the instance

before running the job move or delete the %LINK_DIR%\adapters\log<sfdc_adapter_instance_name>_trace.txt and <sfdc_adapter_instance_name>_error.txt files, this to get the latest trace and avoid opening a very big file

run the job and check the log file

manoj_d (BOB member since 2009-01-02)

5

Performing a trace is going to take a while to coordinate.

In the mean time we modified the Dataflow that had the problem and changed the IS NULL expression to = ‘5688958’. The Dataflow completed in less than 2 seconds. Based on the results, I have to conclude that the IS NULL expression is not getting pushed down but an equals expression does get pushed down.

eganjp :us: (BOB member since 2007-09-12)

6

try setting the where condition as below
SFDC_Column is null
instead of
SFDC_Column IS NULL

I don’t think case of IS NULL should matter but give it a try and see if that works

manoj_d (BOB member since 2009-01-02)

7

We changed the expression to:

SFDC_Column = '' (double single quotes)

The performance is very similar to

SFDC_Column = 'xxxx'

Where ‘xxxx’ is a value that will produce a result set of very few rows.

So we’re happy now, but the IS NULL should have worked the same way in my opinion.

eganjp :us: (BOB member since 2007-09-12)

8

for IS NULL, did you try to pass the “IS NULL” key word using lowe case instead of upper case ?

is null instead of IS NULL

manoj_d (BOB member since 2009-01-02)

9

We just tried IS NULL in both upper and lower case. Both versions appear to not get pushed down (up?) to Salesforce.

eganjp :us: (BOB member since 2007-09-12)