Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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.*, 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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 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',
    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 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 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA:…

715 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