Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Speeding up this sql view....

Posted on 2012-03-30
5
Medium Priority
?
198 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
[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
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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 SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

721 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