May 18, 2015 at 10:38 am
Ed Wagner (5/15/2015)
A SELECT * in a cursor...So that means if a column is added to a table the system uses, the interface breaks at every point where it reads from that table immediately on doing the FETCH. The cursor fetches into variables, which won't match the column list returned by *, so it breaks.Well, I guess that's one round of ammunition. It sounds like you have your work cut out for you and I don't envy you in the cultural battle you have to engage in.
Let me guess:
1. Is the application is connecting with the 'sa' account too?
2. Did they solve their slow query problem by using NOLOCK on every table to speed things up?
3. They don't have any foreign keys defined because they just get in the way of data being inserted?
4. They have no indexes because they got too fragmented years ago, so the natural answer was to drop them. Besides, the SELECT * wasn't using them for some strange reason.
5. There's no reason to worry about SQL injection because they've never seen it happen
I honestly hope I'm wrong on all these guesses.
That is hilarious and Scary Accurate!
1. The application connects with an account that has Sysadmin privs
2. Every session starts out by setting the isolation level to READ_UNCOMMITTED and also has NOLOCK on the SQL statements
3. There is no referential integrity defined in the database at all.
4. There are TONS of indexes. Some tables have upwards of 20 indexes but almost none of them are actually used.
5. I asked the question about SQL Injection. I was told that since all of the SQL statements are built up dynamically in the application, SQL Injection is not a problem. There is now a dent in the wall of my office where I was banging my head against it.
May 18, 2015 at 10:56 am
Vyper426 (5/14/2015)
I Really appreciate everyone who contributed information. I must also apologize for neglecting to let everyone know the other issue that brings me such joy in this new position: Almost all of the queries that contain the SELECT * from one or more tables is contained inside of a Cursor. In fact, every screen within the system operates using Cursor Processing. An example of this is the Shipment Movement screen. This screen has a single record which contains the header information about a given shipment and 1 - N detail rows containing delivery stop information. The details are displayed in a grid which allows any item to be updated and stops to be added and/or deleted. Once the user makes any needed changes and submits the delivery, a cursor processes all of the detail rows.When I asked why the software was written using such antiquated patterns, They just said 'Thgat is the way we have always done it and we see no reason to change.
Aint' Life Grand!
Might as well go back to COBOL and ISAM database files if that is how they want to work.
May 18, 2015 at 11:18 am
Vyper426 (5/18/2015)
Ed Wagner (5/15/2015)
A SELECT * in a cursor...So that means if a column is added to a table the system uses, the interface breaks at every point where it reads from that table immediately on doing the FETCH. The cursor fetches into variables, which won't match the column list returned by *, so it breaks.Well, I guess that's one round of ammunition. It sounds like you have your work cut out for you and I don't envy you in the cultural battle you have to engage in.
Let me guess:
1. Is the application is connecting with the 'sa' account too?
2. Did they solve their slow query problem by using NOLOCK on every table to speed things up?
3. They don't have any foreign keys defined because they just get in the way of data being inserted?
4. They have no indexes because they got too fragmented years ago, so the natural answer was to drop them. Besides, the SELECT * wasn't using them for some strange reason.
5. There's no reason to worry about SQL injection because they've never seen it happen
I honestly hope I'm wrong on all these guesses.
That is hilarious and Scary Accurate!
1. The application connects with an account that has Sysadmin privs
2. Every session starts out by setting the isolation level to READ_UNCOMMITTED and also has NOLOCK on the SQL statements
3. There is no referential integrity defined in the database at all.
4. There are TONS of indexes. Some tables have upwards of 20 indexes but almost none of them are actually used.
5. I asked the question about SQL Injection. I was told that since all of the SQL statements are built up dynamically in the application, SQL Injection is not a problem. There is now a dent in the wall of my office where I was banging my head against it.
I'd strongly recommend to prepare 3 envelopes.:unsure:
May 18, 2015 at 4:09 pm
Vyper426 (5/18/2015)
Ed Wagner (5/15/2015)
A SELECT * in a cursor...So that means if a column is added to a table the system uses, the interface breaks at every point where it reads from that table immediately on doing the FETCH. The cursor fetches into variables, which won't match the column list returned by *, so it breaks.Well, I guess that's one round of ammunition. It sounds like you have your work cut out for you and I don't envy you in the cultural battle you have to engage in.
Let me guess:
1. Is the application is connecting with the 'sa' account too?
2. Did they solve their slow query problem by using NOLOCK on every table to speed things up?
3. They don't have any foreign keys defined because they just get in the way of data being inserted?
4. They have no indexes because they got too fragmented years ago, so the natural answer was to drop them. Besides, the SELECT * wasn't using them for some strange reason.
5. There's no reason to worry about SQL injection because they've never seen it happen
I honestly hope I'm wrong on all these guesses.
That is hilarious and Scary Accurate!
1. The application connects with an account that has Sysadmin privs
2. Every session starts out by setting the isolation level to READ_UNCOMMITTED and also has NOLOCK on the SQL statements
3. There is no referential integrity defined in the database at all.
4. There are TONS of indexes. Some tables have upwards of 20 indexes but almost none of them are actually used.
5. I asked the question about SQL Injection. I was told that since all of the SQL statements are built up dynamically in the application, SQL Injection is not a problem. There is now a dent in the wall of my office where I was banging my head against it.
Isn't this why they hire us? Every place I've gone to has had these identical problems for a very long time. It's going to take a while to "let them see it your way". The only way to convince the stalwarts is to "prove it" and there's only one way to do that.
Ed... IIRC, the reason why you're so accurate in your "guess" is because those were the exact problems that you ran into where you work and you're still fixing some of it. How did you finally convince them to move forward on these? It sure as hell wasn't by talking. You had to prove it with code until they finally admitted on their own that "you might have something there". Now they listen and they can't say "prove it" because you already have. 😉
There is no ammunition better than a comparison between code.
--Jeff Moden
May 19, 2015 at 8:55 am
What does the business care about?
Response time?
Accuracy?
Safe nimbleness/agility (i.e. changes don't break existing code, like column adds)?
First you must mean the same thing as the developers when speaking of measurements - have the same operational definition. Then you can take reliable, valid measures of each way - ideally, fine one or two developers who are willing to test different ways in their application, so it's really apples to apples (Here's your application on SELECT col1, col2. Here's your application on SELECT *. Any questions?).
After that, you bounce the results up against what the business thinks is important... and work on your business case for changing the existing stuff as it's touched.
May 20, 2015 at 1:28 am
Personally I would tell your managers that there's no point in you being there if the developers insist on continuing these diabolical practices and either you get the management support you need to enforce the changes you were hired to bring in (otherwise why did they need a DBA?) or you walk.
There's plenty of other jobs that would apprciate your skills.
May 20, 2015 at 5:56 am
Jeff Moden (5/18/2015)
Vyper426 (5/18/2015)
Ed Wagner (5/15/2015)
A SELECT * in a cursor...So that means if a column is added to a table the system uses, the interface breaks at every point where it reads from that table immediately on doing the FETCH. The cursor fetches into variables, which won't match the column list returned by *, so it breaks.Well, I guess that's one round of ammunition. It sounds like you have your work cut out for you and I don't envy you in the cultural battle you have to engage in.
Let me guess:
1. Is the application is connecting with the 'sa' account too?
2. Did they solve their slow query problem by using NOLOCK on every table to speed things up?
3. They don't have any foreign keys defined because they just get in the way of data being inserted?
4. They have no indexes because they got too fragmented years ago, so the natural answer was to drop them. Besides, the SELECT * wasn't using them for some strange reason.
5. There's no reason to worry about SQL injection because they've never seen it happen
I honestly hope I'm wrong on all these guesses.
That is hilarious and Scary Accurate!
1. The application connects with an account that has Sysadmin privs
2. Every session starts out by setting the isolation level to READ_UNCOMMITTED and also has NOLOCK on the SQL statements
3. There is no referential integrity defined in the database at all.
4. There are TONS of indexes. Some tables have upwards of 20 indexes but almost none of them are actually used.
5. I asked the question about SQL Injection. I was told that since all of the SQL statements are built up dynamically in the application, SQL Injection is not a problem. There is now a dent in the wall of my office where I was banging my head against it.
Isn't this why they hire us? Every place I've gone to has had these identical problems for a very long time. It's going to take a while to "let them see it your way". The only way to convince the stalwarts is to "prove it" and there's only one way to do that.
Ed... IIRC, the reason why you're so accurate in your "guess" is because those were the exact problems that you ran into where you work and you're still fixing some of it. How did you finally convince them to move forward on these? It sure as hell wasn't by talking. You had to prove it with code until they finally admitted on their own that "you might have something there". Now they listen and they can't say "prove it" because you already have. 😉
There is no ammunition better than a comparison between code.
Yeah, you recall correctly...I didn't have to content with the NOLOCK worst practice, though. Thank goodness!
The cursor with SELECT * can be easily through a performance comparison. A demonstration of SQL injection goes a long way in building motivation. The sysadmin privs on connections from applications should be made clear through a demo on SQL injection that goes a little farther. Say, for example, that you create a test database on your server. In your injection demo, drop it and then explain that the database didn't have to be a test database. It should be made pretty clear.
The referential integrity, data types and indexes should be enough to convince people of the performance benefit that good design and tuning brings. I found that focusing on percentages if more effective that focusing on time. When people hear that it takes 1 ms to run a query efficiently versus 50 ms to run it poorly, it doesn't have the impact as saying that it takes 50 times as long. Then, when you follow that up with telling them that it runs 30,000 times a day and do a little math for them, it adds reinforcement by giving them scope.
Whatever tactic you use to try to change the culture, be sure to get the buy-in of the boss. It's through their mandates that positive change is going to occur. In the end, it's the company's data that's going to be protected. Also, be aware that this is a cultural battle that you're going to fighting, so it won't be a simple one.
May 20, 2015 at 6:10 am
Ed Wagner (5/20/2015)
Also, be aware that this is a cultural battle that you're going to fighting, so it won't be a simple one.
I agree with everything you said, but I think this is the most important.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 20, 2015 at 6:15 am
Grant Fritchey (5/20/2015)
Ed Wagner (5/20/2015)
Also, be aware that this is a cultural battle that you're going to fighting, so it won't be a simple one.I agree with everything you said, but I think this is the most important.
Thanks. I look at it again and see my sloppy grammar. I meant "going to be fighting."
May 20, 2015 at 6:24 am
Ed Wagner (5/20/2015)
Grant Fritchey (5/20/2015)
Ed Wagner (5/20/2015)
Also, be aware that this is a cultural battle that you're going to fighting, so it won't be a simple one.I agree with everything you said, but I think this is the most important.
Thanks. I look at it again and see my sloppy grammar. I meant "going to be fighting."
Forums. Language Nazis need not apply. Your meaning was clear.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 5, 2015 at 10:03 am
You could try a little role-play.
Call your developers into a meeting, and have a box of paper left outside the room.
Tell a developer you need some paper and ask them to bring the box in. Take a sheet and ask them to put the box outside again.
Repeat the process with someone else, and say they need to bring in the whole box because that is like doing a SELECT *.
Then ask a third person, and say this time they only need to bring in one sheet because they are doing a SELECT col1.
Seeing some of the impact may help prove your point.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 5, 2015 at 12:36 pm
EdVassie (6/5/2015)
You could try a little role-play.Call your developers into a meeting, and have a box of paper left outside the room.
Tell a developer you need some paper and ask them to bring the box in. Take a sheet and ask them to put the box outside again.
Repeat the process with someone else, and say they need to bring in the whole box because that is like doing a SELECT *.
Then ask a third person, and say this time they only need to bring in one sheet because they are doing a SELECT col1.
Seeing some of the impact may help prove your point.
Heh... not nearly as much fun as high velocity pork chops in the wood shed. 😀
--Jeff Moden
June 5, 2015 at 12:40 pm
EdVassie (6/5/2015)
You could try a little role-play.Call your developers into a meeting, and have a box of paper left outside the room.
Tell a developer you need some paper and ask them to bring the box in. Take a sheet and ask them to put the box outside again.
Repeat the process with someone else, and say they need to bring in the whole box because that is like doing a SELECT *.
Then ask a third person, and say this time they only need to bring in one sheet because they are doing a SELECT col1.
Seeing some of the impact may help prove your point.
I still stand by just changing the column order on them and watching them cry while trying to trouble shoot why all their SELECT * queries break.
Bonus points if you can convince management there's a need to reorganize the columns and it shouldn't have any impact on the developers anyways since they're following best practices right?
June 16, 2015 at 10:52 am
ZZartin (6/5/2015)
EdVassie (6/5/2015)
You could try a little role-play.Call your developers into a meeting, and have a box of paper left outside the room.
Tell a developer you need some paper and ask them to bring the box in. Take a sheet and ask them to put the box outside again.
Repeat the process with someone else, and say they need to bring in the whole box because that is like doing a SELECT *.
Then ask a third person, and say this time they only need to bring in one sheet because they are doing a SELECT col1.
Seeing some of the impact may help prove your point.
I still stand by just changing the column order on them and watching them cry while trying to trouble shoot why all their SELECT * queries break.
Then guess who gets the blame for breaking the app?
Besides, if the app uses column names, the app might continue to function. I just tried it here with vb, changed the table to have one more column, and the selected column is now in position 4 instead of position 3 (ordinal position) and it was handled correctly.
Bonus points if you can convince management there's a need to reorganize the columns and it shouldn't have any impact on the developers anyways since they're following best practices right?
Well I wouldn't do that unless there were REALLY a reason to rearrange columns. Otherwise, I'd be lying and heaven forbid, I couldn't produce a believeable reason for rearranging columns and the developers would also now be all too willing to blame the breakage on me.
I've been in a similar position and I gotta tell you, if the apps working ok, and nobody appreciates the correctness of what I'm saying, I'd drop the issue. Essentially, I bring issues like this up as nicely as I can WITHOUT actively trying to break apps, and if I don't make a sale, then thats the end of that.
June 17, 2015 at 4:29 am
As much as I do agree with all the arguments against using SELECT * I still think that your developer does not have to change this bad habit until it becomes a business case. If and only if BOTH performance does suffer AND your management does not want to double the processing power, then you might have a strong point against the current approach. If it ain't broke, don't fix it. I know that will leave you with a very unpleasant feeling about this whole case, but such is life. Mention it to the right people, and as soon as the problems you predicted will surface, you'll only have to say: I told you so. Remember, if these problems do surface, you'll probably have to work together with this developer to fix them, whether you like it or not. Sometimes all you can do about it is making clear to anyone you are not the one to blame.
Viewing 15 posts - 16 through 30 (of 60 total)
You must be logged in to reply to this topic. Login to reply