Multiple Query Plans

As my last post, this is again something I got from MongoDB.

Execution plans are vital to measure performance of your queries. How many times, you are puzzled with why that index is not using? why it is doing a scan?

In MongoDB, there is a option to view all the execution plans with query engine considered and what was used eventually.

syntax for that is db.users.find({UserID:14049}).explain(true)

explain() will tell you what is the query plan used and true parameter will give you all the query plans considered for the execution.

However, though this is a great feature to have, in case of SQL Server implementation may not be easy. In MongoDB anyway queries have one collection only and query plan is not complex

Dropping Duplicates

If you search for "Duplicate rows sql server", in Google you will get about 136,000 results. This will tell how series the problem is. I am not going to add another post to above huge number of articles. What I going to explain here is how some other database system does this.

I was able to attend to a MongoDB conference last month and came across few features which are nice to have features in SQL Server.

In MongoDB, there are few ways for creating indexes, one is stated below.

db.users.ensureIndex({UserID:1},{"unique":true, "dropDups":true})

Just to tell you what the above script is, users is the "table" (Collection in MongoDB) and UserID is the "column" (Attribute in MongoDB") and ensureIndex is the command to create the index.

so, in this statement, unique:true will create a unique index BUT if there are duplicates records, it will retain only the first "record" ( Document in MongoDB" ) and others will be deleted because of the dropDups:true statment.

Now, this is very efficient way of deleting duplicates, but you need to pay more attention before issuing this command.