2
Vote

Timeout issues

description

Hello,
Uploading and saving images is timing out - the query is taking 51 secs to execute. And a profile of the query shows there is an index scan taking place

The query is:
            select cpd.contentNodeId as nodeId, cpd.dataNText as xmlValue, cpt.Alias as propertyAlias from cmsDocument cd
                inner join cmsPropertyData cpd ON cd.nodeId = cpd.contentNodeId
                inner join cmsPropertyType cpt ON cpd.propertytypeid = cpt.id
            where cd.newest = 1
            and cd.versionId = cpd.versionId
            and cpd.dataNtext like '<DAMP fullMedia="">%'
            and cpd.dataNtext like '%749896%'
92% of the query is running a clustered index scan over cmsPropertyData. Can you recommend a solution please?

comments

petergledhill wrote Oct 28, 2014 at 10:24 AM

We have the same problem. This query takes over a minute to return when first executed.

I've tried optimizing the query to only include nodes which contain a damp data type but this actually slowed it down further because the column dataTypeId on cmsPropertyType wasn't indexed.

However, my understanding is that it only really needs to be called if you have a data type which uses the 'Full Media Xml' option.

All our pickers use the 'Id' option so I've written a workaround which checks to see if this is the case then bypasses this query all together.

I've made a pull request so hopefully it'll get put into the next version of DAMP.

https://damp.codeplex.com/SourceControl/network/forks/petergledhill/PerformanceImprovements/contribution/7625