How to optimize this query of make it faster

Posted on 2013-11-27
Last Modified: 2013-12-16
alter procedure getac
select a1.*, 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 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 24

Assisted Solution

chaau earned 167 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 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 166 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',
    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 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 167 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 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

828 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