The Implications of Excellent Coding: Part 1 – Best Practices

In this two-part podcast Rob Steward explains the difference between good and bad coding, and how good coding can benefit your ODBC, JDBC, or .NET applications.

Part 1, which runs 7:05, focuses on coding best practices, while Part 2 provides real-world examples of the impact that good coding can make.

Click on the following link to listen to the podcast: http://dataaccesshandbook.com/media/RobSteward_GoodCoding_1.mp3

Rob Steward:

Great question, and there are large segments of the book devoted to answering that questions. Specifically, there are three chapters of the book, one for ODBC, one for JDBC, and one for ADO.NET that are devoted solely to good coding techniques. There are a lot of coding samples in there, there’s a lot of explanation of why one thing is good verses bad. I’ll give you a couple of examples of what we talk about in the book.

For example, with any of those APIs that I just mentioned, ADO.NET, ODBC or JDBC, there are multiple ways to execute a statement. For example, in ODBC, you could call a function called SQLExecDirect, or you can call SQLPrepare followed by SQL Execute. Now most people do not understand when is the write choice to use one or the other of those methods. There are advantages, particularly performance and scalability advantages, to picking one verses the other. In the book we talk about this, when do we choose one or the other, but I’ll walk through that one example here.

Prepare and Execute actually is very different than Exec Direct. I can execute the same statement either way. It’s going to work either way, but depending on what I want to do one is going to work much better than the other. So Prepare and Execute, two APIs that I have to call rather Exec Direct.

I should use Prepare and Execute anytime I have a parameterized SQL statement that I intend to execute more than once, and here’s why: Because when I call Prepare, what I actually do, I incur an extra round trip to the database server. So it’s an extra command that I send up there, I do the Prepare, it sends the SQL statement. What it tells the database server to do is generate an execution plan. Now an execution plan on a database server is really – when it goes through your SQL statement and you may do something like ‘select * from table where last name equals Steward.’ What an execution plan would look like for a statement like that is sort of a structure that says, okay, the tables involved in this SQL statement are whatever table names (employ table names), and the columns in the where clause are last names. And I know that there is an index built on last names, so I store that information to say okay, when I evaluate and I look for where last name equals Steward then I’m going to use X index. And it loads this thing in the memory and it holds onto it. So that when I actually go to execute that statement, it doesn’t have to parse my SQL anymore; it’s already done that, and it already knows which indexes to use, and which table to load into the memory, and which segments those indexes may need or may not need, and whether it needs to make temporary tables, and all kinds of things like that are in the execution plan. That takes some time to generate.

When you do a Exec Direct what that does, and the driver sends that across the Exec Direct, then the database will execute your SQL statement, evaluate it, generate the result set for it, and then toss out that execution plan. When I do a Prepare and Execute, once I’ve done the execute it generates the results, but it doesn’t toss out the execution plan. In an example like I just gave you, a pretty simple SQL statement, select * from table where last name equals Steward, even in that case the generation of the execution plan is actually going to take significantly longer than generating the results set.

So if I generate it once, that same statement, I’m actually better off because I saved the database roundtrip by using Exec Direct, because I don’t need the database to save that execution plan. I’m not going to execute the statement again. Now if I want to do that execution over and over, then I probably want to use Prepare and Execute. Because I do that initial prepare, the database spends all time generating the execution plan, but when it’s done executing my statement, it holds onto it. And it knows that when it sees that statement again, ‘I don’t need to go back through that logic and figure out the indexes and the tables and all that other stuff, I already know what it is.”

Now, I said at the beginning parameterize SQL. So instead of where ‘last name equals Steward,’ I would have, ‘where last name equals ?’. And then I would bind those parameters. I would bind that parameter value to some buffer in my application. In the case of JDBC or ADO.NET it would just be some string object that is bound to that ? or to that parameter within the SQL statement. Instead of literal value, so instead of using a literal value, so instead of saying where last name equals ‘Steward,’ I want to use parameter markers. Prepared statements in databases within their execution plans understand that there is no difference as far as what tables are involved and indexes, and the parsing of the SQL, and the data types and things involved, there’s no difference regardless of what that value is. So it can be Steward, it can be Smith, it can be Jones, it doesn’t matter, the execution of the SQL statement is the same. So if I’m going to execute it over and over, I want to put parameter markers in there, and I want to use Prepare and Execute. That’s just one tip. There are many of these things, and many tips covered within the book where we say, ‘here’s a choice that you can make, this choice is better for these situations and this other choice is better for these other situations.’