Function value lookup

Tror du lookups er hurtigere end joins?

nix... men du er velkommen til at gøre denne SQL mere overskuelig:
Planen er at gøre det nemmere at læse...

[SQL]

declare @lang as int, @surveyid as uniqueidentifier

SELECT
    ISNULL(Groups.OrderIndex,0) AS tGroupOrder,
    ISNULL(QuestionsSelector.OrderIndex,0) AS tQuestionOrder,

    Survey.ROW_ID AS SurveyID,
    Survey.Folder_FK AS SurveyFolderID,
    SurveyText.Text AS SurveyTitle,
    SurveyDescription.Text AS SurveyDescription,
    Survey.StartDate,
    Survey.EndDate,
    Survey.Anonymous,
    Survey.Layout_FK,
    ISNULL(Survey.CustomExitHTML,' ') AS CustomExitHTML,
    Survey.AfterSubmit,
    ISNULL(SurveyHeaderText.Text,'') AS SurveyHeader,
    ISNULL(SurveyFooterText.Text,'') AS SurveyFooter,
    ISNULL(SurveyProperties.XMLSettings,'') AS SurveyProperties ,

    Groups.ROW_ID AS GroupID,
    Groups.Quiz_FK,
    GroupText.Text AS GroupText,
    Groups.Text_FK AS Group_Label_ID,
    Groups.Type AS GroupType,
    Groups.AnswerGroup1_FK AS MatrixAnswerGroup1ID,
    Groups.AnswerGroup2_FK AS MatrixAnswerGroup2ID,
    ISNULL(AnswerGroup2Title.Text,'') AS AnswerGroup2TitleText,
    ISNULL(AnswerGroup1Title.Text,'') AS AnswerGroup1TitleText,
    Groups.CondShowType AS GroupCondShowType,
    Groups.AnswerGroup1Place,
    Groups.AnswerGroup2Place,
    Groups.AnswerGroup1Layout,
    Groups.AnswerGroup2Layout,
    Groups.PageBreakAfter,
    GroupProperties.XMLSettings AS GroupProperties,
    ISNULL(GroupHeaderText.Text,'') AS GroupHeader,
    ISNULL(GroupFooterText.Text,'') AS GroupFooter,
--Remove! /
    --QuestionsSelector.ROW_ID AS QID,
    --QuestionsSelector.Group_FK AS GFK,
    QuestionTextSel.Text AS QTXT,
    --QuestionTextSel.TextLanguage as QL,
--Remove! \
   
    Questions.ROW_ID AS QuestionID,
    Questions.Group_FK,
    ISNULL(QuestionText.Text,'Null') AS QuestionText,
    QuestionText.TextLanguage,
    Questions.Text_FK AS Question_Label_ID,
    Questions.AnswerGroup_FK,
    Questions.Optional,
    Questions.AllowUserComments,
    Questions.Layout AS QuestionLayout,
    Questions.isHorizontal,
    ISNULL(CommentText.Text,'Null') AS CommentText,
    Questions.CommentTitle_FK,
    Questions.CommentRows,
    Questions.CommentColumns,
    Questions.CommentTitlePlace,
    Questions.AnswerGroupPlace AS AnswerPlace,
    Questions.CondShowType AS QuestionCondShowType,
    QuestionProperties.XMLSettings AS QuestionProperties,
    ISNULL(QuestionFooterText.Text,'') AS QuestionFooter,
    ISNULL(QuestionHeaderText.Text,'') AS QuestionHeader
   
   
FROM 
--|||||
    dbo.tblQuiz Survey
      INNER JOIN dbo.tblLabelText SurveyText
        ON Survey.Text_FK = SurveyText.Label_FK
      LEFT OUTER JOIN dbo.tblLabelText SurveyDescription
        ON Survey.Description_FK = SurveyDescription.Label_FK
      LEFT OUTER JOIN dbo.tblProperties SurveyProperties
        ON Survey.Properties_FK = SurveyProperties.ROW_ID
      LEFT OUTER JOIN dbo.tblLabelText SurveyHeaderText
                      INNER JOIN dbo.tblAdditionalText SurveyHeader
                        ON SurveyHeaderText.Label_FK = SurveyHeader.Label_FK
                            AND SurveyHeaderText.TextLanguage = @lang
        ON Survey.ROW_ID = SurveyHeader.Parent_FK
      LEFT OUTER JOIN dbo.tblLabelText SurveyFooterText
                      INNER JOIN dbo.tblAdditionalText SurveyFooter
                        ON SurveyFooterText.Label_FK = SurveyFooter.Label_FK
                            AND SurveyFooterText.TextLanguage = @lang
        ON Survey.ROW_ID = SurveyFooter.Parent_FK
