Another story from the app with the sprocs. That was a classical 2-tier CS application which we transformed into an asp.net 1.1 app. Once a year the application has to copy and transform huge amounts of data. This is done by running a batch of stored procedures which run in one huge transaction after which the database is ready for the next year. It takes quite a lot of time, up till now 8 hours was not unusual. The growing usage of the app will only increase the amount of time needed. Some feedback on the progress would be nice. For a windows client all of this is not that difficult to build, to get something likewise in asp.net is a different ballgame. We managed to get something working. It does have a drawback though; more on that in the end.
The outline of our approach:
- Create a table with a progress message and a timestamp
- Start a transaction and enlist all sprocs
- Start the sprocs one by one
- In between add progress records to the temp tables
- Wrap the process up in a method which is started in a new thread
- Start the thread and navigate to another page which periodically queries the progress table
Some essential parts of the code
A helper function kopieSproc wraps up an individual stored procedure in a SqlCommand, sets the parameters and sets the timeout of the command. Setting up the transaction and enlisting the procs:
SqlCommand cmdPRKOPIEJRBDGAFD = kopieSproc(“dbo.PRKOPIEJRBDGAFD”, idFaculteit, vanJaar, naarJaar);
// More sprocs
SqlCommand cmdPRKOPIEROOSTER = kopieSproc(“dbo.PRKOPIEROOSTER”, -1, vanJaar, -1);
sqlConnection1.Open();
SqlTransaction trans = sqlConnection1.BeginTransaction();
// Alle sprocs in 1 transaction
cmdPRKOPIEJRBDGAFD.Transaction = trans;
// More sprocs
cmdPRKOPIEROOSTER.Transaction = trans;
A helper function reportStatus fires a sqlcommand to write a status record to the table. This command should not enlist in the transaction. The transaction is meant to get an all or nothing result, changes are not visible until the transaction is committed. The status row has to be visible the moment it is added to the table.
Running the transaction:
try
{
reportStatus(cmdStatus, “Start kopiëren cursusjaar”);
reportStatus(cmdStatus, “Start kopieren jaarbudget school”);
rc = cmdPRKOPIEJRBDGAFD.ExecuteNonQuery();
reportStatus(cmdStatus, string.Format(“{0} regels toegevoegd”, rc));
// More sprocs
reportStatus(cmdStatus, “Start kopieren roostergegevens”);
rc = cmdPRKOPIEROOSTER.ExecuteNonQuery();
reportStatus(cmdStatus, string.Format(“{0} regels toegevoegd”, rc));
reportStatus(cmdStatus, “Commit naar database”);
trans.Commit();
reportStatus(cmdStatus, “Kopieren voltooid”);
}
catch(Exception ex)
{
trans.Rollback();
reportStatus(cmdStatus, ex.Message);
reportStatus(cmdStatus, string.Format(ApplicationMessages.CursusJaarKanFaculteitNietKopieren, idFaculteit));
}
finally
{
sqlConnection1.Close();
reportStatus(cmdStatus, “Connectie met DB gesloten”);
}
So these lines of code can take hours and hours to complete. After each successful step, or after an exception a row is added to the table. Never mind the Dutch, the idea should be clear.
The whole process is wrapped up in a method KopieerFaculteit, which is a member of a component in the data layer. A web page starts a new thread for the method and navigates away.
private void maakKopie()
{
int idVanJaar = int.Parse(DropDownListVan.SelectedValue);
int idNaarJaar = int.Parse(DropDownListNaar.SelectedValue);
JaarData.KopieerFaculteit(int.Parse(DropDownListFaculteit.SelectedValue), idVanJaar, idNaarJaar);
}
private void Button1_Click(object sender, System.EventArgs e)
{
System.Threading.Thread t = new System.Threading.Thread(new System.Threading.ThreadStart(maakKopie));
t.Start();
Response.Redirect(“../SysteemBeheer/KopieerStatus.aspx”);
}
The maakKopie method collects the parameters from the form and will keep running for quite some time on its own thread. The user is redirected to the status page.
The follow the status, the status page has to requery the database periodically. Refreshing the page is automated by setting the (browser’s) requery interval in the head of the page to 30 seconds.
<HEAD>
<title>KopieerStatus</title>
<meta name=”GENERATOR” Content=”Microsoft Visual Studio .NET 7.1″>
<meta name=”CODE_LANGUAGE” Content=”C#”>
<meta name=”vs_defaultClientScript” content=”JavaScript”>
<meta name=”vs_targetSchema” content=”http://schemas.microsoft.com/intellisense/ie5″>
<meta HTTP-EQUIV=”REFRESH” CONTENT=”30″>
</HEAD>
The result is that the application is responsive, none of the responses takes long to generate. The application is also informative, the user will be kept up to date about the status of the process. The application will stay alive, as it has to handle a request every 30 seconds.
A small drawback is debugging. Any exception, however futile (like an error message which is to long to fit in the status field) trashes the thread with a quite non-informative “system error” message. So before running the code in a new thread you’ll have to try it on the main thread. And now you have to set the page time-out to something large. Which does have it’s drawbacks.
But, as said in the beginning of this story, there is a larger drawback. The thread running the sql batch lives in the IIS application. As long as the application is alive the thread will keep running. But there are a lot of ways the application can be restarted. In .net terms the appdomain is recycled and your thread dies.
- IIS is restarted
- A content file of the application, like an aspx page or the web.config, is touched
- Spontaneously, IIS restarts the application to free up resources.
You can control the first two, but you cannot (afaik) control the latter one. Googling around on recycling an appdomain will give you a lot of information but no solution. The only one is to limit the amount of work you do in one go. Which does make a lot of sense but in our case that would be a major re-architecting of the application. All the BL is in the sprocs and their interaction. It works and it’s result are exactly what’s desired. So better stay away from changing that.
Any suggestions are welcome. For the moment we’re happy with this.
