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 ListWriting subqueries against UDT DataWriting subqueries against UDT Data
Previous
 
Next
New Post
1/11/2007 11:35 AM
 

ajwaka wrote
But an rtrim() won't work.  Seems if alot of rows are passed, this could affect performance?

For sure. We don't recommend querying the UDT via SQL with these kind of SQL. It is the slowest way do do so. We get these questions reguarly, because they need reporting or are trying to export the data. So we show the only way we are aware though we would never go this way in a productive system.

ajwaka wrote
Also,  I can't select between different dates  ie: Between 1/1/2007 and 1/1/2006 because of the ntext.  Any Ideas here?  It seems my "easiest solution" isn't that after all.

You could change your query to cast the ntext date values to date on the server.  But this would stress the server even more.

 

 
New Post
1/11/2007 11:58 AM
 
Stefan,

Thanks for the input.  What about using the Substring() function.  Now I know this is for my specific case, where I know what type of info is placed into the columns, and UDT stores the date such as 2007-01-11T00:00:00 and the other columns are currency, and generally NOT bigger than 10 places (going up to 9999999.00 - a hair shy of 10 Million).

So using Substring(C1.FieldValue,1,10) --(for 10 places to get the date)--  I can then "sort" or compare the data, same going with the Currency field - because they become varchars. 

Now, I'm not sure where I can test the timing of these queries for speed.  (I'd like to know though!!)  But this is a specific case scenario, where they will have 1 input a week, so that comes to searching through 260 UDT rows if done for 5 years (I would like to believe some module will pop up by then!!)  So it's not like it will be sifting through thousands of rows.

Please, if my thinking and reasoning is off, inform me.  The only way I can really learn!!!


Andrew Walker

Learn to make your own beer and wine at homeIf you enjoy making your own beer and/or wine - be sure to check out http://www.ForemostBrewing.com
 
New Post
1/11/2007 1:11 PM
 

You can do these query, no doubt and you will get your answers fast.

The sql query is runinng well, and I don't think that you can measue any big effect regarding the load of one page.

But DotNetNuke is often used in a hoseted enviroment, sometime you have multiple instances ot portals running on one machine. Or you are running big community sites with thousands of users. A comman usage of the kind of query would slow down the system quite seriously.

There is one addional issue: your query might break in future. The Sql tables are no part of a public interface, we are free to change them on every module update if it make sense. This is a general issue of all modules that query data out of sql server in a direct way instead of using the provided framework functions.

But don't get irritated by my comments: if your query is acting fine and runs reasonably fast for you, everything is ok.

 
New Post
1/11/2007 1:20 PM
 
Stefan,

Great points to ponder.  I will definately have to keep myself "into the game" with possibilities of changes.

Thank you for your time and answers, it's very much appreciated!!!


Andrew Walker

Learn to make your own beer and wine at homeIf you enjoy making your own beer and/or wine - be sure to check out http://www.ForemostBrewing.com
 
New Post
3/18/2007 3:36 PM
 
leupold wrote

UDT stored all data serialized for flexibility - so all user defined tables with different structures can be stores in the same database tables. the tradeoff is, that it is more difficult to access the data. it is easier, if you use SQL 2005 with the pivot statement, UDT itself used a core function that created a dataset from the serialized data.

For your concrete example I you can use the following query (assume that FieldID of CreatedDate is 132 and FieldID of Total is 143):

SELECT C2.Value as Total, C1.Value as CreatedDate 
FROM UserDefinedData AS C1 INNER JOIN UserDefinedData AS C2 on C1.RowID = C2.RowID 
WHERE C1.FieldID = 132 and C2.FieldID = 143

If you want to go down this path with a SQL query, it should be based like this (assuming parameters above are the same, and the ModuleId = 543):

SELECT

 c2.FieldValue as Total
,c1.FieldValue as CreatedDate
From Test01_UserDefinedRows AS r
LEFT OUTER JOIN Test01_UserDefinedData AS c1 ON r.UserDefinedRowId = c1.UserDefinedRowId AND 132 = c1.UserDefinedFieldId 
LEFT OUTER JOIN Test01_UserDefinedData AS c2 ON r.UserDefinedRowId = c2.UserDefinedRowId AND 143 = c2.UserDefinedFieldId 
WHERE r.ModuleId = 543

The above statement avoids the problem of missing records in UserDefinedData.

 

 
Previous
 
Next
HomeHomeDNN Open Source...DNN Open Source...Module ForumsModule ForumsForm and ListForm and ListWriting subqueries against UDT DataWriting subqueries against UDT Data


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