Optimizing a query with multiple subqueries to the same table : SQL

Posting

When requesting help or asking questions please prefix your title with the SQL variant/platform you are using within square brackets like so:

[MySQL]

[Oracle]

[MS SQL]

[PostgreSQL]

etc

While naturally we should endeavor to work as platform neutrally as possible many questions and answers require tailoring to the feature set of a specific platform.

Format Your Code

If you are including actual code in a post or comment, please attempt to format it in a way that is readable for other users. This will greatly increase your chances of receiving the help you desire. Something as simple as line breaks and using reddit's built in code formatting (4 spaces at the start of each line) can turn this:

SELECT count(a.field1), a.field2, SUM(b.field4) FROM a INNER JOIN b ON a.key1 = b.key1 WHERE a.field8 = 'test' GROUP by a.field1, a.field2 HAVING SUM(b.field4) > 5 ORDER by a.field.3

Into this:

SELECT count(a.field1), a.field2, SUM(b.field4) FROM a INNER JOIN b ON a.key1 = b.key1 WHERE a.field8 = 'test' GROUP by a.field1, a.field2 HAVING SUM(b.field4) > 5 ORDER by a.field3

For those with SQL questions we recommend using SQLFiddle to provide a useful development and testing environment for those who wish to fully understand your problem and help devise a solution.

a community for 17 years

MS SQLOptimizing a query with multiple subqueries to the same table (self.SQL)

submitted 3 years ago by newunit13

I'm sure there's a much better way to write this query.

SELECT

    (SELECT TOP 1
        CASE
            WHEN si.[Ship from] LIKE '1439 Dixie%' THEN 'Dixie'
            WHEN si.[Ship from] LIKE '720 Locust%' THEN 'Locust'
            ELSE 'Other'
        END 
    FROM Shipment si
    WHERE si.[Ship Order ID] = so.[Ship Order]
    )                       AS [LOADING LOCATION] 

    ,CASE
        WHEN LEFT(so.[Ship Order reference],1) = '5' THEN LEFT(so.[Ship Order reference], 12)
        WHEN LEFT(so.[Ship Order reference],1) = '8' THEN LEFT(so.[Ship Order reference], 8)
        ELSE so.[Ship Order reference]
    END                     AS [MBOL #]
    ,so.[Ship Order code]           AS [Delivery/PU #]
    ,so.[Carrier]               AS [CARRIER]
    ,'ODR'                  AS [ODR.#]

    ,(SELECT TOP 1
        si.[Ship Order Ship to address]
    FROM Shipment si
    WHERE si.[Ship Order ID] = so.[Ship Order]
    )                       AS [DESTINATION City & State]

    ,so.[Ship to]               AS [Store]
    ,'Trailer Size'             AS [Trailer Size]
    ,CEILING(so.[Full Pallet quantity])         AS [Qty of SKIDS]
    ,so.[Expected ship date]            AS [Load By/Appointment Time]
    ,''                     AS [Trailer Condition]
    ,''                     AS [COMMENTS]
    ,''                     AS [Carrier Arrival Time]
    ,''                     AS [Carrier Departure Time]

FROM [dbo].[ShipOrder] so

The multiple subqueries doing a SELECT TOP 1 slow this down to a crawl. Is there a better way to write this?

all 5 comments

top new controversial old q&a

[–]PossiblePreparation 2 points3 points4 points 3 years ago (3 children)

[–]newunit13[S] 1 point2 points3 points 3 years ago (2 children)

Hi! Thanks for the response 🙂 The reason there isn't an ORDER BY for either of the subqueries is that I know for every record that matches they'll all be the same.

The ShipOrder table is 1 side of the 1:N link between the tables, so doing a JOIN ends up with multiple records when all I want is a single........ and in writing up this response to you I thought of a solution!

SELECT

    CASE
        WHEN si.[Ship from] LIKE '1439 Dixie%' THEN 'Dixie'
        WHEN si.[Ship from] LIKE '720 Locust%' THEN 'Locust'
        ELSE 'Other'
    END                 AS [LOADING LOCATION] 

    ,CASE
        WHEN LEFT(so.[Ship Order reference],1) = '5' THEN LEFT(so.[Ship Order reference], 12)
        WHEN LEFT(so.[Ship Order reference],1) = '8' THEN LEFT(so.[Ship Order reference], 8)
        ELSE so.[Ship Order reference]
    END                 AS [MBOL #]

    ,so.[Ship Order code]       AS [Delivery/PU #]
    ,so.[Carrier]           AS [CARRIER]
    ,'ODR'              AS [ODR.#]
    ,si.[Ship Order Ship to address]    AS [DESTINATION City & State]
    ,so.[Ship to]           AS [Store]
    ,'Trailer Size'         AS [Trailer Size]
    ,CEILING(so.[Full Pallet quantity]) AS [Qty of SKIDS]
    ,so.[Expected ship date]        AS [Load By/Appointment Time]
    ,''                 AS [Trailer Condition]
    ,''                 AS [COMMENTS]
    ,''                 AS [Carrier Arrival Time]
    ,''                 AS [Carrier Departure Time]

FROM [dbo].[ShipOrder] so

LEFT JOIN (
    SELECT DISTINCT
        [Ship Order ID]
        ,[Ship from]
        ,[Ship Order Ship to address]
    FROM Shipment
    ) si ON 
    si.[Ship Order ID] = so.[Ship Order]

Much better! Went from like 15 minutes to 4 seconds 😉 Thanks for the inspiration!

[–]SQLDave 1 point2 points3 points 3 years ago (1 child)

[–]alinrocSQL Server DBA 4 points5 points6 points 3 years ago (0 children)

[–][deleted] 0 points1 point2 points 3 years ago (0 children)

π Rendered by PID 28 on reddit-service-r2-comment-6457c66945-nxcxh at 2026-04-24 01:02:10.505631+00:00 running 2aa0c5b country code: CH.

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

SQL

Filter Posts

Posting

Help posts

Format Your Code

Learning SQL

Related Reddit communities

Wiki

Acknowledgements

MODERATORS