Sunday, March 9, 2008

Introduction To Queries

This is the Database Programmer blog, for anybody who wants practical advice on database use.

There are links to other essays at the bottom of this post.

This blog has two tables of contents, the Topical Table of Contents and the list of Database Skills.

Today we are going to look at performance, direct SQL coding, and then begin with the basics of the SQL SELECT command.

Disk Activity Determines Performance

Before we even look at the SQL SELECT command, we must know what motivates the experienced database programmer to pick certain kinds of queries and avoid others. The first motivator is of course performance. We all want our programs to go fast. If you want a fast database program, then you have to think about the hard disk.

The slowest device in a computer is the disk drive. The price of disk reads is so much higher than in-memory operations that there is nothing to gain by optimizing code unless disk reads are optimized first. I often tell my programmers to consider in-memory operations to be "free" when coding, and to concentrate all optimization efforts on reducing disk reads. This approach may gloss over some important truths, but it is an excellent starting point for database beginners.

Optimizing disk reads comes down to writing efficient queries on top of well-designed tables. With that being said, we have one more short note to cover before going into the syntax of the queries.

A Quick Note on Inline SQL

Many authors and framework programmers discourage the use of SQL SELECT statements directly in application code. This essay contains no opinions on that question.

However, it is important to know that the SELECT statement must be coded somehow and sent to the server, whether you code it manually or some framework tool generates it for you. This essay is all about how to code (or generate) that SELECT statement. Knowing how it all works is a requirement if you code your own framework or if you suspect your chosen framework may not be your friend in all cases.

Introducing SQL Select

The simplest query is four words (or symbols) long. If your database has a table of countries then here is a very simple query that will work:


This query will return all rows and columns from a table. Depending on what language you code in, these may come back in an associative array, an array of generic objects (or similar), or some other special-purpose object like a ResultSet.

You have probably been told not to use the "*" in a SELECT, for performance reasons. This is usually good advice. In the simplest case, you save bandwidth by only retrieving the columns that are of interest to you. If your table contains long varchar or text (aka clob) columns there are even more reasons to avoid "SELECT *". When you have long varchar and text columns, they may be stored outside of the main storage for the table, causing the server to look in two places to retrieve each row. Therefore, avoiding "SELECT *" and always specifying just the columns you need reduces disk reads on the server and reduces bandwidth delivering the results.

But as this is an introductory essay it is important to know how to retrieve a complete table, so I have used the "SELECT *" here.

Filtering Results with WHERE

The WHERE clause limits which rows from the base table will go into the query results. You specify a WHERE clause as one or more boolean conditional expressions. Multiple expressions can be separated by AND and OR, using parentheses to group expressions. You can review your product's documentation to see all of the comparisons and functions that are available. A moderate WHERE clause might look like this:

