Query much slower in SQL Server 2012 compared to SQL Server 2005 -
recently moved our database sql server 2005 sql server 2012. since experiencing performance problems when use following query:
select dbo.candidates.candidateid candidates_candidateid, dbo.candidates.datecreated candidates_datecreated, dbo.candidates.gender candidates_gender, dbo.titles.titlename titles_titlename, dbo.addresses.cityname addresses_cityname, dbo.countries.countryname candidates_countryname, dbo.candidates.phonenumber candidates_phonenumber, dbo.candidates.mobilephonenumber candidates_mobilephonenumber, dbo.candidates.birthdate candidates_birthdate, dbo.vwconcatenatedbranchespercandidate.branches_branchename, dbo.states.statename states_statename, dbo.substates.substatename substates_substatename, dbo.candidates.statedate candidates_statedate, dbo.candidates.nationality candidates_nationality, dbo.candidates.availablefromdate candidates_availablefromdate, dbo.candidates.availableuntildate candidates_availableuntildate, dbo.availabilities.availabilityname availabilities_availabilityname, dbo.availabledurations.availabledurationname availabledurations_availabledurationname, dbo.candidates.hoursperweekmin candidates_hoursperweekmin, dbo.candidates.hoursperweekmax candidates_hoursperweekmax, dbo.vwcandidateslastletter.candidates_datelastletter, dbo.vwcandidateslastemail.candidates_datelastemail, dbo.candidates.firstname candidates_firstname, dbo.candidates.initials candidates_initials, dbo.candidates.infix candidates_infix, dbo.candidates.surname candidates_surname, dbo.vwconcatenatedemailaddressespercandidate.email emailaddresses_emailaddress, dbo.vwconcatenatedcurrentfunctionnamespercandidate.candidates_currentfunctionnames, dbo.candidatesources.sourcename candidatesources_sourcename, dbo.candidates.candidatesourcetext candidates_candidatesourcetext, candidateisactiveinamatch.candidates_activeinothermatch, dbo.vwcandidatesusersfullname.candidates_fullusername, dbo.candidates.remark candidates_remark, dbo.candidates.hasworkedforus candidates_hasworkedforus, dbo.generatefullname(dbo.candidates.surname, dbo.candidates.firstname, dbo.candidates.initials, dbo.candidates.infix) candidates_fullname, dbo.vwcandidateslastmarketingmail.candidates_datemarketingmail, dbo.addresses.streetname addresses_streetname, dbo.addresses.postalcode addresses_postalcode, dbo.candidates.availabilitynote, dbo.candidates.greeting, dbo.candidates.startdate candidates_startdate, dbo.candidates.enddate candidates_enddate dbo.vwcandidateslastemail right outer join dbo.candidatesources right outer join dbo.candidates left outer join dbo.vwcandidateslastmarketingmail on dbo.candidates.candidateid = dbo.vwcandidateslastmarketingmail.candidates_candidateid left outer join dbo.titles on dbo.candidates.titleid = dbo.titles.titleid left outer join dbo.vwcandidatesusersfullname on dbo.candidates.candidateid = dbo.vwcandidatesusersfullname.candidates_candidateid left outer join dbo.candidateisactiveinamatch() candidateisactiveinamatch on dbo.candidates.candidateid = candidateisactiveinamatch.candidateid on dbo.candidatesources.candidatessourceid = dbo.candidates.candidatesourceid left outer join dbo.vwconcatenatedcurrentfunctionnamespercandidate on dbo.candidates.candidateid = dbo.vwconcatenatedcurrentfunctionnamespercandidate.candidateid left outer join dbo.vwconcatenatedemailaddressespercandidate on dbo.candidates.candidateid = dbo.vwconcatenatedemailaddressespercandidate.candidateid left outer join dbo.availabledurations on dbo.candidates.availabledurationid = dbo.availabledurations.availabledurationid left outer join dbo.availabilities on dbo.candidates.availabilityid = dbo.availabilities.availabilityid left outer join dbo.vwconcatenatedbranchespercandidate on dbo.candidates.candidateid = dbo.vwconcatenatedbranchespercandidate.candidateid left outer join dbo.states on dbo.candidates.stateid = dbo.states.stateid left outer join dbo.substates on dbo.candidates.substateid = dbo.substates.substateid , dbo.states.stateid = dbo.substates.stateid on dbo.vwcandidateslastemail.candidates_candidateid = dbo.candidates.candidateid left outer join dbo.vwcandidateslastletter on dbo.candidates.candidateid = dbo.vwcandidateslastletter.candidates_candidateid left outer join dbo.countries inner join dbo.addresses on dbo.countries.countryid = dbo.addresses.countryid on dbo.candidates.addressid = dbo.addresses.addressid the query uses subviews use custom user defined aggregate concatenate function (the views start vwconcatenated...) , subviews use following query (they end lastemail, lastletter etc...):
select dbo.candidates.candidateid candidates_candidateid, max(dbo.actions.datecreated) candidates_datelastemail dbo.candidates inner join dbo.actions_candidates on dbo.candidates.candidateid = dbo.actions_candidates.candidateid inner join dbo.actions on dbo.actions_candidates.actionid = dbo.actions.actionid inner join dbo.actiontypes on dbo.actions.actiontypeid = dbo.actiontypes.actiontypeid (dbo.actiontypes.actiontypecodeinternal = n'candidate_email') or (dbo.actiontypes.actiontypecodeinternal = n'sent_email') group dbo.candidates.candidateid in sql server 2012 query throws warning containing following message: operator used tempdb spill data during execution spill level 1.
it looks query ins't cached because when run same query second time takes long previous attempt. when run same query twice in sql 2005 second time returns within seconds.
why sql server 2012 , 2005 handle query differently , why slower in sql 2012?
links execution plans
Comments
Post a Comment