Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Speeding up this sql view....

Posted on 2012-03-30
5
Medium Priority
?
199 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
ID: 37788614
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 70

Expert Comment

by:Scott Pletcher
ID: 37788877
I don't think that's functionally equivalent to the first query.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 37789209
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:
Brendt Hess earned 2000 total points
ID: 37789567
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
ID: 37805598
Thank you, this helped out to speed it up, the query doesn't time out now.....
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

782 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