Salesforce Input Performance

[mpurdy]

I am trying to extract the Contact ID (from Salesforce) for all the records in my Alteryx job. I have a field that uniquely identifies these records and the only way that I can see to extract particular records from Salesforce is to create an Alteryx Macro and update the WHERE condition of the Salesforce Input using this field. But this method seems to be very slow. I am not sure if it is because the lookup on the particular field is slow (in Salesforce) or if there are a lot of overheads in Alteryx (does it re-establish the Salesforce connection for each row?). I don't have access to execute the query in Salesforce but I have asked our developer to run the Query Plan for it to make sure that it is not a Salesforce issue. Does anyone have any advice or suggestions on how to run a query in Salesforce to find the Contact ID for a list of constituents? There are too many records in Salesforce to extract them all out and match in Alteryx. Thanks in advance.

[NeilR · Accepted solution]

If you're using a batch macro to execute a single query for each lookup record then, yes, there will be overhead in kicking off the connection to salesforce for each lookup record. I'd suggest using the IN operator to group multiple lookup values into each query. The syntax in the where clause would then, for example, look like:

id in ('a12345','b67890')

I've attached a workflow that demonstrates how to construct the where clause for 100 lookup values at a time. If you feed this into your batch macro instead of the single lookup value then it could potentially increase performance close to 100X. You could try increasing the number even higher, but keep in mind that the 10.0-10.1 version of the Salesforce Input tool has a maximum WHERE clause length of 10,000. Hope this helps.

[mpurdy]

Thanks for your suggestion and example, it was very useful. I have copied the logic and tested it for my scenario and it performs a lot better than individually querying for each ID.

I just want to make sure that updating the WHERE condition of the Salesforce Input using a Macro in Alteryx is the only way that you can query specific ID's from Salesforce isnt it? There is no other way that I should be using to achieve this? Thanks

[NeilR]

Correct, this is the only way currently to do this. However, in a future release you will have the ability to submit a custom Salesforce Object Query Language (SOQL) query from within the tool. It's possible that you'd be able to translate what you're doing in your Alteryx workflow (joins, etc) to SOQL, but it depends on what you're doing.

[mpurdy]

Ok, thanks for all your help. I'm looking forward to this future update, any idea on when it is scheduled for? It would make life a lot easier!

[NeilR]

Soon 

[asteryx]

Hi,

Hoping you're still subscribed to this old thread...

I'm having a similar problem. I have a long list of records to pull from Salesforce and have built a macro that feeds the Salesforce connector a "table" name and a where clause that update the connector inside the macro.But I have two problems: the where clause gets truncated by Alteryx, and I get "URI too long" error from Salesforce once I get the strings through.

My first question: I'm using 11.7, so I should be able to use SOQL, but I don't see how to update that using a macro (so I can send different queries without editing the connector each time). Can you explain that if it is possible?

Second,

I have tried your workflow above, but I still get truncation. My workaround is the following, but it would be much better to use the summarize tool. I have tried putting a select or a formula in front that forces a field type (using W_String and V_WString), but it doesn't seem to help. I don't understand why the truncation continues to occur. It seems to be limited to 264 because the largest batch I can run is 21 for records that are 11 characters each. Then I have 50 separate queries to Salesforce (and 1000 is actually a small sample).

The flow below is:

- Input Tool: a single column (WhereValue) with about 1000 rows

- Formula Tool adds a new column (WhereList = null) to hold the concatenated list

-Multi-Row Formula to build a single row including every record (see formula in pic)

-Sample Tool takes last row which has the complete result

-Formula Tool to Trim the extra ',' from the result

I appreciate any help.