CODEWITHSUNDEEP
sql-serversql-server-2012
skiskd
skiskd

Reputation: 423

SQL procedure takes very long time?

How to optimized below sql server procedure, tblPggg table has only 5000 records, and rest tables have only 50,100,200,10, records. Even it takes 15/20 secs on local on live server as well,

This is run on sql server 2012 If possible also want to optimize for the commented column in below SQL

        select distinct    
    tblppp.Name as Name,
    tblppp.pid as ID,
    tblgpp.FirstName +' '+ tblgpp.LastName as gName,
    tblgpp.GID  as gid,
    tblPggg.prEntryID,
    tblPggg.Start,
    tblPggg.End,
    CONVERT(nvarchar(50),DATEDIFF(MINUTE,tblPggg.Start,tblPggg.End)) +' minutes' as Duration,    
    tblPggg.Status as status
    --,[dbo].funcGetRatingOfPatrolRun(tblppp.pid,tblPggg.prEntryID) as PatrolRating -- commented

    from tblPggg (nolock) as tblPggg  inner join
    tblppp (nolock) as tblppp on tblPggg .pid = tblppp.pid inner join
    tblgpp (nolock) as tblgpp on tblgpp.GID=tblPggg .GID inner join    
    tblsss (nolock) as tblsss on patrolRun.SiteID = tblsss.SiteID inner join
    tblAaaa (nolock) as tblAaaa on tblsss.AreadID =tblAaaa.AreaID inner join
    tblCccc (nolock) as tblCccc on tblsss.ClientID = tblCccc.ClientID

when I show query estimated execution plan it shows up me 49% cost goes to (Hash Match Inner join )

Upvotes: 0

Views: 56

Answers (1)

KumarHarsh
KumarHarsh

Reputation: 5094

  1. There are atleast 3 table in your query whose columns are not playing any role.So show your real query with columns and function which is require.

  2. Why are you using distinct ?Due to which table,you are getting duplicate records.Is duplicate records bug ? Can you re-write query in such a manner that you don't get duplicate record without using distinct.It is always good idea to probe distinct.It often lead to bug.

  3. After only optimizing your query,you can think about indexes.

Upvotes: 1

Related Questions