darren_ferris (3/20/2014)
Thanks for the replies guys.Kevin, you're right, customers do tend to spawn up new databases once they reach the size limit in SQL Express. I'll try the clustered index approach, the data we're inserting is already sorted in the order of the index I'll attempt to use, so hopefully that will help things.
Jeff, I've attached a file which contains one example of some of the dynamic SQL we have in place. Let me know what you think.
Thanks again,
Darren
I think you're in deep Kimchi. 🙂 Let's take a look at your first IF block of code...
IF @useAuditTrail = 1
BEGIN
Set @Sel =
'SELECT DISTINCT f.ComponentNo, f.SubFormAnalyID, f.FeatureAnalyID, f.RepeatNo, f.SimulationNo, f.IsVarParam, f.LibItemID, f.LibVerNo, f.IntValue, f.RealValue, f.TextValue, pc.CompAnalyID, Feature.EnumTypeID, ' + @ParentNo
+ ' From ' + @NW_Feature + ' as f ' +
'INNER JOIN ' + @NW_Component + ' as nc ON f.ComponentNo = nc.ComponentNo ' +
'INNER JOIN PCC_Component as pc ON nc.PhysID = pc.PhysID ' +
'INNER JOIN Feature ON f.FeatureAnalyID = Feature.AnalyID AND Feature.EnumTypeID <> 14 AND Feature.EnumTypeID <> 15 ' +
' INNER JOIN ' + @NW_Feature + ' ON f.SimulationNo <= (Select MAX(SimulationNo) FROM ' + @NW_Feature + ' As nwf1
Where f.ComponentNo = nwf1.ComponentNo
AND f.SubFormAnalyID = nwf1.SubFormAnalyID
AND f.FeatureAnalyID = nwf1.FeatureAnalyID
AND f.RepeatNo = nwf1.RepeatNo) '
END
ELSE
BEGIN
Set @Sel =
'SELECT DISTINCT f.ComponentNo, f.SubFormAnalyID, f.FeatureAnalyID, f.RepeatNo, f.SimulationNo, f.IsVarParam, f.LibItemID, f.LibVerNo, f.IntValue, f.RealValue, f.TextValue, pc.CompAnalyID, Feature.EnumTypeID, ' + @ParentNo
+ ' From ' + @NW_Feature + ' as f ' +
'INNER JOIN ' + @NW_Component + ' as nc ON f.ComponentNo = nc.ComponentNo ' +
'INNER JOIN PCC_Component as pc ON nc.PhysID = pc.PhysID ' +
'INNER JOIN Feature ON f.FeatureAnalyID = Feature.AnalyID AND Feature.EnumTypeID <> 14 AND Feature.EnumTypeID <> 15 ' +
' INNER JOIN ' + @NW_Feature + ' ON f.SimulationNo = (Select MAX(SimulationNo) FROM ' + @NW_Feature + ' As nwf1
Where f.ComponentNo = nwf1.ComponentNo
AND f.SubFormAnalyID = nwf1.SubFormAnalyID
AND f.FeatureAnalyID = nwf1.FeatureAnalyID
AND f.RepeatNo = nwf1.RepeatNo) '
END
My first step in trying to make something like this easier is to remove all the dynamic stuff from the first query and end up with some readable code with some "variable tokens" that I'd replace later on. The formatting becomes nice and vertically aligned making it easier to see everything. Like a good DBA, I added aliases to everything that was missing them and renumbered the rest to make sense of it all. Like this...
SELECT DISTINCT
f1.ComponentNo
,f1.SubFormAnalyID
,f1.FeatureAnalyID
,f1.RepeatNo
,f1.SimulationNo
,f1.IsVarParam
,f1.LibItemID
,f1.LibVerNo
,f1.IntValue
,f1.RealValue
,f1.TextValue
,pc.CompAnalyID
,ft.EnumTypeID
,<<@ParentNo>>
FROM dbo.<<@NW_Feature>> AS f1
JOIN dbo.<<@NW_Component>> AS nc ON f1.ComponentNo = nc.ComponentNo
JOIN dbo.PCC_Component AS pc ON nc.PhysID = pc.PhysID
JOIN dbo.Feature AS ft ON f1.FeatureAnalyID = ft.AnalyID
[font="Arial Black"] JOIN dbo.<<@NW_Feature>> AS f2[/font] ON f1.SimulationNo <= ( SELECT MAX(SimulationNo)
FROM <<@NW_Feature>> AS nwf1
WHERE f1.ComponentNo = nwf1.ComponentNo
AND f1.SubFormAnalyID = nwf1.SubFormAnalyID
AND f1.FeatureAnalyID = nwf1.FeatureAnalyID
AND f1.RepeatNo = nwf1.RepeatNo
)
WHERE f1.EnumTypeID NOT IN (14,15)
;
That's when I saw the reason for the DISTINCT... the bolded text in the code above forms a nasty ol' CROSS JOIN on whatever table @NW_Feature will describe. Notice that the "f2" alias isn't used anywhere else... not even in one of the ON clauses like it needs to be.
If you could fix that little problem in my "first step" code above, then I'd be happy to show you how to eliminate the massive amount of duplication of code that you have in the text file that you attached while making all of the code much easier to read and troubleshoot.
--Jeff Moden
Change is inevitable... Change for the better is not.