SQL procedure takes very long time?

Question

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 )

@shree.part18- no, index placed.on which column which type of index will be best fit and will will produced the output within 2/3 sec.? — skiskd, Nov 25 '15 at 07:02
Take a look at this answer to start: http://stackoverflow.com/questions/107132/what-columns-generally-make-good-indexes — shree.pat18, Nov 25 '15 at 07:04
Who is patrolRun? Why use join on tblAaaa and tblCccc if not used on select list? — Joe Taras, Nov 25 '15 at 07:24
You should take a loot at execution plain and see what happened there, after that you can optimize your query — Edward N, Nov 25 '15 at 08:26

score 1 · Answer 1 · answered Nov 25 '15 at 08:01

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.
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.
After only optimizing your query,you can think about indexes.

SQL procedure takes very long time?

1 Answers1