Solved

Performance problem

Posted on 2007-11-24
8
333 Views
Last Modified: 2008-09-08
HI guys,
i am seriouslfacing performance issue for the following query specified in the following sp. I have analyzed the query and found that it is taking time because of And clause applied in this query. How to change this add clause to get best performance. Currently it is taking time 30 secs to show 63 records. Can anyone help me? It's very urgent. For your reference I herewith attach the sp below.

==============================================================================
Create PROCEDURE dbo.spSeacrhRoleProfile
 @Industry VARCHAR(50)=null,      
 @Client_id int = null,      
 @Job_Family_Code CHAR(3) =null,      
 @Job_SubFamily_Id int =null,      
 @Career_Level_Id int=null,      
 @Career_Stream_Code CHAR(1)=null,      
 @Position_Differentiator_Id int=null,      
 @Sub_Speciality_Id int=null,      
 @Factor_Name VARCHAR(500)='',      
 @Contact_Desc VARCHAR(500)='',      
 @Performance_Criteria_Desc VARCHAR(512)='',      
 @Accountability VARCHAR(500)='',      
 @MinimumRequirements VARCHAR(500)='',      
 @CoreCompetency VARCHAR(500)='',      
 @FunctionalCompetancy VARCHAR(500)='',      
 @ProficencyLevel VARCHAR(500)=''
         
AS      
BEGIN      
 SET NOCOUNT ON      
      SET @Factor_Name = '%'+ @Factor_Name + '%'      
      SET @Contact_Desc = '%'+ @Contact_Desc + '%'      
      SET @Performance_Criteria_Desc = '%'+ @Performance_Criteria_Desc + '%'      
      SET @Accountability = '%'+ @Accountability + '%'      
      SET @MinimumRequirements = '%'+ @MinimumRequirements + '%'      
      SET @CoreCompetency = '%'+ @CoreCompetency + '%'      
      SET @FunctionalCompetancy = '%'+ @FunctionalCompetancy + '%'      
      SET @ProficencyLevel = '%'+ @ProficencyLevel + '%'      

  SELECT distinct [MUPCS Code] = JF.Job_Family_Code + '.' + JSF.Job_SubFamily_Code + '.' +  
        CS.Career_Stream_Code + CL.Career_Level_Code + rp.Activity_Code,    RP.RoleProfile_id,      
        [Role Title]= Job_Family_Desc + ' ' + Career_Level_Desc +', ' +Job_SubFamily_Desc,        
       [Industry]= Industry_Name,  [Job Family]=Job_Family_Desc,
       [Job Sub Family]=Job_SubFamily_Desc,      
       [Career Stream]=Career_Stream_Desc,      
       [Career Level]=Career_Level_Desc,    
      RP.client_Id as [Sanitized Client Id]
  FROM dbo.RoleProfile as RP      
  join MstClient MstC on RP.Client_Id = MstC.Client_Id      
  join mstJobSubFamily JSF on RP.Job_SubFamily_Id = JSF.Job_SubFamily_Id      
  join mstJobFamily JF on JSF.Job_Family_Code = JF.Job_Family_Code      
  join mstCareerLevel CL on RP.Career_Level_Id = CL.Career_Level_Id      
  join MstCareerStream CS on CL.Career_Stream_Code = CS.Career_Stream_Code      
  LEFT JOIN dbo.RoleProfile_ProficiencyLevel as RPPL ON RPPL.RoleProfile_Id = RP.RoleProfile_Id          
 LEFT JOIN dbo.MstProficiencyLevel as PL ON PL.Proficiency_Level_Id =RPPL.Proficiency_Level_Id          
 LEFT JOIN dbo.LevelFactors as LF ON LF.RoleProfile_Id = RP.RoleProfile_Id      
 LEFT JOIN dbo.MainContacts as MC ON MC.RoleProfile_Id = RP.RoleProfile_Id        
 LEFT JOIN dbo.PerformanceCriteria as PC ON PC.RoleProfile_Id = RP.RoleProfile_Id        
 LEFT JOIN dbo.AcctCategory as AC ON AC.RoleProfile_Id = RP.RoleProfile_Id      
 left JOIN dbo.KeyActivities as KA on KA.Acc_Category_Id=AC.Acc_Category_Id          
 LEFT JOIN dbo.Competency as C ON C.RoleProfile_Id = RP.RoleProfile_Id        
 left JOIN dbo.CompIndicator as CI on CI.Competency_Id=C.Competency_Id          
 WHERE 1=1      
 AND MSTC.INDUSTRY_NAME =(CASE WHEN  @INDUSTRY IS NULL THEN MSTC.INDUSTRY_NAME ELSE @INDUSTRY END)      
 AND RP.CLIENT_ID = (CASE WHEN  @CLIENT_ID IS NULL THEN RP.CLIENT_ID ELSE @CLIENT_ID END)      
  AND RP.JOB_SUBFAMILY_ID = (CASE WHEN  @JOB_SUBFAMILY_ID IS NULL THEN RP.JOB_SUBFAMILY_ID ELSE @JOB_SUBFAMILY_ID END)      
 AND JF.JOB_FAMILY_CODE =(CASE WHEN  @JOB_FAMILY_CODE IS NULL THEN JF.JOB_FAMILY_CODE ELSE @JOB_FAMILY_CODE END)      
 AND RP.CAREER_LEVEL_ID = (CASE WHEN  @CAREER_LEVEL_ID IS NULL THEN RP.CAREER_LEVEL_ID ELSE @CAREER_LEVEL_ID END)      
