Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««4,1914,1924,1934,1944,195»»»

Are the posted questions getting worse? Expand / Collapse
Author
Message
Posted Monday, October 28, 2013 9:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 07, 2014 7:35 AM
Points: 1,172, Visits: 2,413
L' Eomot Inversé (10/26/2013)
Jeff Moden (10/26/2013)
L' Eomot Inversé (10/24/2013)
wolfkillj (10/23/2013)
I've published my first technical blog post. I'd appreciate any feedback from this group on both the technical aspects and the writing. Thanks, everyone!

Nice article. I liked seeing the use of STR for conversion as one option for solving the problems. Most people who write about approximate muneric problems appear to assume that the problem is rounding errors in the floating point arithmetic rather than conversion errors and representation errors, but you have spotted that the thing that needed addressing to fix these examples was conversion error (because convert and cast and implicity conversion can all cause errors by doing too much rounding and sometimes by rounding incorrectly, but STR doesn't because you can specify the precision wanted).
Until we get modern floating point in SQL, it will be sensible to use exact numerics for latitudes and longitudes; and even when we do we may still find that we have to use STR to avoid conversion errors if we use approximate numerics.


STR is pretty bad for performance and can produce some really unexpected rounding errors even with smaller numbers. Please see the following article for some of what I'm talking about.
http://www.sqlservercentral.com/articles/T-SQL/71565/


My recommendation is to forget that STR even exists.

Certainly STR causes problems when using it requires an implicit conversion from an exact numeric to an approximate numeric, because that conversion can introduce representation errors. But if one is starting from floating point that doesn't happen, because no implicit conversion is needed.

I guess I should go and try to discover whether a cast to decimal(38,18) followed followed by rounding to decimal(p,x) , where p and x are the required precision and scale for the output format, and then cast to varchar(p) has worse performance than just calling str when the starting point is float(53) but I take the view that the cost shown by the code example at the end of your spackle note on STR is largely caused by the implicit conversion from int to float which wouldn't be there if the starting point were a float, and when starting from float there isn't that performance penalty.

An interesting point that the wolfkillj article was making was that implicit conversion from float to varchar does the wrong thing and introduces errors that are not introduced by STR, and my point was that it good for an article to notice that because most authors are too busy believing that float rounding is worse than exact numeric rounding, which is the opposite of the truth: the rules for calculating the precision and scale of the result of an arithmetic operation on decimal values ensure that any long chain of calculation must include frequent precision-and-scale-reduction casts which introduce vastly greater rounding error than would an equivalent chain of calculation using FLOAT(53) throughout, which wouldn't need those casts.

That doesn't mean that I don't think STR should be avoided in every case where the starting point is not an approximate numeric: it certainly should, no-one would want to suffer representation errors arising from a pointless implicit conversion to float. But it shouldn't be forgtten altogether - if one has approximate numeric data that needs to be reported STR is a useful tool. Of course creating cases where STR is useful by using approximate numerics where they are not appropriate would be stupid - and generally they are appropriate only where long chains of calculation will happen or, sometimes, where the input is known to be approximate values, not exact ones. Approximate numerics as we currently have them are, as wolfkillj's blog entry pointed out, not a good way of representing lattitude and longitude if one wants to calculate the distance between two points. Another extremely good point made by the blog entry is that a believable wrong answer is worse than an unbelievable wrong answer.

Of course the whole problem should go away as soon as we get the latest floating point standard implemented in SQL, along with the conversions that it needs: either an improvement to STR to avoid conversion to the wrong sort of FLOAT or an enhancement to CONVERT to allow scale and precision to be specified for VARCHAR output when the input type isn't the wrong sort of FLOAT; this is because the one of the new FLOAT types suffers from representation errors only for values for which exact numerics have exactly the same errors. Maybe that kind of FLOAT should be classed as an exact numeric - but I suspect that will not happen. Actually I suspect that getting the latest floating point standard into SQL Server also won't happen, at least not any time soon.


I took the code from Jeff's article and modified it to operate on a temp table of 1,000,000 float values (which I populated by inserting the first 1,000,000 integers from my tally table since IDENTITY() doesn't work on a float column) . Here are the results:

(1000000 row(s) affected)
========== Cast, Concatenate, and Size ==========

SQL Server Execution Times:
CPU time = 983 ms, elapsed time = 984 ms.
========== The STR() Method is SLOWER ==========

SQL Server Execution Times:
CPU time = 842 ms, elapsed time = 839 ms.


