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 «««23456»»

Writing Nearly Codeless Apps: Part 4 Expand / Collapse
Author
Message
Posted Saturday, October 30, 2010 8:39 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
Jeff Moden (10/30/2010)

First, I whole-heartedly agree with the principle of storing the same AuditDate when adding a "group" of rows for the very reason stated. To wit, my question would be, why would you add a group of rows one row at a time instead of loading them as a complete set with a single insert?

I certainly would load rows into a single table as a group, but even those insertions must be done through individual calls to the RAP insertion procedure. So even though they are queued and streamed, they are still done with individual stored procedure calls. Even if they were all inserted with a single statement, it is not clear that a "datetime" performed for each row would produce an identical value in each row.

Then of course there is the fact that most writes to the database do not involve merely a single table. If I write a parent and child record together and I expect to retreive them together, they'd better have the same AuditDate on them.
Post #1013536
Posted Sunday, October 31, 2010 1:53 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:01 PM
Points: 76, Visits: 232
wbrianwhite (10/30/2010)
All of the other techniques mentioned can built into a generator


I have a hard time believing this. How would a code generator know enough about your application structure, data size, and database performance to 'decide' now is the time to use an indexed view? CTEs can sometimes be used for performance, if you know which indexes are out there and are analyzing query plans of different approaches for a proc. Otherwise they're useful for code maintainability, which wouldn't be an issue in generated code.


The same way humans do: recognize the pattern. For CTE's, the most useful use is in traversing hierarchies in adjacency list form. Easy enough to recognize.
Post #1013616
Posted Sunday, October 31, 2010 4:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 17, 2011 6:20 AM
Points: 8, Visits: 39
RobertYoung (10/31/2010)
wbrianwhite (10/30/2010)
All of the other techniques mentioned can built into a generator


I have a hard time believing this. How would a code generator know enough about your application structure, data size, and database performance to 'decide' now is the time to use an indexed view? CTEs can sometimes be used for performance, if you know which indexes are out there and are analyzing query plans of different approaches for a proc. Otherwise they're useful for code maintainability, which wouldn't be an issue in generated code.


The same way humans do: recognize the pattern. For CTE's, the most useful use is in traversing hierarchies in adjacency list form. Easy enough to recognize.


Robert is right on this one. That's why we focus on the database design, which also considers the ORM that we generate. We know that unique constraints, indexes, and foreign keys will all be seen by the ORM generator, and so optimizing the retrieval process. Like I said, we don't look for the ORM to do anything but generate the plumbing to get the data in and out of the database and into objects, nothing more. The trick with the ORM, and especially generated ORMs, is to know which ORM does what and every extent of their capabilites, developer productivity increase, etc. Expecting the ORM generator to do everything for you, I believe, is naive.
Post #1013642
Posted Sunday, October 31, 2010 10:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
David Ziffer (10/30/2010)
Even if they were all inserted with a single statement, it is not clear that a "datetime" performed for each row would produce an identical value in each row.


Datetime is only calculated once per query so no worries there.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1013668
Posted Sunday, October 31, 2010 11:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
David Ziffer (10/30/2010)
I certainly would load rows into a single table as a group, but even those insertions must be done through individual calls to the RAP insertion procedure.


I'm not trying to be a smart guy here. Just an opinion on my part. What you've stated above is part of the reason why I don't use such procedures. If you need to insert a "group" of rows, it just shouldn't be done one row at a time. As with anything else, "It Depends" but, comparatively speaking, it's much too resource intensive to insert groups of rows in a RBAR fashion. It would be really cool if your product knew how to receive and insert more than one row in a set based fashion.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1013669
Posted Monday, November 1, 2010 1:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:02 PM
Points: 6,133, Visits: 8,395
Jeff Moden (10/31/2010)
David Ziffer (10/30/2010)
I certainly would load rows into a single table as a group, but even those insertions must be done through individual calls to the RAP insertion procedure.


I'm not trying to be a smart guy here. Just an opinion on my part. What you've stated above is part of the reason why I don't use such procedures. If you need to insert a "group" of rows, it just shouldn't be done one row at a time. As with anything else, "It Depends" but, comparatively speaking, it's much too resource intensive to insert groups of rows in a RBAR fashion. It would be really cool if your product knew how to receive and insert more than one row in a set based fashion.


I'm not 100% sure, butI _think_ I have already mentioned that I'd prefer the logic for auditing and keeping history to be placed in a trigger instead of stored procedures. And Jeff's argument is one of the two major reasons why I favor that approach. (The other one being that a trigger is much harder to circumvent than a CRUD procedure).
And especially with a completely generated framework, there is (IMO) little reason NOT to choose for triggers. The development of a potentially complex trigger and the extensive testing is only required once.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1013696
Posted Monday, November 1, 2010 9:01 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
Jeff Moden (10/31/2010)
David Ziffer (10/30/2010)
I certainly would load rows into a single table as a group, but even those insertions must be done through individual calls to the RAP insertion procedure.

... If you need to insert a "group" of rows, it just shouldn't be done one row at a time. As with anything else, "It Depends" but, comparatively speaking, it's much too resource intensive to insert groups of rows in a RBAR fashion. It would be really cool if your product knew how to receive and insert more than one row in a set based fashion.

