Link to home
Start Free TrialLog in
Avatar of Andy Green
Andy GreenFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Subselects V slow.

Hi

Running on SQL Server 2008

I have a large (ish) query with 4 sub selects in the where clause:

    And vw.id not In (Select Appointment_ID From AppointmentEvent Where isnull(ArrivalTime,0) <> 0 And isnull(CompleteTime,0) <> 0)
    And vw.id not In (Select Appointment_ID From AppointmentEvent Where Event_ID = -11 And isnull(RequestTime,0) <> 0) --Exclude Return to wait
    And vw.id not In (Select Appointment_ID From AppointmentEvent Where isnull(RequestTime,0) <> 0 And isnull(CompleteTime,0) = 0 And Event_ID > 0)--Exclude Any User defined Activity
    And vw.id not In (Select Appointment_ID From AppointmentEvent Where Event_ID = -3 And Location_ID = @Location_ID) -- Exclude patients where call has already been made
    

Open in new window


These ran OK until we got to about 14000 rows in AppointmentEvent  table. Because we are looking for rows NOT IN the table is there a more efficient way to do this.

Would Joins be better?

Andy
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Andy Green

ASKER

Thanks Vitor

For now this combining has worked, and since this is a production environment, I'm going to run with this for now.

Thanks again saved my *rse.

Andy