I haven't had time to analyze these results or examine the causes yet (late for a meeting as it is). I did notice that CASTing the float value 1,000,000 to varchar(10) returned the result in scientific notation (as does CASTing 0.000001 to varchar(10), which becomes a problem when concatenating it into a string that is intended to be a valid WKT definition of a spatial object! I'll look into this a little more later . . . stay tuned!


Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1508950
Posted Monday, October 28, 2013 9:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 07, 2014 7:35 AM
Points: 1,172, Visits: 2,413
Sean Lange (10/28/2013)
wolfkillj (10/28/2013)
rodjkidd (10/28/2013)
I'm with Lynn.
I know what I know and I know people (Paul Randal, Paul White, Jeff and Gail etc) who know a heck of a lot more than me!

I've been asked how good I am at SSIS - my answer has been I've used to solve problems, and I've known more than the rest of the team, but I know a lot of people who are experts with it.

I the like the idea that the more you know and the more other people you know you end up rating yourself lower because you peer group has got bigger and more knowledgeable so you adjust your personal rating.

We just filled a perm role in our team, apparently the guy came over as expert in the interviews. I ended up working opposite him. Nice guy seemed to know stuff but also lacked a lot of knowledge. He quit one month in, last Friday, he had decided he was out of his depth. :-O The only thing I can think was last two roles he was top of the pile and by default "The expert", moves to a job where he's no longer at the top and rather than use it to his advantage (learning + experience) has walked. That's the third person the position has been offered to, 2 didn't even make to first day! I would say does anyone want a perm role, but there is major reorganisation going on, and I'm not sure the permanent roles are very safe at the moment. Hopefully we'll know Jan / Feb next year, so I doubt they will re-advertise this side of Christmas.

Rodders...


Maybe your guy was one of those "expert beginners" we've heard about.


Thanks for sharing that link. It is an interesting article. I have been around some of those people. Some of them have been able to get past being an expert beginner, others have had to take their expert noviceness on the road.


Just for the record, I didn't find that link on my own. I definitely read about it elsewhere. I think Steve Jones may have discussed it in the daily newsletter a few weeks back in a piece about stagnation and rot in development teams, IIRC.


Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1508952
Posted Monday, October 28, 2013 9:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:11 PM
Points: 2,411, Visits: 3,440
Revenant (10/25/2013)
Luis Cazares (10/25/2013)
I'm losing faith in humanity here as I've been interviewing some candidates for BI developers at my job. The last one claiming to have 8 years of experience as .NET developer (using SQL Server) and knowing 75% of SQL Server couldn't make a simple query on his head (SELECT * FROM Product WHERE ProductName != 'Milk') saying he would use DISTINCT to filter values from a query and he used just jobs to import data from a flat file (no bcp, bulk insert or SSIS).
I wonder what's going on in the world if people claim all that experience without knowing the simple basics.

I have been there before. I developed a simple trick: I ask the candidate how they rate their skills on a scale of 1 to 10. If the candidate has less than 12 years on SQL Server and rates himself - much less frequently herself - 8 or higher, the interview effectively ends right there.


That's a no-win for the candidate. I was asked to rate myself once. I rated myself a 9 on the technology. The interviewer said that's impossible; no one can be that high. The next interview I was asked to rate myself on the same technology I said 6. The interviewer said it's too low; that I have a low opinion of myself. So, now, when I'm asked to rate myself, I relate those two stories and ask what number they're looking for.

What is the right number? I have settled on 7 to 8. Most interviewers want to hear that.

Tom

Post #1508987
Posted Monday, October 28, 2013 9:56 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:52 AM
Points: 41,530, Visits: 34,446
OCTom (10/28/2013)
That's a no-win for the candidate. I was asked to rate myself once. I rated myself a 9 on the technology. The interviewer said that's impossible; no one can be that high.


If 9/10 is impossible, then that's not a scale out of 10, it's a scale out of 8.

I say 9/10 on the core database engine (not all the extra fluff that SQL includes). Mostly because if I don't know immediately how to do something I know at least the basic requirements and exactly where to look for the details.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1508996
Posted Monday, October 28, 2013 10:04 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:18 PM
Points: 4,245, Visits: 3,324
GilaMonster (10/28/2013)
OCTom (10/28/2013)
That's a no-win for the candidate. I was asked to rate myself once. I rated myself a 9 on the technology. The interviewer said that's impossible; no one can be that high.


If 9/10 is impossible, then that's not a scale out of 10, it's a scale out of 8.

I say 9/10 on the core database engine (not all the extra fluff that SQL includes). Mostly because if I don't know immediately how to do something I know at least the basic requirements and exactly where to look for the details.

Guys who work on that technology (building 35) usually rate themselves 8 out of 10.
Post #1509002
Posted Monday, October 28, 2013 10:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
OCTom (10/28/2013)
Revenant (10/25/2013)
Luis Cazares (10/25/2013)
I'm losing faith in humanity here as I've been interviewing some candidates for BI developers at my job. The last one claiming to have 8 years of experience as .NET developer (using SQL Server) and knowing 75% of SQL Server couldn't make a simple query on his head (SELECT * FROM Product WHERE ProductName != 'Milk') saying he would use DISTINCT to filter values from a query and he used just jobs to import data from a flat file (no bcp, bulk insert or SSIS).
I wonder what's going on in the world if people claim all that experience without knowing the simple basics.

