Luis,

I attached Execution Plan.

The rest I covered already.

S_CLIENT_SHARED

S_CLIENT_ACCOUNT_ASSOCIATION_SHARED

have 80 million records.

Other tables are very small.

Without JOIN with S_CLIENT_ACCOUNT_ASSOCIATION_SHARED

sql runs 1 second.

With this JOIN it takes 20 seconds

Gerald. Thanks for your reply.

FATCA_STG.S_CLIENT_ACCOUNT_ASSOCIATION_SHARED indexes:

------------------------------------------------------------------

SOURCE_CODE, BUS_PROC_DT, ACCOUNT_IDENTIFIER

SOURCE_CODE, BUS_PROC_DT, CUSTOMER_NUMBER

BUS_PROC_DT

FATCA_STG.S_CLIENT_SHARED indexes:

------------------------------------------------------------------

SOURCE_CODE, BUS_PROC_DT, CUSTOMER_NUMBER

ID

At the beginning that (1=1) drew my attention as well

but query slows down only when you

JOIN

FATCA_STG.S_CLIENT_ACCOUNT_ASSOCIATION_SHARED

Without this...

RESOLVED by re-setting Visual Studio settings.

Steps below..

Start / Run

devenv /ResetSettings

devenv /ResetSkipPKGS

Close Visual Studio

Open again

Sorry. I was not detailed enough.

It's SSIS Variable Expression Builder I am talking about.

Hello Koen,

I just noticed this returns the same results.

Do I really need this last line ?

SELECT *

FROM prj_demo_data

WHERE prj_code IN

(

SELECT prj_code

FROM prj_demo_data

WHERE demo_code = 'DE1' and demo_data_vchar = 'U'

INTERSECT

SELECT...

God Bless you , Koen Verbeeck !

First I thought what an easy task...and then I felt embarrassed when after 3 hours I was still not able

to select those pairs.

I need...

Alan B.

Unfortunately it is Third Party Vendor Application database.

We are not allowed to change schema.

We store all our code in external database where we pull data from views that

are...

Thanks guys for your response !

ahperez is referring to a good article about parameter sniffing

but it mostly makes recommendations for the case of stored procedure.

In my case it is a...

Thanks Steve.

There are good arguments.

I wrote them down.

ScottPletcher,

We don't have authority to alter table definitions.

This is Financial Application database by third party.

We don't own the database.

We even create all our custom code in a separate (Diff_Data) database

that...

Another interesting thing with [ prj_detail ] table.

A very simple query runs 4 seconds with hard_coded value co_code = '7001'

and it runs

1 minute using parameter.

use proddb6

declare @P_CO_CODE varchar(4)

set @P_CO_CODE =...

I am attaching DDL script with indexes for [prj_detail] table.

STATS last updated on October 6th.

In Production it is October 27th.

I don't know if it's good or bad.

I compared execution plans

with parameter 'VEH' and 'COM'

and in both cases it is this step that is taking up all the resources:

Index Scan (NonClustered)

[prj_detail].[IXN_prj_detail__per_end_date__detail_type_ind__evc_code__evc_type_code]

Cost: 91%

Another pattern I noticed is that...

I am attaching "UnitPricingTrans.sqlplan" file

Looks like it was cached with @P_UNIT_TYPE = 'VEH' (runs 7 seconds).

With all other parameter values it runs 2.5 minutes now.

I tried "WITH RECOMPILE" to clear cache but nothing changed.

7...