EverSQL Database Performance Knowledge Base

Question 1

EverSQL Database Performance Knowledge Base

Improve query with WHERE clause using same subquery multiple times

Database type:

I've got a SQL query I fail to improve right now. It works, but it's a bit ugly.

I want to fetch:

an id from table A
a name from table B

using a WHERE clause which in itself fetches a value from another SQL query.

I would like to replace the following two instances of this SQL query used for the WHERE clause, with one instance:

SELECT intImageGalleryID FROM tblEPiServerCommunityImageGalleryImage
WHERE intID = 123123

How can it be done?

Using SQL Server.

Here's the complete SQL query:

SELECT intID,   
        (SELECT strName
        FROM tblEPiServerCommunityImageGallery
        WHERE intID = 
            (SELECT intImageGalleryID
            FROM tblEPiServerCommunityImageGalleryImage
            WHERE intID = 123123)
            ) as name   
FROM tblEPiServerCommunityClub
    WHERE intImageGalleryID =   
        (SELECT intImageGalleryID
        FROM tblEPiServerCommunityImageGalleryImage
        WHERE intID = 123123)

Thanks!

Answer

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.

The optimization process and recommendations:

Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

CREATE INDEX tblepiservercommun_idx_intid ON tblEPiServerCommunityImageGalleryImage (intID);

The optimized query:

SELECT
        tblEPiServerCommunityImageGalleryImage.intImageGalleryID 
    FROM
        tblEPiServerCommunityImageGalleryImage 
    WHERE
        tblEPiServerCommunityImageGalleryImage.intID = 123123

Question 2

EverSQL Database Performance Knowledge Base

Improve query with WHERE clause using same subquery multiple times

How to optimize this SQL query?

The optimization process and recommendations:

Optimal indexes for this query:

The optimized query:

Related Articles