Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

SQL Query Question

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
mig1980
Asked:
mig1980
  • 15
  • 8
  • 5
  • +6
8 Solutions
 
nickinthoozCommented:
you can put a limit on the number of records returned

LIMIT 10;
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Scott PletcherSenior DBACommented:
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
mig1980Author Commented:
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
 
liijaCommented:
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
 
mig1980Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
lcohanDatabase AnalystCommented:
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
 
mig1980Author Commented:
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
 
mig1980Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
mig1980Author Commented:
As I mentioned a half hour ago, the query doesn't produce correct results. The termination data is not correct for some reason.
0
 
Scott PletcherSenior DBACommented:
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
 
Anthony PerkinsCommented:
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
 
appariCommented:
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
 
appariCommented:
@acperkins: sorry didn't refresh the page before posting, almost same code
0
 
Anthony PerkinsCommented:
No problem, it happens.
0
 
mig1980Author Commented:
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
 
liijaCommented:
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
 
mig1980Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
liijaCommented:
Impossible to say without data, statistics, indexes and execution plan. A good guess more or less.
0
 
mig1980Author Commented:
Can you give me options of dealing with the group by?
0
 
Scott PletcherSenior DBACommented:
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
 
Scott PletcherSenior DBACommented:
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
 
appariCommented:
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
 
mig1980Author Commented:
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
 
appariCommented:
yes same thing as you posted early, get the new costs with the changed query.
0
 
mig1980Author Commented:
Gotcha...I will post those up tomorrow as I am wrapping up for the day. Thank you all for your continued support.
0
 
mig1980Author Commented:
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
 
Scott PletcherSenior DBACommented:
Did you try my latest two versions as well?
0
 
mig1980Author Commented:
I actually did try your query. I let it run for 9 minutes and I finally stopped it. It never finished.
0
 
mig1980Author Commented:
Any further assistance?
0
 
appariCommented:
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
 
liijaCommented:
...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
 
mig1980Author Commented:
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
 
Anthony PerkinsCommented:
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
 
mig1980Author Commented:
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
 
PaulCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 15
  • 8
  • 5
  • +6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now