March 11, 2016 at 3:14 am
My company has adopted a new way of stored proc creation. Because the developers are mostly responsible for creating their stored procs, it was decided they have to use a generator to generate stored procs based on the table structure and columns.
This auto generator will create a proc parameter for each column, as well as an OR clause for each column. If it is a SELECT proc, it will automatically SELECT all columns, similar for INSERT and UPDATE statements.
For example:
TableA has columns: Col1 INT, Col2 VARCHAR (10), Col3 BIT DEFAULT 0
The proc will then be generated as follows:
CREATE PROC procName @Col1 INT = NULL, @Col2 VARCHAR (10) = NULL, @Col3 BIT = NULL
AS
SELECT Col1, Col2, Col3
FROM TableA
WHERE
(@Col1 IS NULL OR @Col1 = Col1)
AND
(@Col2 IS NULL OR @Col2 = Col3)
AND
(@Col3 IS NULL OR @Col2 = Col3)
The above may not seem too bad but what about tables with numerous columns (up to 50 or more). The procs will then have 50 parameters and 50 OR statements, and with this I have a big problem.
Yes the developers were told to remove unnecessary columns from the auto generated code, but they don’t do that.
What are your thoughts on this?
I don’t like these generic-like procs but I am having a hard time convincing them of this. They need to know Why, and I just know from experience on large amounts of data that multiple OR Statements like this makes execution really slow….
March 11, 2016 at 3:29 am
You're going to have terrible performance, even if you only have one or two of those. Terrible and erratic performance.
https://www.simple-talk.com/content/article.aspx?article=2280
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2016 at 6:09 am
This is going to be an utter disaster taking this approach.
Generated code is fine, to a point. However, it needs to be generated with actual logic behind it. I'd suggest you have your developers look to something like Entity Framework. It's not perfect, but it's certainly better (in most cases) than what they're currently proposing. Anywhere from 85% to 95% of the code generated from Entity Framework functions just fine. That last 5%-15% you will have to write traditional stored procedures for, but that vast majority of the work is already done.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 11, 2016 at 11:07 am
Extremely strong second to what Gail and Grant have both said.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2016 at 11:14 am
Previous place i worked at had a code base like that, created by an offshore team.
There was only a project manager, and no onsite developer reviewing the code produced, so no significant code review was done.
They were complaining about utterly poor performance, and ended up bringing me and another guy in, and it cost them our two salaries for three months, plus the cost of still paying their offshore team to not do anything while we were fixing the issues until their contract was finally cancelled.
it was very satisfying to be rewriting queries that improved by multiple orders of magnitude for each rewrite, but it got monotonous.
Based on that experience, unless you have a database that is extreamly low number of rows, so the hardware masks the performance issues, I can pretty much promise rewrite will be required within days of release, due to poor performance.
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply