SQL Server Performance

SP Tuning

Discussion in 'T-SQL Performance Tuning for Developers' started by benwilson, May 23, 2005.

  1. benwilson New Member

    Hi All,<br />I havent really done too much performance tuning so far in my time as a DBA - i have really just been spending my time trying to get things that work regardless of performance...until now [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />I have written the below procedure, and was hoping to get some tips on general things that might improve it, and specific things i should look at in execution plans, etc.<br /><br />Anyway, here it is...Any tips would be hugely appreciated!<br /><br />Ben<br /><br /><pre id="code"><font face="courier" size="2" id="code">CREATE PROCEDURE Run_TrainingLoadTrainingTreeByTerminal @TerminalId_ NVARCHAR (<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />AS<br /><br />DECLARE @BookTreeTable TABLE<br />(BookName NVARCHAR(100), <br />BookDisplayName NVARCHAR(100), <br />BookVersion INT, <br />BookDescription NVARCHAR(500), <br />ChapterName NVARCHAR(100), <br />ChapterDisplayName NVARCHAR(100),<br />ChapterVersion INT, <br />ChapterOrder INT, <br />ChapterDescription NVARCHAR(500), <br />ScreenName NVARCHAR(100), <br />ScreenDisplayName NVARCHAR(100), <br />ScreenVersion INT, <br />ScreenOrder INT, <br />BackgroundImage NVARCHAR(50), <br />BackgroundImageX INT, <br />BackgroundImageY INT, <br />TopicName NVARCHAR(100), <br />TopicDisplayName NVARCHAR(100), <br />TopicVersion INT, <br />TopicOrder INT, <br />MinimumTime INT,<br />DocumentText NVARCHAR(3500), <br />AccumulatedTimeCurrentVersion INT, <br />AccumulatedTimeMajorVersion INT)<br /><br />INSERT INTO @BookTreeTable (BookName,BookDisplayName, BookVersion, BookDescription, ChapterName, ChapterDisplayName, ChapterVersion, ChapterOrder, <br />ChapterDescription, ScreenName, ScreenDisplayName, ScreenVersion, ScreenOrder, BackgroundImage, BackgroundImageX, BackgroundImageY, TopicName, <br />TopicDisplayName, TopicVersion, TopicOrder, MinimumTime, DocumentText, AccumulatedTimeCurrentVersion, AccumulatedTimeMajorVersion)<br /><br />SELECT dbo.tbl_TrainingBook.BookName, dbo.tbl_TrainingBookVersion.BookDisplayName, dbo.tbl_TrainingBookVersion.BookVersion, <br /> dbo.tbl_TrainingBookVersion.BookDescription, dbo.tbl_TrainingBookChapter.ChapterName, dbo.tbl_TrainingChapterVersion.ChapterDisplayName, <br /> dbo.tbl_TrainingBookChapter.ChapterVersion, dbo.tbl_TrainingBookChapter.ChapterOrder, dbo.tbl_TrainingChapterVersion.ChapterDescription, <br /> dbo.tbl_TrainingScreenTopic.ScreenName, dbo.tbl_TrainingScreenVersion.ScreenDisplayName, dbo.tbl_TrainingChapterScreen.ScreenVersion, <br /> dbo.tbl_TrainingChapterScreen.ScreenOrder, dbo.tbl_TrainingScreenVersion.BackgroundImage, dbo.tbl_TrainingScreenVersion.BackgroundImageX, <br /> dbo.tbl_TrainingScreenVersion.BackgroundImageY, dbo.tbl_TrainingTopicVersion.TopicName, dbo.tbl_TrainingTopicVersion.TopicDisplayName, <br /> dbo.tbl_TrainingScreenTopic.TopicVersion, dbo.tbl_TrainingScreenTopic.TopicOrder, dbo.tbl_TrainingTopicVersion.MinimumTime, tbl_TrainingTopicVersion.DocumentText,<br /> AccumulatedTimeCurrentVersion.AccumulatedTimeCurrentVersion, TerminalAccumulatedTimeMajorVersion.AccumulatedTimeMajorVersion<br />FROM dbo.tbl_TrainingBook INNER JOIN<br /> dbo.tbl_TrainingBookVersion ON dbo.tbl_TrainingBook.BookName = dbo.tbl_TrainingBookVersion.BookName AND <br /> dbo.tbl_TrainingBook.CurrentVersionNo = dbo.tbl_TrainingBookVersion.BookVersion INNER JOIN<br /> dbo.tbl_TrainingBookChapter ON dbo.tbl_TrainingBookVersion.BookName = dbo.tbl_TrainingBookChapter.BookName AND <br /> dbo.tbl_TrainingBookVersion.BookVersion = dbo.tbl_TrainingBookChapter.BookVersion INNER JOIN<br /> dbo.tbl_TrainingChapterVersion ON dbo.tbl_TrainingBookChapter.ChapterName = dbo.tbl_TrainingChapterVersion.ChapterName AND <br /> dbo.tbl_TrainingBookChapter.ChapterVersion = dbo.tbl_TrainingChapterVersion.ChapterVersion INNER JOIN<br /> dbo.tbl_TrainingChapterScreen ON dbo.tbl_TrainingChapterVersion.ChapterName = dbo.tbl_TrainingChapterScreen.ChapterName AND <br /> dbo.tbl_TrainingChapterVersion.ChapterVersion = dbo.tbl_TrainingChapterScreen.ChapterVersion INNER JOIN<br /> dbo.tbl_TrainingChapter ON dbo.tbl_TrainingChapterVersion.ChapterName = dbo.tbl_TrainingChapter.ChapterName INNER JOIN<br /> dbo.tbl_TrainingScreenVersion ON dbo.tbl_TrainingChapterScreen.ScreenName = dbo.tbl_TrainingScreenVersion.ScreenName AND <br /> dbo.tbl_TrainingChapterScreen.ScreenVersion = dbo.tbl_TrainingScreenVersion.ScreenVersion INNER JOIN<br /> dbo.tbl_TrainingScreenTopic ON dbo.tbl_TrainingScreenVersion.ScreenName = dbo.tbl_TrainingScreenTopic.ScreenName AND <br /> dbo.tbl_TrainingScreenVersion.ScreenVersion = dbo.tbl_TrainingScreenTopic.ScreenVersion INNER JOIN<br /> dbo.tbl_TrainingScreen ON dbo.tbl_TrainingScreenVersion.ScreenName = dbo.tbl_TrainingScreen.ScreenName INNER JOIN<br /> dbo.tbl_TrainingTopicVersion ON dbo.tbl_TrainingScreenTopic.TopicName = dbo.tbl_TrainingTopicVersion.TopicName AND <br /> dbo.tbl_TrainingScreenTopic.TopicVersion = dbo.tbl_TrainingTopicVersion.TopicVersion INNER JOIN<br /> dbo.tbl_TrainingTopic ON dbo.tbl_TrainingTopicVersion.TopicName = dbo.tbl_TrainingTopic.TopicName LEFT OUTER JOIN<br /> (SELECT TopicName, TopicVersion, SUM(AccumulatedTime) AS AccumulatedTimeCurrentVersion<br /> FROM dbo.tbl_TrainingTerminalSessionTopicProgress<br /> WHERE (TerminalId = @TerminalId_)<br /> GROUP BY TopicName, TopicVersion) AccumulatedTimeCurrentVersion ON <br /> dbo.tbl_TrainingScreenTopic.TopicName = AccumulatedTimeCurrentVersion.TopicName COLLATE Latin1_General_CI_AS AND <br /> dbo.tbl_TrainingScreenTopic.TopicVersion = AccumulatedTimeCurrentVersion.TopicVersion LEFT OUTER JOIN<br /> (SELECT TopicName, TopicVersion / 1000000 AS MajorVersion, SUM(AccumulatedTime) AS AccumulatedTimeMajorVersion<br /> FROM dbo.tbl_TrainingTerminalSessionTopicProgress<br /> WHERE (TerminalId = @TerminalId_)<br /> GROUP BY TopicName, TopicVersion / 1000000) TerminalAccumulatedTimeMajorVersion ON <br /> dbo.tbl_TrainingScreenTopic.TopicName = TerminalAccumulatedTimeMajorVersion.TopicName COLLATE Latin1_General_CI_AS AND <br /> dbo.tbl_TrainingScreenTopic.TopicVersion / 1000000 = TerminalAccumulatedTimeMajorVersion.MajorVersion<br />WHERE (dbo.tbl_TrainingBook.Disable = 0) AND (dbo.tbl_TrainingChapter.Disable = 0) AND (dbo.tbl_TrainingScreen.Disable = 0) AND <br /> (dbo.tbl_TrainingTopic.Disable = 0) AND (dbo.tbl_TrainingBookVersion.Disable = 0) AND (dbo.tbl_TrainingChapterVersion.Disable = 0) AND <br /> (dbo.tbl_TrainingScreenVersion.Disable = 0) AND (dbo.tbl_TrainingTopicVersion.Disable = 0) AND (dbo.tbl_TrainingBookChapter.CurrentVersion = 1) AND<br /> (dbo.tbl_TrainingChapterScreen.CurrentVersion = 1) AND (dbo.tbl_TrainingScreenTopic.CurrentVersion = 1)<br /><br />--Current Books<br />SELECT DISTINCT BookName, BookDisplayName, BookVersion, BookDescription <br />FROM @BookTreeTable<br /><br />--Current Chapters<br />SELECT DISTINCT BookName, BookVersion, ChapterName, ChapterDisplayName, ChapterVersion, ChapterOrder, ChapterDescription <br />FROM @BookTreeTable<br /><br />--Current Screens<br />SELECT DISTINCT BookName, BookVersion, ChapterName, ChapterVersion, ScreenName, ScreenDisplayName, ScreenVersion, ScreenOrder, BackgroundImage, BackgroundImageX, BackgroundImageY<br />FROM @BookTreeTable<br /><br />--Current Topics<br />SELECT DISTINCT BookName, BookVersion, ChapterName, ChapterVersion, ScreenName, ScreenVersion, TopicName, TopicDisplayName, TopicVersion, TopicOrder, MinimumTime, ISNULL(AccumulatedTimeCurrentVersion, 0) AS AccumulatedTimeCurrentVersion, ISNULL(AccumulatedTimeMajorVersion, 0) AS AccumulatedTimeMajorVersion<br />FROM @BookTreeTable<br /><br />--Current Events<br />SELECT DISTINCT BTT.BookName, BTT.BookVersion, BTT.ChapterName, BTT.ChapterVersion, BTT.ScreenName, BTT.ScreenVersion, BTT.TopicName, BTT.TopicVersion, EventName, EventOrder<br />FROM tbl_TrainingTopicEvent TTE INNER JOIN @BookTreeTable BTT ON TTE.TopicName = BTT.TopicName AND TTE.TopicVersion = BTT.TopicVersion<br />WHERE TTE.Disable = 0<br /><br />--Current Highlights<br />SELECT DISTINCT BTT.BookName, BTT.BookVersion, BTT.ChapterName, BTT.ChapterVersion, BTT.ScreenName, BTT.ScreenVersion, TTEH.TopicName, TTEH.TopicVersion, TTEH.EventName, TTEH.EventOrder, TTEH.HighlightWidth, TTEH.HighlightHeight, TTEH.HighlightX, TTEH.HighlightY<br />FROM tbl_TrainingTopicEvent TTE INNER JOIN @BookTreeTable BTT ON TTE.TopicName = BTT.TopicName AND TTE.TopicVersion = BTT.TopicVersion <br />INNER JOIN tbl_TrainingTopicEventHighlight TTEH ON TTE.TopicName = TTEH.TopicName AND TTE.TopicVersion = TTEH.TopicVersion<br />AND TTE.EventName = TTEH.EventName AND TTE.EventOrder = TTEH.EventOrder<br />WHERE TTE.Disable = 0 AND TTEH.Disable = 0<br /><br />--Current Callouts<br />SELECT DISTINCT BTT.BookName, BTT.BookVersion, BTT.ChapterName, BTT.ChapterVersion, BTT.ScreenName, BTT.ScreenVersion, TTEC.TopicName, TTEC.TopicVersion, TTEC.EventName, TTEC.EventOrder, TTEC.CalloutText, TTEC.CalloutWidth, TTEC.CalloutHeight, TTEC.CalloutX, TTEC.CalloutY, TTEC.InstructionType, TTEC.DonglePosition<br />FROM tbl_TrainingTopicEvent TTE INNER JOIN @BookTreeTable BTT ON TTE.TopicName = BTT.TopicName AND TTE.TopicVersion = BTT.TopicVersion <br />INNER JOIN tbl_TrainingTopicEventCallout TTEC ON TTE.TopicName = TTEC.TopicName AND TTE.TopicVersion = TTEC.TopicVersion<br />AND TTE.EventName = TTEC.EventName AND TTE.EventOrder = TTEC.EventOrder<br />WHERE TTE.Disable = 0 AND TTEC.Disable = 0<br /><br />--Current Images<br />SELECT DISTINCT BTT.BookName, BTT.BookVersion, BTT.ChapterName, BTT.ChapterVersion, BTT.ScreenName, BTT.ScreenVersion, TTEDI.TopicName, TTEDI.TopicVersion, TTEDI.EventName, TTEDI.EventOrder, TTEDI.ImagePath, TTEDI.ImageX, TTEDI.ImageY<br />FROM tbl_TrainingTopicEvent TTE INNER JOIN @BookTreeTable BTT ON TTE.TopicName = BTT.TopicName AND TTE.TopicVersion = BTT.TopicVersion <br />INNER JOIN tbl_TrainingTopicEventDemonstrationImage TTEDI ON TTE.TopicName = TTEDI.TopicName AND TTE.TopicVersion = TTEDI.TopicVersion<br />AND TTE.EventName = TTEDI.EventName AND TTE.EventOrder = TTEDI.EventOrder<br />WHERE TTE.Disable = 0 AND TTEDI.Disable = 0<br />GO<br /></font id="code"></pre id="code">
  2. dineshasanka Moderator

    Do you really need nvarchar can't go with varchat


    quote:Originally posted by benwilson

    Hi All,

    (BookName NVARCHAR(100),
    BookDisplayName NVARCHAR(100),
    BookVersion INT,
    BookDescription NVARCHAR(500),
    ChapterName NVARCHAR(100),
    ChapterDisplayName NVARCHAR(100),
    ChapterVersion INT,
    ChapterOrder INT,
    ChapterDescription NVARCHAR(500),
    ScreenName NVARCHAR(100),
    ScreenDisplayName NVARCHAR(100),
    ScreenVersion INT,
    ScreenOrder INT,
    BackgroundImage NVARCHAR(50),
    TopicName NVARCHAR(100),
    TopicDisplayName NVARCHAR(100),
    TopicVersion INT,
    TopicOrder INT,
    MinimumTime INT,
    DocumentText NVARCHAR(3500),
    AccumulatedTimeCurrentVersion INT,
    AccumulatedTimeMajorVersion INT)

    [/code]
  3. benwilson New Member

    we use nvarchars as standard throught our system, so yes, i think i need to stick with them here...would this really improve performance? if so, is this because of the size of the variables that would be created are smaller with varchars?
  4. dineshasanka Moderator

    Use char/varchar columns instead of nchar/nvarchar if you do not need to store unicode data.
    So, you can reduce the table's size, this can improve performance of your queries and some maintenance tasks (such as backup, restore and so on).


    quote:Originally posted by benwilson

    we use nvarchars as standard throught our system, so yes, i think i need to stick with them here...would this really improve performance? if so, is this because of the size of the variables that would be created are smaller with varchars?
  5. mmarovic Active Member

    I don't like the concept you applied. Better build selects from source tables directly instead of making one big multi-join select and then filter results from there. Also separate selects in their own stored procedures.
  6. benwilson New Member

    ok...if i was to do it as a number of separate selects rather than the big one, and also split it up into a number of stored procs, i would then have to pass around a whole lot of table variables- would this be more efficient?
  7. benwilson New Member

    Here is version 2 of the procedure using smaller selects...if anything, i would say it is slightly slower than the first code i posted (the last 3 selects for highlights, callouts and images seems to be the main bottleneck). Once again, any tips would be greatly appreciated!:<br /><pre id="code"><font face="courier" size="2" id="code">CREATE PROCEDURE Run_TrainingLoadTrainingTreeByTerminal @TerminalId_ NVARCHAR (<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />AS<br /><br />DECLARE @BookTable TABLE<br />(BookName NVARCHAR(100), <br />BookDisplayName NVARCHAR(100), <br />BookVersion INT, <br />BookDescription NVARCHAR(500), <br />BookOrder INT)<br /><br />DECLARE @ChapterTable TABLE<br />(BookName NVARCHAR(100),<br />BookVersion INT, <br />ChapterName NVARCHAR(100), <br />ChapterDisplayName NVARCHAR(100),<br />ChapterVersion INT, <br />ChapterOrder INT, <br />ChapterDescription NVARCHAR(500))<br /><br />DECLARE @ScreenTable TABLE<br />(ChapterName NVARCHAR(100),<br />ChapterVersion INT, <br />ScreenName NVARCHAR(100), <br />ScreenDisplayName NVARCHAR(100), <br />ScreenVersion INT, <br />ScreenOrder INT, <br />BackgroundImage NVARCHAR(50), <br />BackgroundImageX INT, <br />BackgroundImageY INT)<br /><br />DECLARE @TopicTable TABLE<br />(ScreenName NVARCHAR(100),<br />ScreenVersion INT,<br />TopicName NVARCHAR(100), <br />TopicDisplayName NVARCHAR(100), <br />TopicVersion INT, <br />TopicOrder INT, <br />MinimumTime INT,<br />DocumentText NVARCHAR(3500), <br />AccumulatedTimeCurrentVersion INT, <br />AccumulatedTimeMajorVersion INT)<br /><br />INSERT INTO @BookTable (BookName, BookDisplayName, BookVersion, BookDescription, BookOrder)<br />SELECT dbo.tbl_TrainingBook.BookName, dbo.tbl_TrainingBookVersion.BookDisplayName, dbo.tbl_TrainingBookVersion.BookVersion, <br /> dbo.tbl_TrainingBookVersion.BookDescription, dbo.tbl_TrainingBookVersion.BookOrder<br />FROM dbo.tbl_TrainingBook INNER JOIN<br /> dbo.tbl_TrainingBookVersion ON dbo.tbl_TrainingBook.BookName = dbo.tbl_TrainingBookVersion.BookName AND <br /> dbo.tbl_TrainingBook.CurrentVersionNo = dbo.tbl_TrainingBookVersion.BookVersion<br />WHERE (dbo.tbl_TrainingBook.Disable = 0) AND (dbo.tbl_TrainingBookVersion.Disable = 0)<br /><br />INSERT INTO @ChapterTable (BookName, BookVersion, ChapterName, ChapterDisplayName, ChapterVersion, ChapterOrder, ChapterDescription)<br />SELECT BT.BookName, BT.BookVersion, dbo.tbl_TrainingChapterVersion.ChapterName, dbo.tbl_TrainingChapterVersion.ChapterDisplayName, dbo.tbl_TrainingChapterVersion.ChapterVersion, <br /> dbo.tbl_TrainingBookChapter.ChapterOrder, dbo.tbl_TrainingChapterVersion.ChapterDescription<br />FROM dbo.tbl_TrainingBookChapter INNER JOIN<br /> dbo.tbl_TrainingChapterVersion ON dbo.tbl_TrainingBookChapter.ChapterName = dbo.tbl_TrainingChapterVersion.ChapterName AND <br /> dbo.tbl_TrainingBookChapter.ChapterVersion = dbo.tbl_TrainingChapterVersion.ChapterVersion INNER JOIN<br /> dbo.tbl_TrainingChapter ON dbo.tbl_TrainingChapterVersion.ChapterName = dbo.tbl_TrainingChapter.ChapterName INNER JOIN @BookTable BT<br />ON BT.BookName = tbl_TrainingBookChapter.BookName AND BT.BookVersion = tbl_TrainingBookChapter.BookVersion<br />WHERE (dbo.tbl_TrainingChapter.Disable = 0) AND (dbo.tbl_TrainingChapterVersion.Disable = 0) AND (dbo.tbl_TrainingBookChapter.CurrentVersion = 1)<br /><br />INSERT INTO @ScreenTable (ChapterName, ChapterVersion, ScreenName, ScreenDisplayName, ScreenVersion, ScreenOrder, BackgroundImage, BackgroundImageX, BackgroundImageY)<br />SELECT CT.ChapterName, CT.ChapterVersion, dbo.tbl_TrainingScreenVersion.ScreenName, dbo.tbl_TrainingScreenVersion.ScreenDisplayName, dbo.tbl_TrainingScreenVersion.ScreenVersion, <br /> dbo.tbl_TrainingChapterScreen.ScreenOrder, dbo.tbl_TrainingScreenVersion.BackgroundImage, dbo.tbl_TrainingScreenVersion.BackgroundImageX, <br /> dbo.tbl_TrainingScreenVersion.BackgroundImageY<br />FROM dbo.tbl_TrainingChapterScreen INNER JOIN<br /> dbo.tbl_TrainingScreenVersion ON dbo.tbl_TrainingChapterScreen.ScreenName = dbo.tbl_TrainingScreenVersion.ScreenName AND <br /> dbo.tbl_TrainingChapterScreen.ScreenVersion = dbo.tbl_TrainingScreenVersion.ScreenVersion INNER JOIN<br /> dbo.tbl_TrainingScreen ON dbo.tbl_TrainingScreenVersion.ScreenName = dbo.tbl_TrainingScreen.ScreenName INNER JOIN @ChapterTable CT ON<br />CT.ChapterName = tbl_TrainingChapterScreen.ChapterName AND CT.ChapterVersion = tbl_TrainingChapterScreen.ChapterVersion<br />WHERE (dbo.tbl_TrainingChapterScreen.CurrentVersion = 1) AND (dbo.tbl_TrainingScreenVersion.Disable = 0) AND (dbo.tbl_TrainingScreen.Disable = 0)<br /><br />INSERT INTO @TopicTable (ScreenName, ScreenVersion, TopicName, TopicDisplayName, TopicVersion, TopicOrder, MinimumTime, DocumentText, AccumulatedTimeCurrentVersion, AccumulatedTimeMajorVersion)<br />SELECT ST.ScreenName, ST.ScreenVersion, dbo.tbl_TrainingTopicVersion.TopicName, dbo.tbl_TrainingTopicVersion.TopicDisplayName, dbo.tbl_TrainingTopicVersion.TopicVersion, <br /> dbo.tbl_TrainingScreenTopic.TopicOrder, dbo.tbl_TrainingTopicVersion.MinimumTime, dbo.tbl_TrainingTopicVersion.DocumentText, <br /> AccumulatedTimeCurrentVersion.AccumulatedTimeCurrentVersion, TerminalAccumulatedTimeMajorVersion.AccumulatedTimeMajorVersion<br />FROM dbo.tbl_TrainingScreenTopic INNER JOIN<br /> dbo.tbl_TrainingTopicVersion ON dbo.tbl_TrainingScreenTopic.TopicName = dbo.tbl_TrainingTopicVersion.TopicName AND <br /> dbo.tbl_TrainingScreenTopic.TopicVersion = dbo.tbl_TrainingTopicVersion.TopicVersion INNER JOIN<br /> dbo.tbl_TrainingTopic ON dbo.tbl_TrainingTopicVersion.TopicName = dbo.tbl_TrainingTopic.TopicName LEFT OUTER JOIN<br /> (SELECT TopicName, TopicVersion / 1000000 AS MajorVersion, SUM(AccumulatedTime) AS AccumulatedTimeMajorVersion<br /> FROM dbo.tbl_TrainingTerminalSessionTopicProgress<br /> WHERE (TerminalId = @TerminalId_)<br /> GROUP BY TopicName, TopicVersion / 1000000) TerminalAccumulatedTimeMajorVersion ON <br /> dbo.tbl_TrainingTopicVersion.TopicName = TerminalAccumulatedTimeMajorVersion.TopicName COLLATE Latin1_General_CI_AS AND <br /> dbo.tbl_TrainingTopicVersion.TopicVersion / 1000000 = TerminalAccumulatedTimeMajorVersion.MajorVersion LEFT OUTER JOIN<br /> (SELECT TopicName, TopicVersion, SUM(AccumulatedTime) AS AccumulatedTimeCurrentVersion<br /> FROM dbo.tbl_TrainingTerminalSessionTopicProgress<br /> WHERE (TerminalId = @TerminalId_)<br /> GROUP BY TopicName, TopicVersion) AccumulatedTimeCurrentVersion ON <br /> dbo.tbl_TrainingTopicVersion.TopicName = AccumulatedTimeCurrentVersion.TopicName COLLATE Latin1_General_CI_AS AND <br /> dbo.tbl_TrainingTopicVersion.TopicVersion = AccumulatedTimeCurrentVersion.TopicVersion INNER JOIN @ScreenTable ST<br />ON ST.ScreenName = tbl_TrainingScreenTopic.ScreenName AND ST.ScreenVersion = tbl_TrainingScreenTopic.ScreenVersion<br />WHERE (dbo.tbl_TrainingScreenTopic.CurrentVersion = 1) AND (dbo.tbl_TrainingTopicVersion.Disable = 0) AND (dbo.tbl_TrainingTopic.Disable = 0)<br /><br />--Current Books<br />SELECT DISTINCT BookName, BookDisplayName, BookVersion, BookDescription, BookOrder<br />FROM @BookTable<br />ORDER BY BookOrder<br /><br />--Current Chapters<br />SELECT DISTINCT BookName, BookVersion, ChapterName, ChapterDisplayName, ChapterVersion, ChapterOrder, ChapterDescription <br />FROM @ChapterTable<br />ORDER BY ChapterOrder<br /><br />--Current Screens<br />SELECT DISTINCT CT.BookName, CT.BookVersion, ST.ChapterName, ST.ChapterVersion, ST.ScreenName, ST.ScreenDisplayName, ST.ScreenVersion, ST.ScreenOrder, ST.BackgroundImage, ST.BackgroundImageX, ST.BackgroundImageY<br />FROM @ScreenTable ST INNER JOIN @ChapterTable CT ON ST.ChapterName = CT.ChapterName AND ST.ChapterVersion = CT.ChapterVersion<br />ORDER BY ScreenOrder<br /><br />--Current Topics<br />SELECT DISTINCT CT.BookName, CT.BookVersion, CT.ChapterName, CT.ChapterVersion, ST.ScreenName, ST.ScreenVersion, TT.TopicName, TT.TopicDisplayName, TT.TopicVersion, TT.TopicOrder, TT.MinimumTime, ISNULL(AccumulatedTimeCurrentVersion, 0) AS AccumulatedTimeCurrentVersion, ISNULL(AccumulatedTimeMajorVersion, 0) AS AccumulatedTimeMajorVersion<br />FROM @TopicTable TT INNER JOIN @ScreenTable ST ON TT.ScreenName = ST.ScreenName AND TT.ScreenVersion = ST.ScreenVersion INNER JOIN @ChapterTable CT ON<br />CT.ChapterName = ST.ChapterName AND CT.ChapterVersion = ST.ChapterVersion<br />ORDER BY TopicOrder<br /><br />--Current Events<br />SELECT DISTINCT CT.BookName, CT.BookVersion, CT.ChapterName, CT.ChapterVersion, ST.ScreenName, ST.ScreenVersion, TT.TopicName, TT.TopicVersion, TTE.EventName, TTE.EventOrder<br />FROM @TopicTable TT INNER JOIN @ScreenTable ST ON TT.ScreenName = ST.ScreenName AND TT.ScreenVersion = ST.ScreenVersion INNER JOIN @ChapterTable CT ON<br />CT.ChapterName = ST.ChapterName AND CT.ChapterVersion = ST.ChapterVersion INNER JOIN tbl_TrainingTopicEvent TTE ON TTE.TopicName = TT.TopicName <br />AND TTE.TopicVersion = TT.TopicVersion<br />WHERE TTE.Disable = 0<br />ORDER BY EventOrder<br /><br />--Current Highlights<br />SELECT DISTINCT CT.BookName, CT.BookVersion, CT.ChapterName, CT.ChapterVersion, ST.ScreenName, ST.ScreenVersion, TT.TopicName, TT.TopicVersion, TTE.EventName, TTE.EventOrder, TTEH.HighlightWidth, TTEH.HighlightHeight, TTEH.HighlightX, TTEH.HighlightY<br />FROM @TopicTable TT INNER JOIN @ScreenTable ST ON TT.ScreenName = ST.ScreenName AND TT.ScreenVersion = ST.ScreenVersion INNER JOIN @ChapterTable CT ON<br />CT.ChapterName = ST.ChapterName AND CT.ChapterVersion = ST.ChapterVersion INNER JOIN tbl_TrainingTopicEvent TTE ON TTE.TopicName = TT.TopicName <br />AND TTE.TopicVersion = TT.TopicVersion INNER JOIN tbl_TrainingTopicEventHighlight TTEH ON TTE.TopicName = TTEH.TopicName AND TTE.TopicVersion = TTEH.TopicVersion AND<br />TTE.EventName = TTEH.EventName AND TTE.EventOrder = TTEH.EventOrder<br />WHERE TTE.Disable = 0 AND TTEH.Disable = 0<br />ORDER BY TTE.EventOrder<br /><br />--Current Callouts<br />SELECT DISTINCT CT.BookName, CT.BookVersion, CT.ChapterName, CT.ChapterVersion, ST.ScreenName, ST.ScreenVersion, TT.TopicName, TT.TopicVersion, TTE.EventName, TTE.EventOrder, TTEC.CalloutText, TTEC.CalloutWidth, TTEC.CalloutHeight, TTEC.CalloutX, TTEC.CalloutY, TTEC.InstructionType, TTEC.DonglePosition<br />FROM @TopicTable TT INNER JOIN @ScreenTable ST ON TT.ScreenName = ST.ScreenName AND TT.ScreenVersion = ST.ScreenVersion INNER JOIN @ChapterTable CT ON<br />CT.ChapterName = ST.ChapterName AND CT.ChapterVersion = ST.ChapterVersion INNER JOIN tbl_TrainingTopicEvent TTE ON TTE.TopicName = TT.TopicName <br />AND TTE.TopicVersion = TT.TopicVersion INNER JOIN tbl_TrainingTopicEventCallout TTEC ON TTE.TopicName = TTEC.TopicName AND TTE.TopicVersion = TTEC.TopicVersion AND<br />TTE.EventName = TTEC.EventName AND TTE.EventOrder = TTEC.EventOrder<br />WHERE TTE.Disable = 0 AND TTEC.Disable = 0<br />ORDER BY TTE.EventOrder<br /><br />--Current Images<br />SELECT DISTINCT CT.BookName, CT.BookVersion, CT.ChapterName, CT.ChapterVersion, ST.ScreenName, ST.ScreenVersion, TT.TopicName, TT.TopicVersion, TTE.EventName, TTE.EventOrder, TTEDI.ImagePath, TTEDI.ImageX, TTEDI.ImageY<br />FROM @TopicTable TT INNER JOIN @ScreenTable ST ON TT.ScreenName = ST.ScreenName AND TT.ScreenVersion = ST.ScreenVersion INNER JOIN @ChapterTable CT ON<br />CT.ChapterName = ST.ChapterName AND CT.ChapterVersion = ST.ChapterVersion INNER JOIN tbl_TrainingTopicEvent TTE ON TTE.TopicName = TT.TopicName <br />AND TTE.TopicVersion = TT.TopicVersion INNER JOIN tbl_TrainingTopicEventDemonstrationImage TTEDI ON TTE.TopicName = TTEDI.TopicName AND TTE.TopicVersion = TTEDI.TopicVersion AND<br />TTE.EventName = TTEDI.EventName AND TTE.EventOrder = TTEDI.EventOrder<br />WHERE TTE.Disable = 0 AND TTEDI.Disable = 0<br />ORDER BY TTE.EventOrder<br />GO<br /></font id="code"></pre id="code">
  8. mmarovic Active Member

    What I mean is to split this proc into separate stored procedures that will return selects directly from source tables instead of using table variables and filter later from them or join them. It is not absolutely necessary to remove all of table variables... More when i analyze your code.
  9. mmarovic Active Member

    Please try to define clustered primary keys on your table variables and let us know if performance improved.
  10. mmarovic Active Member

    Here is an example of approach I had in mind:<pre id="code"><font face="courier" size="2" id="code">CREATE PROCEDURE Run_TrainingLoadTrainingTreeByTerminalCurrentImages <br />@TerminalId_ NVARCHAR (<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />AS<br /><br />--Current Images<br />SELECT DISTINCT <br />tb.BookName, <br />tbCh.BookVersion, <br />tbCh.ChapterName, <br />tbCh.ChapterVersion, <br />tcScr.ScreenName, <br />tcScr.ScreenVersion, <br />tsTopic.TopicName, <br />tsTopic.TopicVersion, <br />TTE.EventName, <br />TTE.EventOrder, <br />TTEDI.ImagePath, <br />TTEDI.ImageX, <br />TTEDI.ImageY<br />FROM<br />dbo.tbl_TrainingScreenTopicas tsTopic<br />JOIN dbo.tbl_TrainingTopicVersion as ttVerON tsTopic.TopicName = ttVer.TopicName AND tsTopic.TopicVersion = ttVer.TopicVersion <br />JOIN dbo.tbl_TrainingTopic as tTopicON ttVer.TopicName = tTopic.TopicName <br />LEFT OUTER JOIN<br />(SELECT <br />TopicName, <br />TopicVersion / 1000000AS MajorVersion, <br />SUM(AccumulatedTime) AS AccumulatedTimeMajorVersion,<br /> FROM <br />dbo.tbl_TrainingTerminalSessionTopicProgress<br /> WHERE <br />TerminalId = @TerminalId_<br /> GROUP BY <br />TopicName, <br />TopicVersion / 1000000<br />) <br />as tatmv ON ttVer.TopicName = tatmv.TopicName COLLATE Latin1_General_CI_AS AND ttVer.TopicVersion / 1000000 = tatmv.MajorVersion <br />LEFT OUTER JOIN <br />(SELECT <br />TopicName,<br />TopicVersion, <br />SUM(AccumulatedTime) AS AccumulatedTimeCurrentVersion<br /> FROM <br />dbo.tbl_TrainingTerminalSessionTopicProgress <br /> WHERE <br />TerminalId = @TerminalId_<br /> GROUP BY <br />TopicName, <br />TopicVersion<br />) as atcv ON ttVer.TopicName = atcv.TopicName COLLATE Latin1_General_CI_AS AND ttVer.TopicVersion = atcv.TopicVersion<br />join dbo.tbl_TrainingChapterScreenas tcScron tcScr.ScreenName = tsTopic.ScreenName and tcScr.ScreenVersion = tsTopic.ScreenVersion<br />JOIN dbo.tbl_TrainingScreenVersionas tsVerON tcScr.ScreenName = tsVer.ScreenName AND tcScr.ScreenVersion = tsVer.ScreenVersion <br />JOIN dbo.tbl_TrainingScreen as tScrON tsVer.ScreenName = tScr.ScreenName<br />joindbo.tbl_TrainingBookChapteras tbChon tbCh.ChapterName = tcScr.ChapterName AND tbCh.ChapterVersion = tcScr.ChapterVersion<br />JOIN dbo.tbl_TrainingChapterVersionas tcVerON tbCh.ChapterName = tcVer.ChapterName AND tbCh.ChapterVersion = tcVer.ChapterVersion <br />JOIN dbo.tbl_TrainingChapter as tChON tcVer.ChapterName = tCh.ChapterName <br />JOIN dbo.tbl_TrainingBookas tbon tb.BookName = tbCh.BookName<br />joindbo.tbl_TrainingBookVersion as tbVerON tbCh.BookVersion = tbVer.BookVersion and tb.BookName = tbVer.BookName and tb.CurrentVersionNo = tbVer.BookVersion<br />JOIN dbo.tbl_TrainingTopicEvent as TTE ON TTE.TopicName = ttVer.TopicName AND TTE.TopicVersion = ttVer.TopicVersion <br />JOIN dbo.tbl_TrainingTopicEventDemonstrationImage <br />as TTEDI ON TTE.TopicName = TTEDI.TopicName AND TTE.TopicVersion = TTEDI.TopicVersion AND<br />TTE.EventName = TTEDI.EventName AND TTE.EventOrder = TTEDI.EventOrder<br />WHERE <br />TTE.Disable = 0 AND <br />TTEDI.Disable = 0 and<br />(tCh.Disable = 0) AND <br />(tcVer.Disable = 0) AND <br />(tbCh.CurrentVersion = 1) and<br />(tb.Disable = 0) and <br />(tbVer.Disable = 0) and<br />(tcScr.CurrentVersion = 1) AND <br />(tScr.Disable = 0) and<br />(tsVer.Disable = 0) AND <br />(tsTopic.CurrentVersion = 1) AND <br />(ttVer.Disable = 0) AND <br />(tTopic.Disable = 0)<br />ORDER BY TTE.EventOrder<br />GO</font id="code"></pre id="code">After re-writting the code like that you can try to optimize code, like to see what disable = 0 conditions can be ommited and joins removed.

Share This Page