Requesting Advanced T-SQL help

Need some guru help with a monster T-SQL query. Trying to make it more
efficient, possibly less code, and anything else I can do to it to make it
run faster. This is an SP and Tables created by the person I replaced, but
I need to deal with it...for now.
I have a Status table that has 50 columns and currently contains 3.3+
million records in it. I don't have the luxury of altering the table, so I
am stuck with this thing (table structure attached at the end of this post).
Still being relatively new here, I wouldn't even know where to begin
changing it if I could. Anyhow...
Quick description: Our website provides a search utility that deals with
this one table, and is a typical search in that you can search by name, a
date range, member ID, etc. There is also a ListBox that displays Doctors
the user view info on, which allows multiple-selection. Making the
appropriate selections, providing any other search criteria, the search app
queries against this rather large table and returns a listing of associated
records. That's the goal.
The ugly truth...
The query that fires off from this search is equally gargantuan (SP code
posted below) and was taking for-ev-er to run. I was able to play around
with the indexes on the table (one clustered and one non-clustered) which
dropped the execution time of a single run to an average of 5 seconds. If I
tried any additional indexes, or modifying these two, it only seemed to make
the execution take longer. In order to search on multiple doctor numbers
(that pesky multi-select listbox), the .asp code runs the stored proc once
for each doctor number and then appends each set of records that comes back
and displays them in a 'list' on the webpage. But when it takes at best 4~5
seconds per sp execution, selecting 3 or 4 Doctors will have the users
frustrated beyond belief waiting for the search results.
So, I ask for the help of T-SQL Gurus here on improving this monster. I
have tried, but am not getting anywhere. Here is the code, and I hope to
get some suggestions. Thanks
-- Andrew
P.S.: I hope the below is enough as I cannot share the data in the table due
to privacy concerns.
Table Structure
CREATE TABLE [MedMC_Claim_Status] (
[TMBR#] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MMFNAM] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MMINIT] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MMLNAM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TADOC#] [decimal](9, 0) NULL ,
[AHCCCS_ID] [decimal](9, 0) NULL ,
[DFNAM] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DINIT] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DLNAM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DSSN#] [decimal](9, 0) NULL ,
[DFTAX#] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TPAYE#] [decimal](9, 0) NULL ,
[PDFNAM] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDINIT] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDLNAM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TCLAIM] [decimal](9, 0) NOT NULL ,
[TSRVDT] [smalldatetime] NULL ,
[TSRVTDT] [smalldatetime] NULL ,
[TSTATS] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TSTATS_DESC] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TFLAG4] [decimal](6, 0) NOT NULL ,
[TRCVDT] [smalldatetime] NULL ,
[TPROC] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TMOD2] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TMOD3] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TREVCD] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TQTY] [decimal](7, 0) NULL ,
[TAUTH#] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TADDDT] [smalldatetime] NULL ,
[TCHRGS] [decimal](8, 2) NULL ,
[TAUTH] [decimal](8, 2) NULL ,
[TCOPAY] [decimal](8, 2) NULL ,
[TCOB] [decimal](8, 2) NULL ,
[TFLD16] [decimal](8, 2) NULL ,
[TWHIAM] [decimal](8, 2) NULL ,
[TDIS] [decimal](8, 2) NULL ,
[TDED] [decimal](8, 2) NULL ,
[TPADDT] [smalldatetime] NULL ,
[TCHK#] [decimal](6, 0) NULL ,
[TINV#] [decimal](6, 0) NULL ,
[DENITY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDENITY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HLDESC] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HFLAG2] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DADR1] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DADR2] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DCITY] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DSTAT] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DZIPC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dtmCrea_Date] [datetime] NULL CONSTRAINT
[DF_MedMC_Claim_Status_dtmCrea_Date] DEFAULT (getdate())
) ON [PRIMARY]
GO
Table Indexes
CREATE CLUSTERED INDEX [CL_MedMCClaimStatus_TmbrNum,TDocNum,TCl
aim,TFlag4]
ON [dbo].[MedMC_Claim_Status]([TMBR#], [TADOC#], [TCLAIM], [TFLAG4]) ON
[PRIMARY]
CREATE INDEX [NCL_MedMCClaimStatus_MMFnam,MMLnam,AHCC
CSID,DFTaxNum] ON
[dbo].[MedMC_Claim_Status]([MMFNAM], [MMLNAM], [AHCCCS_ID], [DFTAX#]) ON
[PRIMARY]
GO
Search Query
CREATE PROCEDURE stp_MedMC_Claim_1st_Level_Retrieve
@.TADOC# DECIMAL(9,0) = NULL,
@.DFTAX# char(12) = NULL,
@.TSRVDTfrom datetime = NULL, --'1/1/2005'
@.TSRVDTto datetime = NULL,
@.FINAL_STATUS VARCHAR(20) = null,
@.TMBR# VARCHAR(12) = NULL,
@.MMFNAM VARCHAR(12) = NULL,
@.MMLNAM VARCHAR(12) = NULL,
@.Loc decimal(9,0) = null
AS
set nocount on
SELECT * FROM
(
select
A.TMBR#,
A.TCLAIM,
A.TFLAG4,
C.TOTALCOUNT,
REPLACE(RTRIM(LTRIM(A.MMFNAM)) + ' ' + RTRIM(LTRIM(A.MMINIT)) + '. ', ' .
', ' ') + RTRIM(LTRIM(A.MMLNAM)) AS MMNAME,
A.TADOC#,
A.DFTAX# ,
A.AHCCCS_ID,
A.DADR1,
CASE
WHEN A.DENITY = 'P' THEN REPLACE(RTRIM(LTRIM(A.DFNAM)) + ' ' +
RTRIM(LTRIM(A.DINIT)) + '. ', ' . ', ' ') + RTRIM(LTRIM(A.DLNAM))
ELSE RTRIM(LTRIM(A.DLNAM)) + RTRIM(LTRIM(A.DFNAM))
END AS DNAME,
A.TPAYE#,
CASE
WHEN A.PDENITY = 'P' AND RTRIM(LTRIM(A.PDINIT)) <> '' THEN
REPLACE(RTRIM(LTRIM(A.PDFNAM)) + ' ' + RTRIM(LTRIM(A.PDINIT)) + '. ', ' . ',
' ') + RTRIM(LTRIM(A.PDLNAM))
ELSE RTRIM(LTRIM(A.PDLNAM)) + RTRIM(LTRIM(A.PDFNAM))
END AS PDNAME,
CONVERT(varchar(10), A.TSRVDT, 101) as TSRVDT,
A.TSTATS,
A.TSTATS_DESC,
B.TTFLD16,
case
when D.S1 > 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 = 0 AND D.S5 = 0 then
'RECEIVED'
when D.S1 = 0 AND D.S2 > 0 then 'PROCESSING'
when D.S1 = 0 AND D.S2 = 0 AND D.S3 > 0 then 'PAID'
when D.S1 = 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 > 0 then 'DENIED'
when D.S1 = 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 = 0 AND D.S5 > 0 then
'VOID'
END AS FINAL_STATUS
from MedMC.dbo.MedMC_Claim_Status A
INNER JOIN
(
select TMBR#, TCLAIM, MAX(TFLAG4) AS TFLAG4
from MedMC.dbo.MedMC_Claim_Status
WHERE AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND DFTAX# = COALESCE(@.DFTAX#, DFTAX#)
and TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and coalesce(@.TSRVDTto,
@.TSRVDTfrom, TSRVDT)
AND TMBR# = COALESCE(@.TMBR#, TMBR#)
AND MMFNAM LIKE COALESCE(@.MMFNAM, MMFNAM)
AND MMLNAM LIKE COALESCE(@.MMLNAM, MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
GROUP BY TMBR#, TCLAIM
) E
INNER JOIN
(
SELECT TMBR#, TCLAIM, SUM(TFLD16) AS TTFLD16
FROM MedMC.dbo.MedMC_Claim_Status
WHERE AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND DFTAX# = COALESCE(@.DFTAX#, DFTAX#)
and TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and coalesce(@.TSRVDTto,
@.TSRVDTfrom, TSRVDT)
AND TMBR# = COALESCE(@.TMBR#, TMBR#)
AND MMFNAM LIKE COALESCE(@.MMFNAM, MMFNAM)
AND MMLNAM LIKE COALESCE(@.MMLNAM, MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
GROUP BY TMBR#, TCLAIM
) B
INNER JOIN
(
select TMBR#, TCLAIM, COUNT(*) AS TOTALCOUNT
from MedMC.dbo.MedMC_Claim_Status
WHERE AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND DFTAX# = COALESCE(@.DFTAX#, DFTAX#)
and TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and coalesce(@.TSRVDTto,
@.TSRVDTfrom, TSRVDT)
AND TMBR# = COALESCE(@.TMBR#, TMBR#)
AND MMFNAM LIKE COALESCE(@.MMFNAM, MMFNAM)
AND MMLNAM LIKE COALESCE(@.MMLNAM, MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
GROUP BY TMBR#, TCLAIM
) C
INNER JOIN
(
select
TMBR#, TCLAIM,
SUM(CASE when TSTATS = '1' then 1 else 0 END) AS S1,
SUM(CASE when TSTATS = '2' then 1 else 0 END) AS S2,
SUM(CASE when TSTATS = '3' then 1 else 0 END) AS S3,
SUM(CASE when TSTATS = '4' then 1 else 0 END) AS S4,
SUM(CASE when TSTATS = '5' then 1 else 0 END) AS S5
from MedMC.dbo.MedMC_Claim_Status
WHERE AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND DFTAX# = COALESCE(@.DFTAX#, DFTAX#)
and TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and coalesce(@.TSRVDTto,
@.TSRVDTfrom, TSRVDT)
AND TMBR# = COALESCE(@.TMBR#, TMBR#)
AND MMFNAM LIKE COALESCE(@.MMFNAM, MMFNAM)
AND MMLNAM LIKE COALESCE(@.MMLNAM, MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
GROUP BY TMBR#, TCLAIM
) D
ON C.TMBR# = D.TMBR# AND C.TCLAIM = D.TCLAIM
ON B.TMBR# = C.TMBR# and B.TCLAIM = C.TCLAIM
ON E.TMBR# = B.TMBR# and E.TCLAIM = B.TCLAIM
ON A.TMBR# = E.TMBR# AND A.TCLAIM = E.TCLAIM AND A.TFLAG4 = E.TFLAG4
where 1 = 1
AND A.AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND A.DFTAX# = COALESCE(@.DFTAX#, A.DFTAX#)
AND TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and coalesce(@.TSRVDTto,
@.TSRVDTfrom, TSRVDT)
AND A.MMFNAM LIKE COALESCE(@.MMFNAM, A.MMFNAM)
AND A.MMLNAM LIKE COALESCE(@.MMLNAM, A.MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
) T
where 1 = 1
AND FINAL_STATUS = COALESCE(UPPER(@.FINAL_STATUS), FINAL_STATUS)
ORDER BY TSRVDT desc
set nocount offOn Thu, 11 May 2006 15:41:13 -0700, Andrew wrote:

>Need some guru help with a monster T-SQL query. Trying to make it more
>efficient, possibly less code, and anything else I can do to it to make it
>run faster. This is an SP and Tables created by the person I replaced, but
>I need to deal with it...for now.
>I have a Status table that has 50 columns and currently contains 3.3+
>million records in it. I don't have the luxury of altering the table, so I
>am stuck with this thing (table structure attached at the end of this post)
.
>Still being relatively new here, I wouldn't even know where to begin
>changing it if I could. Anyhow...
Hi Andrew,
First some quick comments on the table. Keep them until you DO have time
to improve the schema.
* FIND AND DECLARE THE KEY!!!!!! You curently have two nonunique
indexes, but no PRIMARY KEY constraint, no UNIQUE constraint and no
UNIQUE indexes. This is an open invitation to duplicated data. It also
deprives the query optimizer of essential knowledge about your data that
it can use to find more efficient execution plans. And it limits my
possibilities to modify your query.
* Most of the columns allow NULLs. Allowing NULLs on such a high
fraction of the columns is quite unlikely. Change as many of them to NOT
NULL as you can.
* Consider changing the decimal (x, 0) columns to int.
* Change the varchar(1) columns to char(1), unless you really need to
store the difference between '' (empty string) and ' ' (one space). Each
varchar column incurs two bytes of overhead to store the length. No
problem for longer varchar columns (you'll save more than two bytes on
average), but a waste for short strings.

>Quick description: Our website provides a search utility that deals with
>this one table, and is a typical search in that you can search by name, a
>date range, member ID, etc.
Read Erlands article: http://www.sommarskog.se/dyn-search.html. There
are many ways to improve performance of flexible search queries such as
yours. In the rest of my reply, I won't address this part of your query
since Erland already covered that very well.
One thing I will mention because it can really make a HUGE difference:
do you really need a LIKE search in all these cases? Searching for
equality is often lots faster. A LIKE search for a pattern that starts
with % or _ (or a pattern unknown to the optimizer, e.g. because of a
variable) almost always results in table or index scans.
(snip)
>The query that fires off from this search is equally gargantuan (SP code
>posted below) and was taking for-ev-er to run. I was able to play around
>with the indexes on the table (one clustered and one non-clustered) which
>dropped the execution time of a single run to an average of 5 seconds.
Congratulations - that's a great achievement! But no reason to stop
there.

> If I
>tried any additional indexes, or modifying these two, it only seemed to mak
e
>the execution take longer.
Then you have probably found the best set of indexes for this query. I
haven't looked at the indexes and their usability; I concentrated on
rewriting the query itself. Maybe you should fiddle the indexes some
more after testing my version of the query (below).

> In order to search on multiple doctor numbers
>(that pesky multi-select listbox), the .asp code runs the stored proc once
>for each doctor number and then appends each set of records that comes back
>and displays them in a 'list' on the webpage. But when it takes at best 4~
5
>seconds per sp execution, selecting 3 or 4 Doctors will have the users
>frustrated beyond belief waiting for the search results.
Indeed. Luckily, Erland has covered this common problem as well. Check
out this article: http://www.sommarskog.se/arrays-in-sql.html. I suggest
that you use one of the techniques to get the selected doctor numbers in
a table, then join against that table. This is another aspect of your
question that I won't cover any further.

>So, I ask for the help of T-SQL Gurus here on improving this monster. I
>have tried, but am not getting anywhere. Here is the code, and I hope to
>get some suggestions. Thanks
(snip H U G E query)
The first thing that struck me is that you have a totall of four derived
tables that all use the EXACT same FROM and WHERE clause (unless I am
overlooking some minor difference?). It looks as if your predecessor
thought that you were limited to one aggregate per subquery. You can
replace these four subqueries with one. That also rids you of the (in
this case) unneeded non-standard ordering of JOIN and ON clauses. Was
your predecessor coding for job security?
The main query also repeats the exact same WHERE clause (apart from the
totally unneeded 1 = 1), but it lacks the GROUP BY that is present in
the derived tables. I can't say that I like this - you might end up with
several detail rows that each show the same aggregated results in
addition to the different details. I guess "AND A.TFLAG4 = E.TFLAG4" is
intended to solve this, but I'm not sure if this is failsafe. This is
where I miss knowing the keys!
Another suspicious part is the CASE statement for the status:

> case
> when D.S1 > 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 = 0 AND D.S5 = 0 then
>'RECEIVED'
> when D.S1 = 0 AND D.S2 > 0 then 'PROCESSING'
> when D.S1 = 0 AND D.S2 = 0 AND D.S3 > 0 then 'PAID'
> when D.S1 = 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 > 0 then 'DENIED'
> when D.S1 = 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 = 0 AND D.S5 > 0 then
>'VOID'
> END AS FINAL_STATUS
Are you aware that many combinations of S1, S2, ..., and S5 will fall
through all the WHEN clauses and yield a NULL result? (For instance if
both S1 and S2 are >0, or when all are =0, to name just two examples). I
don't know your data, so you'll have to decide if this is a problem. A
possible rewrite (depending on your data -- don't copy blindly, it is
not the exact same logic!!) that takes advantage of the order of
evaluation of the CASE expression would be:
CASE
WHEN D.S1 > 0 THEN 'Received'
WHEN D.S2 > 0 THEN 'Processing'
WHEN D.S3 > 0 THEN 'Paid'
WHEN D.S4 > 0 THEN 'Denied'
ELSE 'Void'
END
But in the rewritten query bellow, I'll retain the original.
The only "safe" improvement of your query that I see is the removal of
the duplicated derived tables. I expect a good performance improvement
from that. I'm quite sure that there are more possibilities to optimize
the query even further, but they require a better understanding of the
data than I have (at least knowledge of the keys!).
Here's my suggestion for a rewritten query:
SELECT * FROM
(
select
A.TMBR#,
A.TCLAIM,
A.TFLAG4,
B.TOTALCOUNT,
REPLACE(RTRIM(LTRIM(A.MMFNAM)) + ' ' + RTRIM(LTRIM(A.MMINIT)) + '. ',
' . ', ' ') + RTRIM(LTRIM(A.MMLNAM)) AS MMNAME,
A.TADOC#,
A.DFTAX# ,
A.AHCCCS_ID,
A.DADR1,
CASE
WHEN A.DENITY = 'P'
THEN REPLACE(RTRIM(LTRIM(A.DFNAM)) + ' ' + RTRIM(LTRIM(A.DINIT)) + '.
', ' . ', ' ') + RTRIM(LTRIM(A.DLNAM))
ELSE RTRIM(LTRIM(A.DLNAM)) + RTRIM(LTRIM(A.DFNAM))
END AS DNAME,
A.TPAYE#,
CASE
WHEN A.PDENITY = 'P' AND RTRIM(LTRIM(A.PDINIT)) <> ''
THEN REPLACE(RTRIM(LTRIM(A.PDFNAM)) + ' ' + RTRIM(LTRIM(A.PDINIT)) +
'. ', ' . ', ' ') + RTRIM(LTRIM(A.PDLNAM))
ELSE RTRIM(LTRIM(A.PDLNAM)) + RTRIM(LTRIM(A.PDFNAM))
END AS PDNAME,
CONVERT(varchar(10), A.TSRVDT, 101) as TSRVDT,
A.TSTATS,
A.TSTATS_DESC,
B.TTFLD16,
case
when B.S1 > 0 AND B.S2 = 0 AND B.S3 = 0 AND B.S4 = 0 AND B.S5 = 0
then 'RECEIVED'
when B.S1 = 0 AND B.S2 > 0 then 'PROCESSING'
when B.S1 = 0 AND B.S2 = 0 AND B.S3 > 0 then 'PAID'
when B.S1 = 0 AND B.S2 = 0 AND B.S3 = 0 AND B.S4 > 0 then 'DENIED'
when B.S1 = 0 AND B.S2 = 0 AND B.S3 = 0 AND B.S4 = 0 AND B.S5 > 0
then 'VOID'
END AS FINAL_STATUS
from MedMC.dbo.MedMC_Claim_Status A
INNER JOIN
(
select TMBR#, TCLAIM,
MAX(TFLAG4) AS TFLAG4,
SUM(TFLD16) AS TTFLD16,
COUNT(*) AS TOTALCOUNT,
SUM(CASE when TSTATS = '1' then 1 else 0 END) AS S1,
SUM(CASE when TSTATS = '2' then 1 else 0 END) AS S2,
SUM(CASE when TSTATS = '3' then 1 else 0 END) AS S3,
SUM(CASE when TSTATS = '4' then 1 else 0 END) AS S4,
SUM(CASE when TSTATS = '5' then 1 else 0 END) AS S5
from MedMC.dbo.MedMC_Claim_Status
WHERE AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND DFTAX# = COALESCE(@.DFTAX#, DFTAX#)
and TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and
coalesce(@.TSRVDTto, @.TSRVDTfrom, TSRVDT)
AND TMBR# = COALESCE(@.TMBR#, TMBR#)
AND MMFNAM LIKE COALESCE(@.MMFNAM, MMFNAM)
AND MMLNAM LIKE COALESCE(@.MMLNAM, MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
GROUP BY TMBR#, TCLAIM
) B
ON A.TMBR# = B.TMBR# AND A.TCLAIM = B.TCLAIM AND A.TFLAG4 = B.TFLAG4
WHERE
A.AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND A.DFTAX# = COALESCE(@.DFTAX#, A.DFTAX#)
AND TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and
coalesce(@.TSRVDTto, @.TSRVDTfrom, TSRVDT)
AND A.MMFNAM LIKE COALESCE(@.MMFNAM, A.MMFNAM)
AND A.MMLNAM LIKE COALESCE(@.MMLNAM, A.MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
) T
where FINAL_STATUS = COALESCE(UPPER(@.FINAL_STATUS), FINAL_STATUS)
ORDER BY TSRVDT desc
(Untested, since you didn't post INSERT statements with sample data to
test on and expected results to verify my results).
Hugo Kornelis, SQL Server MVP|||In addition to what Hugo posted, I grabbed the query execution plan and
noticed a lot of table scans and sort operations. If you can figure out a
way to tighten up the WHERE clauses and the indexing a little more to match
these queries, you can probably improve performance dramatically. All those
LTRIM(RTRIM()) and REPLACE() statements in the WHERE clause don't help,
since it has to perform those operations on every single row. The RTRIM's
are superfluous, and why not LTRIM() it at insert/update time?
Take a look at the query execution plan for the best ideas on how to
optimize.
You probably already know it's a bad idea to use a # character in
variable/parameter/column names - I guess the guy you replaced wasn't aware
:)
"Andrew" <AndrewR2k1@.hotmail.com> wrote in message
news:unyFEvUdGHA.5048@.TK2MSFTNGP04.phx.gbl...
> Need some guru help with a monster T-SQL query. Trying to make it more
> efficient, possibly less code, and anything else I can do to it to make it
...|||Another option to explore (after you look at Hugo's and Mike's) is the
issue of your clustered index. You didn't say how often data is
inserted into the primary table, or what your maintenance plan is , but
I'd be willing to bet that with such a wide clustered index, you are
looking at some heavy duty fragmentation. You might try moving your
clustered index to the last column in the table (the one that defaults
to GETDATE()) to see if that improves response; rebuild the current
clustered index as a nonclustered index on those same columns.
Of course, if you rarely insert data, then disregard my advice;
instead, focus on a good defragmentation plan that runs often enough to
keep your indexes in shape.
Stu