:) Ah yes, I see there a few people still left who think sprocs have some consequential performance benefit over dynamic SQL. The myth lives on. This is not a quick read, but it will help to disspell one of the most pervasive myths I've ever seen over data-access.
When it comes to performance, so few people actually run their own tests and just take the word of others as gospel...
Frans vs. Rob thread.
Ah yes, the fabled debate between Frans and Rob. I believe the final word was: http://weblogs.asp.net/rhoward/archive/2003/11/18/38446.aspx "If the ad-hoc sql is a parameterized statement, then the perf will generally be similar. Otherwise the folks are incorrect
- especially if the statement does anything complex such as a join, a sub-query, or anything interesting in the where clause AND the statement happens more than once. This is to say nothing about other DBA issues such as securing objects, maintenance, abstraction,
and reducing network traffic, troubleshooting, etc." So, only one case, parameterized SQL, where this is the case. Regardless, let's get back to the "black box" theory - in the world of O/R mappers, how does one customize the code that's output? To me, that's
key. -Jason
Jason L. Alexander
Chief Solutions Architect, »Telligent Systems ASP.NET MVP, ASPInsider, MCAD
> Where I do NOT agree is that most applications don't need a "dynamic" flexible query engine. You misunderstand what I was trying to get across. Let me clarify. I have never come across an application that did not need flexible queries. That is an absolute
must, particularly on applications such as ours that server up record sets sometimes 70k rows at a time. But we can serve that need in one or few sprocs designed to meet that need for a particular part of the app (UI). But considering the total collection
of the sprocs that support the application, those needed to support flexible queries represent a small percentage. The bulk of the queries made in the application are not user queries, but rather system supportive calls - ones in which we have a single specific
id for an object or a specific fk id, etc). These calls may be used to pull a single record for displaying the detail of an object or for pulling all of the, lets say CompanyContact's for a Company. All of which are conditions for which the generator can understand
at design time and write solid concrete methods and sprocs. Our templates also generate the sprocs we use for flexible filtering / sorting. But because we control those templates we are able to constantly optimize and hone those templates/sprocs to improve
performance and meet our needs. And if our data model is forced to go in a different direction because of client requirements, we are able to adapt very quickly and create a new data access architecture that is the best optimized for that data model. > So
I no longer see much, if any, "cost" associated with runtime mapping vs. other methods. > Also, note that there has been a lot of discussion on these forums and in the various blogs > about the old belief that stored procs perform better due to pre-compilation
and caching As for neglible performance hit I have no numbers to adequately debate the subject. I speak only based on the simple notion that work done now is work that doesn't have to be done later. Due to the scale of our applications, we've found that it
is prudent to take every possible step to improve performance, no matter how small the benefit. Code Generation allows us to make those improvements without a major undertaking of recoding and without creating a rat's nest of code. Also, on dyn-sql vs sprocs
perf I don't have data necessary to debate that and as I conceded before I know that dyn-sql is super improved in recent releases of MSSQL (particularly with the addition of parameterized queries), and may even perform as well as sprocs if the queries are
appropriately static (allowing caching). Of course if they are so static, it seems dynamic sql isn't particularly necessary. Stored Procs work best for us due to code isolation, readability, version control, error handling, easy "hot-fix" deployment (single
sproc deployment is less risky than a .Net app dll push), and ease of generation. If well written dyn-sql queries now run as well as sprocs, I can accept that, tho I'd find it hard to believe they perform better. Simply put, the code generation approach gives
us what we feel is the most flexibility, room to grow, and performance. If an O/R mapper meets all the needs of a particular development effort, then it is a certainly a good and viable option. For our needs they fell a bit short, and we weren't afforded the
access to improve or modify the "engine".
Oh and Gabe, not looking to start a shouting match, but Frans hardly convinced me. http://weblogs.asp.net/rhoward/archive/2003/11/18/38446.aspx edit: well, strike that...I see *some* of his points, but I'm unconvinced of the advantage of dyn sql over sprocs
for our use.
Wow... looks like we opened a hornets nest here. This is EXACTLY the reason that I think template based code generation is the best way to go. Go figure, it seems that people really like having control of their own architecture, flexibility to do things
their way and not being locked into someone else's idea of "the right way". As we all know, there are certainly some BIG egos out there that think they know everything and that their way is "the right way", but in my experiences I learn more and more every
day and the more people I work with I tend to learn more and more and with that new knowledge I might want to change the way I do some things. It sure is nice to have the flexibility to do so. If I were to go with an O/R mapper those decisions wouldn't be
left up to me. I would be stuck with a pretty black box that I can't open and IMO that sucks! Paul, you seem to refer to code generation as if it were only a slight step above manual coding? I think you may be thinking about passive generation where the generated
code is used merely as a keystroke saver and not as part of your architecture/build process. In my use of code generation my templates are my source code. I do not modify the generated code and the code is re-generated as part of my build process. If I make
a change to the template it is reflected throughout my application as soon as I rebuild. This is called active generation and I think that we should all strive to use code generation this way as much as possible. There are some situations where that might
not be possible like generating presentation layers. In most cases this would be generated passively as a kick start and then hand modified because of the very customized nature of presentation layers. Also, CodeSmith has the ability to preserve custom code
within source files so that you can have the best of both worlds. You can still actively re-generate these classes, but still have the ability to add custom logic to them. I think this is extremely powerful.
Eric J. Smith
Chief Software Architect
CodeSmith Tools, LLC
http://www.codesmithtools.com
<< I think you may be thinking about passive generation where the generated code is used merely as a keystroke saver and not as part of your architecture/build process. >> Ever heard of CASE tools? I have first-hand knowledge of multi-million dollar projects
that ended up as failures because managment bought the siren call of faster-development through code generation tools. I recall one in particular that was saved only because a very intelligent and persistant developer (who later became an architect) kept showing
management and senior engineers the facts that the generated code would never work for what they needed. After millions lost he finally convinced them to pull-out the generator and the project was implemented, almost 3 years late. I've never been able to pin-point
why, but sometimes code-generation work for me, but other times its obviously the devil. I will note one pattern, any code-generator I wrote never lives long. They may save me a bunch of work for a specific task one time, and I may even try to design them
with reuse in mind, but they always end up on shelf. Perhaps this because the technology de jour keeps changing and its too much work to constantly maintain a generator. They are throw-away tools.
You seem to refer to specialized code generation tools that have little to no flexibility. I personally have no use for these kind of tools. I refer to these tools as magic button generators. You press a button and *poof* out comes the magic code. The type
of code generation I'm referring to is template based code generation where the output of the templates is up to the template developer and the only reason that template based code generation would fail is because the template author failed. CodeSmith is a
platform for code generation, it's up to the developer to use it correctly and make it work for them. Also, CodeSmith does not target any one specific language. It can output any ASCII based language and the templates themselves can be written in C#, VB.NET
or JScript. I personally don't see .NET going away any time soon so I'm not too worried about "technology de jour".
Eric J. Smith
Chief Software Architect
CodeSmith Tools, LLC
http://www.codesmithtools.com
For the record, if anyone bothered to look at the templates we wrote, you would see that we use dynamic sql inside of a stored procedure. We use sp_executesql and parameterize the query as well. As Eric mentioned active generation is a concept alot of people
aren't familiar with. In our templates that I provided a link to; we demonstrate the use of active generation as well as passive. I have blogged about it here (http://weblogs.asp.net/jgonzalez/archive/2004/01/15/59115.aspx) We implemented what we like to call
"Poor man's partial classes" We have a Generated namespace and we write all of our actively generated code into this folder/namespace. CodeSmith also provides a mechanism for you to write code directly into regions via merge strategies. I want my code to look
the way I want it to look. I want my code to work the way I want it to work. I believe that EntityBroker and LLBLGen are solid products, but they are under the control of someone other than me. I am at their mercy to optimize performance or to trust them to
get everything correct. Those products are simply not powerful enough when compared to CodeSmith. I can use CodeSmith for damn near anything that I do. If I can write it in ASCII, guess what... so can CodeSmith. I don't want to turn this into a reglious war
over what is better, but I would like to put Thomas and Frans' argument that their products/their thinking is the only way to go to bed. Clearly CodeSmith is an alternative. To say anything contrary is illiberal. I would like to reiterate that CodeSmith isn't
the ONLY solution, but its the best i've seen.
CodeSmith does require competency, O/R Mappers do not necessarily. I could plug an O/R Mapper and not understand exactly what its doing. It could seem like "magic" to an unskilled developer. With CodeSmith you can gradually build it into your architecture.
I think the whole Code Generation == Evil is pure nonsense. First Generation code generators don't last long, those are magic button, black box code generators. The code for generation is inside the executing application. Second generation code generators
are like moving the template code to an external file, and replacing @ codes in the file. While it will get the job done, it is a pain to maintain, not nowhere near as dynamic as an environment like CodeSmith. CodeSmith actually provides an interface for retrieving
schema information from the database and allows you to bind your template to your metadata for the generated output. This is what is usually referred to as a Third Generation code generator.
:: You can still actively re-generate these classes, but still have the ability to add custom logic to them. I think this is extremely powerful. AND :: I do not modify the generated code and the code is re-generated as part of my build process. do you now modify
it or not? :-) anyway, Like thona said, i think your generated code pieces are seperated classes. with no context or relations. what about trying to 'address' the Address of Client like this. _Client.Address ? and Quering for Clients with streetnames like
"Text%" ? with your option you need some more lines of code to get this working. and dynamically generate SQL is in this case just more powerfull (or you need to generate a SP for every situation/field). good O/R-mappers have also a nice caching mechanism,
inheritance features and brings all or your classes in 'relation' when needed. In the past I tried codesmith code-generation in combination of dotnetnuke. it IS just a keystroke saver. maybe you can build some O/R-mapper above it, and use it to dynamically
generate the SQL :-) I would say, download a trial version of EntityBroker or llblgen Pro.
Bluemagics Weblog: "I'll always make your dark sky blue!"
Gabe Halsmer
Participant
880 Points
176 Posts
Re: Your favorite O/R Mapper?
Aug 10, 2004 03:40 AM|LINK
jlalexander
Participant
810 Points
162 Posts
ASPInsiders
Re: Your favorite O/R Mapper?
Aug 10, 2004 03:59 AM|LINK
Chief Solutions Architect, »Telligent Systems ASP.NET MVP, ASPInsider, MCAD
p00k
Member
90 Points
18 Posts
Re: Your favorite O/R Mapper?
Aug 10, 2004 04:15 AM|LINK
p00k
Member
90 Points
18 Posts
Re: Your favorite O/R Mapper?
Aug 10, 2004 04:27 AM|LINK
ejsmith
Member
79 Points
17 Posts
ASPInsiders
Re: Your favorite O/R Mapper?
Aug 10, 2004 04:58 AM|LINK
Chief Software Architect
CodeSmith Tools, LLC
http://www.codesmithtools.com
Gabe Halsmer
Participant
880 Points
176 Posts
Re: Your favorite O/R Mapper?
Aug 10, 2004 05:19 AM|LINK
ejsmith
Member
79 Points
17 Posts
ASPInsiders
Re: Your favorite O/R Mapper?
Aug 10, 2004 05:44 AM|LINK
Chief Software Architect
CodeSmith Tools, LLC
http://www.codesmithtools.com
likwid
Member
280 Points
56 Posts
Re: Your favorite O/R Mapper?
Aug 10, 2004 06:21 AM|LINK
likwid
Member
280 Points
56 Posts
Re: Your favorite O/R Mapper?
Aug 10, 2004 06:42 AM|LINK
Bluemagics
Participant
1955 Points
391 Posts
Re: Your favorite O/R Mapper?
Aug 10, 2004 09:32 AM|LINK