asked on

Help improve query execution time

Hi All,

Execution of the query below takes roughly 20 mins. qry1 has nearly 3 million records. The other table sizes are about 30k each. The problem is certainly at the 'NOT IN' point, but I'm not familiar enough with 'EXISTS' or other methods to speed this up.

	SELECT				 CASE 
				 
						WHEN 
							J1.Provider_Type_Description = 'C' 
							OR 
							J1.Provider_Type_Description = 'H' 
						THEN 
							J1.Provider_Type_Description 
							
						WHEN J1.Provider_Id NOT IN (SELECT T1.Provider_ID FROM qry1 T1

											INNER JOIN qry2 J1
														ON J1.Provider_Id = T1.Provider_Id
														
													INNER JOIN qry3 J2
														ON J1.Specialisation_Description = J2.Specs)
																	
						 
						THEN							
							'T'
						ELSE
							'O' 				
						END as Type_1 
			
				
FROM qry0 T1

LEFT JOIN
	(SELECT DISTINCT Provider_Id, Provider_Name, Provider_Type_Description, provider_owner_name FROM qry4) AS J1
	ON
		T1.provider_id  = J1.provider_id

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

James Elliott

ASKER

Great, I'm now using not exists and have indexed on both provider_id columns, resulting in an execution time of 1min45secs which is more than I had hoped.

Thanks

ste5an

Still much too slow.. post your actual execution plan.

James Elliott

ASKER

I've tried taking out all commercially sensitive column/field/table names but it's taking way too long.

Thanks anyway.

ste5an

Well, when it's okay for you.. But 3M rows is not much and testing against 30k also.

James Elliott

ASKER

Think my problem lies here, if that's any use to you?

Rgds

ste5an

I don't think so. Imho you have not indexed all queries properly which are part of the actual problem (qry0-qry1).

SELECT  CASE WHEN J1.Provider_Type_Description = 'C'
                  OR J1.Provider_Type_Description = 'H' THEN J1.Provider_Type_Description
             WHEN NOT EXISTS ( SELECT   *
                               FROM     qry1 IT1
                                        INNER JOIN qry2 IJ1 ON IJ1.Provider_Id = IT1.Provider_Id
                                        INNER JOIN qry3 IJ2 ON IJ1.Specialisation_Description = IJ2.Specs
                               WHERE    J1.Provider_Id = IT1.Provider_ID ) THEN 'T'
             ELSE 'O'
        END AS Type_1
FROM    qry0 T1
        LEFT JOIN ( SELECT DISTINCT
                            Provider_Id ,                            
                            Provider_Type_Description                            
                    FROM    qry4
                  ) AS J1 ON T1.provider_id = J1.provider_id;

Open in new window

James Elliott

ASKER

should I be adding a non-clustered index on all PK fields?

ste5an

When you're interested in further optimizing your query, then we need the actual exection plan XML. Otherwise it's not really possible to help.

The only general approach: You need covering indices in the tables used by your views qry0-qry4.