6

I have several WFFM forms in my solution. Some of them have a very large number of responses (30000+). When I attempt to download that form data, I get timeout errors and the whole CM server seems to grind to a halt producing repeated 500 timeout errors afterwards. Is there some way to download the data in chunks (by some filtering mechanism?) or is there some accepted approach for clearing out data after a certain age? This seems strange as I can write a query to bring back the equivalent data and that takes only a few seconds (albeit not nicely formatted with form fields as columns).

2

Adding indexing onto the two WFFM database tables increases performance significantly, enough to eliminate the timeout issues. I used the following SQL script to add indexing to the FieldData and FormData tables and to rebuild the indexes.

--Create index on FieldData table
CREATE NONCLUSTERED INDEX [IX_FormId] ON [dbo].[FieldData]
(
   [FormId] ASC
)WITH (
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

--Create index on FormData table
CREATE NONCLUSTERED INDEX [IX_FormItemId] ON [dbo].[FormData]
(
    [FormItemId] ASC
)WITH (
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

--Rebuild indexes on FieldData table
ALTER INDEX ALL ON [dbo].[FieldData]
REBUILD WITH (
    FILLFACTOR = 80,
    SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = ON);
GO

--Rebuild indexes on FormData table
ALTER INDEX ALL ON [dbo].[FormData]
REBUILD WITH (
    FILLFACTOR = 80,
    SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = ON);
GO

Previous Comment

After bringing a backup of our data and attaching it locally, I ran SQL Profiler while running the export. It seems that WFFM actually issues a separate SELECT statement for each form submission, which seems crazy to me. I guess this explains the slowness/timeouts when working with significant amounts of data. Not sure if there is a solution to this, beyond simply writing your own SQL query to extract the data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.