Problem solve Get help with specific problems with your technologies, process and projects.

The SELECT command

There are a number of points to remember when building efficient queries. Read about them in this book excerpt.

The following tip was excerpted from Chapter 8, 'Building Fast-Performing Data Models,' from the book "Beginning Database Design" by Gavin Powell, courtesy of WROX Publishing. Click here for the complete collection of book excerpts.

The SELECT Command

The SELECT command is used to query the database. There are a number of points to remember when intending to build efficient queries:

  • Querying all fields: Retrieving specific field names is very slightly more efficient than retrieving all fields using the * character. The * character requires the added overhead of metadata interpretation lookups into the metadata dictionary—to find the fields in the table. In highly concurrent, very busy databases (OLTP databases), continual data dictionary lookups can stress out database concurrency handling capacity. Consider the following query:

    SELECT NAME FROM AUTHOR;

    This is faster than this query:

    SELECT * FROM AUTHOR;If there is an index, use it. Reading field values directly from an index without reading a table at all is faster because the index occupies less physical space. There is, therefore, less I/O activity. In the ERD snippet shown in Figure 8-1, reading the EDITION table, with the following query should be able to force a direct read of the index because primary keys are automatically indexed. The ISBN field is the primary key for the EDITION table.

  • Reading indexes:

    SELECT ISBN FROM EDITION;


    Figure 8-1: Reading indexes instead of tables.

    Not all database engines allow direct index scans, even when a SELECT command might encourage it.

    Simple aliases: Shorter alias names can help to keep SQL code more easily readable, particularly for programmers in the future having to make changes. Maintainable code is less prone to error and much easier to tune properly. Consider the following query:

    SELECT A.NAME, P.TITLE, E.ISBN
    FROM AUTHOR A JOIN PUBLICATION P USING (AUTHOR_ID)
    JOIN EDITION E USING (PUBLICATION_ID);

    This is much easier to deal with than this query:

    SELECT AUTHOR.NAME, PUBLICATION.TITLE, EDITION.ISBN
    FROM AUTHOR JOIN PUBLICATION USING (AUTHOR_ID)
    JOIN EDITION USING (PUBLICATION_ID);

    Why? There is less code. Less code is easier to handle. Less is more in this case.

    The above tip was excerpted from Chapter 8, 'Building Fast-Performing Data Models,' from the book "Beginning Database Design" by Gavin Powell, courtesy of WROX Publishing. Click here for the complete collection of book excerpts.

  • Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

    Start the conversation

    Send me notifications when other members comment.

    Please create a username to comment.

    -ADS BY GOOGLE

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close