Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

How to optimize this query of make it faster

alter procedure getac
as
begin
select a1.*,b.photo from (select  iname,Course, Semester,UID,upper(sname) as sname, upper(fname) as fname,UPPER( mname) as mname, uppeR(subjectcode) as subjectcode,UPPER( subjectname) as subjectname,
ems as 'Detained Status'
from mstmassstudents
group by iname,Course,semester,UID,sname,fname,mname,subjectcode,subjectname,ems
)as a1,custudents b
where a1.uid=b.uid
and a1.course='BE-ME(App.Sc.)' and a1.semester=1
AND b.fflag=1 AND b.eflag=1 and b.lflag=0 and b.FLAG=1
and b.photo is not null
order by a1.iname,a1.course,a1.semester,a1.uid,a1.subjectcode
end


This is the procedure, can we more optimize for faster output.
Secondly what are the fields to be indexed.
0
searchsanjaysharma
Asked:
searchsanjaysharma
3 Solutions
 
chaauCommented:
There is absolutely no reason for the group by clause in your inner sub-query. The resulting query will be changed to this:
alter procedure getac
as
begin
select 
a1.iname,
a1.Course, 
a1.Semester,
a1.UID,
upper(a1.sname) as sname, 
upper(a1.fname) as fname,
UPPER(a1.mname) as mname, 
uppeR(a1.subjectcode) as subjectcode,
UPPER(a1.subjectname) as subjectname,
a1.ems as 'Detained Status',
b.photo 
from mstmassstudents as a1 INNER JOIN custudents b 
   ON a1.uid=b.uid and a1.course='BE-ME(App.Sc.)' and a1.semester=1
   AND b.fflag=1 AND b.eflag=1 and b.lflag=0 and b.FLAG=1 
   and b.photo is not null
order by a1.iname, a1.course, a1.semester, a1.uid, a1.subjectcode
end

Open in new window

This may improve the performance.

However, you may still need to add the indexes. The obvious candidates for mstmassstudents are:
Index 1: course
Index 2: semester
Index 3: iname (optional)
Index 4: subjectcode (optional)

The candidates for custudents are:
Index 1: fflag
Index 2: eflag
Index 3: lflag
Index 4: FLAG
Index 5: photo (this may not be required)

I assume you already have UID defined as a primary key in both tables.

I recommend you first try the updated version of the stored procedure, and then try adding the indices one-by-one, as I suggested.
0
 
cstruwigCommented:
Yes there may be a lot of room for improvement.

Problems
The stored procedure brings back all the photo data of all matching students.
This could be a lot of data to retrieve in one go.
It unnecessarily uses a subquery and possibly unnecessarily tries to group the data.
On top of that the data is being sorted before its returned.

Unless this is for a report I would suggest that you rethink the approach.

If you have to stick to the current approach then try the following code as a starting point.

ALTER PROCEDURE getac
AS
BEGIN
      SELECT  MST.iname, MST.Course, MST.Semester, MST.UID, UPPER(MST.sname) AS sname, UPPER(MST.fname) AS fname, UPPER(MST.mname) AS mname, UPPER(MST.subjectcode) AS subjectcode,
            UPPER(MST.subjectname) AS subjectname, MST.ems AS 'Detained Status', CUS.photo
    FROM    mstmassstudents MST
                  INNER JOIN custudents CUS
                  ON MST.uid = CUS.uid
                  AND MST.course = 'BE-ME(App.Sc.)'
                  AND MST.semester = 1
            AND CUS.fflag = 1
            AND CUS.eflag = 1
            AND CUS.lflag = 0
            AND CUS.FLAG = 1
            AND CUS.photo IS NOT NULL

    ORDER BY MST.iname, MST.course, MST.semester, MST.subjectcode
END
GO

Primary Key on mstmassstudents.uid
Index on custudents.uid
Index on mstmassstudents.iname
0
 
LowfatspreadCommented:
You need to tell us how many rows each table contains
how many rows you expect to output
why you think you need to have the group by for the first table....
what the query is trying to achieve (it possibly returns multple rows for a Student?)

and do you really not want to output a row if you don't have a photo?


an index on mstmassstudents of uid,semester,course may be of benefit
an index on custudents of uid, fflag,eflag,lflag,flag (,photo what is the datatype of photo?)

or it maybe beneficial to create a filtered indexes on the tables instead...

how often will this or similar queries be run?

alter procedure getac
as
begin
;with a1 as (select  iname, UID,upper(sname) as sname
                   , upper(fname) as fname,UPPER( mname) as mname
                   , uppeR(subjectcode) as subjectcode
                   ,UPPER( subjectname) as subjectname
                   ,ems as 'Detained Status'
                   ,row_NUMBER() OVER (PARTITION BY 
                        iname,Course,semester,UID,sname,fname,mname,subjectcode,subjectname,ems) 
                    AS RN
               from mstmassstudents
              Where a1.course='BE-ME(App.Sc.)' 
                and a1.semester=1  
 )
   ,b AS (sELECT UID,PHOTO
            FROM CUstudents as b
           Where b.fflag=1 AND b.eflag=1 
             and b.lflag=0 and b.FLAG=1 
             and b.photo is not null     
          )
select a1.iname
     ,'BE-ME(App.Sc.)' as course,1 as semester
     ,sname,fname,mname,subjectcode,subjectname,[detained status]
     ,b.photo
 from  a1
inner join b
   on a1.uid=b.uid 
Where rn=1
order by iname,uid,subjectcode 
end

Open in new window


ps adding single column indexes to tables isn't normally a valid option (unless there is high cardinality of the data values)  course maybe a valid example , but semester almost definitly wont be...

there are some valid scenario's where a group by  in a subselect could be of value, but my first thought is that either you have duplicate data in the table, (possibly due to mismatches in the student names...)  , but the current group by you're using probably wouldn't address that...

similarly there is no reason a sub select approach (per se) shouldn't perform ...

use the approach that feels most natural for the question you're trying to solve, and that will be most easily understood for future maintenance requirements.... (documentation....documentation!)
0
 
rshqCommented:
Hi

  Maybe use of Tuning Advisor  help.
0
 
searchsanjaysharmaAuthor Commented:
tx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now