November 1, 2010 at 1:46 am
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.
November 1, 2010 at 9:01 am
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.
November 1, 2010 at 11:29 am
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.
November 1, 2010 at 12:11 pm
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
November 1, 2010 at 7:08 pm
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
Change is inevitable... Change for the better is not.
November 1, 2010 at 7:31 pm
Jeff Moden (11/1/2010)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.
Thank you. RAP seems to have a procedural programmer's approach to a database currently. A database is treated as just a fancy version of storing things in a series of individual files on the web server. An insert of a group of rows is a single insert and should be treated as such to take advantage of the atomicity of transactions. Otherwise you need to recreate transactions in the app layer, which will simply never be as good. If for no other reason, that you could spend minutes inserting your rows before you get a failure 8500 records in, and by the time you go to delete all those rows someone has already reported on the data that was sitting in the database in a committed status. Heck, someone might have already done something to the data creating a FK to it, making it impossible to back out when you realize you need to.
Perhaps RAP could accept a large xml string, and insert into tables in a set based way from that. I don't know how well that can be automated though. It's a fairly easy technique for passing in sets of data from a GUI as a set, even parent-child relationships.
November 1, 2010 at 7:34 pm
RobertYoung (11/1/2010)
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%5B/quote%5D
Yes... I'm quite familiar with CTEs. I am saying 'how will a generator recognize they should be used'? CRUD will never be aware of nested relationships or recursive self-joins. It just handles inserting one row, retrieving one row, etc.
November 1, 2010 at 7:51 pm
wbrianwhite (11/1/2010)
Jeff Moden (11/1/2010)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.
Thank you. RAP seems to have a procedural programmer's approach to a database currently. A database is treated as just a fancy version of storing things in a series of individual files on the web server. An insert of a group of rows is a single insert and should be treated as such to take advantage of the atomicity of transactions. Otherwise you need to recreate transactions in the app layer, which will simply never be as good. If for no other reason, that you could spend minutes inserting your rows before you get a failure 8500 records in, and by the time you go to delete all those rows someone has already reported on the data that was sitting in the database in a committed status. Heck, someone might have already done something to the data creating a FK to it, making it impossible to back out when you realize you need to.
Perhaps RAP could accept a large xml string, and insert into tables in a set based way from that. I don't know how well that can be automated though. It's a fairly easy technique for passing in sets of data from a GUI as a set, even parent-child relationships.
Hi Brian... I know who David Ziffer is... he wrote the article. I've probably missed it in one of your posts on this thread but who are you, what is your relationship to RAP, and why are you answering for David Ziffer? No insult meant especially since it was a well put post above... I just want to know.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 1, 2010 at 8:42 pm
wbrianwhite (11/1/2010)
RobertYoung (11/1/2010)
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%5B/quote%5D
Yes... I'm quite familiar with CTEs. I am saying 'how will a generator recognize they should be used'? CRUD will never be aware of nested relationships or recursive self-joins. It just handles inserting one row, retrieving one row, etc.
The generator merely needs to know what the human knows: if a table contains SelfId and ParentId (or ChildId), then the table is, by definition, an adjacency list; and therefore is managed with a CTE process. Yes, the developer and the generator have to agree on what column names constitute Id; but the humans do too.
November 2, 2010 at 10:40 am
Jeff Moden (11/1/2010)
Hi Brian... I know who David Ziffer is... he wrote the article. I've probably missed it in one of your posts on this thread but who are you, what is your relationship to RAP, and why are you answering for David Ziffer? No insult meant especially since it was a well put post above... I just want to know.
I wasn't answering for him, I was just thanking you for expressing my problem with RAP so succinctly.
November 2, 2010 at 10:50 am
wbrianwhite (11/2/2010)
Jeff Moden (11/1/2010)
Hi Brian... I know who David Ziffer is... he wrote the article. I've probably missed it in one of your posts on this thread but who are you, what is your relationship to RAP, and why are you answering for David Ziffer? No insult meant especially since it was a well put post above... I just want to know.
I wasn't answering for him, I was just thanking you for expressing my problem with RAP so succinctly.
Ah... got it. Thanks for the feedback, Brian.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 3, 2010 at 5:15 pm
Am I the only one using Microsoft's Entity Data Model (EDM) technology as an ORM for modeling as well as for the data access layer? This is a part of their new Entity Framework model introduced in .NET 3.5 SP1.
I can tell you that for years we have both written our own code generation utilities as well as implemented 3rd party variations - and the MS version is by far the most superior from many perspectives.
I highly encourage all of you SQL folks to start becoming very familiar with this, if you are not already.
On a final note, while I don't agree with auto-generating databases, I do agree with standardizing on table structures with basic things like a primary keys, status fields and naming standards.
RAP will not take foothold. Microsoft Entity Framework, however, has the full backing of Microsoft's neverending resources at its disposal 🙂
November 4, 2010 at 4:52 am
jyurich (11/3/2010)
Am I the only one using Microsoft's Entity Data Model (EDM) technology as an ORM for modeling as well as for the data access layer? This is a part of their new Entity Framework model introduced in .NET 3.5 SP1.I can tell you that for years we have both written our own code generation utilities as well as implemented 3rd party variations - and the MS version is by far the most superior from many perspectives.
I highly encourage all of you SQL folks to start becoming very familiar with this, if you are not already.
On a final note, while I don't agree with auto-generating databases, I do agree with standardizing on table structures with basic things like a primary keys, status fields and naming standards.
RAP will not take foothold. Microsoft Entity Framework, however, has the full backing of Microsoft's neverending resources at its disposal 🙂
I think that everyone is hopeful for the EF to be the main ORM for .NET developers. However, compared to several of the other ORMs, it has not matured enough to compete. Other ORM packages also provide different bells/whistles that otherwise would have needed to be completely written from scratch using L2S or EF. Now that can be debated ad nauseam, but I think the bottom line is for developers to feel comfortable with it. I think after another year or so, it will be used more widely.
November 4, 2010 at 8:08 am
jyurich (11/3/2010)
Am I the only one using Microsoft's Entity Data Model (EDM) technology as an ORM for modeling as well as for the data access layer? This is a part of their new Entity Framework model introduced in .NET 3.5 SP1.I can tell you that for years we have both written our own code generation utilities as well as implemented 3rd party variations - and the MS version is by far the most superior from many perspectives.
I highly encourage all of you SQL folks to start becoming very familiar with this, if you are not already.
On a final note, while I don't agree with auto-generating databases, I do agree with standardizing on table structures with basic things like a primary keys, status fields and naming standards.
RAP will not take foothold. Microsoft Entity Framework, however, has the full backing of Microsoft's neverending resources at its disposal 🙂
From the beginner's guide from the URL: "In fact, I've never before written a line of SQL, yet I was able to build a rich web application thanks to an ORM."
That scares the bejeezus out of me.
"Whereas one database may require a String, another may require that it be called a VarChar. When using the EF, this minor annoyance is abstracted by the database provider."
Right. Because companies are always switching out minor, unimportant stuff like their database vendor. What a useless feature. If a company is really switching from Windows+SQL Server they are probably switching to Linux + MySQL or Sun + Oracle. Neither of which would be in any way supported. Abstracting away database column types is like abstracting away the datatype of a variable. Would it be better for an application developer or worse to only have "number" instead of Int32, Int64, Float, etc. Worse. So why would it be better for a database developer? It is REALLY important to specify the right string datatype. If your ORM creates varchar and you need to support japanese characters that were only supported in Nvarchar, what do you do then?
"Kamran
22 Jul 2010 5:12 PM
It's important to note that it's still important to know SQL so you understand what is happening behind-the-scenes. If you don't, just as in LINQ-to-SQL, you could really mess up your app with unoptimized code.
It's so hard to make things like this easy for a beginner yet introduce them to important concepts like concurrency and optimized queries."
Exactly. What is EF doing when you save? When you read? What isolation level is used for reading? How can you tweak that for some cases where dirty read is allowable, and other cases where it's not? How do you begin/commit a transaction from entity framework code? How do you add error handling in the sql if the transaction failed?
"Moni
29 Sep 2010 3:23 AM
...
The whole premise of "ORMs help developers be more efficient and focused, since they don't need to spend brain cycles thinking about how to communicate with the database." is fundamentally flawed and incorrect. If you make an app you have to think about scalability and performance. In order to be aware of those issues you need to know about the SQL queries that are generated via EF and the underlying architecture. If you ignore that aspect then you'll have a serious problem sometime further in the development cycle.
...."
Again, agree completely. None of that matters in small apps, but small app shops don't tend to employ many SQLServerCentral readers.
November 4, 2010 at 8:27 am
wbrianwhite (11/4/2010)
jyurich (11/3/2010)
Am I the only one using Microsoft's Entity Data Model (EDM) technology as an ORM for modeling as well as for the data access layer? This is a part of their new Entity Framework model introduced in .NET 3.5 SP1.I can tell you that for years we have both written our own code generation utilities as well as implemented 3rd party variations - and the MS version is by far the most superior from many perspectives.
I highly encourage all of you SQL folks to start becoming very familiar with this, if you are not already.
On a final note, while I don't agree with auto-generating databases, I do agree with standardizing on table structures with basic things like a primary keys, status fields and naming standards.
RAP will not take foothold. Microsoft Entity Framework, however, has the full backing of Microsoft's neverending resources at its disposal 🙂
From the beginner's guide from the URL: "In fact, I've never before written a line of SQL, yet I was able to build a rich web application thanks to an ORM."
That scares the bejeezus out of me.
Again, agree completely. None of that matters in small apps, but small app shops don't tend to employ many SQLServerCentral readers.
The notion that anybody off the street can specify databases, yet wouldn't be allowed to code XYZ module without 10 years of experience with XYZ, puzzled me for quite a while. I ultimately came to the conclusion that coders, 1) have no real clue about data and 2) are terminally arrogant.
Small apps, if any good, tends to become a Big App in due time. By then, it's "too much trouble" to clean up the mess. Spolsky still advocates (last I looked, anyway) Big Design Up Front; be optimistic that your small app will grow up to be a Big App and design accordingly. Think about the catalog first, don't just do a ByteDump and let the client code blot the ACID.
Viewing 15 posts - 46 through 59 (of 59 total)
You must be logged in to reply to this topic. Login to reply