[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SQL Query Optimization

Posted on 2011-05-03
Medium Priority
Last Modified: 2012-06-27

I have created a web application which displays a list of jobs that are eligible to be billed.  This is populated using a SQL query within a stored procedure as shown below:

SELECT * FROM ijob WHERE ijob_id IN

	 SELECT ijobelement.ijob_id
		FROM ijobelement
		 LEFT JOIN ijob T1 ON ijobelement.ijob_id = ijob.ijob_id
		 LEFT JOIN ijobtype T2 ON T1.ijobtype_id = T2.ijobtype_id
		-- Do not include jobs which have been invoiced in the old system
		ijob.iinvoice_id is null
		-- Do not include jobs with non-billable job types
		(T2.ijobtype_name <> 'Meeting' AND T2.ijobtype_name <> 'Other' AND T2.ijobtype_name <> 'Training' AND T2.ijobtype_name <> 'Sign Making')
		-- This is a billable job type but we now check to see if it is eligible to be billed
		-- Startup job items and not billed
		((ijobelement_type = 0 AND ijobelement_billdate is null)
		-- ONE-OFF JOB items which have not been billed and we are > 7 days from their start date
		(ijobelement_type = 1 AND ijobelement_billdate is null AND ((DATEDIFF(d, ijobelement_startdate, GETDATE())) >= 7))
		-- RECURRING JOB items which have NOT been invoiced and we are >= 7 Days from the start date
		(ijobelement_type = 2 AND 
			((ijobelement_billdate is null AND ((DATEDIFF(d, ijobelement_startdate, GETDATE())) >= 7))
			-- RECURRING JOB which is NOT COMPLETE and has not been invoiced in the last 7 days
			 (ijobelement_billdate is not null AND 
				((ijob.ijob_complete = 'N' AND ((DATEDIFF(d, ijobelement_billdate, GETDATE())) >= 7))
				-- RECURRING JOB which IS COMPLETE and its last invoice date is before the job completed date
				(ijob.ijob_complete = 'Y' AND (ijobelement_billdate < ijob.ijob_completed_date))) ) ) )
		-- Closedown task which is not billed and the parent job is complete
		(ijobelement_type = 3 AND ijobelement_billdate is null AND ijob.ijob_complete = 'Y'))

Open in new window

While this query works it takes quite a while and I was hoping that maybe someone could suggest some ways in which I could optimize the query.  Currently it takes 23 seconds to run and return 420 rows which seems fairly excessive.

Although I'm comfortable with SQL I am not an expert by any means and generally try yo write procedures which are 'readable' and easy to understand rather than give the best performance but in this case I need to get some more speed out of the query.

Maybe some additional indexing would help?  ijob.ijob_id and ijobtype.ijobtype_id are both primary keys on their respective tables.  ijobelement_type can only be 0-3 and therefore I have not created an index for it.  Jo other indexes are in  place
Question by:ChrisMDrew
LVL 18

Accepted Solution

deighton earned 1200 total points
ID: 35511603
You should look at the actual execution plan for the query if you can, are there any indexes you could add to speed things up?  It will tell you which parts of the query are slow

Is it the inner query with the joins that is taking the time?  Test that by itself, then knock out some of the WHERE clauses to see if there is a particular search that is taking more of the time, then try to devise an index to help it.

Also you often have things like, ((DATEDIFF(d, ijobelement_startdate, GETDATE())) >= 7))

could you work out a date parameter there in advance, and select with
ijobelement_startdate <= @RelevantDate

then you might be able to index ijobelement_startdate, with better results (avoiding a datediff calculation on every row)

Assisted Solution

s_niladri earned 800 total points
ID: 35511605
Few suggestion from my side. You can create the following index
1. Create non-clustered index on ijob.ijobtype_id as it is being used in join clause
2. Create clustered/ non - clustered index on ijobelement.ijob_id as it is also being used in join clause

 Please look at the join clause and where clause properly to create few more indexes. I personally also not very keen to create much indexes but if required you have to.
Do you reqlly require all fields from  ijob table else repalce SELECT * FROM ijob with proper column names. In case of SQL Server 2008 you can carete filter indexes based on condition being used in query. You can replace IN caluse with EXISTS clause by putting a join condition in the innter query with ijob table mentioned outside. GETDATE()> 7 is common condition and used multiple times in the query. In case you use SQL Server 2008 you can create a CTE for this and can use the same cte in your query. It reduces online calculation.
There are couple of performance tuning tips available in web. Please follow that. Few of links are



Author Closing Comment

ID: 35708877
Thanks to both - some useful suggestions which gave some good results - it was the DateDiff which was taking all of the time - working out the date limits before hand and then using these in the query gave a several hundread percent increase in speed!

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

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.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

873 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