BUG: Connection Pooling does not work Native SqlClient Repost (from microsoft.public.dotnet.framework.adonet) RRS feed

  • Question

  • No hacking, no tuning, just Linq To SQL and a DataContext object, no matter
    how quick or how slow the statements are repeated,

    The point is, that if I add Pooling=false to the connection string, the performance improves!  As soon as I add Pooling=true (leave it default), it will connect/reconnect and do that like a madman but sp_reset_connection is executed as well and a lot of reads are caused in SQL Server 2005 Sp3.

    I've had people that answered this question as if I were a newbee, but don't assume this. I know what I'm doing.

    This is .NET 3.5 on Windows 2008 and on Vista and IIS7.
    SQL SErver 2005 SP3 standard edition 1 GB RAM and practically idle (doing nothing!)
    This is an ASP.NET application, the obvious things have been checked for.
    (Such as the connection is is really really really standard!) There is only one special case. It uses an data source=alias that is using a port.

    What can I do else to reach the normal connection pooling goal?

    A statement were such a connection/reconnection happens is here... (it happens anywhere!)

    The bold line is NOT consumed within the same connection, but it closes (physically, and reopens when the tw colllection (line 10) is enumerated. So if 'foot' or 'tw' is having 100 items in it's colletion, the connection is opened and closed for 200 times! So nobody can 'accuse' me for forgetting to dispose a connection. This is native LINQ.

    2 var query = Texts.Where(t => t.bookeditionid == bookEditionId  
    3           && t.bookchapteralinea.bookchapter.chapter == chapter  
    4           && t.bookchapteralinea.bookchapter.bookid ==  
    5                bookeditions.Where(be => be.bookEditionid == bookEditionId).Select(s => s.bookid).Single()  
    6                             ).  
    7              OrderBy(o => o.textid).  
    8            Select(t => new 
    9                        {  
    10                   tw = textwords.Where(p => p.textid == t.textid),  
    11                    foot = TextFootNotes.Where(p => p.textid == t.textid),  
    12                  text = new 
    13 {  
    14                     t.textid,  
    15                         t.Alineaid,  
    16                         t.timestamp,  
    17                         t.bookeditionid,  
    18                         t.BookChapterAlineaid  
    19                         }  
    20                    }.  
    21 ToList();  


    Wednesday, January 7, 2009 11:49 PM