--|||||
      LEFT OUTER JOIN dbo.tblGroup Groups
    ON Groups.Quiz_FK = Survey.Row_ID
      LEFT JOIN dbo.tblLabelText GroupText
        ON Groups.Text_FK = GroupText.Label_FK
      LEFT OUTER JOIN dbo.tblLabelText AnswerGroup1Title
        ON Groups.AnswerGroup1Title_FK = AnswerGroup1Title.Label_FK
        and AnswerGroup1Title.TextLanguage = @lang
      LEFT OUTER JOIN dbo.tblLabelText AnswerGroup2Title
        ON Groups.AnswerGroup2Title_FK = AnswerGroup2Title.Label_FK
        and AnswerGroup2Title.TextLanguage = @lang
      LEFT OUTER JOIN dbo.tblProperties GroupProperties
        ON Groups.Properties_FK = GroupProperties.ROW_ID
      LEFT OUTER JOIN dbo.tblLabelText GroupHeaderText
                      INNER JOIN dbo.tblAdditionalText GroupHeader
                        ON GroupHeaderText.Label_FK = GroupHeader.Label_FK
                            AND GroupHeaderText.TextLanguage = @lang
        ON Groups.ROW_ID = GroupHeader.Parent_FK and GroupHeader.type = 1
      LEFT OUTER JOIN dbo.tblLabelText GroupFooterText
                      INNER JOIN dbo.tblAdditionalText GroupFooter
                        ON GroupFooterText.Label_FK = GroupFooter.Label_FK
                            AND GroupFooterText.TextLanguage = @lang
        ON Groups.ROW_ID = GroupFooter.Parent_FK and GroupFooter.type = 2
--||||
-- SELECTOR
      LEFT OUTER JOIN dbo.tblQuestion QuestionsSelector
    ON QuestionsSelector.group_fk = Groups.row_id
      LEFT OUTER JOIN dbo.tblLabelText QuestionTextSel
        ON QuestionTextSel.Label_FK = QuestionsSelector.Text_FK
        and QuestionTextSel.TextLanguage = 1 --all ENG
--||||
      LEFT OUTER JOIN dbo.tblQuestion Questions
    ON QuestionsSelector.row_ID = Questions.row_id
      LEFT OUTER JOIN dbo.tblLabelText QuestionText
        ON QuestionText.Label_FK = Questions.Text_FK
        and QuestionText.TextLanguage = @lang
     
      LEFT OUTER JOIN dbo.tblLabelText CommentText
        ON Questions.CommentTitle_FK = CommentText.Label_FK
            AND CommentText.TextLanguage = @lang
      LEFT OUTER JOIN dbo.tblProperties QuestionProperties
        ON Questions.Properties_FK = QuestionProperties.ROW_ID
      LEFT OUTER JOIN dbo.tblLabelText QuestionHeaderText
              INNER JOIN dbo.tblAdditionalText QuestionHeader
                ON QuestionHeaderText.Label_FK = QuestionHeader.Label_FK
                    AND QuestionHeaderText.TextLanguage = @lang
        ON Questions.ROW_ID = QuestionHeader.Parent_FK
            AND QuestionHeader.Type = 1
      LEFT OUTER JOIN dbo.tblLabelText QuestionFooterText
              INNER JOIN dbo.tblAdditionalText QuestionFooter
                ON QuestionFooterText.Label_FK = QuestionFooter.Label_FK
                    AND QuestionFooterText.TextLanguage = @lang
        ON Questions.ROW_ID = QuestionFooter.Parent_FK
            AND QuestionFooter.Type = 2
WHERE  (Survey.ROW_ID = @SurveyID)

ORDER BY
    tGroupOrder, tQuestionOrder

[/SQL]

Næ, fy for pokker! Lav du hellere en opsplitning af data vha. functions. Hvis du ikke har for mange tekstlabels må det være en intern array eller en memorytable (kan man det i MSSQL?) de skal over i, sorteret og med binært eller lignende hurtig opslagsmetode.

Er der mange records i labelstabellen?

tjoe... alle labels som bruges på Survey'et ligger i Labels-tabellen

jeg har ikke de vilde ideer om hvordan man kan optimere SQL'en... den er pænt uoverskuelig efterhånden !

i give up