Louis's profileThe SQL Doctor is In (Re...PhotosBlogListsMore  |  Tools Help |
|
|
June 21 Why aren't people using databases!!First off, let me state that the two companies I will mention are two of my favorite companies. I drive Ford cars pretty much exclusively, and I buy a large percentage of my electronics from Best Buy. I recommend them both quite highly. I also love the dealership, and just bought a CUV from them. The problem is that over the past few days, I was hit by a couple of examples that makes it clear to me they have no clue about the power of databases.
My car is an SVT Focus, and it takes different parts than all of the other Focus models. I always use the dealer's service department or their fast lube joint for my service, as I have the 100000 mile warranty and I want no problems with it later down the road. I just hit my 60000 mile service, and for the 10th time out of 12, they put the wrong type of motor oil in. AND I MENTION IT TO THEM EVERY TIME! What's worse is that there is usually some note written in the intruction notes about it. The problem is that Ford clearly doesn't have a database of it's vehicles involved in the process.
When I envision this process, I think of a "standard service" entity, and it has line items for each "task". So, 60000 mile service: Check Brakes, Change Fuel Filter, Replace Transmission Fluid, etc. Then for each task a set of default part numbers for that vehicle, a drop down box with alternative parts (hey, even more costly upsell items, as I love my little car!) including a USER PROVIDED and OTHER options and then some notes to explain other and why other. No muss, no fuss, ONE PERSON has to note that SVT Focus' use 5w30 instead of 5w20 and ~10 gallons of motor oil is not wasted.
Clearly the problem isn't completely trivial, but it is very straightforward. I mean, right now they look up what oil to use from a sheet of paper. It is like this:
...other cars Focus 2002-05 5w20
...other cars
Focus SVT 2002-2005 5w30
...other cars
Almost all cars us 5w20, so they stop at the first one. I canot say that I blame them. It is not a really small list. The problem is that they are using a system that treats the computer like a paper filing system, and using their brains for trivia. I would rather they let the computer remember details and they spend their time figuring out problems with cars.
The second example was from my wife, the non technical person, who was trying to find a product for me at Best Buy (again, one of my favorite places) and the clerk couldn't find the product. So he asks her for the model number (yeah, my wife had the model number :) and when she lacks this knowledge the clerk claims to not be able to help out. Well, needless to say this was not her first stop of the day, and she "forcefully" (in a churchy sort of way) says you must be kidding.
So the guy goes to a demo computer and looks it up on a search engine. Whu? I mean, why is this technology built into their system at their customer service area? Or even point of sale computers? Again, gather ALL product information, even those you don't sell. Then use something like full-text indexing to let the person enter: Product Name. Then list the item, and where in the store it is located. Or, if they don't sell it (and they didn't) list alternatives, or something that states why they don't have it. Hey, better still, just put one of these kiosks up in the middle of the store and let me do the search?
The point of this mini-rant is that I want a piece of this action. Sure there are logistics involved. How do you get this information to the terminals, how to do this kind of distributed computing? But wait, I almost guarantee you that data from both the dealership and the retail store are going back up to the parent to go into a data warehouse so they can figure out how to sell more widgets. You know what the first step is, and I will bet it doesn't have a dimension on their sales fact table? Real satisfaction, not the kind that comes from a survey when the dealership has begged you not to say bad things (or they get in trouble big time, apparently)
I mean, why did I go into the store instead of using the web? Because I want instant gratification. But not if I have to work or wait for it... June 08 Newsgroup/Forum Posting...First, I love answering questions. I would do it for a living if anyone would let me (particularly if the questions were about the Simpson's TV show.)
Come on people, spend a few minutes when you want people's help and do a very few things:
1. Explain the problem in the subject and start of the body of your message
2. Show the code you are trying to run, when applicable. 3. Show the desired results 4. Include DDL for any tables or objects, and INSERT statements to load the tables. In other words, instead of:
Col
-----
1
Do:
CREATE TABLE tableName ( Col int )
INSERT INTO tableName VALUES (1) 5. Give any additional information that you can find that might be pertinent. For example, check out this post for information about showplan so you can display the plan of a query that is behaving badly for you. Any kind of logged information that might be pertinent can be useful.
Now, for each of these, you don't have to do them every time. Sometimes will won't even know where to start, so the general approach just to say 'Help!' is acceptable. However, you should know that if you do the least amount of work, you are very likely to get one or more requests for more information.
And then you have to wait even longer.
Seldom good: ================================== Subject:
-------- The WHERE clause doesn't work Explanation:
------------ The stupid where clause doesn't work. Can someone explain why I cannot match anything to anything else? Better:
================================== Subject:
---------- Cannot figure out the WHERE clause Explanation:
--------------- I keep trying:
SELECT *
FROM testTable WHERE testColumn But it doesn't work!
Best: ================================== Subject:
------------ Cannot figure out the WHERE clause with a Boolean value Explanation:
--------------- I keep trying:
SELECT *
FROM testTable WHERE testColumn But it doesn't work. Here is my table structure, and some data:
CREATE TABLE testTable
( testTableId int, -- other columns removed testColumn bit NOT NULL ) INSERT INTO testTable(testColumn)
SELECT 1,1 UNION ALL SELECT 2,0 UNION ALL SELECT 3,1 UNION ALL SELECT 4,1 GO What I expected to see was:
testTableId testColumn
----------- ---------- 1 1 3 1 4 1 Now, the many many folks who spend their free time more than happy to spend their free hours answering questions can see what you want, what you are trying, and pretty much what you might be missing. What you will also find is that almost anyone who does this very often and likes queries will race to answer your question. Why? Because a good percentage of the questions people answer are interesting problems that we would never come across otherwise. I have learned a lot of cool techniques from trying to answer cool questions, some of which have added to my "bag of tricks." Had this actually been a real question, each of the questions would have been answered differently:
Seldom good: ================================== WHERE clauses generally do in fact work. So we need more information.
Better: ================================== This won't work:
SELECT *
FROM testTable WHERE testColumn What datatype is testColumn...
<From here, a small book can be written on the various guesses about why you thought this was right.>
Best: ======================= Bit columns aren't Booleans. SQL doesn't have a Boolean type, so you have to do something like:
SELECT *
FROM testTable WHERE testColumn = 1 Done. See how easy Edit: Added in DDL and INSERT suggestion based on Denis' comment.
Edit: Added a link to a showplan topic April 26 Blogging about my own companyI was in a meeting today, working on something completely tangent to the meeting (related, but not so much connected to the meeting) when my direct manager and my project manager, and several of my coworkers started razzing me about whether or not I was blogging. I wasn't (this time) but it started a small conversation about whether or not I could blog about things directly related to my company. I was assured that dispite my concern that it wouldn't be a problem. It is certainly very common that everytime I learn something new I type my blog keyword into Slickrun and create a new blog entry for "later"
It isn't like I hide the company I work for, and I am quite proud of the job that the team that I work on does and look forward to sharing some of the technology some time once we have finished it up for a product we are going to be shipping. The problem is, I will never praise anything that I feel I have to praise, nor will I simply bash something unless it truly needs it. For example, if you read my Richmond Code Camp post, I mention that I went to three excellent sessions, but I later mention that I learned something from a session I didn't mention. I felt the presenter had much to learn, but being a critic about technical sessions for me is hard because I know many of the people who present, and I like all that I know personally. This fellow who did the less than adequate presentation is possibly in the position I was in 6 years ago when I gave a presentation on normalization that launched me in a learning frenzy to understand normalization well enough that led to me writing a book (my head was really large when I started the presentation, but very very small afterwards.
Back to the topic at hand, Microsoft has two very prominent examples of bloggers that critique their own company. One, Robert Scoble, (whom I have never met but attended a session of his at a Microsoft Publisher's Conference that kind of launched my blogging anti-career,) is a sanctioned technical evangelist who often is critical of his company. The other is the famed Mini-Microsoft blog who often skewers his company. Two days ago, Scoble put his take on Mini-Microsoft in a wonderful post How Microsoft can shut down Mini-Microsoft which was very honest and open.
My favorite quote was: "I don't think the way you deal with dirty laundry is to get rid of the person hanging the laundry in the public square that way. Deal with the folks who are dirtying up the linen!" That is really powerful if you think about it. But it is always a fine line between the squeaky wheel getting the grease and it getting replaced by a new wheel that hasn't yet learned to squeak.
So the question is, do I talk about my company? When we do things that are good? Or bad? I know for a fact that at least one of my superiors read this blog, one that I have great respect for (which is pretty much likely to be any of you that read this far in this post.) I would like to be able to mention best practices/snafus/mistakes/goofups etc, but no doubt the people being referred to would know who they were unless I was talking about myself (I am usually the person who makes a mistake that burns me!)
Opinions?
November 10 What is this carp?I have started seeing large numbers of wierd trackback entries, not from my fellow bloggers, but from commercial sites: h t t p : / / h y d r o c o d o n e - w i t h d r a w a l . u n d o n e t . c o m / g e n e r i c . h t m l
Of what value is this and how the heck do they do it? You would have to have a brain the size of a peanut to just click through to that (of course I did it to see where it took me, but the size of my brain has never been in question.) It is a link to some online drug company. Here is the question I have, and if it offends you, then please email me and we will discuss a bridge I have for sale in NY):
WHAT MORON WOULD PURCHASE MEDICAL SUPPLIES/DRUGS FROM A SPAMMER?
I wouldn't purchase a compact disc from a spammer, much less some chemical that will affect my health. Geez, this stuff could be made by some tweaker in a meth lab when business is slow. No reputable company would behave this way, not a moderately reputable company. I am all for purchasing stuff on the net, and fine with advertising on the web. There are some good things to be purchased, and I might even consider purchasing drugs (legal ones of course) from a website, but not when their idea of good advertising is spamming my trackbacks on a SQL Server (ish) based blog. Yeeks.
September 26 Unnerving error messages...I was checking my blog for comments (because I am one of the most optimisticly pessimistic people on earth!) this morning when I get the following error message from spaces:
There has been a catastrophic error. Please stand by.
Catastrophic? Yikes, they have been attacked by pirates! Or worse, one might imagine, a thermonuclear device has just hit the MSN building, perhaps? Nope, tried again and it worked....perhaps..Internal error, refreshing in 10 seconds would have been clearer? September 15 I love beta software...But sometimes I am not sure why! I sign up for every beta, and participate pretty well in most of them (some more than others of course, since I am a SQL Server kind of guy.) It can be pretty infuriating when your stuff doesn't work, but it is great to know how the new "thing" works long in advance of everyone else.
So why am I ranting about this tonight (so late too) Well, I have to install the latest CTP of SQL Server 2005 and I have been having devilish little problems with all the different versions of DLL's and software I have installed on my machine. No, I am not one of the "smart" ones who install beta editions on a virtual PC. I have production VPC's to support my DRP clients, but when it comes to beta software I ride the cutting edge all the way.
Oh well, SQL Server is installing now, so I can finally stop watching Celebrity Poker and go to bed... August 19 Identities, the ongoing debate...The big debate rages on:
The blog that apparently got some of this started
I am a big supporter of surrogate keys, as they give tables a very easy implementation pattern to deal with in code, both in SQL and in Client Code. It is always known that a table will have a single column, and how it will be used. This argument generally turns into an identity battle, but I don't see identities as the real problem. Identities are just a very easy to implement surrogate key. Keep in mind what a surrogate is. Surrogate does not mean identity, nor does it mean single column. From dictionary.com the definition is "One that takes the place of another; a substitute." It implies that the other exists. A surrogate mother couldn't be surrogate without the other person existing. Otherwise the person is just a mother.
So what is the big deal? I have argued with Mr Celko quite often about this subject in the newsgroups, as well as others and I can never get any answer other than they are phyiscal locators, a position Mr Preston also seems to hold. There is nothing truly physical about them other than how they are generated. Just because they make get used like this in the clustered index means didley. You can cluster on something else if you want to and still have an identity column. By this logic the clustered natural key is now a physical locator So? Every value has to come from somewhere, identities come from some magic built into SQL Server. Better they implement the magic than me. The funny thing is I agree with most everything that is said about having verifiable natural keys, I just think that having a surrogate key ALSO is incredible valuable.
Along these lines, absolute requirement for a table keywise is to have at least one UNIQUE constraint (PRIMARY KEY or UNIQUE) that logically makes a row unique. Far far too often the newbie mistakes an identity column for true uniqueness, and that is where things go downhill <b>fast</b>. For the people who might have read my book http://www.apress.com/book/bookDisplay.html?bID=266 (you know who you are) you probably got sick of me saying it. In the 2005 version http://www.apress.com/book/bookDisplay.html?bID=10005, due to a lot of talks like this I am a lot more open to either form of PRIMARY, use a natural key, use a surrogate key, whatever. Not that I was ever against it, I have just noticed that I was kind of insistant that one way is best. Clearly, in my mind, neither is wrong. Just don't allow duplicate rows amongst the logical attributes and everything will work itself out.
So why do I like identity columns? I will tell you:
* It makes a design cleaner and easier to follow - Say I use the SSN, lastName, firstName, birthDate as the key of my employee table (or even just SSN.) I then see these columns in another table, possibly the timeClock table, or the payroll table. First off, we have an awkward set of columns to deal with (and if they weren't foreign keys they would be a normalization issue) but there is nothing wrong with that. What I have never liked is that I have to have system knowledge now to know that these are the key of the employee table, rather than just floating columns. Looking for an employeeId to join to the employee table is far more natural.
* Performance of joins - 9 out of 10 times in production code you still have to make the join from parent to child to resolve other data. For example, payroll to employee to get some other bit of information about the employee, like his pay scale. So if joins will likely be necessary, we should optimize them. Instead of using a 30-50 byte variable width key, we have a 4 byte key. And due to the way Microsoft implements indexes, it also makes a great key to cluster on.
* Updates never need to cascade - Say I use a person's name in the key of a table, which is quite often a good thing to do. what happens when their name changes? Must we propagate changes all throughout the system? And what if it was a variable length column and the size of the data increased? We might have pages splits and rewrites of data going on everywhere. If we use surrogate keys, all updates of data change the data in one place, and logically the change propagates to all children
* concurrency - because identity values are doled out without regards to transactions, getting the next values is very fast and while all inserts are single threaded through this process, it is much faster to get a new unique value than checking the values in the table (where you often have to single thread at a query level.) Of course there are more clever routines for generating a unique value from a hash, or whatever, and they are still useful for building the natural key. However, if you want an increasing key, identities work very fast and support good concurrency.
* programming ease - all table look alike at a high level, so building procedures and client code is easier to generalize. I have thousands of lines of functional code built around generating code around the single column format. This mind you would not be a reason to use identities if the other reasons didn't exist. It is however a nice side effect.
The real thing is why not? It does not hurt anything to have a surrogate if you know what you are doing. In fact it most cases it helps.... August 10 Why lawyers should be repurposed as QA peopleWhen the flight attendant deliver our snacklets, one of them was a was a pack of peanuts that actually had written on it:
The product was packaged in a factory that handles peanuts and other nuts.
Okey doke. When this is written on a pack of cookies, or a carton of ice cream, good deal. Thank you for protecting our fine citizens from accidentally ingesting peanuts when they though they were eating peanut-free. But sweet googley moogly, where freaking else would I want my peanuts packaged? An auto parts plant? Chicken farm? The vineyards of Earnest and Julio Gallo?
So my point here is (as if there really is a good one) is that if lawyers have that kind of attention to detail to see the possibility of getting a settlement for a person accidentally eating peanuts from a package of peanuts, the surely could catch the case where a programmer forgets to put a begin - end in a scope block after an IF statement. Heck, they could probably figure out how to punish programmers for their innocent mistakes also. Either way, it would get them out of the business of stating the completely obvious or driving recklessly in the middle of the desert or not to ignore the laws of gravity or whatever. Either way, it is good to be home typing this in MY chair, watching MY tivo! July 30 My hotel's internet service really sucks this trip...So my posting this last week and next week will be slim. It is actually better for me because I can spend more time writing and less time surfing the web trying to find the next stupid thing to buy on Ebay or whatever, but it stinks when I need to do some research or blog, but I am getting a lot of needed work done too.
Either way, don't give up on me, I will be back in full force starting to look at all of the features of SQL Server that I have avoided up to now to keep into the database design features that are my favorite bits. I will also be blogging about the PASS conference soon, and will be part of a team of volunteers that will be implementing a new community website for PASS. June 19 Oh No! Tech Jobs are becoming extinct!edited. "become exinct?" I really shouldn't blog at night! According to USA Today and Gartner (http://www.usatoday.com/tech/news/2005-06-19-tech-jobs_x.htm) "The research firm Gartner Inc. predicts that up to 15% of tech workers will drop out of the profession by 2010, not including those who retire or die. Most will leave because they can't get jobs or can get more money or job satisfaction elsewhere. Within the same period, worldwide demand for technology developers — a job category ranging from programmers people who maintain everything from mainframes to employee laptops — is forecast to shrink by 30%. " Wow, if this is true, then we here in SQL land are in trouble. I think I will get out now and go get a "consulting" job, since, as the guy who is being interviewed (a 22 year old!) says that: "A consulting job injects you into companies at a higher level," he said. "You don't feel like you're doing basic stuff." I don't know about any of you, but I personally feel our industry will at least hold steady, even with the numerous jobs going offshore to parts of the world where people will work for nearly nothing. What is more likely to happen is that these folks will surf the web that they are working on and realize that they aren't getting paid enough, and that the companies that work locally will start to need more and more tech workers, especially since people who program all day are going to start to want this technology applied to their lives. Will there ever be a glut, sure. And we did grow way too fast a few years back and salaries grew way too fast when the stock brokers got involved and turned a great movement into yet another new way to cause a stock market to crash. In fact, I believe it is a very poor idea to even inject this kind of idea into the minds of our college students. Basically this guy is saying that he is going to waste his technical skills and become a manager. A manager "helping to lead projects at multinational companies." Yikes, anyone who feels that leading projects of any kind that is not relevated to producing technology of some sort never had his heart in it to start with. I figure that pretty much anyone who is sitting here writing a blog instead of sleeping, or you who is reading it instead of doing real work or spending time with their family would never give up the technology bandwagon and if you are like me, if I couldn't do this stuff for a paycheck, I would still be forced by sheer love of technology itself. Now I am driven by the screams of the bed that is just four feet behind me: May 17 Passport Rant!Am I the only one who has clicked the save my email address and password button like fifteen million times on all of the sites that I use that use passport as the authentication mechanism (and I generally like Passport!) It just drives me nuts when I am not connected to my fingerprint reader (if you don't have one, get one, they are really quite cool.) Anyhow, just felt like screaming and since my family is asleep, this was the next best way! April 15 "Tax Day" or "Anti-Christmas"Singing: "It's the least, wonderful time, of the year...!" If you are an American citizen, and you owe money on your taxes, you were probably busy today too, taking care of the final bits and pieces of the puzzle, squeezing it for all it is worth (I found another $150 I missed, so that ain't bad.) Waiting to the last minute to file your taxes is not only your right, but darn it, it makes it more fun. All that is left is to visit another Government agency and ask them to put your tax envelope on the back of the slowest donkey they have. I mean really, once the postmark is on there, if the check doesn't arrive for six months you will still be fine (as long as you have proof of course, because eventually they will come for you. So, what do you think about getting money back or having to pay money on April 15? I like to owe a small amount (so I most certainly did not like what I had to do tonight!) so as to maximize my usage of my cash for the year. Some people like to use it as a vacation fund. I see both ways having good sides and bad sides. Please share your opinion, or don't because either way I am going to keep writing, but it would be more fun if I wasn't always talking to myself (I get enough of that in my office all day!) April 05 I have to leave the office, I am feeling SCRUMmy.Ah, I had gone like a year or so since the last management fad came along that will revolutionalize my work week. Today we are to be introduced to the concept of the Scrum. What the heck am I talking about? I don't 100% know myself, but from http://www.controlchaos.com/, we get the following: "Scrum is an agile, lightweight process that can be used to manage and control software and product development using iterative, incremental practices. Wrapping existing engineering practices, including Extreme Programming and RUP, Scrum generates the benefits of agile development with the advantages of a simple implementation. Scrum significantly increases productivity and reduces time to benefits while facilitating adaptive, empirical systems development." We shall see. Generally speaking management techniques are all just a way to stir the pot and try to make people feel like they are doing something new. The waterfall process is still the same process it has been forever. Sometimes we take smaller bites, and sometimes larger, but it is still the same amount of work! I will keep you up to date on how our scrummines comes. March 15 Why you should run grammar checker when you rant!I should have proofread my comments I sent to sswug yesterday. Oiks I made a few grammatical errors that make me sound like I have a third grade edumacation. Well, at least my book has editors to fix this, I stand by the comments :) If you want to see it in all it's glory, check here. It is also posted in yesterday's blog, but I may have changed the grammar by now. I fixed a few minor things when I originally posted. March 11 The wireless internet in my hotel was down all dayI am at a conference, and it was down for a lot of folks. So I figured I would call tech support and get their help getting is straight. Wow, that was a mistake. I talked to two people who were slightly less helpful than bashing my head into a wall. They had me rebooting, restarting, clearing files, pinging stuff, and finally their advice: get your winsock reset. Guh? I have been working on wireless technologies for eight years now. I paid like 700 bucks for my first wireless access point and router (I paid 30 for my latest wap!) and I have never had this many problems. Strangely enough, this morning it works. Go figure. March 03 Garbage in, garbage outActually this is more of a Gadget Rant than a pure rant, because I am mad at my Tivo. It recorded Clean Sweep instead of Lost. What the devil was it thinking? Shouldn't it have just changed channels out of pure taste. Sure, I forgot to order my priorities, and I forgot to check it, and I forgot to make sure and record it on my Media Center PC, just in case. It is the true problem with computers. They don't think. They just do what we tell them. When will this change? I want my computer to see that I record Lost and watch it on the same night. Then, when it is going to not be recorded, send me an email. "Hey, doofus, you are about to record a mind numbing show your wife likes instead of a great show she also likes. And if you miss Lost she will kick you, and how." Oh well. I am downloading the file right now from TV Torrents. I hope it is not illegal. If they include commercials I cannot see why it would be. I actually would prefer it. It is good to have commercials on new shows because I want to see some movie trailers, and some car commercials. Of course I fast forward through most of them. I would like to not have commercials on late night TV. When I think of girls going wild I would prefer to think of one of the ladies at church putting extra sugar on the sugar cookies at Christmastime. February 04 Why does this burn me up so much?As I recently became an RSS head, one of the feeds I subscribed to was Slashdot. Some good information, but some pure idiotic drivel like this (actually the article that it is linked to is really barforistic: http://slashdot.org/article.pl?sid=05/02/04/1947224&from=rss You know, I have been a power user of Windows since the 1.0 product (it was fun!) and I have only gotten one or two viruses. I have had very few crashes, and very few troubles. Heaven knows I am not really a safe computer. I do have a firewall now because of my router, but it is rudimentary. Next week I will spend a week on a hotel lan with no real protection except what Windows gives me, and it will certainly not be the first time. Then the comments on Slashdot get stupid: "Microsoft have repeatedly threatened & upped the price on vendors who sell machines without the OS pre-installed. " Upped the price for the OS? Then why don't they find a replacement? Like Linux? People desire ease of use far too much for that. The problem of course is just like the analogy of the car used by the author of the article, but he gets it wrong: "Here is your brand new car, sir. Drive it off the lot. Yay yay new car. Suddenly, new car shuts off. New car barely starts again and then only..." If you try to drive the car in an unsafe way, your car will die. You have to do maintainence to a car, just like a computer. Clearly the real problem is that a great number of Windows users are not power users. They are average people who, when a window pops up saying your computer might not be protected, they get scared and click the window. Or when they get phished and give up their personal information to a scumbag, they don't even realize it. So we can have hard to use, small target OS's or easy to use, powerful, OS's that take a little bit of knowledge. Perhaps Microsoft will come out with the: "If you know nothing about computers and want to be safe OS that will strip out all of the powerful goodies that most of us love" Who knows, it could just be me! |
|
|
%3a+Rant!&referrer=){kind=small}
