Hungarian notation convention
-
Jackal (10/2/2008)
Hi Jeff,The following article contains a sample test.
http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/hitsandmisses/1776/
When I replicated the test (in SS2K), it did appear to be as the article listed. However, re-doing the test this morning (in SS2K5) does not provide the results as the article defines. I guess it would be best for Mike to comment on this, as it is his article.
Jackal
Ok... this one really intrigued me especially since I've never heard of such a thing before. Thanks for bringing it up, Jackal!
Even though the article is well above average for readability and clarity and the author makes some really good points that may have been true in the past, the testing I did pretty much convinces me that the whole article has the basic fault of being incorrect on every point except for the point about the two part naming convention.
Here's the proc I made...
USE AdventureWorks--DROP PROCEDURE dbo.TestProcedure
GO
CREATE PROCEDURE dbo.TestProcedure @LastName nvarchar(50)
AS
SELECT *
FROM Person.Contact
WHERE LastName = @LastName
GO
And here's the code I ran from two different SPID's...
Exec dbo.TestProcedure @LastName = 'Miller'Exec TestProcedure @LastName = 'Miller'
Exec dbo.testprocedure @LastName = 'Miller'
Exec dbo.TestProcedure @lastname = 'Miller'
Exec dbo.TestProcedure @Lastname = 'Miller'
Exec dbo.TestProcedure @LastName = 'Miller'
Exec dbo.TestProcedure @LastName = 'Miller'
Exec TestProcedure @LastName = 'Miller'
Exec TestProcedure @LastName = 'Miller'
Exec dbo.testprocedure @LastName = 'Miller'
Exec dbo.TestProcedure @lastname = 'Miller'
Exec dbo.TestProcedure @Lastname = 'Miller'
Exec dbo.TestProcedure @LastName = 'Miller'
Exec dbo.TestProcedure @LastName = 'Miller'
Exec TestProcedure @LastName = 'Miller'
Notice that the first call to the procedure is in the absolute correct case for both the proc name and the parameter. Here's what I got on the first set of runs after the proc was made... I have a couple of other things turned on like CACHE INSERT and RECOMPILE. Do notice that even the proc with the 1 part naming convention and all case "flavors" that I used all got cache hits... not a single cache miss except for the original "compile" of the script....
Like I said, the article was very well written and may have been true, at one time... but as of SQL Server 2005 SP2, most of that article is not true. Do notice, however, how long it took the 1 part naming convention to run until SQL Server really "got the idea" of what to run. That's why it's so very important to use at least a 2 part naming convention. 🙂
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff,
Thanks for posting back about the case sensitivity of procedure executions, makes for good reading.
Oh, and on the point of the dba getting over-ridden by 'business needs'. Trust me, he did get angry and I beleive he even ended up resigning over some similar issues where he was not being taken notice off.
I agree 100% with the previous comments on coding standards. I mean, whats the point of having a dba if you dont let them do there job.
Jackal
-
Any developer that does not do this, is a lazy developer and a person I definately do not want on my team. A little harsh maybe, but I've had to re-format many a developers code just to see what the procedure is supposed to do.
A little time now can save a lot of time later.
In all honestly I'd say it's as much about competence and general attitude as 'laziness'. Occluded and badly laid out code in my experience goes hand in hand with shoddy thinking and highly sub-optimal solutions and generally unmaintainble code. Anyone else here tend to find that the more poorly and inconsistently the code is laid out the more often it's a snakepit of cursors and totally unnecessary temp tables and runs forever?
-
Andrew Gothard (10/3/2008)
...it's a snakepit of cursors and totally unnecessary temp tables and runs forever?
You just described the code at my new job... 😛 except instead of cursors, it's just the opposite... huge, slow joins with criteria that make it impossible to get any better than scans on some really large tables instead of seeks (or starter seeks), crazy indexes on everything that keep simple inserts and updates from ever having a chance of being fast and still don't help selects, using replication like it was a stored procedure, and a really poor general understanding of how a database should work.
Even the ETL side of the house is a "bomb" because it's riddled with Perl scripts and other external handlers. They used Perl because they couldn't think of a way to do an "impossible" split in T-SQL. I've rewritten one of those scripts, all in T-SQL... it used to take 40 minutes to execute... now, it only takes 90 seconds. Because that script get's used about 200 times a day, they actually had to build a staging scheduler and a monster support system around it. It makes it very difficult to do any sort of "rerun", etc, etc. We'll be able to do away with that whole system and replace it with just a couple of high performance stored procedures that also happen to be highly scalable.
And, the way they used DTS is an absolute nightmare... everything we do requires the handling of multiple files across multiple customer databases. There's more bloody custom built, unnecessary Active-X components just to do a simple loop back or to get a file list from a directory than you can shake a stick at and it's all as slow as molasses in January.
Hardcoding is rampant. We can't even change the main system password because they hardcoded it into GUI's, stored procs, and a bazillion DTS jobs.
And, heh, there might be 3 lines of embedded comments for every 500 lines of stored procedure.
The cool part about all of that is... none of the original folks are at the company anymore. We have some great people who made some great documentation for the system in the last several months that also happen to be some pretty good designers and Developers on top of that and we're rebuilding the system one sick stored proc or DTS job at a time. They hired me because they recognized that when you're handling as much data as the system requires, you should probably do it in the database and they just didn't know how to do it. Things like that "impossible" split I told you about.
I told you that story to tell you this one... this whole miserable system is what happens when you don't have coding standards and when you want everything in a hurry. I've even had to write "verification code" because no one was able to prove that the system was actually working correctly. The company was in deep Kimchie because of the code that is the bread-n-butter of the company couldn't handle any scalability to speak of. It had almost gone bankrupt because of major customer disappointment in all areas just because of bad, slow, non-scalable code. And, no, not even the purchase of new hardware has been able to fix any of that.
You managers out there... take heed of the code. There's never really enough time to do it again... you need to do it right the first time even if it takes a little longer (which it really doesn't). Taking heed of the code also means hiring great Developers and I've found that a whole lot of letters behind their name doesn't necessarily mean they're a great developer... learn how to do a bloody interview! 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff, when you get that place straightened out, write it all down and publish it as "Tales from the deep Kimchie"; sounds like it would be best seller!
-
Jeff, when you get that place straightened out, write it all down and publish it as "Tales from the deep Kimchie"; sounds like it would be best seller!
YES !!! Go for it!
-
Jeff Moden (10/2/2008)
Andrew Gothard (10/2/2008)
J (10/2/2008)
"'dbo.selectthisandthat"For my taste, I find this harder to read (mid-50 eyesight and all), it requires more attention to figure out the meaning of the name. I feel something is lost in readability - for humans.
I am sure there are other tricks to improve performance by making the code (a bit) less readable...
Don't think it's anything to do with age mate - IMO it's just a horrible crabby style of coding that deliberately ignores any consideration of legibility or the way people actually read. Right up there with everything on one line, no spaces after commas, no spaces after keywords and sundry other vileness leading to unreadable gibberish
Wow! Two more folks with the right idea! I love it! 🙂
Well - don't get me started on using **** like x, t1, t2 etc as pseudonyms for tables with meaningful names either. I have heard two 'reasons' for this. "It saves typing". FFS! It makes it more readable. Eh? Ok - if you're working with something like BaaN and use tblSalesHeaderCompanyCompanyName rather than ttdsls040400 - fair enough. If you're telling me that c in the middle of god knows how many lines rather than the actual table name is 'more readable' than you're talking utter nonsense.
I don't use x as a variable name when I'm coding - I don't use it to refer to a table with a meaningful name either.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them. -
Jeff Moden (10/3/2008)
Andrew Gothard (10/3/2008)
...it's a snakepit of cursors and totally unnecessary temp tables and runs forever?
You just described the code at my new job... 😛 except instead of cursors, it's just the opposite... huge, slow joins with criteria that make it impossible to get any better than scans on some really large tables instead of seeks (or starter seeks), crazy indexes on everything that keep simple inserts and updates from ever having a chance of being fast and still don't help selects, using replication like it was a stored procedure, and a really poor general understanding of how a database should work.
Even the ETL side of the house is a "bomb" because it's riddled with Perl scripts and other external handlers. They used Perl
http://upload.wikimedia.org/wikipedia/en/f/f4/The_Scream.jpg
You managers out there... take heed of the code. There's never really enough time to do it again... you need to do it right the first time even if it takes a little longer (which it really doesn't). Taking heed of the code also means hiring great Developers and I've found that a whole lot of letters behind their name doesn't necessarily mean they're a great developer... learn how to do a bloody interview! 😉
Nice idea - but the problem is that explaining database specific issues to certain types of developer is like trying to explain general dev issues to a particularly stroppy end-user. If they don't understand it - how do they interview on it? I've been asked in the past "Are you good with cursors" "Brilliant - I never use them". This is often misunderstood
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them. -
andrew gothard (10/4/2008)
"Are you good with cursors" "Brilliant - I never use them". This is often misunderstoodHeh... I often ask that question to find out what the interviewee actually knows about development. Your answer is the one I'm looking for. 😛
When someone asks me that question on an interview, my answer is similar. But, then I also turn the question around an ask something like, "Why, do you folks use a lot of cursors here?". If it's the kind of company that I know I'll like working for, they'll immediately puke in the basket at the very thought. Makes for a good ice-breaker. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
What we do in our shop is to add a prefix to the name of the field like:
sName: (s) for Char, Varchar
dBirthdate (d) for Datetime
nPayment (n) numeric fileld dec, int, smallint
In the case you have to upgrade a numeric field smallint to int for intance then the name on the database will remain the same.
Hope this help you in what you are looking for.
-
16 months later I should hope the guy has got his answer already... 🙂
-
Do you have one?
-
Heh... "It Depends".... might be a very long interview. 😛
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
bossimal (9/25/2008)
Hi All,Hungarian notation is awful and inflexible. I have attached the database naming conventions I developed for our organisation (identifying references removed) and it's worked wonders for our databases and applications. Our previous databases were very messy and we often faced problems of changing data types etc as previously described by Jeff. Now, it's easier to read, code and maintain and the lagtime to get new developers up to speed has decreased as the notation is far more intuitive.
Any constructive feedback is welcome.
Jane
Jane,
I tried opening your document but I get redirected to the Forum Page.
Could you please send me the document?
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 14 posts - 46 through 58 (of 58 total)
You must be logged in to reply to this topic. Login to reply