Solved

Performance problem

Posted on 2007-11-24
8
319 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
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
how to fix this error 14 48
Managing SQL log files, SQL Server 2014 6 56
SQL Connection (Error 18456) 14 36
separate column 24 21
In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now