I have been there before. I developed a simple trick: I ask the candidate how they rate their skills on a scale of 1 to 10. If the candidate has less than 12 years on SQL Server and rates himself - much less frequently herself - 8 or higher, the interview effectively ends right there.


That's a no-win for the candidate. I was asked to rate myself once. I rated myself a 9 on the technology. The interviewer said that's impossible; no one can be that high. The next interview I was asked to rate myself on the same technology I said 6. The interviewer said it's too low; that I have a low opinion of myself. So, now, when I'm asked to rate myself, I relate those two stories and ask what number they're looking for.

What is the right number? I have settled on 7 to 8. Most interviewers want to hear that.

Tom



I generally ask a few questions first. I ask him how the scale is weighted. They usually give me a funny look but when I explain that I want to know if my rating is based on being compared to other people that I know or if it is being compared to the cumulative knowledge of sql at large. This has usually resulted in some interesting responses. I then generally that since it is a base 10 response I like to think of it as a percentage. Compared to the cumulative sql knowledge I would say I am somewhere around a 6 (60% of what I need to know about most things I have readily at my fingertips with no research required), if compared to other people then I would go a bit higher. I even once had an interviewer say that he had never considered the question in the light that I suggested. He told me to forget it because the question is too vague and arbitrary.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1509010
Posted Monday, October 28, 2013 10:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:10 PM
Points: 2,763, Visits: 5,909
Rating someone on an average for all SQL Server is not a great idea. I could rate myself high on developing T-SQL, but not on DBA or ETL stuff. It's more about being aware on what you know and what you don't but want to learn about.
Another guy came for an interview saying he worked on performance improvement but he removed cursors by changing them with while loops and couldn't answer why temp tables are better than table variables for performance.
Instead of using raw numbers, I prefer to go further on which are the strengths and weaknesses of each candidate.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1509026
Posted Monday, October 28, 2013 10:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
Luis Cazares (10/28/2013)
Rating someone on an average for all SQL Server is not a great idea. I could rate myself high on developing T-SQL, but not on DBA or ETL stuff. It's more about being aware on what you know and what you don't but want to learn about.
Another guy came for an interview saying he worked on performance improvement but he removed cursors by changing them with while loops and couldn't answer why temp tables are better than table variables for performance.
Instead of using raw numbers, I prefer to go further on which are the strengths and weaknesses of each candidate.


Agreed!!! My point of putting it back on the interviewer is to hopefully shed some light on how ridiculous and ambiguous the question is.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1509035
Posted Monday, October 28, 2013 10:56 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 10:37 PM
Points: 3,306, Visits: 2,351
Sean Lange (10/28/2013)
Luis Cazares (10/28/2013)
Rating someone on an average for all SQL Server is not a great idea. I could rate myself high on developing T-SQL, but not on DBA or ETL stuff. It's more about being aware on what you know and what you don't but want to learn about.
Another guy came for an interview saying he worked on performance improvement but he removed cursors by changing them with while loops and couldn't answer why temp tables are better than table variables for performance.
Instead of using raw numbers, I prefer to go further on which are the strengths and weaknesses of each candidate.


Agreed!!! My point of putting it back on the interviewer is to hopefully shed some light on how ridiculous and ambiguous the question is.

The question sounds like it was created by HR people to rank candidates, not by database people who know there are many different areas of expertise. For example, I'm lucky I can spell SSAS, but don't ask me to go any further than that.



Tally Tables - Performance Personified
Best practices on how to ask questions
Post #1509046
Posted Monday, October 28, 2013 11:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:10 PM
Points: 2,763, Visits: 5,909
Ed Wagner (10/28/2013)
Sean Lange (10/28/2013)
Luis Cazares (10/28/2013)
Rating someone on an average for all SQL Server is not a great idea. I could rate myself high on developing T-SQL, but not on DBA or ETL stuff. It's more about being aware on what you know and what you don't but want to learn about.
Another guy came for an interview saying he worked on performance improvement but he removed cursors by changing them with while loops and couldn't answer why temp tables are better than table variables for performance.
Instead of using raw numbers, I prefer to go further on which are the strengths and weaknesses of each candidate.


Agreed!!! My point of putting it back on the interviewer is to hopefully shed some light on how ridiculous and ambiguous the question is.

The question sounds like it was created by HR people to rank candidates, not by database people who know there are many different areas of expertise. For example, I'm lucky I can spell SSAS, but don't ask me to go any further than that.

You're really lucky, I might get confused when spelling SSIS and SSAS.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1509054
« Prev Topic | Next Topic »

Add to briefcase «««4,1914,1924,1934,1944,195»»»

Permissions Expand / Collapse