AND CS.CAREER_STREAM_CODE= (CASE WHEN  @CAREER_STREAM_CODE IS NULL THEN CS.CAREER_STREAM_CODE ELSE @CAREER_STREAM_CODE END)      
 AND ISNULL(RP.POSITION_DIFFERENTIATOR_ID,-1) = (CASE WHEN  @POSITION_DIFFERENTIATOR_ID IS NULL THEN ISNULL(RP.POSITION_DIFFERENTIATOR_ID,-1) ELSE @POSITION_DIFFERENTIATOR_ID END)      
 AND ISNULL(RP.SUB_SPECIALITY_ID,-1) = (CASE WHEN  @SUB_SPECIALITY_ID IS NULL THEN ISNULL(RP.SUB_SPECIALITY_ID,-1) ELSE @SUB_SPECIALITY_ID END)      
 AND (
            (LF.FACTOR_NAME like @FACTOR_NAME OR LF.FACTOR_NAME is null)
 OR (LF.FACTOR_LANGUAGE_VALUE like @FACTOR_NAME or LF.FACTOR_LANGUAGE_VALUE is null)
         )           
 AND (
           (MC.CONTACT_TYPE like @CONTACT_DESC or MC.CONTACT_TYPE is null)
   OR  (MC.CONTACT_NAME like @CONTACT_DESC or MC.CONTACT_NAME is null)      
         )
 AND (
           (PC.PERFORMANCE_CRITERIA_TYPE like @PERFORMANCE_CRITERIA_DESC or
           PC.PERFORMANCE_CRITERIA_TYPE is null)
OR (PC.PERFORMANCE_CRITERIA_DESC like @PERFORMANCE_CRITERIA_DESC or
      PC.PERFORMANCE_CRITERIA_DESC is null)      
        )
AND (
         (AC.ACC_CATEGORY_DESC like @ACCOUNTABILITY or AC.ACC_CATEGORY_DESC is null)
 OR (AC.BALANCED_SCORECARD_CATEGORIES like @ACCOUNTABILITY or
       AC.BALANCED_SCORECARD_CATEGORIES is null)
   OR (AC.LEVEL_OF_RESPONSIBILITY like @ACCOUNTABILITY OR AC.LEVEL_OF_RESPONSIBILITY is
         null)
OR(KA.KEY_ACTIVITIES_DESC like @ACCOUNTABILITY or KA.KEY_ACTIVITIES_DESC is null)
    )      
