Jack Corbett (8/4/2009)
You could have opened a big can of worms here.
Why do you think I'm asking this on one of the biggest SQLServer sites around populated with of the most experienced DBA people that I know of? 🙂
I am a huge proponent of doing data access and manipulation through stored procedures and many ORM tools make this difficult.
Which is why I'm "rolling my own" O/RM system.
The positives of ORM tools from the developer side is you don't have to write a lot of SQL and the classes are built for you so you should get faster initial development.
For this project, meh.
The negatives are that the ORM tools usually don't provide the most performant SQL and make it hard to use stored procedures. You also have to change the compiled app to address many performance problems. You can't make changes to the database without making changes to the application. If you use stored procedures or views as an API (see today's editorial[/url]) to your database then you can performance tune the SQL more easily and you can make schema changes without affecting the application.
This is a time where I can get the architecture for new developments going in a direction that doesn't suck. Please see the bottom of this post, after the quotes, for architecture questions.
Just my 2¢ worth.
Appreciated, thanks.
Grant Fritchey (8/5/2009)
But, properly applied, working with the developers to arrive at a good relational desgin, monitoring development so that you can spot when a particular process needed a stored procedure rather than generated code, ORM tools could be a god-send for fast & accurate delivery of well structured systems. But I think that this is the exception and not the rule.
See my reply to todd.mcdaniel below.
Steve Jones - Editor (8/5/2009)
but we now have some performance issues, and it is incredibly hard at times to change things.
Why, don't you have experienced DBA's on board? Have you considered outsourcing? 😉
To me, the initial developer of the app wasn't worth the issues we now have.
That's why I'm "rolling my own" O/RM system. The existing systems just weren't a good enough fit for our system, and I want to use SPs to update/insert records, especially because we have some business rules that need to be implemented when certain things are inserted into the database.
todd.mcdaniel (8/6/2009)
It sounds like you are possibly an application developer and not a DBA. It also sounds like the application development group may drive the database design. This is the biggest no-no in the development world.Database design left to application developers will almost certainly have flaws and often large ones and these flaws will manifest themselves in time. Sometimes on day one when the application actually goes live but more often down the road when more users are added or more data builds up in the database.
Not only am I the Lead Developer, Lead Programmer, Senior User Interface Designer and Lead Architect, but I am also the the DBA-in-Chief (I like the term "DBA-by-accident" that I learnt here 🙂 I recognise your concerns, that's why I've spent a lot of time hanging around SQLSS and trying to sharpen my database skills quite a bit in the last few months.
If you do not have a dedicated and qualified DBA I would suggest you bring this concern to management. If you do, congratulations and use that person wisely.
We're understaffed as it is, so...
Good luck.
Thanks.
aloj (8/6/2009)
I am not a DBA and I speak from experiences as a developer. Most ORM tools support Stored Procedures. Sometimes you might need to tweak something. For example, Entity Framework (at least in current reincarnation) will not work with Insert Stored Procedures that return newly inserted record ID as an output parameter. So, you might be forced to change your SP generator to return that ID as a recordset (Select SCOPE_IDENTYTY() AS ID).
This is a legacy database, what is this strange "SCOPE_IDENTYTY()" that you speak of? 🙂 When I got here three years ago there were no keys. None. The only index was a UNIQUE index on the history table. I'm not allowed to modify some aspects of the database because those changes (e.g. adding fields) would break the VB6 app and we don't have enough resources right now to make a "significant breaking change" to the VB6 app. The work I've done I've tried to do to the best of my ability, both code- and database-wise with keys, ID fields, indices and stored procedures.
It was easy to see that page does not load right (even in development). It's just that people are rushed to write and check in code and nobody cares about consequences because the deadline has to be met (who cares about slow database under such circumstances) and only after things go to production and start causing trouble or make application unusable for end users we send escalation team to fix an issue.
Use a database loaded full of test data during development! I try and use at least 200 000 rows or so, preferably more. Thanks for teaching me this SQLSS, I picked up some problems in the database by doing this.
----------
Regarding the data objects interfacing with the database, I want see is objects reading and writing via stored procedures. I feel that the coding of the SPs is really sped up by using SSMS's "Script Table As -> SELECT to", as well as avoiding "Select * from". This is not for performance, but because I am trying to build a good API into the database. I discovered this about two years ago on a different project. Also, it will hopefully make things easier to learn if/when a new guy joins us.
The database architecture, well, I need a professional DBA to look at that and sit with me so that we can get things going in a decent direction. Until that gets approved, I guess I'm just going to soldier on.