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.

							J1.Provider_Type_Description = 'C' 
							J1.Provider_Type_Description = 'H' 
						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)
						END as Type_1 
FROM qry0 T1

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

Open in new window

LVL 12
James ElliottManaging DirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
what would be helpful is the explain plan.

in short, the translation of the syntax goes like this:
AND field NOT IN ( SELECT column FROM ...  WHERE ... )
translates into:
AND NOT EXISTS ( SELECT NULL FROM ... WHERE ... AND field = column )

usually, some index is missing, likely on "field" of the outer table...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
James ElliottManaging DirectorAuthor Commented:
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.

ste5anSenior DeveloperCommented:
Still much too slow.. post your actual execution plan.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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

Thanks anyway.
ste5anSenior DeveloperCommented:
Well, when it's okay for you.. But 3M rows is not much and testing against 30k also.
James ElliottManaging DirectorAuthor Commented:
Think my problem lies here, if that's any use to you?

ste5anSenior DeveloperCommented:
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
                            Provider_Id ,                            
                    FROM    qry4
                  ) AS J1 ON T1.provider_id = J1.provider_id;

Open in new window

James ElliottManaging DirectorAuthor Commented:
should I be adding a non-clustered index on all PK fields?
ste5anSenior DeveloperCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.