Unnecessary database run-time processing

Use a fully qualified table name (including database name) in your SQL statement.

Always explicitly specify the database name along with each table name in a statement. (Use “main” if it’s the main database). For example, the following code includes an explicit database name main:

SELECT employeeId 
FROM main.employees

Explicitly specifying the database name prevents the runtime from having to check each connected database to find the matching table. It also prevents the possibility of having the runtime choose the wrong database. Follow this rule even if a SQLConnection is only connected to a single database. Behind the scenes, the SQLConnection is also connected to a temporary database that is accessible through SQL statements.

Use explicit column names in SQL INSERT and SELECT statements.

The following examples show the use of explicit column names:

INSERT INTO main.employees (firstName, lastName, salary) 
VALUES ("Bob", "Jones", 2000) 
     
SELECT employeeId, lastName, firstName, salary 
FROM main.employees

Compare the preceding examples to the following ones. Avoid this style of code:

-- bad because column names aren't specified 
INSERT INTO main.employees 
VALUES ("Bob", "Jones", 2000) 
     
-- bad because it uses a wildcard 
SELECT * 
FROM main.employees

Without explicit column names, the runtime has to do extra work to figure out the column names. If a SELECT statement uses a wildcard rather than explicit columns, it causes the runtime to retrieve extra data. This extra data requires extra processing and creates extra object instances that aren’t needed.

Avoid joining the same table multiple times in a statement, unless you are comparing the table to itself.

As SQL statements grow large, you can unintentionally join a database table to the query multiple times. Often, the same result could be achieved by using the table only once. Joining the same table multiple times is likely to happen if you are using one or more views in a query. For example, you could be joining a table to the query, and also a view that includes the data from that table. The two operations would cause more than one join.