Results 1 to 4 of 4
  1. #1
    MrBologna is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    48

    Changing from In() to Exists

    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;

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    if your query is in Access, then it doesnt need to change.
    unless youre moving the queries to sql svr too.

  3. #3
    MrBologna is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    48
    I think I figured it out. Doing a Left join on the Open_Items_List table instead of recursively looking at a SELECT greatly improved my run time.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    things like this

    ((Inventory.Order_No) Like "*" & [Forms]![Jobs_Inventory]![inputJobNo] & "*")
    will severely slow your query as the initial * (or % if query written in sql server) negates the use of an index

    here

    AND ((Inventory.Qty_Still_Needed) Not Like 0)
    you are applying a string comparator to a number

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Not exists
    By ASWilliams in forum SQL Server
    Replies: 1
    Last Post: 07-20-2016, 03:49 AM
  2. Update Only Where Value Exists
    By Lorlai in forum Queries
    Replies: 2
    Last Post: 03-06-2012, 11:48 AM
  3. If table exists then
    By SorenIX in forum Programming
    Replies: 2
    Last Post: 06-26-2011, 08:42 AM
  4. Add columns if not exists
    By ysrini in forum Access
    Replies: 1
    Last Post: 02-16-2010, 06:39 PM
  5. How to use if not exists in access?
    By halifaxdalRaymondXie in forum Queries
    Replies: 1
    Last Post: 01-14-2010, 03:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums