1

So I've been writing these queries to get a summarized report of Opens, Clicks, Sent, Delivered, DeliveryRate.

To date I've been getting this data from the Data Views, but I ran into a snag.

We have been for the past three months recording attributes in every send using the __AdditionalEmailAttribute fields under properties.

Those attributes are stored in the SendLog, but as far as I can tell, there is nowhere else to grab them from. Is that right?

I can rejigger my queries to pull everything from the SendLog, but my SendLog is 33,504,805 rows at the time of this post, and I don't think we have any plans to clear it any time soon.

Is that HUGE? Is it safe to run queries on such a large DE?

What's better, performance wise? Querying the Data Views or the SendLog? I guess they are all just tables in the system so maybe it doesn't matter, but the size of the SendLog is really concerning me.

Please advise. Thanks!

  • 2
    You should always query data views and sendlogs based on a where clause that ties down the data to something a bit more relevant like getting all data where EventDate is equal to whatever you need it to be. But getting all the data from the data views is definitely going to cause you performance issues and especially if you are joining multiple data views. – thechrishaddad Aug 7 '17 at 14:10
  • I'm doing it with separate queries, so not a lot of joins. But I don't like how messy it is and there are several steps to make it work, and a lot of queries to keep track of. – garek007 Aug 7 '17 at 14:39
  • You should definitely look at controlling the size of your send log, but Chris is correct, IMHO. It's a bit of an ordeal now with an account in flight, but putting a retention policy on the table's a good design and hive off records somewhere else. On larger data, lots of queries is the most reliable way of running reports/automations without timeouts. Not elegant, but reducing the amount of data you're working with for a given report's the way to go. Your Send Log has an index on [JobID, ListID, BatchID, SubscriberID]. Try and always access it using the index. – Macca Aug 7 '17 at 14:53
  • @AndrewMcLeod, what do you mean an index? So how big is too big for the SendLog? And how would I hive off records? To another table you mean? – garek007 Aug 7 '17 at 15:10
  • 2
    @garek007 Indexes improve the performance of queries you execute that include the indexed fields in the WHERE clause or joins in your query - en.wikipedia.org/wiki/Database_index. Marketing Cloud automatically creates indexes on the fields mentioned above for this data extension, on fields you define as the Primary Key of your Data Extension or on fields that you define in your join when you add to Contact Builder. When you need to run a report or select data from such larger tables as part of an automation, for performance reasons, break the data down into chunks where you can. – Macca Aug 7 '17 at 22:20
0

Working on a similar problem. That send log is definitely a performance concern. I would suggest creating a separate process that breaks the tables into smaller chunks and use primary keys on the target data extensions

  • That's what I am doing now, but I don't like it. a lot of items to keep track of. Maybe when I'm done I'll post my query so it can help others. – garek007 Aug 7 '17 at 14:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.