April 29, 2011 at 2:47 pm
I've inherited a database which is far from consistent in it's naming conventions (as well as normalization, but thats another topic).
We have the methods of data access in a now 3.5 C# web app:
1. Random dynamically created inline SQL (trying to phase this out)
2. Entity Spaces for 2.0. Phasing this out, but this is currently the majority of the application.
3. Entity Framework. Phasing this in.
The database is underusing the hardware... never above half the ram on the box (128gb), rare spikes in CPU... disk io and network io are the largest bottlenecks and I can't even say this is currently much of an issue.
So that said, the only thing holding me back from doing some basic refactoring is that we have generated code in production using many of our tables directly.
I'm having the idea that at the very least I could create a new table as I like with great consistent names, dropping columns not used and put up a view with the same name that the object used to hold, with the same bad naming and bogus unused columns.
This would give us some breathing room to change all the internal SQL to use the real table name and phase out the badly named code. It would also provide and easy way to remap columns.
I could then let developers know that they need to update their code pointing to dbo. (yes, I'm serious) to say dbo.ApplicationUser or Users or a field called Profency to Proficiency.
I could likely do some basic type correction as well.
The main limitations I'm seeing that might be an issue:
1. I can't refactor tables too much, because splitting one table into three I can use a View to abstract selections, but not updates. You can't update two tables in the same statement.
2. Extra overhead? Some people here think Views are really bad for performance. I think they can be used poorly, but can be a handy tool.
3. I'm not sure how the relationships will show in EF or to what extent the developers are using them. If they're using dbo. with 10 FKs and I swap it with a view, I'm not sure that the view will expose those relationships.
I definitely wouldn't want to end up with a view and table for everything, but I think it might make a decent segway into introducing refactorings without pulling out the rug on the old object.
Is this a valid refactoring pattern? Anti-pattern? I didn't find much looking this up in Google. I think when I put View and performance or Optimization into a statement I just get Indexed Views.
I really appreciate any feedback you have!
April 29, 2011 at 3:20 pm
It's a common method to workaround dealing with bad design methods when inherited. I would recommend you do it in smaller chunks, however, working with the design teams towards proc usage for your updates and the like (allowing for avoiding the entire view problem entirely) first. Then use views as select windows to avoid the underlying schema, allowing you to slowly work your way through the problem, affecting the procs and the like as you go under the hood.
First step should be moving all writes to CRUD procs.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 29, 2011 at 4:25 pm
Well I'm a DBA first, C# developer second, web developer third, so I fully agree with a preference toward a centralized CRUD for immediate performance and centralization of logic.
My Director of Software believes otherwise and wants to move to the Best Practices route of having all of the logic in a fully abstracted data layer. I understand the reasons why he's doing it, they're trying to keep the database as light as possible.
Good to hear that it doesn't sound like using a view to abstract things will just immediately be awful. I might even be slick and do a good portion of it without their knowledge and then one day let them know to use a different EF object. I think the only way anyone would notice is if they were trying to look up the table on the Table Object Explorer. Nobody really uses SSMS very often and I'm the only person who deals with lower level things like system views/functions.
I talked to a developer, they have relationships turned off on EF because it was lazy loading insane amounts of data unnecessarily... Well that helps me with that aspect.
April 29, 2011 at 4:51 pm
STherrien-978820 (4/29/2011)
Well I'm a DBA first, C# developer second, web developer third, so I fully agree with a preference toward a centralized CRUD for immediate performance and centralization of logic.My Director of Software believes otherwise and wants to move to the Best Practices route of having all of the logic in a fully abstracted data layer. I understand the reasons why he's doing it, they're trying to keep the database as light as possible.
It's called ORM. Check out any number of threads on something like nHibernate to see a number of reasons why you want to run away screaming. A db tightly coupled to the ORM/Ntier layers is a performance nightmare, and a waste of a perfectly good DB engine. MySQL is free, and is just as useful a datastore. The database SHOULD have load, and a lot of it. That's its purpose in life. Anything that needs to be done one row at a time should be up in the app tier. Anything that's multi-row is DB tier. A lot of what's percieved as row iterations is actually still multi-row and DB tier.
Good to hear that it doesn't sound like using a view to abstract things will just immediately be awful. I might even be slick and do a good portion of it without their knowledge and then one day let them know to use a different EF object.
Don't do this. One unexpected error with a boss that is as stubborn as it appears and you're going to be on the firing line.
I think the only way anyone would notice is if they were trying to look up the table on the Table Object Explorer. Nobody really uses SSMS very often and I'm the only person who deals with lower level things like system views/functions.
I talked to a developer, they have relationships turned off on EF because it was lazy loading insane amounts of data unnecessarily... Well that helps me with that aspect.
I wish you luck. You're looking at a major change in thinking where you're at.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 30, 2011 at 8:04 am
STherrien-978820 (4/29/2011)
The main limitations I'm seeing that might be an issue:1. I can't refactor tables too much, because splitting one table into three I can use a View to abstract selections, but not updates. You can't update two tables in the same statement.
True...BUT you can get round this using an INSTEAD OF trigger. I've done this with great success in the past.
STherrien-978820 (4/29/2011)
2. Extra overhead? Some people here think Views are really bad for performance. I think they can be used poorly, but can be a handy tool.
Views directly onto a table do nothing to performance provided they dont include logic. It's common practice at my current company to have logical DB's with views and stored procedures. The views reference the "actual" database and provide a degree of decoupling for maintance and dba work etc.
STherrien-978820 (4/29/2011)
3. I'm not sure how the relationships will show in EF or to what extent the developers are using them. If they're using dbo. with 10 FKs and I swap it with a view, I'm not sure that the view will expose those relationships.
The relationships will still be enforced provided you retain them during the refactoring.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply