Tag Archives: SQL

All set to think

Posted on by
Reply

A short post today.

This past weekend I attended another SQL Saturday event. This one in Washington DC.  For anyone interested in SQL Server, I highly recommend these events. Typically the cost is just $10 to attend for the day.

I had put in a bid to present but unfortunately was declined.  That’s fine, there were plenty of other seminars worth my time to attend.

One of them, Common Coding Mistakes and how to Mitigate Them by William Wolf was a good example of that. @SQLWareWolf did a great job of illustrating a number of fairly common mistakes.  That itself was worth the price of admission. But what I really enjoyed was an observation of his, one that I’ve had in the past.  As a DBA, I can often spot stored procs written by a programmer with a non-SQL background.  The tell-tale is that many (certainly not all) programmers who come to SQL from another background often think in terms of rows, not sets.

An example of this would be the case of a programmer opening up a cursor and looping through the cursor to set the date on a number of records.  Of course for any person with a SQL background, we understand that can be a single statement which will execute far faster.

Now, there are certainly times when row by row is the only way to do it, but if you see that in a sproc or script, I’ll bet you 5:1 it was written by a programmer who didn’t know better.

Now, to be fair as a DBA who learned to program when “object oriented” wasn’t a buzz-word, I’ve been doing a lot of VB.net programming lately and I have to admit often I’ll find myself down a hole of twisty paths before I’ll realize I’m writing bad code and if I simply think of the data I’m trying to modify as an object, suddenly things get FAR more clear and easier.  I would not be surprised if 5 years from now the next programmer comes along, looks at my code and thinks, “What was he thinking? He obviously understood objects, but didn’t do everything he should have using objects.”

But in both cases, over time the non-SQL programmer coming to SQL will learn and I hope my OO code is slowly getting better.

No real moral here other than common observations and that hopefully overtime, we all improve.

Think in Russian

Posted on by
Reply

In the classic Cold War thriller, Firefox, Vietnam veteran Mitchell Gant has to steal the top-secret Soviet Union, titular plane.  Among its advanced features is that the systems are controlled by thought.  But only if he thinks in Russian.  This becomes a key plot factor in the climax of the film.

In my last post ; I remarked how many bad solutions I had found to sorting a SQLDataconnection Gridview.  Well I’m happy to say I solved my problem last night. (And as an aside, I will not be posting the solution at this time because while the solution itself is decent, I’m not sure the code is the best.)  Part of my solution was solved by “thinking in Russian”.

As I had mentioned, some of the so-called solutions to this problem that I had found on-line were pretty bad.  In one case the author apparently decided the easiest solution was to decipher the viewstate, extract out the information, sort it and stuff it back into the viewstate.  Now, as I’m writing this, I realize one advantage this has is that it removes a roundtrip from the web server to the database server.  But that’s about it.

I also saw a solution that involved passing the column name back to the code-behind and having that decide which of multiple stored procs to call.  I can’t say I favored this approach since it basically means a lot more maintenance if you ever say want to add a column to your Gridview (which turns out I decided afterwards I may want to do.)

Even worse, I’ve seen people propose things like building the select string on the fly and I can’t even begin to say how bad of an idea that is.

That said, the more I thought about it, the more I realized what the “right” solution was.  Rather than fighting the system, I had to think like the system.

So, after a side trip down to trying to use SqlDataAdapter, I went back to my first approach of using a SqlDataReader.  However, based on one example I saw, I decided to move this to a function of its own.  This, if nothing else resulted in cleaner code (since I was already calling the SQLDataReader in two places (see Rule of Three).  Once I did this, it was a little matter of figuring out how to bind the SqlDataReader to a Datatable and returning that.

Then I could bind the datatable directly to the GridView if I wanted to (which I do on the original Page_Load and Click_Submit OR I  could in the _Sorting event bind it to a local Datatable, sort that and then bind the resulting sorted Datatable to my Gridview.

Worked like a charm.  Well except for one little detail.  And this one I’m still not sure if it is a MSFT bug that lives on for backwards compatibility or I and MANY other developers are doing something wrong, but the GirdView incorrectly will always return “descending” for its sort direction.

So in this case the common (still not convinced it’s the RIGHT or BEST solution) is to stuff a variable in the Viewstate and read that back every time sorting is called and reverse it as needed.

Once I did that, I had working code that could sort my Gridview on the selected column that was clean, easily reproducible and made sense.

I’ve found with my forays into .NET Framework and VB programming that if my initial approach appears overly complicated or just plan wrong, it probably is.  So far in pretty much all cases, I’ve found that if I stop and try to “think in Russian” the solution will appear to me and is generally fairly straightforward and looks right.

Years ago when I studied Latin, I reached the point where I could read Latin natively.  I loved it. But part of the switch is being able to think in the structure of the language.  Not all languages use “SVO” (Subject-Verb-Object) order like English.   Latin, “SOV” (Subject-Object-Verb) order uses.  It takes some getting used to. But once you accept it, things get easier.

So I can’t fly a Mach 6 stealth aircraft, nor do I speak Russian, but I’m starting to think in VB. (Or is that I’m in VB starting to think?)

Order of the day

Posted on by
2

I’m in the middle of designing a db schema for a project I’m working on.

As I was sitting in my car, waiting for the light to change, I remembered a mantra of SQL design.  And what is funny is I often see this fundamental aspect overlooked or even in at least one case, intentionally ignored: namely tables do not have any intrinsic order.

Almost all of my experience has been using MSFT SQL Server, so my thoughts will be based on that, but the general idea is true.  Beginners to SQL will assume “if I put the data in in order, it will come out in order.” Now if they’re a bit more than a beginner, they’ll even test that.  And lo and behold, they’ll probably find their assumption is right when they test it.  Then they’ll put the code and schema into production and find that suddenly things aren’t working right.

They’ll wonder why it worked on their machine, but not on production.  Generally there’s two reasons.  SQL Server is very aggressive about caching, so there’s a chance it’ll read the data out of memory in the order they put it in.  In addition, if it does have to read it off a disk, it’ll most likely do it in a single-threaded manner, following the primary key and the data will come out as expected.

On production though, between the time the data is inserted and later read back, the cache may look very different.  But even more so, a production machine is liable to have multiple disks and multiple CPUs which means multiple read threads will occur and SQL Server will then put the data together in the faster way possible.  Suddenly it’s no longer in the order the programmer wanted it or expected it.

Now, if you’re writing a quick ad-hoc query to get some data out quickly, that’s probably ok.  I’ll admit I do a lot of queries without an order by when I just need to quickly get some data.  But if it becomes time to productize the code, I’ll use an order by.

As I mentioned at the top of this post, the lack of an Order By seems to be a fairly common mistake made by folks new to SQL programming.

But what about the case when it’s intentional?  Years ago we were rolling out some new code and in the code there was a query that did a query against a table.  In theory the table would only ever have one row.  The programmer decided with one row no order by was necessary.  However, as always theory and practice don’t always match and I asked what would happen if there was more than one row?  His answer was to use a “TOP (1)” in the query.  So I asked him what would guarantee he’d get the top row he wanted.  He said he didn’t think the problem would ever arise and resisted using the ORDER BY “for performance” reasons he said.

Well since I wasn’t his manager, I wasn’t about to fight this particular fight.  But I did make a note of it.

Sure enough, about two years later (a year after he had left the company) the page that used this query started to return the wrong results.  A quick look and a quick addition of an ORDER BY and all was well.

It’s always the little things.  And that’s the order of the day.