SELECT country,name
 WHERE country like 'A%'
   AND (   name like 'D%'
        OR name like 'E%'
   AND continent = 'Africa'

Filtering and Performance

The primary purpose of a WHERE clause is to obtain the correct result. However, it is also a very important performance tool. Here is why.

If you are completely and totally new to database programming, you may get the idea that you will skip the WHERE clause and do your filtering in the application. This may seem like a good idea because you save the trouble of learning two languages. Instead of learning SQL plus your application language, you can concentrate on just your application language. And so you make a reasonable decision to use as little SQL as possible and just do everything in application code.

The drawback to this perfectly reasonable suggestion is that it violates our first performance concept, it creates a huge disk read burden. If you need five rows out of 50,000, then filtering in the application requires the database server to read all 50,000 rows off the disk. On top of that, these have to be delivered to your application for processing.

Making use of the WHERE clause means that only 5 rows are read off the disk (this assumes the presence of an index which will be explained in a later essay). In this particular example, using a WHERE clause will perform 1000 times faster than not using it. Of course this is only a single very vague example, but since a database application is composed largely of queries, it is definitely a good idea to have all of these queries start out on solid ground.

Foreign Keys and JOIN

Next week we are going to look at JOINs in much detail, but I want to mention them here briefly. The JOIN clause lets you return results from more than one table, and the JOIN determines how the rows from multiple tables will be matched to each other.

For this week I will say only that good queries will almost always use foreign keys as the basis of their JOINs. We have seen in these essays more than once that the foreign key is the fundamental and only way to connect information in separate tables. Naturally, therefore, the foreign key will loom large in our discussion of JOIN, since JOIN controls the combined retrieval of information from separate tables.

Sorting Query Results

You can sort query results by including an ORDER BY clause in the query. Simply name the columns:

SELECT customer,order,date 
 WHERE date >= '2008-03-01'
 ORDER BY date,customer

Some database servers let you put an ASC or DESC in front of individual columns, while other servers can only apply a DESC or ASC term to the entire sort operation.

Overall application performance comes into play with ORDER BY clauses. It is almost universally true that you can sort faster on the database server than you can in your code. You want to make sure that your manual queries contain ORDER BY clauses, and that your framework is generating them. You do not want to be sorting in application code in most cases.

Order of Terms Matters

You have to put the various clauses into the right order or they will not work. The order is:

  FROM ....
  JOIN .... ON ....
 WHERE ....


This week we began to examine queries, by looking at the very basics of the SQL SELECT query syntax. Not surprisingly, performance issues came up for every single part of the query, from the column list to the ORDER BY.

For performance, we looked at the basic idea that disk reads determine performance, which we will see more of in later weeks. I also mentioned that table design determines query efficiency, but we have not gotten very deep into that yet.

Related Essays

This blog has two tables of contents, the Topical Table of Contents and the list of Database Skills.

Other essays relating to SQL SELECT are:


Bruno Martinez said...

I hope I'm not spamming the comments today :)

I didn0t understand "However, there are other cases, especially when using varchar or text (aka clob) columns, when there are fewer disk reads when you avoid the columns you have no use for." How come you avoid columns with *?

KenDowns said...

Bruno, I rephrased the statement in question to make it clearer. The problem with long varchar and text columns is that they are stored outside of the main table storage area, forcing the server to go to two separate spots on disk. If you habitually do SELECT * you will force the reading of lots of data you probably don't need, on top clogging the pipe to deliver it to you.

JAYESH said...

awesome guidelines... THANK'S..

Dwarakesh babu said...

Hello. This post couldn’t be written any better! Reading this post reminds me of my previous roommate. He always kept chatting about this. I will forward this page to him. Fairly certain he will have a good read. Thank you for sharing.
AWS Training in Bangalore |Best AWS Training Institute in Bangalore BTM, Marathahalli
AWS Training in Chennai | AWS Training Institute in Chennai Velachery, Tambaram, OMR

sathya shri said...

The knowledge of technology you have been sharing thorough this post is very much helpful to develop new idea. here by i also want to share this.

angularjs Training in bangalore

angularjs Training in btm

angularjs Training in electronic-city

angularjs Training in online

angularjs Training in marathahalli

mouni yoga said...

This is ansuperior writing service point that doesn't always sink in within the context of the classroom. In the first superior writing service paragraph you either hook the reader's interest or lose it. Of course your teacher, who's getting paid to teach you how to write an good essay, 
online Python certification course | python training in OMR | Python training course in Chennai

sarah blossom1234567 said...

Great thoughts you got there, believe I may possibly try just some of it throughout my daily life.
Devops training in sholinganallur
Devops training in velachery
Devops training in annanagar
Devops training in tambaram

Chitra devi said...

A very nice guide. I will definitely follow these tips. Thank you for sharing such detailed article. I am learning a lot from you.

rpa training in electronic-city | rpa training in btm | rpa training in marathahalli | rpa training in pune

janani said...

Some us know all relating to the compelling medium you present powerful steps on this blog and therefore strongly encourage contribution from other ones on this subject while our own child is truly discovering a great deal. Have fun with the remaining portion of the year.

Java training in Annanagar | Java training in Chennai

Java training in Chennai | Java training in Electronic city

geetha sridhar said...

After reading your post I understood that last week was with full of surprises and happiness for you. Congratz! Even though the website is work related, you can update small events in your life and share your happiness with us too.

Data Science course in kalyan nagar | Data Science course in OMR

Data Science course in chennai | Data science course in velachery

Data science course in jaya nagar | Data science training in tambaram

Ananya Krishnan said...

DevOps is currently a popular model currently organizations all over the world moving towards to it. Your post gave a clear idea about knowing the DevOps model and its importance.

Good to learn about DevOps at this time.

devops training in chennai | devops training in chennai with placement | devops training in chennai omr | devops training in velachery | devops training in chennai tambaram | devops institutes in chennai | devops certification in chennai | trending technologies list 2018

Anbarasan14 said...

Your blog is nice. I believe this will surely help the readers who are really in need of this vital piece of information. Thanks for sharing and kindly keep updating.

IELTS Coaching in JP Nagar Bangalore
IELTS in JP Nagar
IELTS Classes in JP Nagar
IELTS Training Institute near me
Spoken English Classes in JP Nagar
English Speaking Course in JP Nagar Bangalore
Spoken English Classes in Bangalore JP Nagar

dhivya said...

In the beginning, I would like to thank you much about this great post. Its very useful and helpful for anyone looking for tips. I like your writing style and I hope you will keep doing this good working.
Angularjs Institute in Bangalore
Best Angularjs Training in Bangalore
Best Institute For ccna Course in Bangalore
Best ccna Training Institute in Bangalore

Anexas Europe said...

The post was amazing. It showcases your knowledge on the topic. Thanks for Posting.
CPHQ Online Training in Kabul. Get Certified Online|
CPHQ Training Classes in Al Farwaniyah

Rithi Rawat said...

Very nice post here thanks for it .I always like and such a super contents of these post.Excellent and very cool idea and great content of different kinds of the valuable information's.
machine learning course in Chennai
machine learning training center in Chennai
machine learning certification in Chennai
Android training in chennai
PMP training in chennai

Unknown said...

Thank you for excellent article.

Please refer below if you are looking for best project center in coimbatore

final year projects in coimbatore
Spoken English Training in coimbatore
final year projects for CSE in coimbatore
final year projects for IT in coimbatore
final year projects for ECE in coimbatore
final year projects for EEE in coimbatore
final year projects for Mechanical in coimbatore
final year projects for Instrumentation in coimbatore

IT Tutorials said...

Really useful information. Thank you so much for sharing.It will help everyone.Keep Post. RPA training in chennai | RPA training in Chennai with placement

Aman CSE said...

One of the best content i have found on internet for Data Science training in Chennai .Every point for Data Science training in Chennai is explained in so detail,So its very easy to catch the content for Data Science training in Chennai .keep sharing more contents for Trending Technologies and also updating this content for Data Science and keep helping others.
Cheers !
Thanks and regards ,
Data Science course in Velachery
Data Scientists course in chennai
Best Data Science course in chennai
Top data science institute in chennai

sasireka said...

I recently came across your blog and have been reading along. I thought I would leave my first comment.

devops online training

aws online training

data science with python online training

data science online training

rpa online training

sai ram said...

The site was so nice, I found out about a lot of great things. I like the way you make your blog posts. Keep up the good work and may you gain success in the long run.
Microsoft Azure online training
Selenium online training
Java online training
Python online training
uipath online training

Anjali Siva said...

The given information was excellent and useful. This is one of the excellent blog, I have come across. Do share more.
Data Science Training in Chennai
Data Analytics Training in Chennai
Data Science Certification in Chennai
Data Science Training in Velachery
R Training in Chennai
R Programming Training in Chennai
Machine Learning Training in Chennai
Machine Learning institute in Chennai
Data Science Course in Chennai