Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Writing Nearly Codeless Apps: Part 5 Expand / Collapse
Author
Message
Posted Tuesday, November 2, 2010 8:25 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 2:24 PM
Points: 69, Visits: 182
Comments posted to this topic are about the item Writing Nearly Codeless Apps: Part 5
Post #1015007
Posted Wednesday, November 3, 2010 12:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 15, 2011 2:41 PM
Points: 1, Visits: 15
This is a very interesting area that I've also been working on for 20+ years. I've found that using a standard POJO makes high level productivity for medium to high complexity applications not possible. Here is an example of actual code less development. 8 minute overview video:
http://www.planetjavainc.com/videos/WOW_WAS/WOW_WAS.html (takes a minute to load with camtasia). -Paul
Post #1015486
Posted Wednesday, November 3, 2010 6:26 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 2:24 PM
Points: 69, Visits: 182
pholm (11/3/2010)
This is a very interesting area that I've also been working on for 20+ years. I've found that using a standard POJO makes high level productivity for medium to high complexity applications not possible. Here is an example of actual code less development. 8 minute overview video:
http://www.planetjavainc.com/videos/WOW_WAS/WOW_WAS.html (takes a minute to load with camtasia). -Paul
That is pretty cool. WOW kind of takes over where RAP leaves off. RAP is about building the database and the data layer, then it leaves you to code the upper levels by hand (although it provides strict templates for these). WOW presumes the existence of a database already. It strikes me as being similar to the MS Access form generation tools, with a wizard built in (proabaly MS Access has such a wizard too; I haven't really used it in years).
Post #1015642
Posted Thursday, November 4, 2010 3:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:36 PM
Points: 6,002, Visits: 8,267
Hi David,

I'm surprised to see so little comments yet. But who knows, maybe I'm not the only one with a reading backlog.

In the beginning of your article, you write that RAP rewards developers for creating their foreign keys by generating lookup routines. But in the rest of the article, you only describe routines generated upon primary keys and unique constraints. So technicallly, the developer can still get away with not defining the foreign key, as long as the PK and UQ constraints are defined.

More important is that the performance of your generated code on larger tables will be miserable. This is because you use multi-statement table-valued functions to retrieve rows. These are bad news for the optimizer - it has no other option but to execute them first, store the results, then combine these (unindexed) result sets for thte final result. Ouch!

You should convert to inline table-valued functions. These, like views, are treated as macros - the reference to the function is first replaced by its definition, and only then will the optimizer be invoked to work out an execution plan. You'll see a major performance gain!

As an example:
create function UFTBadmUser##PK
(
-- declare the 'As Of' parameter
@AsOf datetime,
-- declare other parameters
@UserId bigint
)
returns table
as
return (select UserId,
LoginName,
Notes,
AuditDate,
AuditUserId,
AuditStatus
from TBadmUser
where UserId = @UserId
and @AsOf is null
union all
select UserId,
LoginName,
Notes,
AuditDate,
AuditUserId,
AuditStatus
from TBadmUser#
where UserId = @UserId
and AuditStatus <> 'D'
and A.AuditDate = (select max(AuditDate) from TBadmUser#
where UserId = A.UserId
and AuditDate <= @AsOf)
and @AsOf is not null
);

Another possible performance optimization is to create seperate function for querying live and archive data. That reduces complexity in the functions (making it easier for the optimizer to come up with an efficient plan). In my experience, it is quite uncommon to use the same queries on both live and archived data. (And since you generate the code, you could also go the whole way and simply generate functions for only live, for only archive, and for the combination, with a note to the developer to only use the latter when neeed, because the other perform better).



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1015734
Posted Thursday, November 4, 2010 8:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 2:24 PM
Points: 69, Visits: 182
Hugo Kornelis (11/4/2010)
Hi David,

... But in the rest of the article, you only describe routines generated upon primary keys and unique constraints. So technicallly, the developer can still get away with not defining the foreign key, as long as the PK and UQ constraints are defined.

...

You should convert to inline table-valued functions. These, like views, are treated as macros - the reference to the function is first replaced by its definition, and only then will the optimizer be invoked to work out an execution plan. You'll see a major performance gain!


Hugo: Thanks so much for your posts, especially this one. First, RAP does generate functions and procedures for every foreign key; the particular table I chose for illustration purposes just doesn't happen to contain any foreign business keys - I chose it for the sake of brevity. If you look at the ExampleCRM tables that have foreign keys that are defined for business purposes (not just the status-field foreign key) you will see the function and procedure definitions.

Secondly, I like your code here. It never occurred to me to use a union. Are you sure that the union would execute faster than the "if" statement and the two simpler separate queries in RAP's generated code? I guess you are, so I am definitely going to check this out.
Post #1015888
Posted Thursday, November 4, 2010 9:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:36 PM
Points: 6,002, Visits: 8,267
David Ziffer (11/4/2010)
If you look at the ExampleCRM tables that have foreign keys that are defined for business purposes (not just the status-field foreign key) you will see the function and procedure definitions.

I have only read the articels; I did not download the full code. *IF* I can find some extra time, I'll check it out. Thanks for pointing out the obvious to me.

Are you sure that the union would execute faster than the "if" statement and the two simpler separate queries in RAP's generated code?

When used by itself? No, probably not.
But a query that references an inline table-valued function almost always runs rings around a query that uses a multi-statement table-valued function, for the reason I explained in my previous post. If you build the Person and PersonName tables you use in the last example in your article and populate them with a few thousand rows of sample data, you should see the difference. Bump the tables up to a few million rows, and it becomes painfully obvious.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1015959
Posted Thursday, November 4, 2010 10:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 12:02 PM
Points: 42, Visits: 152
  from   TBadmUser#
where UserId = @UserId
and AuditStatus <> 'D'
and A.AuditDate = (select max(AuditDate) from TBadmUser#
where UserId = A.UserId
and AuditDate <= @AsOf)
and @AsOf is not null
);

