I created a method (OutputFile) that takes in data using SQlDataReader and then outputs it to a local file using StreamWriter. To test that the method output the correct data I created a console application and when the function is called from this it takes roughly 4 minutes which was expected as the stored procedure involved brings back a lot of records (~200,000).
public void OutputFile(string query, string path, params object[] parameters)
{
string connectionString = ConfigurationManager.ConnectionStrings["Database"].ConnectionString;
using (var connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(query, connection))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
// Open connection
con.Open();
Debug.WriteLine("Starting FileStream");
using (FileStream fileStream = new FileStream(path, FileMode.Create, FileAccess.Write))
{
Debug.WriteLine("Execute Reader");
using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess))
{
using (StreamWriter writer = new StreamWriter(fileStream))
{
Debug.WriteLine("Writing Files");
while (reader.Read())
{
char[] buffer = new char[4096];
int charsRead = 0;
using (TextReader data = reader.GetTextReader(0))
{
charsRead = data.Read(buffer, 0, buffer.Length);
if (charsRead > 0)
{
writer.WriteLine(buffer, 0, charsRead);
}
}
}
}
}
}
}
}
}
When I moved the method into my MVC application the execution time increased greatly. The first time I ran the application it took 14 minutes to output the file and the second time it took 30 minutes. I have included the output from the debug windows below.
When running the application I made sure to turn check thrown CLR exceptions, but none were thrown.
Thanks in for advance for any help that can explain why this issue is occurring.
Additional notes about the MVC application (the console application did not include these references)
- Uses Entity Framework and Unity Framework
- Uses Glimpse
Debug output from the file that is produced in 14 minutes
Starting FileStream
Execute Reader
The thread 0x56c has exited with code 259 (0x103).
The thread 0x1194 has exited with code 259 (0x103).
The thread 0x568 has exited with code 0 (0x0).
The thread 0x13f8 has exited with code 259 (0x103).
The thread 0xb6c has exited with code 259 (0x103).
Writing Files
The thread 0xfa8 has exited with code 259 (0x103).
The thread 0x136c has exited with code 259 (0x103).
Debug output from the file that is produced in 30 minutes
Starting FileStream
Execute Reader
The thread 0x1290 has exited with code 259 (0x103).
The thread 0x1030 has exited with code 259 (0x103).
The thread 0x11cc has exited with code 259 (0x103).
The thread 0x1310 has exited with code 259 (0x103).
The thread 0x1d4 has exited with code 259 (0x103).
The thread 0xbb8 has exited with code 259 (0x103).
The thread 0x179c has exited with code 259 (0x103).
The thread 0x1390 has exited with code 259 (0x103).
The thread 0x169c has exited with code 259 (0x103).
The thread 0x16a0 has exited with code 259 (0x103).
The thread 0x1424 has exited with code 259 (0x103).
The thread 0x16e8 has exited with code 259 (0x103).
The thread 0x14f0 has exited with code 259 (0x103).
The thread 0x1458 has exited with code 259 (0x103).
The thread 0x17f8 has exited with code 259 (0x103).
The thread 0x1094 has exited with code 259 (0x103).
The thread 0x1484 has exited with code 259 (0x103).
The thread 0x1540 has exited with code 259 (0x103).
The thread 0x1694 has exited with code 259 (0x103).
The thread 0x17c8 has exited with code 259 (0x103).
The thread 0x1660 has exited with code 259 (0x103).
The thread 0x1764 has exited with code 259 (0x103).
The thread 0x1548 has exited with code 259 (0x103).
The thread 0x172c has exited with code 259 (0x103).
The thread 0x17d8 has exited with code 259 (0x103).
The thread 0x1674 has exited with code 259 (0x103).
The thread 0x1380 has exited with code 259 (0x103).
Writing Files
The thread 0x16a8 has exited with code 259 (0x103).
