Nested Using statements affects performance

Question

     using (var db1 = new DataBase1Entities())
        {
           using (var db2 = new DataBase2Entities())
           {
                var list = (from obj in db2.Table1
                            where !db1.Table2.Any(i => i.Table2Col == obj.Table1Col)
                            select obj).ToList();
           }
        }

does anyone know how to retrieve value from one database table and compare it with another database table ? If the above code is correct, then will it cause performance issue ?

Nested `using` is common pattern to use multiple `IDisposable`s. — Sinatr, Nov 09 '17 at 12:43
It's not `using` but resource allocation/freeing that affect the performance; another resource consuming process is the code inside `using` - "code to retrieve data from db1 and compare it with db2" — Dmitry Bychenko, Nov 09 '17 at 12:45
"affect performance" relative to what? Obviously *having code* will *take time to execute*. **What exactly is the question here?** — Lasse V. Karlsen, Nov 09 '17 at 12:48
`"affect performance" relative to what` Based on the context, presumably compared to not using nested using. i.e. have the second `using` **outside** of the first. — mjwills, Nov 09 '17 at 12:50
What type is `db1`? Or: which ORM is this? For the rest: [race your horses](https://ericlippert.com/2012/12/17/performance-rant/). — Gert Arnold, Nov 09 '17 at 13:09
What are the sizes of the `Table1` and `Table2`? What is the expcted fraction of the `Table1` after filtering? — Dmitry Bychenko, Nov 09 '17 at 13:10
no it didnt work.. since i have bulk records in both the tables in different database. i need to get the records from one database table first and compare it with another database table. Is there any other easiest way to do this ? — Dinesh M, Nov 16 '17 at 12:46

MakePeaceGreatAgain · Answer 1 · 2017-11-09T13:01:45.897

5

You seem not to know what a using-statement does. Effectively it´s nothing but a try-finally-block where within the finally there´s a call to Dispose. So your code is translated to the following:

DataBase1Entities db1 = null;
try 
{
    db1 = new DataBase1Entities();
    DataBase1Entities db2 = null;
    try 
    {
        db2 = new DataBase2Entities()
        // do something with db2
    }
    finally 
    {
        if(db2 != null) db2.Dispose(); }
    }
}
finally
{
    if(db1 != null) db1.Dispose();
}

So it´s not the using that may or may not have an effect on performance, but the Dispose. However you shouldn't bother for that at all, as you have to call Dispose. Doing not so is a really bad idea - in particular if it´s done because of any performance-issues. Dispose will release any unmanaged resources, e.g. file handlers. If you´re not calling it there´s no way to release those resources at all which will probably produce memory-leaks.

edited Nov 09 '17 at 13:01

answered Nov 09 '17 at 12:45

MakePeaceGreatAgain

35,491
6
60
111

1

I don't know, but +1 from me. – ProgrammingLlama Nov 09 '17 at 12:54
don't know neither, but because of that +1 from me too. – M. Schena Nov 09 '17 at 12:55
if you check for `null` the code should be `DataBase1Entities db1 = null; try {db1 = new DataBase1Entities(); ... } finally {if(db1 != null) db1.Dispose()};`. Local variables (`db1`, `db2`) are *not initialized* with `null`, that's why `if(db1 != null)` doesn't sence in the current code – Dmitry Bychenko Nov 09 '17 at 12:59
@DmitryBychenko You´re right. Corrected appropriately. – MakePeaceGreatAgain Nov 09 '17 at 13:01
That's nice `using {...}` pattern explanation, +1 – Dmitry Bychenko Nov 09 '17 at 13:07
3

I didn't downvote, but neither do I think that because of an unexplained downvote people should upvote. Try to understand the downvote instead. Like: this may be a nice explanation, but it doesn't answer the question(s). – Gert Arnold Nov 09 '17 at 13:14
I did upvote, but not because there was a downvote or a missing reason for a downvote - In my opinion the question doesn't make sense (sorry!) and this answer could help the OP understand why. – C.Evenhuis Nov 09 '17 at 15:09

score 1 · Answer 2 · answered Nov 09 '17 at 13:09

You could certainly remove the nesting if you wanted to. Something like (untested):

HashSet<YourType> bob;

using (var db1 = new DataBase1Entities())
{
    bob = new HashSet<YourType>(db1.Table2.Select(z => z.Table2Col);
}

using (var db2 = new DataBase2Entities())
{
     var list = (from obj in db2.Table1
                 where !bob.Contains(obj.Table1Col)
                 select obj).ToList();
}

You'd need to profile it to check whether it was any faster. Much of the speed benefit, if any, would likely be due to the use of the HashSet rather than the removal of the nested using.

Nested Using statements affects performance

2 Answers2