Top N Sort issue

Matak 2015-12-10 23:30:17

Hi,

Hoping someone can help me tune the query. The pain point is the Sort that i just cant make any better.
The data set this was run against a minimal set of data and the real data set is significantly larger (i stopped it after an hour).
Happy to create any indexes or rewrite code that will be helpful.

If any other information is needed please let me know.


link text

Sorry for taking so long. Not always easy to get approval for posting the real ddl.
I was only testing the first query in isolation and really shouldnt have. Sorry for wasting your time with that.
Ive now added the two components that have the most performance issues.

Unfortunately i dont know how to format sql code on here so apologies for the mess.
Any help is appreciated.

Here is the index i created based on Pauls suggestion but it didnt help with my issue initially.

CREATE NONCLUSTERED INDEX [idx2] ON [dbo].[deliverable_hist]
(
event_dtm ASC,
deliverable_id,
deliverable_state_id,
event_nme
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

create table #temp2 (
[date] date,
person_of_interest_id int,
episode_id int,
episode_segment_id int,
start_dte datetime,
end_dte datetime,
location_id int
)

CREATE TABLE [dbo].[irna_assessment](
[deliverable_id] [int] NOT NULL,
[questionnaire_id] [int] NOT NULL,
[episode_segment_id] [int] NOT NULL,
[assessment_dte] [datetime] NOT NULL
CONSTRAINT [irna_assessment_pk] PRIMARY KEY CLUSTERED
(
[deliverable_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[deliverable_hist](
[deliverable_hist_id] [int] IDENTITY(1,1) NOT NULL,
[deliverable_id] [int] NOT NULL,
[deliverable_state_id] [int] NOT NULL,
[event_nme] [dbo].[NAME_LONG] NOT NULL,
[event_dtm] [datetime] NOT NULL
CONSTRAINT [deliverable_hist_pk] PRIMARY KEY CLUSTERED
(
[deliverable_hist_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[deliverable](
[deliverable_id] [int] IDENTITY(1,1) NOT NULL,
[deliverable_type_id] [int] NOT NULL,
[deliverable_state_id] [int] NOT NULL,
[deliverable_state_system_user_id] [int] NOT NULL,
[episode_id] [int] NULL,
[location_id] [int] NULL
CONSTRAINT [deliverable_pk] PRIMARY KEY CLUSTERED
(
[deliverable_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[movement](
[movement_id] [int] IDENTITY(1,1) NOT NULL,
[episode_segment_id] [int] NOT NULL,
[offender_relocation_deliverable_id] [int] NOT NULL,
[to_location_id] [int] NULL,
[from_location_id] [int] NULL,
[move_out_dtm] [datetime] NULL,
[move_in_dtm] [datetime] NULL,
[move_out_completed_ind] [dbo].[BOOLEAN] NOT NULL,
[move_in_completed_ind] [dbo].[BOOLEAN] NOT NULL
CONSTRAINT [movement_pk] PRIMARY KEY CLUSTERED
(
[movement_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[offender_relocation](
[deliverable_id] [int] NOT NULL,
[movement_type_id] [int] NOT NULL
CONSTRAINT [offender_relocation_pk] PRIMARY KEY CLUSTERED
(
[deliverable_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[episode_segment](
[episode_segment_id] [int] IDENTITY(1,1) NOT NULL,
[episode_id] [int] NOT NULL,
[person_of_interest_id] [int] NOT NULL,
[offender_state_id] [int] NOT NULL,
[start_dte] [datetime] NOT NULL,
[end_dte] [datetime] NULL
CONSTRAINT [episode_segment_pk] PRIMARY KEY CLUSTERED
(
[episode_segment_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]

SQLkiwi 2015-12-11 10:18:32
On the face of it, there is no reason that a plan without a sort on the inner side of the loop join should not be possible. Be sure you have an index with Object29.Column36 as the leading column. The other three columns from the same table that are referenced in the apply section would need to be in the index as keys or includes to be covering.

If such an index already exists, you may need to use an index hint or forceseek hint temporarily to see why the optimizer is not choosing that option. If you would like a more detailed analysis, please edit your question to include DDL for the tables and indexes, and a un-anonymized version of the query.

Working from the anonymized text alone, the following quick translation indicates that a non-sort plan is possible:

CREATE TABLE #T0 (c1 int, c2 int)        
CREATE TABLE #T1 (c1 int, c2 varchar(50), c3 int, c4 int)
CREATE TABLE #T2 (c1 int)
CREATE TABLE #T3 (c1 int, c2 int, c3 int, c4 int)
 
CREATE INDEX i1 ON #T1 (c1)
 
SELECT * 
FROM #T0
OUTER APPLY
(
    SELECT TOP (1)
        #T1.c1,
        1 AS c2,
        CASE WHEN COALESCE(#T0.c1, #T1.c1, #T2.c1, #T3.c1) < 10 THEN 1 ELSE 0 END AS c3
    FROM #T1
    JOIN #T2 ON #T2.c1 = #T1.c1
    JOIN #T3 ON #T3.c1 = #T2.c1
        AND #T1.c4 >= #T3.c3
        AND #T1.c4 <= #T3.c4
    WHERE 
        #T1.c2 LIKE '%xyz%'
        AND #T1.c3 IN (1, 4, 9)
        AND #T3.c2 = #T0.c1
    ORDER BY
        #T1.c1
) AS OA

I may have missed something there, hence the request for the real DDL and query.

Matak 2015-12-11 12:43:25
Thanks for the info. Late Friday night here so I can't get to this until Monday morning. I will take a look at your info and let you know how I go.