Solved

using max to select a record

Posted on 2013-05-20
12
340 Views
Last Modified: 2013-05-20
I am creating an employee report that allows the user to select date. The report needs to provide the Position information, the salary and the home dept (along with some other data) for all acitve employees on that date. The historical Home dept, Position data, and salary are stored in 3 different tables with a change date. So, I created an sql command and based the report on the command. It took a little over 2 hours to run. So, I created a stored procedure, it takes about 30 minutes to run. Our DBA thinks that is still too long. I am attaching a copy of the stored procedure. I would welcome any suggestions on how this might be made to run faster. Thanks.usp-COO-EmployeeHistory.txt
0
Comment
Question by:qbjgqbjg
[X]
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
12 Comments
 
LVL 4

Expert Comment

by:Kent Fichtner
ID: 39181523
We had the same issue with a report that we built.  The way I did it was to make a stored procedure of the query I am running for crystal.  What the SP does for me is to insert data in to a new table the query for crystal.  Then when I run crystal I just do a SELECT * FROM TABLE and it run really fast.  It works great for us because the report isn't run to often so we just update the table with new information once a day in the morning.

Anther way I sped things up was to do the Where clause in the "Selection Formulas" area in Crystal.  That way SQL does part of the work and crystal does the rest (we have crystal and SQL on two different servers).

hope that helps.
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39181526
1) from the initial looks, change the variable table to permenant table (@EHistTable to __EHistTable)
this might increase the performance ...

2) if appointment_id on the above table is unique then make it primary key, or if a combination is a unique then create a clusterd index on it, after insertion into the EhistTable is done
   2a) just incase if the appointment_id is not unique, then create a non clustered index on top of it, this will definetly boost the performance.

apart from these, I can see there are many outer joins, outer joins will slow your query a lot sometimes, so, try to avoid them.

If you can provide your table schema and indexes, the number of rows on them, then we can suggest more improvements.

Do these at first and let us know how it ran.

By the way, run it thrice and take the average time between 2nd and 3rd, as the first time your stored procedure runs it might take a long time as the query plan is being built at that time and the next time it will be good.

I hope this info helps you out.
0
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39181553
Have you checked the execution plan for the underlying query to see where the most expensive parts are?  There may be some supporting indexes that could be added to some of the underlying tables to speed up the query. Additionally, if you are seeing the largest performance hits coming from the join operations with the table variable, you could consider a temp table with some indexing.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:qbjgqbjg
ID: 39181580
Thanks for the suggestions I will work on it.
0
 

Author Comment

by:qbjgqbjg
ID: 39181594
apart from these, I can see there are many outer joins, outer joins will slow your query a lot sometimes, so, try to avoid them. Where do you see outer joins and how would I avoid them?
0
 

Author Comment

by:qbjgqbjg
ID: 39181603
apart from these, I can see there are many outer joins, outer joins will slow your query a lot sometimes, so, try to avoid them. Do you mean the Left outer joins?
0
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 39181652
One suggestion is to move the subqueries in your WHERE clauses to the FROM clause. When you have subqueries in the WHERE clause, the query is run for each row of data returned from the rest of the query. So if your main query returns 100 rows, the subquery is run 100 times.
0
 

Author Comment

by:qbjgqbjg
ID: 39181662
So for this section:

UPDATE @EHistTable
SET
SHST_CHG_DATE      = ESHSHSTD.SHST_CHG_DATE
, SHST_SALARY      = ESHSHSTD.SHST_SALARY
, SHST_HOURLY_RATE = ESHSHSTD.SHST_HOURLY_RATE
, SHST_DESCRIPTION = ESHSHSTD.SHST_DESCRIPTION

FROM  
 @EHistTable E LEFT OUTER JOIN ESHSHSTD ESHSHSTD
 ON E.APPOINT_ID = ESHSHSTD.SHST_appoint_id
 
WHERE ESHSHSTD.SHST_appoint_id = E.APPOINT_ID and
ESHSHSTD.SHST_CHG_DATE = (select Max(ESHSHSTD.SHST_CHG_DATE) from ESHSHSTD
                                where ESHSHSTD.SHST_appoint_id = E.APPOINT_ID and
                                ESHSHSTD.SHST_CHG_DATE <= @Date)  

What would it look like to move the subquery to the from?
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39181663
Yes I mean the left outer join.

@@Shaun_Kline, I dont think so, if a sub query is in the select clause then what you said is correct, but if it is in the where clause then it wont be, and anyways his subquery is a correlated one, so there is much minimal performance impact (although, if we can avoid it, it will boost the performance, but better than the non-correlative one).
0
 

Author Comment

by:qbjgqbjg
ID: 39181713
I moved the sub queries to the from and I am running it.
0
 

Author Comment

by:qbjgqbjg
ID: 39182282
Moving the subquery to the from helped a lot. It now runs in 13 minutes.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39183409
If you continue with the table based approach (temp or permanent) I think you should put indexes on that table to help improve all subsequent joins.

Also you are consistently using LEFT OUTER JOINS, then following these with where clauses on the left joined table. This produces the effect of an inner join, so you might as well use inner joins, e.g.

UPDATE @EHistTable
SET                                                              
STATUS_DESC = ESYINFOR.DESCRIPTION
FROM   @EHistTable E
LEFT OUTER JOIN ESYINFOR ON E.EMPLOYMENT_STATUS = ESYINFOR.INFO_KEY
WHERE ESYINFOR.INFO_TYPE = 'es'

an INNER JOIN here would produce the same result.
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Parse this column 6 38
Need help in debugging a UDF results 7 68
Substring works but need to tweak it 14 35
Assigning Database Principals to Database Roles 3 38
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

740 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