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.