Go Premium for a chance to win a PS4. Enter to Win


How to optimize this query of make it faster

Posted on 2013-11-27
Medium Priority
Last Modified: 2013-12-16
alter procedure getac
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

This is the procedure, can we more optimize for faster output.
Secondly what are the fields to be indexed.
Question by:searchsanjaysharma
LVL 25

Assisted Solution

chaau earned 668 total points
ID: 39682765
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
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',
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

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.

Accepted Solution

cstruwig earned 664 total points
ID: 39682783
Yes there may be a lot of room for improvement.

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.

      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

Primary Key on mstmassstudents.uid
Index on custudents.uid
Index on mstmassstudents.iname
LVL 50

Assisted Solution

Lowfatspread earned 668 total points
ID: 39683918
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
;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 
                    AS RN
               from mstmassstudents
              Where a1.course='BE-ME(App.Sc.)' 
                and a1.semester=1  
            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]
 from  a1
inner join b
   on a1.uid=b.uid 
Where rn=1
order by iname,uid,subjectcode 

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!)

Expert Comment

ID: 39686080

  Maybe use of Tuning Advisor  help.

Author Closing Comment

ID: 39722979

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

782 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