Solved

Speeding up this sql view....

Posted on 2012-03-30
5
188 Views
Last Modified: 2012-04-04
Hi,

I was wondering if someone could quickly help me rewrite this sql view to speed things up... we have determined this view is a bottleneck in another view that is calling it to get a count for the others views one column.... The view is so slow it times out while trying to run it straight in enterprise manager and or through the webpage that uses it....

Thanks

--------------------------------

SELECT DISTINCT
                      dbo.avoice_contact.candidate_id, dbo.avoice_candidate.location_id, dbo.avoice_candidate.campaign_id,
                      CASE WHEN dbo.avoice_candidate.term_dte = CONVERT(datetime, '01/01/1900', 101) THEN NULL ELSE CONVERT(VARCHAR(10), dbo.avoice_candidate.term_dte, 101)
                      END AS term_dte
FROM         dbo.avoice_contact INNER JOIN
                      dbo.avoice_candidate ON dbo.avoice_contact.candidate_id = dbo.avoice_candidate.candidate_id INNER JOIN
                      dbo.avoice_location ON dbo.avoice_candidate.location_id = dbo.avoice_location.location_id
WHERE     (dbo.avoice_contact.candidate_id IN
                          (SELECT     avoice_candidate_2.candidate_id
                            FROM          dbo.avoice_candidate AS avoice_candidate_2 INNER JOIN
                                                   dbo.avoice_location AS avoice_location_1 ON avoice_candidate_2.location_id = avoice_location_1.location_id AND
                                                   avoice_candidate_2.location_id = avoice_location_1.location_id AND avoice_candidate_2.campaign_id = avoice_location_1.campaign_id
                            WHERE      (avoice_location_1.status = 'A'))) AND (dbo.avoice_contact.signed_card = 1)
0
Comment
Question by:UFCWIT
5 Comments
 
LVL 21

Expert Comment

by:JestersGrind
Comment Utility
I'm not sure why you are using that subquery in the where clause.  Couldn't you do something like this?  

SELECT DISTINCT 
	dbo.avoice_contact.candidate_id, 
	dbo.avoice_candidate.location_id, 
	dbo.avoice_candidate.campaign_id, 
	CASE 
		WHEN dbo.avoice_candidate.term_dte = CONVERT(datetime, '01/01/1900', 101) 
		THEN NULL 
		ELSE CONVERT(VARCHAR(10), dbo.avoice_candidate.term_dte, 101) 
	END AS term_dte
FROM         
	dbo.avoice_contact INNER JOIN
    dbo.avoice_candidate ON dbo.avoice_contact.candidate_id = dbo.avoice_candidate.candidate_id AND 
							dbo.avoice_contact.location_id = dbo.avoice_candidate.location_id AND
							dbo.avoice_contact.campaign_id = dbo.avoice_candidate.campaign_id INNER JOIN
	dbo.avoice_location ON dbo.avoice_candidate.location_id = dbo.avoice_location.location_id
WHERE
	dbo.avoice_location.status = 'A' AND 
	dbo.avoice_contact.signed_card = 1

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
I don't think that's functionally equivalent to the first query.
0
 
LVL 16

Expert Comment

by:DcpKing
Comment Utility
Firstly, I'd suggest you look at both views together: can you modify this view by using the criteria from the one that uses it? If so, combine the two (quite often one uses a view because it gives the right answer, not realising the amount of unnecessary work it does!)

Aside from that,

change CONVERT(datetime, '01/01/1900', 101) into a constant!  (the "case when" piece)

you have the same criterion twice in the subquery:
avoice_candidate_2.location_id = avoice_location_1.location_id AND
avoice_candidate_2.location_id = avoice_location_1.location_id
0
 
LVL 32

Accepted Solution

by:
bhess1 earned 500 total points
Comment Utility
A couple of things come up.  DcpKing (above) touched on the obvious coding issues in your query, so I won't re-discuss this.

When running against a large dataset, i find that using an IN query can be significantly less efficient than changing it to a derived table with a JOIN.  My sample query below does that.

Finally - the most common means of speeding a query like this is to use Covering Indexes.  If you are unfamiliar with the term, this means an index on your underlying table that contains all of the data being used from that table in your query.  Looking at the query you presented me, I would start by ensuring that I have indexes like this on the underlying tables:

CREATE INDEX ix_cv_sc_cid_lid ON avoice_contact 
	(signed_card, candidate_id, location_id)
CREATE INDEX ix_cv_lid_cpid__cid ON avoice_candidate 
	(location_id, campaign_id) INCLUDE (candidate_id)
CREATE INDEX ix_cv_st_lid_cpid ON avoice_location 
	(status, location_id, campaign_id)

Open in new window


The query version I would use would be probably like this:

SELECT DISTINCT 
	dbo.avoice_contact.candidate_id, 
	dbo.avoice_candidate.location_id, 
	dbo.avoice_candidate.campaign_id, 
	CASE 
		WHEN dbo.avoice_candidate.term_dte = '19000101'	
			THEN NULL 
		ELSE CONVERT(VARCHAR(10), dbo.avoice_candidate.term_dte, 101) 
	END AS term_dte
FROM dbo.avoice_contact 
INNER JOIN dbo.avoice_candidate 
	ON dbo.avoice_contact.candidate_id = dbo.avoice_candidate.candidate_id 
INNER JOIN dbo.avoice_location 
	ON dbo.avoice_candidate.location_id = dbo.avoice_location.location_id
INNER JOIN (
	SELECT avoice_candidate_2.candidate_id
	FROM dbo.avoice_candidate AS avoice_candidate_2 
	INNER JOIN dbo.avoice_location AS avoice_location_1 
		ON avoice_candidate_2.location_id = avoice_location_1.location_id 
		AND avoice_candidate_2.campaign_id = avoice_location_1.campaign_id
	WHERE avoice_location_1.status = 'A'
	) ac2
	ON dbo.avoice_contact.candidate_id = ac2.candidate_id
WHERE dbo.avoice_contact.signed_card = 1

Open in new window

This should optimize the performance of this query.
0
 

Author Closing Comment

by:UFCWIT
Comment Utility
Thank you, this helped out to speed it up, the query doesn't time out now.....
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

771 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

12 Experts available now in Live!

Get 1:1 Help Now