![]() ![]() That key field might not exist in the referenced document, in which case it is assumed to be null. The $lookup process matches the foreign field with the local field from the input documents that come down the pipeline. This field defines the documents that you will collect as an array of documents. When you do a lookup in MongoDB, the key field that you specify in the aggregation stage is the field of the documents in the collection you are looking up. We execute the following SQL query in MongoDB using Studio 3T, a MongoDB GUI with a handy SQL Query feature. We will also use the MongoDB profiler to check on the strategy being used. Querying without an indexįor our example, we will start without any indexes and then add them later, making sure that they are being used by checking the timings. I include the extended JSON for this database with the article. It also allows us to do a direct comparison of the two database systems, though we do this with due warnings that it is like comparing apples and kittens. I chose this because you need to do several lookups to get reports from it, and it can come up with some awkward migration problems that are useful for our purposes. Just as a test-bed, we’ll use a conversion to MongoDB of SQL Server’s classic practice database, AdventureWorks. It is the database equivalent to cooking the meal beforehand in the kitchen rather than requiring each guest to cook their own meal at the table. I’m going to argue that, if you provide and maintain summary collections, known as aggregates or pre-aggregates, and you use a thread that is separate from the application in order to maintain them when the data in the tables change, then it doesn’t matter so much. Some people argue that document databases should de-normalise the data to get rid of the requirement for lookups. ![]() These require a lot of lookups, especially for reporting. Why bother with joins in a document database? As well as creating entirely new databases in MongoDB, we are beginning to see a lot more databases ported to MongoDB from the relational. This article explains how to make such a MongoDB database perform reasonably when reporting historic and slowly-changing information. ![]() Data that is liable to change is always best stored in one place only. We can, of course, argue that collections of documents render joins unnecessary, but this is only true of relatively static, unchanging information. We can help Lookup along by providing an index that allows it to do an index nested loops join, but beyond that we have difficulties in getting dramatic improvements in the performance of any ‘JOIN’. It is far more suitable for enumerations where there is a limited range of alternatives. Lookup, the MongoDB equivalent to Joins, cannot yet do Merge joins or hash joins, so is never going to be fast in the current form. Even with indexes in place, some operations that involve aggregation are a lot slower than they are with relational databases: So it is when using ‘joins’ between collections. MongoDB has a prodigious appetite for unstructured data and has its place in a Database Developer’s toolkit. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |