Gullimeel (5/26/2012)

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

Next time I wont save the typing efforts because it has caused much more typing then i thought it would save me(not a nice optimization decision) so that discussion...

Next time I wont save the typing efforts because it has caused much more typing then i thought it would save me(not a nice optimization decision:-)) so that discussion stays...

Gullimeel (5/24/2012)

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

I am aware of that bug and min on ascending order index wont cause the issue but max will do.I could have very well used min but i am...

Gullimeel (5/25/2012)

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

This is simple arithmatic that IO cost + cpu cost is the total operator cost.How optimizer is calculating IO cost or CPU cost that is explained very well in...

This is simple arithmatic that IO cost + cpu cost is the total operator cost.How optimizer is calculating IO cost or CPU cost that is explained very well in Joe...

I told the optimizer alogorithm to calculate the cost based on IO and cpu cost..

Gullimeel (5/25/2012)

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

Paul.

In attached screenshot if you will see that the IO cost estimation is 185.2 and CPU estimation cost is around 4.8. Then total subtree cost should have been around...

Guys,

Thanks for the assistance!

I removed the reference to "AllRecentWages" and got 850k records in 10 seconds, so that was certainly a big part of the issue!

Now i need to figure...

Paul.

In attached screenshot if you will see that the IO cost estimation is 185.2 and CPU estimation cost is around 4.8. Then total subtree cost should have been around 190.15...

You are welcome...

I think the real issue is the table AllRecentwages where the actual rows are 95 (estimated is 16763) for 373k rows plan but is 4.4. million(estimated is 8000+) for 473K...

Try to execute the query with 475 K rows with recompile and see what kind of plan is being generated..

See the first table profiles which returns around 373 K...

Use the following

insert into @temp1 select "account's value"

or

insert into@ temp1 select 'account''s value')

Post both actual execution plans.Otherwise from this statement nothing can not be said..One possible reason could be that when you run proc with 350K rows it generated one plan and...

--Table Driven version.

SELECT T1.ID, T1.CHILD_ID, T2.ID

FROM DBO.table_1 T1 WITH (NOLOCK)

LEFT OUTER JOIN DBO.table_2 T2 WITH (NOLOCK)

ON T1.CHILD_ID = T2.ID

WHERE ISNULL(T1.ISDELETED,0)=0

AND ISNULL(T2.ISDELETED,0)=0

SELECT T1.ID, T1.CHILD_ID, T2.ID

FROM DBO.table_1 T1 WITH (NOLOCK)

LEFT OUTER JOIN...