Solved

SQL Query Question

Posted on 2013-01-28
39
207 Views
Last Modified: 2013-02-21
Good day everyone. I have a query that build a report and we are having issues with it taking over two minutes to pull the data. Our web application timeouts before the data is pulled and report is populated.

Is there anything that can be done to optimize the query?

Select distinct i.Reg, i.F_N + '-' + i.Si As ID, lname+', '+fname+' '+mi as Name, lid, dateadd(year,3,max(truedate)) as Termination, 
startdate, movedate, progCount as PC,i.status as I, s.status as S 
from i inner join s on  i.f_n=s.f_n and i.si=s.si and i.reg=sh.reg 
inner join ex on i.reg=ex.reg and i.f_n=ex.f_n and i.si=ex.si 
inner join data1 d1 on ex.reg=d1.reg and ex.c_n=d1.c_n
where fiscalyear = (select optiondata from ms_w..rptopt where optionname= 'fiscalyear') and 
(movedate is null or movedate>=(select optiondata from ms_w..rptopt where optionname= 'firstday'))
group by i.F_N, i.Si, i.reg, startdate, fiscalyear,progCount,lname,fname,mi, 
d1.verify, i.status, s.status, lid , movedate having d1.verify='Y' and dateadd(year,3,max(truedate))>=startdate and max(truedate)<
(select optiondata from ms_w..rptopt where optionname= 'c_firstdate')

Open in new window


By the way, I changed all database and element names to protect our structure.
0
Comment
Question by:mig1980
  • 15
  • 8
  • 5
  • +6
39 Comments
 
LVL 6

Expert Comment

by:nickinthooz
ID: 38828259
you can put a limit on the number of records returned

LIMIT 10;
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38828311
LIMIT 10 would be only for MySQL, and not for MS SQL server.
in sql server it would be SELECT TOP 10 ...

to see how to optimize, we would need to know how many rows are returned by this query, and what the explain plan is
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38828312
That query could definitely be rewritten to improve efficiency, but I can't begin to do that w/o knowing which table every column comes from.

Please add the appropriate alias to every column in the query.  Btw, that is a standard best practice anytime a join is used, to explicitly identity which table each column comes from.
0
 

Author Comment

by:mig1980
ID: 38828384
No, I need all records that fit that criteria returned and they vary throughout the year. There are 518 rows returned currently by this query but it varies (as explained).

The query searches through  through over 1 million records on one database, over 2 million on another, and over 8 million on the third.

Below is the appropriate alias added to every column:

Select distinct i.Reg, i.F_N + '-' + i.Si As ID, d1.lname+', '+d1.fname+' '+d1.mi as Name, s.lid, dateadd(year,3,max(d1.truedate)) as Termination, 
s.startdate, s.movedate, s.progCount as PC,i.status as I, s.status as S 
from i inner join s on  i.f_n=s.f_n and i.si=s.si and i.reg=sh.reg 
inner join ex on i.reg=ex.reg and i.f_n=ex.f_n and i.si=ex.si 
inner join data1 d1 on ex.reg=d1.reg and ex.c_n=d1.c_n
where s.fiscalyear = (select optiondata from ms_w..rptopt where optionname= 'fiscalyear') and 
(s.movedate is null or s.movedate>=(select optiondata from ms_w..rptopt where optionname= 'firstday'))
group by i.F_N, i.Si, i.reg, s.startdate, s.fiscalyear,s.progCount,d1.lname,d1.fname,d1.mi, 
d1.verify, i.status, s.status, s.lid , s.movedate having d1.verify='Y' and dateadd(year,3,max(d1.truedate))>=s.startdate and max(d1.truedate)<
(select optiondata from ms_w..rptopt where optionname= 'c_firstdate')

Open in new window

0
 
LVL 6

Expert Comment

by:liija
ID: 38828412
Execution plan would help, yes.
Also, run your query once.
Before that
SET STATISTICS IO ON
Ctrl-T (Query-Results-Results to text)

After you run your query you'll see the execution details on the last row
(logical reads, physical reads..). This tells more about the actual query cost.
0
 

