Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Are the posted questions getting worse? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, October 28, 2013 9:10 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, August 24, 2015 12:20 PM Points: 1,064, Visits: 2,582
 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 WolfkillBlog: SQLSouthTwitter: @SQLSouth
Post #1508950
 Posted Monday, October 28, 2013 9:13 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, August 24, 2015 12:20 PM Points: 1,064, Visits: 2,582
Post #1508952
 Posted Monday, October 28, 2013 9:49 AM
 SSCrazy Group: General Forum Members Last Login: Friday, January 30, 2015 7:36 AM Points: 2,723, Visits: 4,152
 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 Group: General Forum Members Last Login: Yesterday @ 5:18 AM Points: 45,619, Visits: 44,147
 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 ShawMicrosoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enterWe stand on the bridge and no one may pass
Post #1508996
 Posted Monday, October 28, 2013 10:04 AM
 SSCertifiable Group: General Forum Members Last Login: 2 days ago @ 4:16 PM Points: 5,427, Visits: 4,607
 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
 SSCoach Group: General Forum Members Last Login: 2 days ago @ 2:44 PM Points: 16,134, Visits: 16,835
Post #1509010
 Posted Monday, October 28, 2013 10:32 AM
 SSCrazy Eights Group: General Forum Members Last Login: 2 days ago @ 7:14 PM Points: 8,200, Visits: 17,740
 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.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2
Post #1509026
 Posted Monday, October 28, 2013 10:44 AM
 SSCoach Group: General Forum Members Last Login: 2 days ago @ 2:44 PM Points: 16,134, Visits: 16,835
 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
 SSCrazy Eights Group: General Forum Members Last Login: Yesterday @ 7:04 PM Points: 9,934, Visits: 9,326
 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.
Post #1509046
 Posted Monday, October 28, 2013 11:13 AM
 SSCrazy Eights Group: General Forum Members Last Login: 2 days ago @ 7:14 PM Points: 8,200, Visits: 17,740
 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.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2
Post #1509054

 Permissions