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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Comment Utility

  Maybe use of Tuning Advisor  help.

Author Closing Comment

Comment Utility

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
table fragmentation 40 73
How can I get this "NOT IN" to work? 5 16
View Sql in stored procedure 13 20
Group by and order by clause 28 36
     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 …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video discusses moving either the default database or any database to a new volume.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now