If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Big and complexe solution

Hello, I am using SQL server 2008 and an ASP.Net interface (if that even matters).

I have written a procedure that is called by the ASP.net interface to either construct and populate a temporary table (if it doesn't already exist) or to just populate it using the parameters passed.

The SQL statement that I have used is nested at three levels and probably somewhat hard to make sense of in its raw form. In an attempt to make this question easier to follow I have made the diagram below that shows the queries and how they nest...

After the results of this query is inserted into a temporary table, a view of the temporary table is created by the ASP.NET interface which pivots the data such that MPTVIDs go accross and sample information goes down with a result count in the middle. An example of this pivot table can be seen below.

You might at this point be wondering why I create a temporary table rather than just have a single 4 level nested SQL statement to do everything at once.

The ultimate purpose of this pivoted view is to allow the user to switch planned testing on and off. The changes that the user makes during runtime will be applied to the temporary table and then once the user is happy with the planning they have selected, they can apply it. At that point another procedure will be executed to take the changes made to the temporary table and apply them to the actual tables.

What I have done so far works. It is not as fast as I would like but I have not actually set any indexes in the database yet and I believe that will significantly speed this process up when I finally bother to do so. That is not my biggest concern though.

I am guessing that this solution is not the most elegant possible. I would appreciate any critical feedback or suggested alternatives.

Oh also, please ignore any keywords that I have used as feild names. I'm aware that it is bad practice. At some point I will probably change them but for now I do not care about them.