Several problems. 1, what is @AsOf is a future date? Your function returns nothing from live or archive table. 2, the select max audit date subquery does not specify "and AuditStatus <> 'D'", so it can return a date that will be excluded by the select statement. The first one is hypothetical, but the second one is a failure type error.

3, why select the max(AuditDate) in the subquery instead of selecting the primary key of the archive table, that seems like a better choice since then the top level select is selecting where PK = known value.

Post #1016042
Posted Thursday, November 4, 2010 7:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 12:02 PM
Points: 42, Visits: 152
wbrianwhite (11/4/2010)
  from   TBadmUser#
where UserId = @UserId
and AuditStatus <> 'D'
and A.AuditDate = (select max(AuditDate) from TBadmUser#
where UserId = A.UserId
and AuditDate <= @AsOf)
and @AsOf is not null
);

Several problems. 1, what is @AsOf is a future date? Your function returns nothing from live or archive table. 2, the select max audit date subquery does not specify "and AuditStatus <> 'D'", so it can return a date that will be excluded by the select statement. The first one is hypothetical, but the second one is a failure type error.

3, why select the max(AuditDate) in the subquery instead of selecting the primary key of the archive table, that seems like a better choice since then the top level select is selecting where PK = known value.




You could also find the archive row with just a left outer self join instead of a correlated subquery. Something like this (haven't tested it):
from TBadmUser# A with(nolock)
left join TBadmUser# B with(nolock)
on A.UserId = B.UserId
and A.UserId = @UserId
and A.AuditStatus <> 'D'
and B.AuditStatus <> 'D'
and B.AuditDate < A.AuditDate
where A.AuditDate <= @AsOf
and B.UserId is null

I've seen left outer self joins perform much better than correlated subqueries that use aggregated functions. Though they're a bit harder to read at first, once you're familiar with the pattern they're fine.
Post #1016352
Posted Thursday, November 4, 2010 8:13 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 2:24 PM
Points: 69, Visits: 182
wbrianwhite (11/4/2010)
[quote]wbrianwhite (11/4/2010)

Several problems. 1, what is @AsOf is a future date? Your function returns nothing from live or archive table. 2, the select max audit date subquery does not specify "and AuditStatus <> 'D'", so it can return a date that will be excluded by the select statement. The first one is hypothetical, but the second one is a failure type error.

3, why select the max(AuditDate) in the subquery instead of selecting the primary key of the archive table, that seems like a better choice since then the top level select is selecting where PK = known value.

You could also find the archive row with just a left outer self join instead of a correlated subquery. Something like this (haven't tested it) ... I've seen left outer self joins perform much better than correlated subqueries that use aggregated functions. Though they're a bit harder to read at first, once you're familiar with the pattern they're fine.

Brian: Several answers:

1. It doesn't make much sense to specify a future audit date, so I would not sacrifice any performance to generate code to specially handle that circumstance. But in any event this code can't possibly be sensitive to whether the date you specify is in the future, because the data in the database has no idea what time it is when you run the query, and furthermore the query is not sensitive to the current time. If you think carefully about the algorithm (or just try it out in the RAP example) you will find that putting in a future date will guarantee that you get the latest version of everything, keeping in mind that the latest version of a deleted record is "no record". But you'll get the latest version expensively, because you'll be going through the more calculation-laden archive table query rather than the ultra-simple current-table query.

2. Either you're expecting a different result than I am or your analysis is flawed. The subquery correctly returns whatever record is the most recent one (meeting the search criterion, in this case matching the correct key) that precedes or equals the AuditDate. If that record happens to be a "D" record then the main query correctly returns no record (which is what we want, because the record has been deleted). If that record is not a "D" record then it returns the most recent version of the record preceding or equalling the date. It is not our intention to return some record no matter what; if a record has been deleted prior to or at the AsOfDate, then we don't want to see it at all.

3. The left-join logic for extracting the maximum date looks interesting and I must say that I never considered such a thing. However I think you'd need to reverse the sign so that we're looking for the "A" record where there is no GREATER "B" record (I believe your logic as published would find the minimum "A", not the maximum that we want). But I would have to test this carefully to see how the "where" condition interacts with the join. And we'd have to change the test for "D" because your query here would neglect to properly return "no record" if the AsOfDate follows the deletion time of the record. And I would be very suspicious of the claimed performance improvement ... it seems to me that the number of records this join would produce would rise exponentially with the number of archived records matching the search criterion.

Thanks for your inputs. I am always grateful when people take the time to analyze my stuff. It's just that in this case I am unable to agree with your assessments (except possibly that a left-join could be used in place of the subquery). I would be interested in your responses to my responses and/or your clarifications, in case there's something here that I'm just not getting.
Post #1016363
Posted Thursday, November 4, 2010 8:19 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 2:24 PM
Points: 69, Visits: 182
Hugo Kornelis (11/4/2010)
[quote]David Ziffer (11/4/2010)
... a query that references an inline table-valued function almost always runs rings around a query that uses a multi-statement table-valued function, for the reason I explained in my previous post. If you build the Person and PersonName tables you use in the last example in your article and populate them with a few thousand rows of sample data, you should see the difference. Bump the tables up to a few million rows, and it becomes painfully obvious.

Well the thing that makes me suspicious here is that your "union" code potentially always hits both tables, whereas the RAP-generated code can hit only the one table that it needs to hit. In the "union" code you presented there is the danger that the optimizer would not be smart enough to realize that in the event of the @AsOfDate being NULL, it doesn't need to even touch the archive table at all.

But I think I will try your suggested test soon to see if your assertion holds true.
Post #1016367
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse