Products

Solutions

Resources

Partners

Community

Blog

About

QA

Ideas Test

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeDNN Open Source...DNN Open Source...Module ForumsModule ForumsForm and ListForm and ListUDT performance / load times issueUDT performance / load times issue
Previous
 
Next
New Post
8/18/2011 7:22 AM
 
Richard, I think you problem is that you are using a linked version o the FnL module instead of a copy.
 
New Post
8/18/2011 8:14 AM
 
Stefan Cullmann wrote:
Richard, sorry, this change would return the very same amount of data as before.

 That's exactly right - the changes are totally benign to F&L but provide a crib for local tailoring as shown below which restricts the data returned (for a filter of the form somefield='somevalue'):

ALTER PROCEDURE [dbo].[UserDefinedTable_GetRows] @TabModuleId int AS BEGIN

DECLARE @ModuleID INT = ( SELECT TOP 1 ModuleID FROM dbo.TabModules WHERE TabModuleId = @TabModuleId )

DECLARE @Filter NVARCHAR (2000) = ( SELECT TOP 1 SettingValue FROM dbo.TabModuleSettings WHERE TabModuleId = @TabModuleId AND SettingName = 'UDT_Filter' )
DECLARE @ComparitorPos INT = CHARINDEX ( '=' , @Filter )

DECLARE @FilterValue NVARCHAR (2000) = REPLACE ( SUBSTRING ( @Filter, @ComparitorPos + 1, 2000 ), '''', '' )

DECLARE @FilterFieldTitle NVARCHAR (50) = LEFT ( @Filter, @ComparitorPos - 1 )

 DECLARE @FilterFieldID INT = ( SELECT TOP 1 UserDefinedFieldID FROM dbo.UserDefinedFields WHERE FieldTitle = @FilterFieldTitle )

SELECT R.UserDefinedRowId, F.FieldTitle, D.FieldValue FROM dbo.UserDefinedRows R

LEFT OUTER JOIN dbo.UserDefinedData D on R.UserDefinedRowId = D.UserDefinedRowId

 INNER JOIN dbo.UserDefinedFields F on D.UserDefinedFieldId = F.UserDefinedFieldId

 WHERE R.ModuleId = @ModuleId
 AND R.UserDefinedRowID IN ( SELECT UserDefinedRowID FROM dbo.UserDefinedData WHERE
UserDefinedFieldID = @FilterFieldID AND CAST (FieldValue AS NVARCHAR(2000) ) = @FilterValue )

 ORDER by R.UserDefinedRowID

END

I think it only needs a very slight tweak in the VB Code to swap ModuleId fro TabModuleId ...

Cheers, Richard

 
New Post
8/18/2011 8:36 AM
 
Hi Stefan

That is deliberate - there is also a requirement to search for all the events attended by a particular user. So there is one 'master' list with 1000 linked (filtered) versions.

I accept that the search over the entire list will be slow with the current data structures but I think they can also be improved.

I think the key is to split the Value field into an index and a separate values table, for HTML fields, text fields, etc.. The next strategy is to change from reading the entire list to reading each column at a time. Where the column is a User look up, for example, the index should be treated as the UserID and a JOIN can be done in the database. This would require just n database queries where n is the number of columns in the user defined table. No cell-level API for lookups or computed fields.

(I would probably add a separate field for dates, but have not had time to think this through)

Cheers

Richard

 
New Post
12/16/2011 11:50 PM
 
I have made the change I suggested (changing ModuleID to TabModuleID in one place in the VB code) which then allows me to implement some F&L filters at the SQL level, which can 'read' and interpret the specific filter for the TabModule being populated. The performance increases have been staggering - orders of magnitude - for some views. Our website Form and List functionality has gone from virtually unusable (lots of timeouts at 90-seconds) to supporting a happy community with responses between 1 and 2 seconds for most filtered views.

Cheers

Richard
 
New Post
12/17/2011 6:19 AM
 

Richard,
If I understand you correctly you are placing mirrored instances of FnL on a lot of pages. That way you have only one schema and one big data source. You are also filtering on page level.  FnL retrieves all data based on ModuleId in a serial stream, which gets transformed into a dataset in memory. Different datatypes like download require further action. At one of the last steps, data gets filtered. I am not surprised that your approach doesn't scale.

 IMHO you are using the module a wrong way. I would consider using templates - that way each page has its own moduleid (or only a small numbers of modules share the same moduleid). No need to filter, and it would be fast enough.

You created a fork  which is not compatible.

 
Previous
 
Next
HomeHomeDNN Open Source...DNN Open Source...Module ForumsModule ForumsForm and ListForm and ListUDT performance / load times issueUDT performance / load times issue


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out