SQL Query Optimization

Posted on 2011-05-03
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

    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)
    LVL 4

    Assisted Solution

    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

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now