Hello!
I am in the process of moving my backend from an Access file to SQL server.
One of the hurdles I've run into is that I use an IN() clause in one of my queries, and it's causing the query to run very slowly. I did not have this problem with an access backend.
From what I gathered off google. SQL server is happier using a WHERE EXISTS clause rather than an IN() clause.
The SQL line I use currently is
Code:
(((Inventory.NEC_Part_No) In (SELECT [NEC_Part_No] FROM [Open_Items_List] WHERE ((Open_Items_List.PO_No)=[Forms]![Jobs_Inventory]![inputPO])))
I am struggling to rewrite this using an exists clause. I could be thinking about it the wrong way, but I'm stuck.
Both of these tables have no referencing and are pretty small so the query should be quick...
Here is the full query for reference:
Code:
SELECT Inventory.Order_No, Inventory.Work_Ticket_No, Inventory.Customer, Inventory.End_User_Job_Name, Inventory.Due, Inventory.Shipped, Inventory.Assembly_No, Inventory.Parts_Bin_Location, Inventory.NEC_Part_No, Inventory.Qty_Required, Inventory.Qty_To_Build, Inventory.Total_Qty_Needed, Inventory.Qty_Ready, Inventory.Description, Inventory.Qty_Still_Needed, Inventory.Order_Parts, Inventory.Work_Ticket_StateFROM Inventory
WHERE (((Inventory.Order_No) Like "*" & [Forms]![Jobs_Inventory]![inputJobNo] & "*")
AND ((Inventory.NEC_Part_No) Like "*" & [Forms]![Jobs_Inventory]![inputPartNo] & "*")
AND ((Inventory.Qty_Still_Needed) Not Like 0)
AND (([Forms]![Jobs_Inventory]![inputPO]) Is Null)
AND (([Forms]![Jobs_Inventory]![Check50])=False))
OR (((Inventory.NEC_Part_No) In (SELECT [NEC_Part_No] FROM [Open_Items_List] WHERE ((Open_Items_List.PO_No)=[Forms]![Jobs_Inventory]![inputPO])))
AND ((Inventory.Qty_Still_Needed) Not Like 0)
AND (([Forms]![Jobs_Inventory]![inputPO]) Is Not Null)
AND (([Forms]![Jobs_Inventory]![Check50])=False)
AND (([Forms]![Jobs_Inventory]![inputPartNo]) Is Null)
AND (([Forms]![Jobs_Inventory]![inputJobNo]) Is Null))
OR (((Inventory.Order_No) Like "*")
AND ((Inventory.Qty_Still_Needed) Not Like 0)
AND (([Forms]![Jobs_Inventory]![inputPO]) Is Null)
AND (([Forms]![Jobs_Inventory]![Check50])=False)
AND (([Forms]![Jobs_Inventory]![inputPartNo]) Is Null)
AND (([Forms]![Jobs_Inventory]![inputJobNo]) Is Null))
OR (((Inventory.Order_No) Like "*" & [Forms]![Jobs_Inventory]![inputJobNo] & "*")
AND ((Inventory.NEC_Part_No) Like "*" & [Forms]![Jobs_Inventory]![inputPartNo] & "*")
AND (([Forms]![Jobs_Inventory]![inputPO]) Is Null)
AND (([Forms]![Jobs_Inventory]![Check50])=True))
OR (((Inventory.Order_No) Like "*")
AND ((Inventory.NEC_Part_No) In (SELECT [NEC_Part_No] FROM [Open_Items_List] WHERE ((Open_Items_List.PO_No)=[Forms]![Jobs_Inventory]![inputPO])))
AND (([Forms]![Jobs_Inventory]![inputPO]) Is Not Null)
AND (([Forms]![Jobs_Inventory]![Check50])=True)
AND (([Forms]![Jobs_Inventory]![inputPartNo]) Is Null)
AND (([Forms]![Jobs_Inventory]![inputJobNo]) Is Null))
OR ((([Forms]![Jobs_Inventory]![inputPO]) Is Null)
AND (([Forms]![Jobs_Inventory]![Check50])=True)
AND (([Forms]![Jobs_Inventory]![inputPartNo]) Is Null)
AND (([Forms]![Jobs_Inventory]![inputJobNo]) Is Null))
ORDER BY Inventory.Due;