Wednesday, January 10, 2007 11:40 PMWell, I'm just pulling hairs trying to figure out the following issue.
This example performs the same way with SqlConnection/OdbcConnection/OleDbConnection etc.
I'm connecting to A SQL Server 2000 Server. My development is done remotely so I have a PPTP VPN connection between my development box and my DB Server.
I'm running .NET aspx code C#.
This code is being run against a very large table (I tried various other large tables with same result)
I've isolated the problem with the DataReader.Close() function. It just takes a VERY long time to return. The more data is in the Result Set of the SELECT statement, the longer it takes. So if I add TOP 10000 to the SELECT statement, it takes 5-6 seconds to close....and it get progressively worst.
And while Close() is hung, I check my VPN thruput (DB Access is only data flowing), I can see a constant Download stream activity going on until the Close() returns. What is going on, why would Close force a fetch of the whole dataset? A Close() should be able to close off the Result set on the SQL server by just dropping it no?
Connection = new SqlConnection("HIDDEN");
Command = new SqlCommand("SELECT * FROM AlarmLog",Connection);
//The execute is very quick (< 1 second)
Reader = Command.ExecuteReader();
//The Close takes a VERY long time
Thursday, January 11, 2007 7:32 AMThe Close method fills in the values for output parameters, return values and RecordsAffected, increasing the time that it takes to close a SqlDataReader that was used to process a large or complex query. When the return values and the number of records affected by a query are not significant, the time that it takes to close the SqlDataReader can be reduced by calling the Cancel method of the associated SqlCommand object before calling the Close method.
Monday, August 24, 2009 2:02 PMHi,
I am also facing same problem. SqlCommand.Cancal() gives only limited performance improvement.
Have you find any alternative solution?
Please let me know if you have done any workaround.
Thanks in advance for any suggation.