Author Comment

by:mig1980
ID: 38828488
Here is the query cost:


Table 'rptOpt'. Scan count 5596, logical reads 11192, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 's'. Scan count 1, logical reads 623510, physical reads 106, read-ahead reads 40969, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ex'. Scan count 4, logical reads 9595, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'i'. Scan count 4, logical reads 51554, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'data1'. Scan count 0, logical reads 889943, physical reads 3915, read-ahead reads 34397, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Warning: Null value is eliminated by an aggregate or other SET operation.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
ID: 38828529
Please try query below.  I think it's equivalent, although we might have to touch it up a bit.


Select /*distinct*/ --put distinct back in only if you have to, it adds huge overhead
    i.Reg, i.F_N + '-' + i.Si As ID, 
    d1.Name, 
    s.lid, 
    dl.Termination, 
    s.startdate, s.movedate, s.progCount as PC,
    i.status as I, 
    s.status as S 
from i 
inner join s on
    i.f_n=s.f_n and i.si=s.si and i.reg=sh.reg 
inner join ex on
    i.reg=ex.reg and i.f_n=ex.f_n and i.si=ex.si 
inner join (
    select 
        reg, c_n,
        lname+', '+fname+' '+mi as Name, 
        dateadd(year,3,max(truedate)) as Termination
    from data1
    where
        verify='Y'
    group by
        reg, c_n, lname+', '+fname+' '+mi
    having
        max(truedate) < (select optiondata from ms_w..rptopt where optionname= 'c_firstdate') 
) as d1 on 
    ex.reg=d1.reg and ex.c_n=d1.c_n and
    d1.Termination >= s.startdate
where 
    s.fiscalyear = (select optiondata from ms_w..rptopt where optionname= 'fiscalyear') and 
    (s.movedate is null or s.movedate>=(select optiondata from ms_w..rptopt where optionname= 'firstday'))

Open in new window


A massive GROUP BY is always a potentially serious performance issue.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 38828666
I would put all subqueries in #temp tables where you can create an index (or CTE)
I would try move all WHERE into JOINs
Why do you need DISTICNT - try see if it can't be dropped as you have only INNER JOINs
Look at the execution plan and see if any indexes are missing
0
 

Author Comment

by:mig1980
ID: 38828711
I apologize but I made a mistake in describing  a few elements.

lname, fname, and mi are not part of the data1 table. They are part of the i table. how would this change be made efficiently?
0
 

Author Comment

by:mig1980
ID: 38828980
I moved the three elements out of the sub-query for d1 and placed them in the initial query. I then ran the query successfully but it would not produce the same results. It produced far more results. Even when using distinct, it still produced more results although it produced about 200 less results than without it.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
ID: 38829106
SELECT /*distinct*/ --put distinct back in ONLY if you MUST, because it adds huge overhead
    i.Reg, i.F_N + '-' + i.Si As ID,
    i.lname+', '+i.fname+' '+i.mi as Name,
    s.lid,
    dl.Termination,
    s.startdate, s.movedate, s.progCount as PC,
    i.status as I,
    s.status as S
from i
inner join s on
    i.f_n=s.f_n and i.si=s.si and i.reg=sh.reg
inner join ex on
    i.reg=ex.reg and i.f_n=ex.f_n and i.si=ex.si
inner join (
    select
        reg, c_n,
        dateadd(year,3,max(truedate)) as Termination
    from data1
    where
        verify='Y'
    group by
        reg, c_n
    having
        max(truedate) < (select optiondata from ms_w..rptopt where optionname= 'c_firstdate')
) as d1 on
    ex.reg=d1.reg and ex.c_n=d1.c_n and
    d1.Termination >= s.startdate
where
    s.fiscalyear = (select optiondata from ms_w..rptopt where optionname= 'fiscalyear') and
    (s.movedate is null or s.movedate>=(select optiondata from ms_w..rptopt where optionname= 'firstday'))
0
 

Author Comment

by:mig1980
ID: 38829238
As I mentioned a half hour ago, the query doesn't produce correct results. The termination data is not correct for some reason.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38829327
Good luck then.  Hopefully someone else can help you.

