Optimize SQL query using arrays

Question

My sql report has lots of inner queries. I have to pass the list of values in where clause. Same values are being passed few times. It takes more than 10 minutes to execute this query. Is there any way to optimise replacing the where condition with array or some other mechanism.

This list of IDs are used multiple times inside the nested queries.

List of IDS= SELECT ID
       FROM Employee
  where City = 'Berlin'

...............

This is a snippet of my query (not fully copied just wanted to show how I used above list in where conditions)

SELECT Job_id,client_id,details
    FROM Clients
where employeedID not in ( ***SELECT ID
           FROM Employee
      where City = 'Berlin*'**)
  where WORKID in (SELECT [WORKID]
  FROM Client_projects
  where DESCRIPTION in (SELECT [DESCRIPTION]
      FROM Projects
         where employeedID in (***SELECT ID
           FROM Employee
      where City = 'Berlin'***)
  where taskname = 'Project in Progress'))) and TASKNAME like '%Progress') and description in (SELECT [DESCRIPTION]
      FROM Client
         where employeeID in (***SELECT ID
           FROM Employee
      where City = 'Berlin'***
  where taskname = 'Project Completed')) and STATUS not in ('Cancelled','Closed')
.........

I tried to create an array to pass the value but couldn't succeed .

SQL Server doesn't have "arrays" - but you can always create a **table variable** `@BerlinEmployees` to hold multiple values, fill it once and use it multiple times — marc_s, Nov 12 '19 at 05:11
@marc_s Thank you. I have created a table variable with a single column to store the employee ID. I have now put this in where clause ( select employeeID from @BerlinEmployees) but haven't seen much improvement. Am i doing right ? — Richard, Nov 12 '19 at 05:33
Table variables have some limitations - how many employee ID's are you storing inside that table variable? — marc_s, Nov 12 '19 at 05:43
Well - the table variables have a limitation in that the SQL Server query optimizer will always consider them to have exactly **one row** of data. The more rows you pack into a table variable, the "worse" this assumption gets. So if you're really eager for performance - maybe you should try to rewrite the query to use proper JOIN's and conditions - instead of these `SELECT .... WHERE ... IN (...)` constructs - that would eliminate the need to store these ID's in a table variable ... — marc_s, Nov 12 '19 at 08:42
The _execution plan_ will show where the database is spending its resources. Please see [paste the plan](https://www.brentozar.com/pastetheplan/instructions/) for a way to include an execution plan in your question. — HABO, Nov 12 '19 at 14:45

score 0 · Answer 1 · answered Nov 12 '19 at 13:16

I would suggest using common table expression instead of repeating select ID..

with ListOfIDS as (
    select ID
    from Employee
    where City = 'Berlin'
 ) select *
   from otherTable 
       join ListOfIDS on ListOfIDS.ID = otherTable.ID
...

this simplifies query.
I also suspect what is killing your performance is TASKNAME like '%Progress' as this is not sargable.

Optimize SQL query using arrays

1 Answers1