Post reply

Table Views and Performance

  • 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

  • June 16, 2008 at 4:44 am

    #829113

    It depends.

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

  • June 16, 2008 at 4:47 am

    #829116

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

  • June 16, 2008 at 4:59 am

    #829119

    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.

  • June 16, 2008 at 6:22 am

    #829154

    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.

  • June 16, 2008 at 8:59 am

    #829262

    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

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

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