It will take some understanding of the actual table relationships to figure out "doesn't produce correct results".
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 63 total points
ID: 38829406
Something like this perhaps:
DECLARE @fiscalyear integer,   -- IMPORTANT: Change all data type to match.
	@movedate datetime,
	@truedate datetime

SELECT	@fiscalyear = optiondata
FROM 	ms_w..rptopt	
WHERE	optionname = 'fiscalyear'

SELECT	@movedate = optiondata
FROM 	ms_w..rptopt	
WHERE	optionname = 'firstday'

SELECT	@truedate = optiondata
FROM 	ms_w..rptopt	
WHERE	optionname = 'c_firstdate'


SELECT  i.Reg,
        i.F_N + '-' + i.Si AS ID,
        d1.lname + ', ' + d1.fname + ' ' + d1.mi AS Name,
        s.lid,
        DATEADD(year, 3, MAX(d1.truedate)) AS Termination,
        s.startdate,
        s.movedate,
        s.progCount AS PC,
        i.status AS I,
        s.status AS S
FROM    i
        INNER JOIN s ON i.f_n = s.f_n AND i.si = s.si AND i.reg = sh.reg
        INNER JOIN ex ON i.reg = ex.reg AND i.f_n = ex.f_n AND i.si = ex.si
        INNER JOIN data1 d1 ON ex.reg = d1.reg AND ex.c_n = d1.c_n
        INNER JOIN ms_w..rptopt r ON s.fiscalyear = r.optiondata
WHERE   s.fiscalyear = @fiscalyear
        AND (s.movedate IS NULL OR s.movedate >= @movedate)
        AND d1.verify = 'Y'
GROUP BY i.F_N,
        i.Si,
        i.reg,
        s.startdate,
        s.fiscalyear,
        s.progCount,
        d1.lname,
        d1.fname,
        d1.mi,
        d1.verify,
        i.[status],
        s.[status],
        s.lid,
        s.movedate
HAVING  DATEADD(year, 3, MAX(d1.truedate)) >= s.startdate
        AND MAX(d1.truedate) < @truedate

Open in new window


But as everyone has pointed out the design is not conducive to much optimization.  So unless you are prepared to redesign your tables you are probably stuck with lousy performance.
0
 
LVL 39

Assisted Solution

by:appari
appari earned 124 total points
ID: 38829419
try this
declare @fiscalyear varchar(15)
declare @firstday varchar(15)
declare @c_firstday varchar(15)

select @fiscalyear = optiondata from ms_w..rptopt where optionname= 'fiscalyear'
select @firstday = optiondata from ms_w..rptopt where optionname= 'firstday'
select @c_firstday = optiondata from ms_w..rptopt where optionname= 'c_firstdate'

