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!

Improve this question
10
  • 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, 2017 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.
    – Stan Alachniewicz
    Aug 7, 2017 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, 2017 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?
    – Stan Alachniewicz
    Aug 7, 2017 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, 2017 at 22:20

1 Answer 1

Reset to default
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

Improve this answer
1
  • 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.
    – Stan Alachniewicz
    Aug 7, 2017 at 14:40

You must log in to answer this question.

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