?
Solved

Help improve query execution time

Posted on 2014-10-30
9
Medium Priority
?
151 Views
Last Modified: 2014-10-30
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

0
Comment
Question by:James Elliott
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 40413115
what would be helpful is the explain plan.

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

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

Author Comment

by:James Elliott
ID: 40413150
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
0
 
LVL 35

Expert Comment

by:ste5an
ID: 40413185
Still much too slow.. post your actual execution plan.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 12

Author Comment

by:James Elliott
ID: 40413363
I've tried taking out all commercially sensitive column/field/table names but it's taking way too long.

Thanks anyway.
0
 
LVL 35

Expert Comment

by:ste5an
ID: 40413376
Well, when it's okay for you.. But 3M rows is not much and testing against 30k also.
0
 
LVL 12

Author Comment

by:James Elliott
ID: 40413415
EE.jpg
Think my problem lies here, if that's any use to you?

Rgds
0
 
LVL 35

Expert Comment

by:ste5an
ID: 40413441
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

0
 
LVL 12

Author Comment

by:James Elliott
ID: 40413448
should I be adding a non-clustered index on all PK fields?
0
 
LVL 35

Expert Comment

by:ste5an
ID: 40413472
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.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question