2

I'm querying a SQL database and want to save the query results in a local mongo db repeatedly (e.g. every 30 minutes). The SQL data is not very consistent that is to say that there are many null values.

The data which I want to save has this format:

{
  "attribute1": value,
  "attribute2": value,
  "hugeArray": [], // 30.000 entries
}

The array has around 100MB and will not get much larger in the future.

Every 30 minutes the sql query will almost give the same result.

I'm a newbie and I don't know whether patching and updating the few old values in the mongodb is faster than dropping the collection and (re-) adding all values.

I do not really care about the order of which the entries are inserted, I will only make one SQL query and store that data in my mongodb database.

I can't make use of documents because of this restriction:

The maximum BSON document size is 16 megabytes.

Is a capped collection a good choice for my circumstances or are there any better solutions and can you reason that?

Matthias Herrmann
  • 2,650
  • 5
  • 32
  • 66
  • Capped collections still have 16MB per document limit. – Alex Blex Sep 27 '17 at 08:13
  • @AlexBlex oh you're right, I haven't seen that number in the restrictions of the capped collection, so I got confused. I'm going to store each sql row in a document. – Matthias Herrmann Sep 27 '17 at 08:30
  • how many of these documents will you have? Would it make sense to put the hugeArray in a collection of its own? – robjwilkins Sep 27 '17 at 08:54
  • @robjwilkins Do you mean I should store each element in the array in an extra document, in a seperate collection for the array? Then yes it would make sense but I don't know how it effects the performance. The elements in the array do not have a complex data structure and have only primitive data types. – Matthias Herrmann Sep 27 '17 at 09:04
  • 1
    @MatthiasHerrmann yes that is what I was thinking. You could perhaps do a bulk write. I think it would be fast to write, but worth testing – robjwilkins Sep 27 '17 at 10:02
  • @robjwilkins Ty, i will implement it like that and Test the Speed – Matthias Herrmann Sep 27 '17 at 13:52

0 Answers0