Dynamic form tables and Pivot optimization

Question

Dynamic form tables and Pivot optimization

kevin 26531

Mr or Mrs. 500

Points: 540
More actions
November 7, 2014 at 3:00 am

#291788

Hi all,
I need help with this one. We have a complex dynamic forms app that lets you creates forms and build the data with it. Field descriptors and data are all held in tables, one row per item , descriptors (field name, type) in one and data in another. We then build a list of field names into a a string, then find all the fields matching these and do a pivot to bring the rows into the columns.
The problem is the execution speed. One form has over 200K rows and is taking 8+ seconds to run, regardless of how many rows we fetch.
One of the issues we have is that the data that is saved with the field can be anything from a 2 letter appreviation, to a two page note. The column "answer" in pivot statement 6 lines from the end is an nvarchar(4000) which may be costing us a lot.
Any help, suggestions on how to make this more efficient. Any other ways to transpose from rows to cols other than a pivot?
thanks,
kevin
Sql as follows:
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @id varchar(10),@orgid varchar(10)
select @cols = STUFF((SELECT ',' + QUOTENAME('Field-' +cast(filtertype as varchar(10)) + '-' + cast(id as varchar(10)) ) as columnname
FROM tbl_forms_fields
WHERE templateid = @templateid
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
---select @cols
set @query = 'SELECT item1, item2, item3, ' + @cols + ' ' + ' from
(select
item1,item2, item 3...
from tbl_forms_data tfd
inner join tbl_forms tf on tf.formid = tfd.formid
inner join tbl_forms_templates tft on tft.id = tf.templateid
inner join tbl_forms_fields tff on tff.templateid = tf.templateid and tff.id = tfd.qid
inner join tbl_users u on u.user_id = tf.creator_id
where tf.templateid = ' + @id + ' and ( tft.orgid = ' + @orgid + ' and dbo.forms_haveaccess(tf.formid,' + cast( @user_id as varchar(10)) +',tft.privacy) = 1)
or ( tf.templateid = ' + @id + ' and tf.orgid = ' + @orgid + ' and tf.embedded = 0 and dbo.forms_haveaccess(tf.formid,' + cast( @user_id as varchar(10)) +',tft.privacy) = 1)
group by item1,item2, item 3 ....
) x
pivot
(
min(answer)
for columnname in (' + @cols + ')
) p
order by formdate desc
'
execute(@query);
GO

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

you can look into the following two options.

1) Check if the UDF used in the WHERE Clause is slowing the query. you can do this by getting the value in a parameter and place that parameter in the query.

2) try to use cross tab query instead of Pivot. If you next question is WHY then following is the excellent article to answer your question. Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url] by Jeff Moden.

hope it helps.

kevin 26531 Mr or Mrs. 500 Points: 540 More actions · Answer 2

Twin.Devil

thanks. I checked the UDF and it's not impacting it (much). I took a look at the link you gave me on cross tabs - will need to get a couple of cups of coffee and see if I can get my head around it.

thanks

kevin 26531 Mr or Mrs. 500 Points: 540 More actions · Answer 3

Done a lot more digging and found that the xml path is taking 84% of the total query time, where the dynamic query is only 14%.

Anyone got any ideas on how to improve the performance here? Is there an alternate to using xml path?

thanks

----------------------------------

select @cols = STUFF((SELECT ',' + QUOTENAME('Field-' +cast(filtertype as varchar(10)) + '-' + cast(id as varchar(10)) ) as columnname

FROM tbl_forms_fields

WHERE templateid = @templateid

FOR XML PATH(''), TYPE

).value('.', 'NVARCHAR(MAX)')

,1,1,'')

Umer Akram SSC-Insane Points: 22209 More actions · Answer 4

kevin 26531 (11/8/2014)
Done a lot more digging and found that the xml path is taking 84% of the total query time, where the dynamic query is only 14%.
Anyone got any ideas on how to improve the performance here? Is there an alternate to using xml path?
thanks
----------------------------------
select @cols = STUFF((SELECT ',' + QUOTENAME('Field-' +cast(filtertype as varchar(10)) + '-' + cast(id as varchar(10)) ) as columnname
FROM tbl_forms_fields
WHERE templateid = @templateid
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

can you share how you determine this "xml path is taking 84% of the total query time, where the dynamic query is only 14%"?

i have used this approach several times, its never caused me any issue as far as performance goes. try to print the dynamic PIVOT query and then execute that query independently to see how much time is getting to be executed.