Ways to optimize slow SQL query performance

Data and Databases
1

I’m facing issues with a SQL query that’s taking a long time to execute. It’s currently running for about 16 minutes, and that’s way too long for my needs.

Here’s the SQL query I’m working with:

WITH training_programs AS
(
    select program_modules.section_id, learner_id, program_modules.program_title, program_modules.min_score, program_modules.prog_id, enrollment_date,
        0 as isFromCompleted, finished_date, organization_id
    from learner_special_programs WITH (NOLOCK)
        inner join program_modules WITH (NOLOCK) on program_modules.prog_id = learner_special_programs.prog_id
    UNION all

    select program_modules.section_id, learner_id, program_modules.program_title, program_modules.min_score, program_modules.prog_id, enrollment_date,
        0 as isFromCompleted, learner_programs.finished_date, organization_id
    from learner_programs WITH (NOLOCK)
        inner join program_modules WITH (NOLOCK) on program_modules.prog_id = learner_programs.prog_id
        UNION all

        select program_modules.section_id, learner_id, program_modules.program_title, program_modules.min_score, program_modules.prog_id,
            null as enrollment_date, 1 as isFromCompleted, null as finished_date, organization_id
        from learner_programs_archived WITH (NOLOCK)
            inner join program_modules on program_modules.prog_id = learner_programs_archived.prog_id
)

select distinct
    u.last_name +', '+ u.first_name fullname,
    u.username as login,
    u.phone_number as phone,
    u.email_address as email
from users u
where u.organization_id in ('1000004')

The low performance seems to stem from the multiple UNION ALL operations. If anyone knows better ways to construct this query or has suggestions for indexing to speed things up, I would greatly appreciate your input.

2

You’re building a massive CTE with multiple UNION ALL operations, but your final SELECT only touches the users table. That’s creating a huge performance bottleneck - the database is processing all those joins and unions for nothing. Just simplify it to SELECT DISTINCT u.last_name + ', ' + u.first_name as fullname, u.username as login, u.phone_number as phone, u.email_address as email FROM users u WHERE u.organization_id = '1000004' since you’re not actually filtering or joining on the training_programs CTE anyway. If you need to filter users by program enrollment, add a WHERE EXISTS clause that hits the relevant tables directly. Also, ditch those NOLOCK hints - they can mess with data consistency and probably aren’t giving you the performance boost you think they are.

3

wow, yeah! you’re totally right. that CTE is just there for show. def just query the users table directly, it’s cleaner and won’t slow things down like that. you want results fast, right? ditch the extra work!

4

wait, I’m curious - are you planning to use that training_programs data later? maybe this is part of a bigger query you’re working on? if not, everyone else is right about just hitting the users table directly. but if you do need program filtering later, what exactly are you trying to do with those three different program tables?