AND (
      (RP.EDUCATION like @MINIMUMREQUIREMENTS or RP.EDUCATION is null)
OR (RP.CERTIFICATIONS like @MINIMUMREQUIREMENTS or RP.CERTIFICATIONS is null)
OR (RP.EXPERIENCE like @MINIMUMREQUIREMENTS or RP.EXPERIENCE is null)
OR (RP.KNOWLEDGE_AND_SKILLS like @MINIMUMREQUIREMENTS or RP.KNOWLEDGE_AND_SKILLS is null)      
  )      
 AND (
        (C.COMPETENCY_DEFINITION  like @CORECOMPETENCY or C.COMPETENCY_DEFINITION is null)
    OR  (C.COMPETENCY_NAME like @CORECOMPETENCY or  C.COMPETENCY_NAME is null)
 OR  (CI.COMPETENCY_INDICATOR_DESC like @CORECOMPETENCY or
        CI.COMPETENCY_INDICATOR_DESC is null)
AND (C.COMPETENCY_TYPE ='C')      
    )      
AND (
   (C.COMPETENCY_DEFINITION  like @FunctionalCompetancy or C.COMPETENCY_DEFINITION is null)
  OR  (C.COMPETENCY_NAME like @FunctionalCompetancy or C.COMPETENCY_NAME is null)
 OR  (CI.COMPETENCY_INDICATOR_DESC like @FunctionalCompetancy or
         CI.COMPETENCY_INDICATOR_DESC is null)
  AND (C.COMPETENCY_TYPE ='F')      
        )      
 AND (
      (RPPL.PROFICIENCY_LEVEL_DETAIL_DESC like @PROFICENCYLEVEL or
       RPPL.PROFICIENCY_LEVEL_DETAIL_DESC is null)
  OR (PL.PROFICIENCY_LEVEL like @PROFICENCYLEVEL or PL.PROFICIENCY_LEVEL is null)      
          )      
             
set nocount off
end
0
Comment
Question by:s_niladri
[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
8 Comments
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20342568
125 points?  Can't be that urgent !!
0
 
LVL 4

Author Comment

by:s_niladri
ID: 20342586
I don't have enough points. That's why I have assigned 125.
0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20342609
k - fair enough - so its 100% of what you own !! :)

Try this...
1) sequentially take out tables from the query until you find the offending one(s)
2) assiduously check all your indexes on these tables - if the field is being filtered on, it should be indexed.
3) for those tables found in (1), pre-process them by creating temporary tables (I think using a table variable @myTable - not #myTable) which is pre-filtered (but all your where clauses in this table.   either one temp table per underlying table, or you can start building the query up - aim is to have eliminated any large joins or complex filtering by the time you run the final query
4) substitute the tables made in (3) for the tables found in (1)
0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 6

Accepted Solution

by:
PaultheBroker earned 125 total points
ID: 20342619
hhmmm - in english this time...

Try this...
1) sequentially take out tables from the query until you find the offending one(s)
2) assiduously check all your indexes on these tables - if the field is being filtered on, it should be indexed.
3) for those tables found in (1), pre-process them by creating pre-filtered temporary tables (I think using a table variable @myTable - not #myTable - is slightly preferable).   Either one temp table per underlying table, or you can start building the query up by including a couple of tables in the temp table - your final aim is to have eliminated any large joins or complex filtering by the time you run the final query
4) substitute the tables made in (3) for the tables found in (1)
0
 
LVL 15

Expert Comment

by:dosth
ID: 20342858
Can you post the Table structures.

also check your table fileds data types and the SQL Params datatype are same

Thanks
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 20343080
You are probably missing an index or two...Do this...

in query analyzer or any GUI command line, enter the following and hit F5:

set showplan_all on

...then highlight your procedure and call it the way you would as if you were to run it(do not worry, it won't run) .  The output will tell you about how SQL will internally run your procedure.  In the first column of the output, look for *Table Scan* and scroll right to look for the column that needs to be indexed.   Once you identify the column copy and paste that column name as well as the the table name to which it belongs...Repeat the same operation for all *Table Scan* lines.  Once this is done you should have a list of columns and table names that you pasted...Next, highlight and run the following

set showplan_all off...

Take all the columns you previsouly pasted and index them....

Your query should run faster...If it does not, you may have to change your code using temporary tables.

That should help...
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

623 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