MadAdmin,

First of all, I updated attached scripts.

Made them smaller to narrow down the problem.

I agree with all your concerns.

Join on a function, TRIM, etc. - bad ideas.

The problem is.

My hands...

Missing Index is only suggested in IDS INSERT plan.

It runs fast anyway. Problem is with GMS2 INSERT query.

There are no suggestions in GMS2 INSERT plan.

i tried this index before. No...

What a surprise!

As soon as I remove DISTINCT from GMS2 INSERT (second insert into temp table)

it runs 10 seconds !

And it inserts 20,955 rows.

How DISTINCT can increase execution time from...

Execution Plans attached as promised...

Luis,

After I analyzed the next slow block (let's call it "GMS2 INSERT"),

I shifted my focus to much bigger issue (in my opinion).

It is very similar to my original query I...

Luis,

So what is the solution

for

The estimated row counts are way off

UPDATE STATISTICS ?

Luis,

So what should I do about

". . . The estimated row counts are way off, but I'm not sure if they're like that from the non-equi joins.."

Hello Luis,

"....No, I'm just saying that if it's a cross join, you should write it as such..."

Do you mean you would lie to see

SELECT .....

FROM

a

CROSS JOIN

b

style ?

Luis,

Indexes are there.

I didn't notice DDL script did not generate index info..

I re-attached new DDL

Added DDL for two problematical tables...

S_CLIENT_SHARED

S_CLIENT_ACCOUNT_ASSOCIATION_SHARED

Thanks Luis.

Without DISTINCT, row count is actually 20 billion something.

Maybe that's what Estimated Row counts show ?

To me if without DISTINCT query returns 20 billion records from 80 million...

Thanks Luis.

So you insist CROSS JOIN is a bottleneck.. I guess.

I actually did a test.

I removed JOIN ON (1=1).

But the execution time did not change at all...

Luis,

I didn't understand this...

regarding

ON (1=1)

after all maybe it's not a big deal because LAST_ANNUAL table

is only one row (always).

so it's not really a CROSS JOIN.

Original query:

SELECT DISTINCT

ACCOUNT_SHARED.SOURCE_CODE

,ACCOUNT_SHARED.ACCOUNT_IDENTIFIER

INTO #COMMON_I_SS_ARRANGEMENT_LOCATION_ADDR_IDS_CURRENT_VALID_ARRANGEMENT

FROM

(SELECT MAX(DATA_SET.BUS_PROC_DATE)...

Folks,

I just discovered an interesting thing.

The original query uses DISTINCT.

SELECT DISTINCT

ASSOCIATION.SOURCE_CODE, ASSOCIATION.CUSTOMER_NUMBER

. . . . . . .

Without DISTINCT

it returns 1.8 billion records !!

With DISTINCT - it returns...