[Solved] Improve query with WHERE clause using same subquery multiple times
Looking to automatically optimize YOUR SQL query? Start for free.

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:

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!

How to optimize this SQL query?

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

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. 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

Related Articles



* original question posted on StackOverflow here.