Jeff: I agree with you and I just haven't had the time how to figure out now to more efficiently do multiple-row insertions yet. Perhaps this will be a future improvement. For the moment, the single-row implementation does serve as an example of how to simultaneously update a "current" and "archive" table.

With regards to RBAR: it may be true that my current implementation of RAP does cause agony for the computer, which must handle insertions at the row level. However the "A" in RBAR suggests that there is perhaps a human who is in agony somewhere. One of the main points of RAP is that the programmer doesn't have to think about any of this stuff at all. At the application level, the programmer just says to RAP's ORM: "write whatever you have." The programmer doesn't think about rows or columns or even tables. In fact, the programmer doesn't have to care about implementation at all (unless of course RAP is too slow for him and we are forced to start programming for more efficiency).

My main purpose here is to illustrate what can be done from a functional perspective, if only we would all stop thinking at the component level and start implementing consistently at the system level. I do not presume that the algorithms I present here are the most efficient possible and I am happy for people's suggestions as to how I might improve RAP at any level - because I do want it to be used in real production systems.

So I thank you for your input and I will almost certainly look into this issue. But once again, the point here is to throw down some sort of prototype for doing things systematically.
Post #1013880
Posted Monday, November 1, 2010 11:29 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
RobertYoung (10/31/2010)
wbrianwhite (10/30/2010)
All of the other techniques mentioned can built into a generator


I have a hard time believing this. How would a code generator know enough about your application structure, data size, and database performance to 'decide' now is the time to use an indexed view? CTEs can sometimes be used for performance, if you know which indexes are out there and are analyzing query plans of different approaches for a proc. Otherwise they're useful for code maintainability, which wouldn't be an issue in generated code.


The same way humans do: recognize the pattern. For CTE's, the most useful use is in traversing hierarchies in adjacency list form. Easy enough to recognize.


Really? How?

I also use them for handling duplicate rows, and I've seen them used for server side paging as well.
Post #1013984
Posted Monday, November 1, 2010 12:11 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:01 PM
Points: 76, Visits: 232
wbrianwhite (11/1/2010)
RobertYoung (10/31/2010)
wbrianwhite (10/30/2010)
All of the other techniques mentioned can built into a generator


I have a hard time believing this. How would a code generator know enough about your application structure, data size, and database performance to 'decide' now is the time to use an indexed view? CTEs can sometimes be used for performance, if you know which indexes are out there and are analyzing query plans of different approaches for a proc. Otherwise they're useful for code maintainability, which wouldn't be an issue in generated code.


The same way humans do: recognize the pattern. For CTE's, the most useful use is in traversing hierarchies in adjacency list form. Easy enough to recognize.


Really? How?

I also use them for handling duplicate rows, and I've seen them used for server side paging as well.


If you mean CTE: http://blog.crowe.co.nz/blog/archive/2007/09/06/Microsoft-SQL-Server-2005---CTE-Example-of-a-simple.aspx
Post #1014018
Posted Monday, November 1, 2010 7:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
David Ziffer (11/1/2010)
Jeff Moden (10/31/2010)
David Ziffer (10/30/2010)
I certainly would load rows into a single table as a group, but even those insertions must be done through individual calls to the RAP insertion procedure.

... If you need to insert a "group" of rows, it just shouldn't be done one row at a time. As with anything else, "It Depends" but, comparatively speaking, it's much too resource intensive to insert groups of rows in a RBAR fashion. It would be really cool if your product knew how to receive and insert more than one row in a set based fashion.

Jeff: I agree with you and I just haven't had the time how to figure out now to more efficiently do multiple-row insertions yet. Perhaps this will be a future improvement. For the moment, the single-row implementation does serve as an example of how to simultaneously update a "current" and "archive" table.

With regards to RBAR: it may be true that my current implementation of RAP does cause agony for the computer, which must handle insertions at the row level. However the "A" in RBAR suggests that there is perhaps a human who is in agony somewhere. One of the main points of RAP is that the programmer doesn't have to think about any of this stuff at all. At the application level, the programmer just says to RAP's ORM: "write whatever you have." The programmer doesn't think about rows or columns or even tables. In fact, the programmer doesn't have to care about implementation at all (unless of course RAP is too slow for him and we are forced to start programming for more efficiency).

My main purpose here is to illustrate what can be done from a functional perspective, if only we would all stop thinking at the component level and start implementing consistently at the system level. I do not presume that the algorithms I present here are the most efficient possible and I am happy for people's suggestions as to how I might improve RAP at any level - because I do want it to be used in real production systems.

So I thank you for your input and I will almost certainly look into this issue. But once again, the point here is to throw down some sort of prototype for doing things systematically.


Heh... there're a couple of dozen people on this site alone who are capable of pulling off huge multi-row inserts from a GUI via a stored procedure. If you'd care to throw some $$$ in their direction, I imagine they'd be happy to write the code for you.

So far as the rest of it goes, I understand that your goal is very well intentioned and making it so developers don't actually need to think about such drudgery as writing insert code. But that's not a real change because a lot of them don't think of it as it is. Your product is an improvement for them because you've perpetuated the notion that a developer doesn't actually have to think about the harm they may be causing to the database, the underlying server, or even the "pipe". You've also given them something to blame because you've also removed the responsibility.

What would really be cool is if a product like this actually did something better than a developer... like figuring out the proper way to insert groups of rows. My apologies for how blunt that sounds but it would nice.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1014233
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse