PsychoDazey
asked on
create xml
I am trying to create a stored procedure that returns the output in XML. I have attached the sample of what I have below, which works, but it is going to be a big hit on performance once there are a more records in the database.
How can I re-write this to make it faster?
How can I re-write this to make it faster?
SELECT QuestionnaireSections.SectionId,ISNULL(sectiontitle,'') AS sectiontitle,ISNULL(SectionDescription,'') AS SectionDescription,ISNULL(NumberingSequence,'') AS NumberingSequence,ISNULL(DisplaySettings,'') AS DisplaySettings,ISNULL(RepeatHeaders,'') AS RepeatHeaders,ISNULL(Startdate,'') AS Startdate,ISNULL(enddate,'') AS enddate,
(SELECT SubSections.SectionId,ISNULL(sectiontitle,'') AS sectiontitle,ISNULL(SectionDescription,'') AS SectionDescription,ISNULL(NumberingSequence,'') AS NumberingSequence,ISNULL(DisplaySettings,'') AS DisplaySettings,ISNULL(RepeatHeaders,'') AS RepeatHeaders,ISNULL(Startdate,'') AS Startdate,ISNULL(enddate,'') AS enddate,
(SELECT Questions.QuestionId AS QuestionId,ResponseOptions AS OptionId,ISNULL(QuestionTypeID,'') AS QuestionTypeID,ISNULL(SortId,'') AS SortId,ISNULL(QuestionDesc,'') AS QuestionDesc,ISNULL(PlanTypeId,'') AS PlanTypeId,ISNULL(ResponseTypeId,'') AS ResponseTypeId,ISNULL(ResponseOptions,'') AS ResponseOptions,ISNULL(IsMultiResponse,'') AS IsMultiResponse,ISNULL(QuestionNum,'') AS QuestionNum,ISNULL(PendFlag,'') AS PendFlag,
(SELECT OptionId AS OptionId,OptionText As OptionText
FROM IMQ.tbl_Q_OptionChoices OptionChoices
WHERE OptionChoices.QuestionId = Questions.QuestionId FOR XML AUTO, TYPE
),
(SELECT QWorkFlowId,QuestionIdCurrent,QuestionIdNext,ISNULL(FlowId,'') AS FlowId,
(SELECT ConditionId ,QuestionId,ConditionFunction,Conditionvalue,ISNULL(Units,'') AS Units,DateOptionId
FROM imq.tbl_Q_WorkFlowDetails WorkFlowDetails
WHERE WorkFlowDetails.QWorkFlowId = WorkFlow.QWorkFlowId FOR XML AUTO, TYPE
)
FROM imq.tbl_Q_WorkFlow WorkFlow
WHERE WorkFlow.QuestionIdCurrent = Questions.QuestionId FOR XML AUTO, TYPE
),
(SELECT SubQuestions.QuestionId AS QuestionId,ResponseOptions AS OptionId,ISNULL(QuestionTypeID,'') AS QuestionTypeID,ISNULL(SortId,'') AS SortId,ISNULL(QuestionDesc,'') AS QuestionDesc,ISNULL(PlanTypeId,'') AS PlanTypeId,ISNULL(ResponseTypeId,'') AS ResponseTypeId,ISNULL(ResponseOptions,'') AS ResponseOptions,ISNULL(IsMultiResponse,'') AS IsMultiResponse,ISNULL(QuestionNum,'') AS QuestionNum,ISNULL(PendFlag,'') AS PendFlag,
(SELECT OptionId AS OptionId,OptionText As OptionText
FROM IMQ.tbl_Q_OptionChoices OptionChoices
WHERE OptionChoices.QuestionId = SubQuestions.QuestionId FOR XML AUTO, TYPE
),
(SELECT QWorkFlowId,QuestionIdCurrent,QuestionIdNext,ISNULL(FlowId,'') AS FlowId,
(SELECT ConditionId ,QuestionId,ConditionFunction,Conditionvalue,ISNULL(Units,'') AS Units,DateOptionId
FROM imq.tbl_Q_WorkFlowDetails WorkFlowDetails
WHERE WorkFlowDetails.QWorkFlowId = WorkFlow.QWorkFlowId FOR XML AUTO, TYPE
)
FROM imq.tbl_Q_WorkFlow WorkFlow
WHERE WorkFlow.QuestionIdCurrent = SubQuestions.QuestionId FOR XML AUTO, TYPE
),
(SELECT SubChildQuestions.QuestionId AS QuestionId,ResponseOptions AS OptionId, ISNULL(QuestionTypeID,'') AS QuestionTypeID,ISNULL(SortId,'') AS SortId,ISNULL(QuestionDesc,'') AS QuestionDesc,ISNULL(PlanTypeId,'') AS PlanTypeId,ISNULL(ResponseTypeId,'') AS ResponseTypeId,ISNULL(ResponseOptions,'') AS ResponseOptions,ISNULL(IsMultiResponse,'') AS IsMultiResponse,ISNULL(QuestionNum,'') AS QuestionNum,ISNULL(PendFlag,'') AS PendFlag,
(SELECT OptionId AS OptionId,OptionText As OptionText
FROM IMQ.tbl_Q_OptionChoices OptionChoices
WHERE OptionChoices.QuestionId = SubChildQuestions.QuestionId FOR XML AUTO, TYPE
),
(SELECT QWorkFlowId,QuestionIdCurrent,QuestionIdNext,ISNULL(FlowId,'') AS FlowId,
(SELECT ConditionId ,QuestionId,ConditionFunction,Conditionvalue,ISNULL(Units,'') AS Units,DateOptionId
FROM imq.tbl_Q_WorkFlowDetails WorkFlowDetails
WHERE WorkFlowDetails.QWorkFlowId = WorkFlow.QWorkFlowId FOR XML AUTO, TYPE
)
FROM imq.tbl_Q_WorkFlow WorkFlow
WHERE WorkFlow.QuestionIdCurrent = SubChildQuestions.QuestionId FOR XML AUTO, TYPE
)
FROM IMQ.tbl_Q_Question SubChildQuestions
INNER JOIN IMQ.tbl_Map_ParentToSubQuestions sq_scq ON SubChildQuestions.questionId = sq_scq.SubquestionId AND SubQuestions.QuestionId = sq_scq.ParentquestionId
WHERE SubQuestions.questionId = sq_scq.ParentquestionId FOR XML AUTO,TYPE
)
FROM IMQ.tbl_Q_Question SubQuestions
INNER JOIN IMQ.tbl_Map_ParentToSubQuestions q_sq ON SubQuestions.questionId = q_sq.SubquestionId
WHERE Questions.questionId = q_sq.ParentquestionId FOR XML AUTO,TYPE
)
FROM IMQ.tbl_Q_Question Questions
INNER JOIN IMQ.tbl_Map_QuestionToSection q_s ON Questions.questionId = q_s.questionId
WHERE q_s.sectionId = SubSections.SectionId OR q_s.sectionId = QuestionnaireSections.SectionId FOR XML AUTO,TYPE
)
FROM IMQ.tbl_QuestionnaireSections SubSections
LEFT JOIN IMQ.tbl_Map_SectionToSubSection s_s ON s_s.SubsectionId = SubSections.SectionId
WHERE s_s.ParentSectionId = QuestionnaireSections.SectionId
FOR XML AUTO, TYPE
)
FROM IMQ.tbl_QuestionnaireSections QuestionnaireSections
WHERE QuestionnaireSections.VersionId = @versionid
FOR XML AUTO, TYPE
Generating XML with SQL is NEVER going to be extremely efficient. But I do notice that the only criteria you have is to join your subqueries out to the parent item. Do you really need to generate your entire structure every time?
ASKER
Yes, the xml structure is going to be saved in the database so it can later be extracted and modified.
But you're not saving the XML in the database, you're building the XML from the data in the database.
ASKER
Right, and then using that data to run a report. My problem isn't generating the XML, its the query that I'm using isn't well-written and it is starting to time out.
You don't have do to it with all of those nested queries. You can do it with a series of joins. This will allow the query to be run with or without it going to XML.
ASKER
Sounds good, can you please give me an example?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.