Select distinct i.Reg, i.F_N + '-' + i.Si As ID, d1.lname+', '+d1.fname+' '+d1.mi as Name, s.lid, dateadd(year,3,max(d1.truedate)) as Termination, 
s.startdate, s.movedate, s.progCount as PC,i.status as I, s.status as S 
from i inner join (Select * from  s where  s.fiscalyear = @fiscalyear and 
(s.movedate is null or s.movedate>=@firstday ) s on  i.f_n=s.f_n and i.si=s.si and i.reg=sh.reg 
inner join ex on i.reg=ex.reg and i.f_n=ex.f_n and i.si=ex.si 
inner join data1 d1 on ex.reg=d1.reg and ex.c_n=d1.c_n
group by i.F_N, i.Si, i.reg, s.startdate, s.fiscalyear,s.progCount,d1.lname,d1.fname,d1.mi, 
d1.verify, i.status, s.status, s.lid , s.movedate having d1.verify='Y' and dateadd(year,3,max(d1.truedate))>=s.startdate and max(d1.truedate)<
@c_firstday 

Open in new window

0
 
LVL 39

Expert Comment

by:appari
ID: 38829422
@acperkins: sorry didn't refresh the page before posting, almost same code
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38829428
No problem, it happens.
0
 

Author Comment

by:mig1980
ID: 38832667
appart...I tried the query you provided and it pulled all the correct records but for some reason, it actually takes longer to pull the records than my original query.

My current query gave results in 1 minute and 23 seconds and the query provided gave results in 2 minutes 40 seconds.
0
 
LVL 6

Expert Comment

by:liija
ID: 38832738
Your original query might be in cache, the new one may be read from disk.
To really compare the performance, this should be tested in test environment.
There you could run before each query
DBCC Dropcleanbuffers

It empties the server cache. Then you could also see the correct physical read amount, which is the most expensive task.

You should also compare the execution plans of these queries.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:mig1980
ID: 38832761
I don't want to empty cache and we do not have a test environment currently. We are working on developing one. Are you saying this query provided is faster but only appears slower because of cache?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38832783
The query plan won't make that much of a difference as long as the massive GROUP BY is there.  Until you deal with that, the query will be inherently slow.
0
 
LVL 6

Expert Comment

by:liija
ID: 38832786
Impossible to say without data, statistics, indexes and execution plan. A good guess more or less.
0
 

Author Comment

by:mig1980
ID: 38832810
Can you give me options of dealing with the group by?
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
ID: 38832842
See if (1) this runs and (2) it helps in any way.



SELECT /*distinct*/ --put distinct back in ONLY if you MUST, because it adds huge overhead
    i.Reg, i.F_N + '-' + i.Si As ID,
    i.lname+', '+i.fname+' '+i.mi as Name,
    s.lid,
    max(dl.Termination) AS Termination,
    s.startdate, s.movedate, s.progCount as PC,
    i.status as I,
    s.status as S
from i
inner join s on
    i.f_n=s.f_n and i.si=s.si and i.reg=sh.reg
inner join ex on
    i.reg=ex.reg and i.f_n=ex.f_n and i.si=ex.si
inner join (
    select
        reg, c_n,
        dateadd(year,3,max(truedate)) as Termination
    from data1
    where
        verify='Y'
    group by
        reg, c_n
    having
        max(truedate) < (select optiondata from ms_w..rptopt where optionname= 'c_firstdate')
) as d1 on
    ex.reg=d1.reg and ex.c_n=d1.c_n    
where
    s.fiscalyear = (select optiondata from ms_w..rptopt where optionname= 'fiscalyear') and
    (s.movedate is null or s.movedate>=(select optiondata from ms_w..rptopt where optionname= 'firstday'))
group by
    i.F_N, i.Si, i.reg, s.startdate, s.fiscalyear, s.progCount, i.status, s.status, s.lid, s.movedate, i.lname, i.fname, i.mi  
having
    dateadd(year,3,max(d1.Termination))>=s.startdate
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
ID: 38832879
Without more knowledge of the tables, below is best I can try, once correcting it for any error(s) of course.  Trying to mitigate the impact of the GROUP BYs as much as possible, since can't get rid of them w/o more knowledge of table.



SELECT /*distinct*/ --put distinct back in ONLY if you MUST, because it adds huge overhead
    i.Reg, i.F_N + '-' + i.Si As ID,
    i.lname+', '+i.fname+' '+i.mi as Name,
    s.lid,
    max(dl.Termination) AS Termination,
    s.startdate, s.movedate, s.progCount as PC,
    i.status as I,
    s.status as S
from (
    select distinct reg, F_N, Si, lname, fname, mi, status
    from i as i2    
) AS i
inner join (
    select distinct s.f_n, s.si, s.reg, s.lid, s.startdate, s.movedate, s.progcount, s.status
    from s as s2
) AS s on
    i.f_n=s.f_n and i.si=s.si and i.reg=s.reg
inner join ex on
    i.reg=ex.reg and i.f_n=ex.f_n and i.si=ex.si
inner join (
    select
        reg, c_n,
        dateadd(year,3,max(truedate)) as Termination
    from data1
    where
        verify='Y'
    group by
        reg, c_n
    having
        max(truedate) < (select optiondata from ms_w..rptopt where optionname= 'c_firstdate')
) as d1 on
    ex.reg=d1.reg and ex.c_n=d1.c_n    
where
    s.fiscalyear = (select optiondata from ms_w..rptopt where optionname= 'fiscalyear') and
    (s.movedate is null or s.movedate>=(select optiondata from ms_w..rptopt where optionname= 'firstday'))
group by
    i.F_N, i.Si, i.reg, s.startdate, s.fiscalyear, s.progCount, i.status, s.status, s.lid, s.movedate, i.lname, i.fname, i.mi  
having
    dateadd(year,3,max(d1.Termination))>=s.startdate
0
 
LVL 39

Assisted Solution

by:appari
appari earned 124 total points
ID: 38833479
try this query, just changed the  order of table joining,

also get the query execution plan and query costs and post.

if nothing else works start from one table and keep on adding one table at a time and see where the query execution time is increasing. check the indexes on the tables. also you can try database engine tuning advisor and see what it advices.

declare @fiscalyear varchar(15)
declare @firstday varchar(15)
declare @c_firstday varchar(15)

select @fiscalyear = optiondata from ms_w..rptopt where optionname= 'fiscalyear'
select @firstday = optiondata from ms_w..rptopt where optionname= 'firstday'
select @c_firstday = optiondata from ms_w..rptopt where optionname= 'c_firstdate'

Select 
distinct i.Reg, i.F_N + '-' + i.Si As ID, d1.lname+', '+d1.fname+' '+d1.mi as Name, s.lid, 
dateadd(year,3,max(d1.truedate)) as Termination, 
s.startdate, s.movedate, s.progCount as PC,i.status as I, s.status as S 
from i 
	inner join ex 
	on i.reg=ex.reg and i.f_n=ex.f_n and i.si=ex.si 
	inner join 
	(Select * from  s where  s.fiscalyear = @fiscalyear and 
		(s.movedate is null or s.movedate>=@firstday ) s 
	on  i.f_n=s.f_n and i.si=s.si and i.reg=sh.reg 
	inner join data1 d1 
	on ex.reg=d1.reg and ex.c_n=d1.c_n and d1.verify='Y' 
group by i.F_N, i.Si, i.reg, s.startdate, s.fiscalyear,s.progCount,d1.lname,d1.fname,d1.mi, 
d1.verify, i.status, s.status, s.lid , s.movedate 
having dateadd(year,3,max(d1.truedate))>=s.startdate and max(d1.truedate)<
@c_firstday 

Open in new window

0
 

Author Comment

by:mig1980
ID: 38833500
I am not an expert in SQL. Could you explain what you mean by Query Execution Plan and Query costs?

Are the query costs not what i posted on ID: 38828488 above?
0
 
LVL 39

Expert Comment

by:appari
ID: 38833502
yes same thing as you posted early, get the new costs with the changed query.
0
 

Author Comment

by:mig1980
ID: 38833522
Gotcha...I will post those up tomorrow as I am wrapping up for the day. Thank you all for your continued support.
0
 

Author Comment

by:mig1980
ID: 38836994
appart...here is the query cost for the query you provided. It took 2 minutes and 40 seconds to run.


Table 'Worktable'. Scan count 50645, logical reads 566517, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'data1'. Scan count 0, logical reads 867798, physical reads 3961, read-ahead reads 22421, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ex'. Scan count 1, logical reads 12986, physical reads 0, read-ahead reads 12420, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'i'. Scan count 0, logical reads 315853, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 's'. Scan count 1, logical reads 606335, physical reads 368, read-ahead reads 38537, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38837039
Did you try my latest two versions as well?
0
 

Author Comment

by:mig1980
ID: 38837167
I actually did try your query. I let it run for 9 minutes and I finally stopped it. It never finished.
0
 

Author Comment

by:mig1980
ID: 38841161
Any further assistance?
0
 
LVL 39

Expert Comment

by:appari
ID: 38841934
use sqlserver query tuning adviser and see if it suggests any indexes.

another suggestion is start from one table and keep adding one table at a  time and see the query performance. when the performance go down check the table and the joins on that table.

finally without knowing actual table structures, relations between tables and existing indexes difficult to guess and change the query.
0
 
LVL 6

Expert Comment

by:liija
ID: 38842759
...and of course, what is your database's health? State of the current indexes?

In tuning concentrate on minimizing the table scans and physical reads. Each physical read is an actual read from your hard drive. There are quite a few table scans performed, which can take time depending on your table sizes. Table scan means that indexes cannot be utilized.

Table 'rptOpt'. Scan count 5596, logical reads 11192, physical reads 0, read-ahead reads 0,
Table 's'. Scan count 1, logical reads 623510, physical reads 106, read-ahead reads 40969, Table 'ex'. Scan count 4, logical reads 9595, physical reads 0, read-ahead reads 0, lob
Table 'i'. Scan count 4, logical reads 51554, physical reads 0, read-ahead reads 0, lob logical
Table 'data1'. Scan count 0, logical reads 889943, physical reads 3915, read-ahead reads 34397, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
0
 

Author Comment

by:mig1980
ID: 38844872
I am not very familiar with configuring indexes. I do see a few indexes in some of our tables but not sure which would apply to this particular query.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38845676
Please don't take this the wrong way, but perhaps it is time you contacted a reputable and experienced SQL DBA in your area, if there is no one else at work that can help you.
0
 

Author Comment

by:mig1980
ID: 38845702
I will start another question regarding the indexes.

Thanks. Just trying to learn. I have been in IT for 10+ years but I am relatively new to the data warehousing side.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 63 total points
ID: 38850062
I think this is the equivalent of the query wth all aliases - although there was one alias "sh." which I presume should have been "s."

I have moved most record selection into the joins and altered the group by to mimic the field list (there seemed to be 2 redundancies (s.fiscalyear and d1.verify)) and there really should be no reason for "select distinct".

I have also altered DATEADD(YEAR,3,MAX(d1.truedate)) to MAX(DATEADD(YEAR,3,d1.truedate)) :: not sure if this really makes any difference and could be changed back if needed.

SELECT
     i.Reg
   , (i.F_N + '-' + i.Si) AS ID
   , i.status AS I
   , s.status AS S
   , s.lid
   , s.startdate
   , s.movedate
   , s.progCount AS PC
   , (d1.lname + ', ' + d1.fname+' ' + d1.mi) AS Name
   , MAX(DATEADD(YEAR,3,d1.truedate)) AS Termination
FROM i
INNER JOIN s ON i.f_n = s.f_n 
            AND i.si  = s.si 
            AND i.reg = s.reg  /* sh.reg changed to s.reg */
            AND s.fiscalyear = (
                               /* assume this is 1 record only and never null */
                               SELECT optiondata
                               FROM ms_w..rptopt
                               WHERE optionname= 'fiscalyear'
                               ) 
           AND
               (
                   s.movedate IS NULL
                OR s.movedate>=(
                                /* assume this is 1 record only and never null */
                                SELECT optiondata
                                FROM ms_w..rptopt
                                WHERE optionname= 'firstday'
                                )
               )
INNER JOIN ex       ON i.reg = ex.reg 
                   AND i.f_n = ex.f_n 
                   AND i.si  = ex.si
INNER JOIN data1 d1 ON ex.reg = d1.reg 
                   AND ex.c_n = d1.c_n 
                   AND d1.verify = 'Y' 
                   AND d1.truedate < (
                                     /* assume this is 1 record only and never null */
                                      SELECT optiondata
                                      FROM ms_w..rptopt
                                      WHERE optionname= 'c_firstdate'
                                      )
GROUP BY
     i.Reg
   , (i.F_N + '-' + i.Si)
   , i.status
   , s.status
   , s.lid
   , s.startdate
   , s.movedate
   , s.progCount
   , (d1.lname + ', ' + d1.fname+' ' + d1.mi)
HAVING MAX(DATEADD(YEAR,3,d1.truedate)) >= s.startdate

Open in new window


This may not achieve a lot of improvement - but should eradicate any need for "select distinct". Would be good to know if it helps at all.

Would it be possible to get the record counts of each table involved the query please?

e.g. select count(*) from i
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

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

13 Experts available now in Live!

Get 1:1 Help Now