October 19, 2010 at 7:23 am
Hi , I have a procedure that collect all data and convert them to XML by FOR XML , when too many records are passed to FOR XML I face with time out, how can i solve this problem ?
the part of code is in the below , can I change the structure to make a better query with a lower cost?
SELECT @XML = (SELECT
(SELECT T.ID AS '@RecordID', T.Date AS '@Date',T.Num AS '@Num',@ItemCount AS '@RowCount', @PageSize AS '@PageSize',
(SELECT ElementID AS 'Field/@ElementID', FieldID AS 'Field/@FieldID',EntityRecordID as 'Field/@RecordID', FieldName AS'FieldName', Caption AS 'Field/@Caption', ElementType AS 'Field/@ElementType',
--EntityName AS 'Field/@EntityName', EntityID AS 'Field/@EntityID' ,
ForeignKey AS 'Field/@ForeignKey', Visible AS 'Field/@Visible', FKPathID AS 'Field/@FKPathID',
value AS 'Field'
--,Style AS 'Field/Style',CaptionStyle AS 'Field/CaptionStyle'
FROM
(
---getting value, element properties for field that has value
SELECT FieldID, FieldName , TFE.ElementID,
CASE WHEN FV.GuidValue IS NOT NULL
THEN CAST(FV.GuidValue AS NVARCHAR(50))
ELSE
CASE ISNULL(TFE.CompositeField,'') WHEN ''
THEN FV.Value
ELSE (SELECT top 1 Value FROM SF.GetCompositeRecords(TFE.EntityID,TFE.CompositeField) Where RecordID = RFK. RecordID)
END END AS [Value],
PageOrder, FieldSetOrder, SortOrder, Caption,
--E.Name AS EntityName ,RFK.FKEntityID AS EntityID,
Visible, ElementType , ForeignKey,
--Style, CaptionStyle,
RFK.FKRecordID AS EntityRecordID, RFK.RecordID AS BaseRecordID, RFK.FKPathID AS FKPathID--, pic.Content
FROMST.RecordFK AS RFK
JOIN ST.FieldValue AS FV ON FV.RecordID = RFK.FKRecordID
JOIN ST.EntityFields AS EF ON EF.ID = FV.FieldID
--JOIN ST.Entity AS E ON E.ID = RFK.FKEntityID
LEFT JOIN #Fields AS TFE ON TFE.FID = EF.ID
WHERE
((
-- filtering records in rfk table using recordID, and fields that are not
-- foreignkey and the fieldID is present in form elements' field ID
RFK.RecordID = T.ID
AND ForeignKey IS NULL
AND FieldID IN (SELECT FID FROM #Fields)
AND
(
-- filtering base on fkpath including records that fkpaths are mached, OR
-- values belong to base record itself
TFE.FKPathID IS NULL
OR TFE.FKPathID = ''
OR TFE.FKPathID = RFK.FKPathID
)
)
OR
(
-- adds foreignkey values that belongs to this records
ForeignKey IS NOT NULL
AND RFK.RecordID = T.ID
))
---filter composite field, they will add in following union
AND (TFE.CompositeField IS NULL OR TFE.CompositeField = '')
UNION All
--Adds composite fields' value to set
SELECT FieldID, FieldName , TFE.ElementID,
CFT.Value AS [Value],
PageOrder, FieldSetOrder, SortOrder, Caption,
--E.Name AS EntityName , RFK.FKEntityID AS EntityID,
Visible, ElementType , ForeignKey,
--Style, CaptionStyle,
RFK.FKRecordID AS EntityRecordID, RFK.RecordID AS BaseRecordID, RFK.FKPathID AS FKPathID--, pic.Content
FROMST.RecordFK AS RFK
JOIN (SELECT ID ,EntityID, CompositeField, [Value] collate database_default AS [Value] ,FieldID FROM
#CalculatedFieldTable)AS CFT ON CFT.ID = RFK.FKRecordID
JOIN ST.EntityFields AS EF ON EF.ID = CFT.FieldID
--JOIN ST.Entity AS E ON E.ID = RFK.FKEntityID
LEFT JOIN #Fields AS TFE ON TFE.FID = EF.ID
WHERE
((
-- filtering records in rfk table using recordID, and fields that are not
-- foreignkey and the fieldID is present in form elements' field ID
RFK.RecordID = T.ID
AND ForeignKey IS NULL
AND FieldID IN (SELECT FID FROM #Fields)
AND
(
-- filtering base on fkpath including records that fkpaths are mached, OR
-- values belong to base record itself
TFE.FKPathID IS NULL
OR TFE.FKPathID = ''
OR TFE.FKPathID = RFK.FKPathID
)
)
OR
(
-- adds foreignkey values that belongs to this records
-- it is over check because no foreignkey can have composite field
-- this 'or codition' can be removed
ForeignKey IS NOT NULL
AND RFK.RecordID = T.ID
))
---Adds composite fields' value to set
AND (TFE.CompositeField IS NOT NULL AND TFE.CompositeField != '')
------
UNION ALL
--Add Aggregate Field
SELECT NULL AS FieldID, NULL AS FieldName , TFE.ElementID,
SF.GetAggregateValue(TFE.ElementID, T.ID)AS [Value],
PageOrder, FieldSetOrder, SortOrder, Caption,
--NULL AS EntityName , NULL AS EntityID,
Visible, ElementType , NULL,
--Style, CaptionStyle,
NULL AS EntityRecordID, NULL AS BaseRecordID, '' AS FKPathID--, pic.Content
FROM#Fields AS TFE
WHERE
TFE.ElementType = 'Aggregate'
) AS F
ORDER BY ForeignKey ASC, F.PageOrder, F.FieldSetOrder, F.SortOrder
FOR XML Path (''), TYPE, BINARY BASE64
)
FROM (SELECT TOP (@TopExp) * FROM #T WHERE (@PageSize = 0) OR (#T.Num > @StartIndex) ) AS T
ORDER BY T.Num
FOR XML Path, TYPE) AS '*',
-- Making complete fields catalog for filling gaps
(
--making complete fields to fill gaps in form
SELECT
ElementID AS 'Field/@ElementID',
FieldID AS 'Field/@FieldID',
Caption 'Field/@Caption',
Name 'Field/@Name',
EntityName AS 'Field/@EntityName',
FKPathID AS 'Field/@FKPathID',
Visible AS 'Field/@Visible',
EntityID AS 'Field/@EntityID',
ElementType AS 'Field/@ElementType',
SortOrder AS 'Field/@SortOrder',
ForeignKey AS 'Field/@ForeignKey',
FieldType 'Field/@FieldType',
PageID AS 'Field/@PageID',
FieldSetID 'Field/@FieldSetID',
SubFormID 'Field/@SubFormID',
Style AS 'Field/Style',
CaptionStyle 'Field/CaptionStyle'
FROM
(
SELECT
ElementID AS ElementID,
EF.ID AS FieldID,
Caption AS Caption,
FieldName AS Name,
E.Name AS EntityName,
EFK.FKPathID AS FKPathID,
Visible AS Visible,
EFK.FKEntityID AS EntityID,
ElementType AS ElementType,
TFE.SortOrder AS SortOrder,
ForeignKey AS ForeignKey,
FT.Name FieldType,
TFE.PageID AS PageID,
TFE.FieldSetID FieldSetID,
NULL AS SubFormID,
Style AS Style,
CaptionStyle AS CaptionStyle,
TFE.PageOrder AS PageOrder,
TFE.FieldSetOrder AS FieldSetOrder
FROMST.EntityFK EFK
JOIN ST.EntityFields EF ON EFK.FKEntityID = EF.EntityID
JOIN ST.FieldType FT ON FT.ID = EF.FieldTypeID
JOIN ST.Entity AS E ON E.ID = EFK.FKEntityID
LEFT JOIN #Fields AS TFE ON TFE.FID = EF.ID
WHERE
--filtering related entities to which have relation to base entity
EFK.EntityID = @EntityID
--not foreign key fields in temp table
AND (TFE.FormID = @FormID OR EF.ForeignKey IS NOT NULL)
AND (
--matching fkpaths
---null or empty fkpath means base entity's fields
TFE.FKPathID IS NULL
OR TFE.FKPathID = ''
---OR matching fkpath with element fkpath
OR TFE.FKPathID = EFK.FKPathID
)
UNION ALL --------------------------
SELECT
FE.ID AS ElementID,
FE.FieldID AS FieldID ,
FE.Caption AS Caption,
FE.Name AS Name ,
NULL AS EntityName ,
FE.FKPathID AS FKPathID ,
FE.Visible AS Visible ,
NULL AS EntityID ,
ET.Type AS ElementType ,
FE.SortOrder AS SortOrder ,
NULL AS ForeignKey ,
NULL AS FieldType ,
P.ID AS PageID ,
FS.ID AS FieldSetID ,
Lower(SubFormID) AS SubFormID,
FE.Style AS Style,
FE.CaptionStyle AS CaptionStyle,
P.PageOrder AS PageOrder,
FS.FieldSetOrder AS FieldSetOrder
FROM
ST.FormElements FE
JOIN ST.ElementType ET ON ET.ID = FE.ElementTypeID
LEFT JOIN ST.FieldSet AS FS on FS.ID = FE.FieldSetID
LEFT JOIN ST.Page AS P ON P.ID = FS.PageID
WHERE
FormID = @FormID
AND (FE.FieldID IS NULL OR FE.FieldID = '00000000-0000-0000-0000-000000000000')
--AND ElementTypeID = @SubFormTypeID
) AS CF
ORDER BY ForeignKey, PageOrder, FieldSetOrder, SortOrder
FOR XML Path(''),Type )
AS CompleteFields,
(
--SELECT (
SELECT P.ID AS 'Page/@ID',
P.Name AS 'Page/@Name',
P.Title AS 'Page/@Caption',
P.PageOrder AS 'Page/@PageOrder',
(
SELECTFS.ID AS 'FieldSet/@ID',
FS.Name AS 'FieldSet/@Name',
FS.DisplayName AS 'FieldSet/@DisplayName',
FS.FieldSetOrder AS 'FieldSet/@FieldSetOrder',
FS.Caption AS 'FieldSet/@Caption',
CASE FS.ColumnNumber WHEN 0 THEN 1 ELSE FS.ColumnNumber END AS 'FieldSet/@ColumnNumber',
FS.Style AS 'FieldSet/@Style',
FS.ElementView AS 'FieldSet/@ElementView',
FS.LegendStyle AS 'FieldSet/@LegendStyle',
FS.CssClass AS 'FieldSet/@CssClass',
FS.LegendCssClass AS 'FieldSet/@LegendCssClass',
FS.InVisible AS 'FieldSet/@InVisible',
FS.[ReadOnly] AS 'FieldSet/@ReadOnly',
FS.MiscData AS 'FieldSet/MiscData'
FROM ST.FieldSet FS
WHERE FS.PageID = P.ID
ORDER BY FS.FieldSetOrder
FOR XML Path(''),TYPE
) AS 'Page'
FROM ST.Page AS P
WHERE P.BaseFormID = @FormID
ORDER BY P.PageOrder
FOR XML Path(''), ROOT('Pages'),Type
)AS [View]
FOR XML Path(''), ROOT ('root'), TYPE
)
October 19, 2010 at 2:26 pm
It looks like it's possible to replace one or two of the UNION with a CASE statement.
It also migt be a good idea to use the "divide'n'conquer" method, meaning to store the subresults of UNION queries that cannot be merged any further into a temp table and do the final xml select based on the temp table (maybe adding an index before...).
Please provide table def and some ready t use sample data including your expected result based on those samples as described in the first link in my signature.
I know, it's going to be some work to get all those data together, but you're asking for an optimization of 250 lines of code...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply