Results 1 to 1 of 1
  1. #1
    Join Date
    Jun 2006

    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 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...

    The SQL for this monster is below.

    SELECT SampMeths.Short AS SeriesShort, SampMeths.Rego AS TrialRego, SampMeths.TreatmentID, 
    	SampMeths.Barcode AS SampleBarcode, SampMeths.TestName AS VarietyTestName, SampMeths.LabNumber AS SampleLabNumber, 
    	Results.Results AS Result, SampMeths.MPTVID, 
    	CASE WHEN (SELECT MAX(RepID) FROM TempPlanningTables) IS NULL THEN 1 ELSE (SELECT MAX(RepID) FROM TempPlanningTables)+1 END AS RepID
    	SELECT SampSide.Short, SampSide.Rego, SampSide.TestName, SampSide.Barcode, SampSide.TreatmentID, SampSide.LabNumber, 
    		MethSide.MPTVID, MethSide.ResultDetailID, SampSide.Barcode + CAST(MethSide.ResultDetailID AS varchar(50)) AS ResultID
    		SELECT dbo.Series.Short, dbo.Trials.Rego, dbo.Varieties.TestName, dbo.Treatments.Barcode, dbo.Treatments.TreatmentID, 
    		FROM dbo.Series INNER JOIN
    			dbo.Trials ON dbo.Series.SeriesID = dbo.Trials.SeriesID INNER JOIN
    			dbo.Varieties INNER JOIN
    			dbo.Samples AS Samples_1 RIGHT OUTER JOIN
    			dbo.Treatments ON Samples_1.TreatID = dbo.Treatments.TreatmentID ON dbo.Varieties.VarietyID = dbo.Treatments.VarietyID ON 
    			dbo.Trials.TrialID = dbo.Treatments.TrialID
    		WHERE (dbo.Trials.TrialID=@Trial) AND (Samples_1.[Level] = 4 OR
    			Samples_1.[Level] IS NULL) AND (Samples_1.SampleTypeID = 1 OR
    			Samples_1.SampleTypeID IS NULL) AND (dbo.Treatments.TreatmentID NOT LIKE '*B*')) AS SampSide CROSS JOIN
    			SELECT dbo.ResultDetails.MPTVID, dbo.ResultDetails.ResultDetailID
    			FROM dbo.MethProdTechVerLists INNER JOIN
    				dbo.ResultDetails ON dbo.MethProdTechVerLists.MPTVID = dbo.ResultDetails.MPTVID
    			WHERE (dbo.MethProdTechVerLists.ReportGroupID=@ReportGroup)) AS MethSide) AS SampMeths LEFT OUTER JOIN
    				dbo.Results ON SampMeths.ResultID = dbo.Results.ResultID
    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.

    SELECT RepID, SeriesShort, TrialRego, TreatmentID, SampleBarcode, VarietyTestName, SampleLabNumber, [285], [301], [292], [303], [273], [302], [289], [281], [258], [254], [308], [342], [264], [318], [288], [275], [270], [267], [332], [333] 
    SELECT * 
    FROM TempPlanningTables 
    WHERE RepID=2
    ) p PIVOT 
    (COUNT (Result) FOR MPTVID IN ([285], [301], [292], [303], [273], [302], [289], [281], [258], [254], [308], [342], [264], [318], [288], [275], [270], [267], [332], [333])) AS pvt 
    ORDER BY SampleLabNumber
    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.
    Last edited by Access Junkie; 10-27-09 at 01:07.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts