SQL Query Question

you can put a limit on the number of records returned

LIMIT 10;

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

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.

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')

Select allOpen in new window

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.

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.

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

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?

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.

As I mentioned a half hour ago, the query doesn't produce correct results. The termination data is not correct for some reason.

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".

@acperkins: sorry didn't refresh the page before posting, almost same code

No problem, it happens.

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.

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.

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?

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.

Impossible to say without data, statistics, indexes and execution plan. A good guess more or less.

Can you give me options of dealing with the group by?

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?

yes same thing as you posted early, get the new costs with the changed query.

Gotcha...I will post those up tomorrow as I am wrapping up for the day. Thank you all for your continued support.

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.

Did you try my latest two versions as well?

I actually did try your query. I let it run for 9 minutes and I finally stopped it. It never finished.

Any further assistance?

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.

...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.

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.

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.

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.