Table Views and Performance

Question

Post reply

Table Views and Performance

umaramiya-724626

Old Hand

Points: 319
More actions
June 16, 2008 at 4:26 am

#208453

Hi,
Currently I am developing a Job Portal using SQL Server 2005. I have certain queries like searching the Candidate/Job with keywords. which needs very high performance. Already important fields are enabled for Text indexing. Can I use Views for quering information. If so, will it add up to the performance ? Please do guide me
Thanks,
Uma Ramiya

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

It depends.

Are you using a lot of Joins or Aggregations it your query?

umaramiya-724626 Old Hand Points: 319 More actions · Answer 2

samples of query given below

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

Select a.intJobPostingID, b.strCompanyName,a.strJobTitle,a.strJobDesc,

c.strCity,dbo.fnState(c.intState,c.intCountry) as 'intState',

Convert(varchar,a.dtAuditDate,101) as 'dtAuditDate',

Convert(varchar,A.dtExpDate,101) as 'dtExpDate'

from tblJobPosting a

left join tblCompany b on a.intCompanyID = b.intCompanyID

left Join tblJobPostingCon c on a.intJobPostingID = c.intJobPostingID

where a.boolActive = 1 and Convert(varchar,a.dtExpDate,101) >= Convert(varchar,getdate(),101) and a.boolDisable = 0

and

(case

When @Category=3 then a.intCategory

else @Category

end)=a.intCategory

and(CASE WHEN @City='' THEN c.strCity ELSE @City END) = c.strCity

and (Case when @State > 0 then @State else c.intState END) = c.intState

and (Case when @State > 0 then @Country else c.intCountry END) = c.intCountry

and dbo.fnZipCodeCoverage(c.strZipCode,@ZipCode,@Radius)=1

and dbo.fnJobSeekerKeywordMatch(@Keyword,a.strJobTitle,a.strJobDesc,@JobTitle)=1

and a.intJobPostingID not in (Select intJobPostingID from tblJobSeekerPosting where intJobSeekerID=@JobSeekerID)

and (case when @Telecommut = 1 then @Telecommut else a.boolTelecommut end) = a.boolTelecommut

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

Select distinct a.intJobSeekerId,a.strFirstName + ' ' + a.strMiddleName + ' ' + a.strLastName as 'Name',

dbo.fnState(a.intState,a.intCountry) as 'intState',a.strCity,b.strTitle,Convert(varchar,a.dtAuditDate,101) as 'dtAuditDate'

from tblJobSeeker a

left join tblJobSeekerResume b on a.intJobSeekerId = b.intJobSeekerId

left join tblJobSeekerDoc c on a.intJobSeekerId = c.intJobSeekerId

where

(case when @Category = 3 then b.intIndustryType else @Category end)=b.intIndustryType

and b.boolStatus=1

and(b.strTitle like '%' + @Keyword + '%' or c.varbinResume like '%' + @Keyword + '%')

and (Convert(varchar,a.dtAuditDate,101) >=(CASE WHEN @AuditFrom='' THEN '01/01/1900' ELSE @AuditFrom END))

and (Convert(varchar,a.dtAuditDate,101) <=(CASE WHEN @AuditTo='' THEN '01/01/3000' ELSE @AuditTo END))

and ((((b.intSalary >= (CASE WHEN (@MinSal= 0) THEN 0 ELSE @MinSal END)

and b.intSalary <= (CASE WHEN (@MaxSal=0) THEN 999999999999999999999 ELSE @MaxSal END)

and b.intCurrency = (CASE WHEN @Currency='' THEN b.intCurrency ELSE @Currency END)

and b.intPayType = (CASE WHEN @PayType='' THEN b.intPayType ELSE @PayType END)))

or (b.intSalary = 0)))

and (b.strJobStatus like '%' + (CASE WHEN @JobStatus ='' THEN b.strJobStatus ELSE @JobStatus END) + '%')

and (b.strJobType like '%' + (CASE WHEN @JobType='' THEN b.strJobType ELSE @JobType END) + '%')

and (a.strCity in (select str from dbo.ExistsFunction(@Locations,',',a.strCity,@Willing,b.boolRelocate)))

and (a.strZipCode = (Case when @ZipCode='' then a.strZipCode else dbo.fngetDistance_Zipcode(a.strZipCode,@ZipCode,@Radius) end))

and (a.intJobSeekerId NOT IN (Select intJobSeekerId from tblCompResumeSaved where intCompanyId = @CompanyId))

steveb. SSC-Forever Points: 46733 More actions · Answer 3

A View could help in this situation as you have a few joins,

but first i would look at writing a more optimized query.

For example

(b.strTitle like '%' + @Keyword + '%' or c.varbinResume like '%' + @Keyword + '%')

will not allow the index for the fields to be used as you are using a '%' before your search clause, perhaps consider using Full-Text indexing for this field.

I will have a further look at your code and try to come up with some suggestions.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

Views are just stored queries, so they do not impact performance on their own.

They tend to produce bad queries because they get re-used in the wrong situation. For example, you may have a view that returns data from 4 tables. Someone needs to join two of these tables to another table - if they use the view, they have two unnecessary table joins.

If you have some really complicated views, you may be able to schema-bind them and index the views - this can help performance or hurt performance as well.

noeld SSC Guru Points: 96590 More actions · Answer 5

umaramiya (6/16/2008)
Hi,
Currently I am developing a Job Portal using SQL Server 2005. I have certain queries like searching the Candidate/Job with keywords. which needs very high performance. Already important fields are enabled for Text indexing. Can I use Views for quering information. If so, will it add up to the performance ? Please do guide me
Thanks,
Uma Ramiya

You should read THIS

* Noel