2

We use the SOAP API method call Retrieve() to get back users' sends, opens, clicks, unsubs and bounces. We specify the required event-type and date-range.

One of our customers have now accumulated so much data for most of these event-types that we start seeing SQL Server type time-out messages: “Error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.”

This happens after 12 minutes. It is not our WCF webservice call timing out, because we have set the timeouts for that to 30 minutes; WCF shows different style time-out messages anyway, this message is typical SQL Server client exception message, presumably generated on ET's database server-side.

Even when we make the time-interval really small (down to 1 second), we still get the time-out. I suspect the database is only indexed on event-type (and we have MANY records for most of these), not including response-date on top of that - or at least that would explain this result.

We already logged this with support who suggested using tracking extracts instead, but given the major urgency with our customer we simply have no time to refactor our app.

Instead it was suggested to increase the time-out. We had been recommended to visit this place to find out how but whatever keywords I use, I can't find a solution. I actually doubt whether there IS a "secret" setting or property for this, given the obvious risk of clients extending the SQL Server time-out on ET's databases. Still if anyone knows one then we'd love to hear about it ;)

2 Answers 2

1

I had this same problem, the trick is to add more criteria in your filter. In your case I would suggest adding Client.ID to the Complex Filter Part. It will still be slow, but it should at least come back without a timeout.

See my similar question here and look at the answer I posted with some example SOAP calls. You can see the types of filtering I used. See: Retrieve NotSentEvent

If it is still too slow I would suggest using a Tracking Extract in conjunction with a Program to post all tracking data to an FTP either daily or hourly.

2
  • Cheers for that. We query by account, event type and start/end date - I think that is as much as filtering goes? The problem is not the amount of data - even a time-span of a single second times out. I believe there I an indexing issue, not including the date/time: That means that if a customer has many records of a certain type, then the database still needs to trawl through huge amounts of data to pick out the correct time-interval. Obviously, that is just my wild guess but would explain it.
    – user9814
    Jun 10, 2014 at 16:30
  • Yeah, I don't disagree, but for some reason adding Client.ID into a complex Filter worked for me. I'm able to retrieve tracking data over the timespan of days or even weeks now without timeouts.
    – Jon Sakas
    Jun 10, 2014 at 16:45
0

You can change the timeouts with custom binding. Have a look at https://help.exacttarget.com/en-US/technical_library/web_service_guide/getting_started_developers_and_the_exacttarget_api/connecting_to_the_web_service_api_using_wcf/

If the data set is STILL too large, you should consider logging into the ET app and writing a Query Activity, and extracting the results to your FTP via a DataExtract Activity and a FileTransferActivity.

Hope this helps.

1
  • Cheers for your response Timothy. It is not the webservice timing out (our WCF time-outs are set to 30 minutes), but the database command, presumably dealing with the query. The message I quoted above is a SQLClient exception message, not WCF or IIS. It does so after 12 minutes consistently. It is also no data-volume issue: even when specifying the tiniest time-interval (expected to return no- or hardly any data) we still suffer from the same issue.
    – user9814
    Jun 11, 2014 at 9:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .