How does table alias names affect performance?

Question

While reading about tuning SQL queries, I read somewhere that 'Always use table alias and prefix all column names with the aliases when you are using more than one table.'

How does table alias names affect performance? Or Do they actually affect?

Answer 1

The alias doesn't affect performance in any practical or measurable way at all (italics added on edit). That is, it would add a barely (if it all) measurable delay to query compilation. Once compiled (and re-used), it has no effect.

An alias removes ambiguity when you have more than one table because you know which table it comes from. It also prevents future table changes from breaking a query. Say, you add an audit column to one table where it already exists in another table. Queries without aliases using both tables will break.

An alias is also mandatory in some cases e.g. schema bound views.

The SQL parsing engine (that reads all queries before executing them, and uses the information to cache the compiled queries in the future for faster execution) is the only thing that looks at the aliases, and uses it to help remove ambiguities in symbol lookups. The system would already produce symbols, just like any other compilable statement in any other language, when it's being parsed prior to execution-storage.

Answer 2

Almost not at all, the performance impact is negligible, but you'll have a much better time reading the query. It's just for your convenience.

The performance impact is allocating a few kb of memory to store alias names, etc. in the SQL Server program itself. Compared to the rest of the operations needed to execute your query, this is almost nothing.

Answer 3

I have experience with alias the query take significantly more time compare to without alias.

I have experience this with PostgreSQL, my query are following.

Without Alias

select
    applicant.application_id,
    form_data,
    application_defect.defect_id
from
    applicant
INNER JOIN
    audit_trail on applicant.email = audit_trail.email
INNER JOIN
    application_defect on applicant.application_id = application_defect.application_id
where
    application_defect.defect_id like '1%'
and
    form_data like '%FaceApi%';

With Alias

select
    ap.application_id,
    au.form_data,
    ad.defect_id
from applicant ap
INNER JOIN
    audit_trail au on ap.email = au.email
INNER JOIN application_defect ad on ad.application_id = ap.application_id
where
    ad.defect_id like '1%'
and
    form_data like '%FaceApi%';

Answer 4

It certainly can. I came here from Google and this was the first result. And all I can say is the accepted answer given here might be incorrect. And it all comes down to how you use aliases. In my case, ORM was generating an alias for every column in the table. And the performance decrease was significant. Double execution time to be precise.

PostgreSQL example version 15:

---

Query generated by ORM:

SELECT id AS id_0, uuid AS uuid_1, preg AS preg_2, type AS type_3, company AS company_4, command AS command_5, desc AS desc_6, add_ref AS add_ref_7, reference AS reference_8, zipcode AS zipcode_9, mid_code_id AS mid_code_id_10, code_name AS code_name_11, code_ip AS code_ip_12, margin AS margin_13, contract_term AS contract_term_14, contract_date AS contract_date_15, misc_data AS misc_data_16, port AS port_17, stts AS stts_18, location_lat AS location_lat_19, location_lon AS location_lon_20 FROM internet_service

Resulted in:

"Seq Scan on internet_service  (cost=0.00..106.03 rows=503 width=1493) (actual time=0.010..0.204 rows=503 loops=1)"
"Planning Time: 0.058 ms"
"Execution Time: 0.230 ms"

---

Query without generating aliases:

SELECT * FROM internet_service

Resulted in:

"Seq Scan on internet_service  (cost=0.00..106.03 rows=503 width=1493) (actual time=0.007..0.097 rows=503 loops=1)"
"Planning Time: 0.054 ms"
"Execution Time: 0.119 ms"

Answer 5

There is some value is using aliases.

1. It improves readability
2. Most SQL compilers cache the query, so only the initial query will perform differently and it is minimal.
3. In most cases you are querying from an application that has to submit the request across a network. The amount of data being transmitted can be greatly reduced using aliases, therefore a large number of requests would take up less network bandwidth and overall increase application performance.