Friday, April 07, 2006

Why SQL programming is so important to a CF Developer?

Right now I am listening to the ColdFusion Podcast, and I have to strongly agree with them, on the importance of learning to write correct and scalable sql code.

There are many different databases, and most cfers, start with access, which is great for beginners or very low traffic sites.

However if you want to build something that has to be able to handle lots of transactions.

Now the first part is to understand what causes blocks of performance?

Well for first off, any time you interact with the database with a cfquery or cfstoredproc, you are creating seperate blocks of memory, that need to be processed.

So how many cfqueries and correctly written queries is what really matters.

When you write the queries, identify the columns of data that you want to bring back. And Only have those columns in your select statement. In addition, having the columns in the correct order, as they are created in the tables, helps for performance...

Learn your table structures, make queries that work with the grain of their structure instead of warping it, making it go thru extra processing just because you were too lazy to put things in the correct order....

Now if it is just one table in the query, there are limits to what more you can do, to improve performance...

But with joins, you can take the query from the cfquery to a view or stored procedure. Views are usually the easiest, to create, from within sql enterprise manager.

Now when you run the page you can identify the performances of each cfquery, and over time you will get a hang for what query is causing problems or not...

For example if you are looping and doing a cfquery within that loop. That is actually causing a performance problem. The better approach is to combine the top query with the query in the loop, so that it is one connection with the server instead of looping.

Part of the problem is having the right tools and using them correctly...

But this all that i have time to discuss, please ask any questions. I'd be glad to help.

3 comments:

  1. Anonymous12:12 PM

    No offense, but your post is totally wrong and ubsurd. Obviously you have never designed an enterprise database before. It doesn't matter what order your setup your query in, it matters about the indexes on the tables, the triggers on the tables, the execution plan being run behind the stored procedure, the disk access speed, the memory available to the server, in short (too late) more stuff then you can possibly imagine.

    I bet a bunch of programmers are sitting there talking about database design in that podcast, because obviously not self respecting DBA would say something like that.

    This is why there are programmers and DBAs and they're seperate professions.

    Sorry this post sounds like a flame, but your post needs to be corrected.

    ReplyDelete
  2. Actually i have done a few enterprise databases, whether that be in oracle and in sql server.

    All the things that you said do matter. But it also matters the sql code you use in the queries.

    But unfortunately most companies do not have both positions. And most developers have to wear both hats.

    But my point is to encourage better sql writing, I do not claim to know it all.

    I was just enthused about that podcast, that it was encouraging coldfusion developers to write better sql code and work with their databases better...

    Isn't that important?

    ReplyDelete
  3. indexes. indexes. indexes... and always thinking about speed considerations when designing your schema.

    ReplyDelete