Thanks all, I upgraded it and installed the sample database problem free.

It turns out my local SQL is NOT 2012, but actually 2008R2!

select @@VERSION
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
    Jun 17 2011...

TheSQLGuru (1/6/2017)

---

1) Spooling back 3M+ rows to a calling program (SSMS I suppose?) is going to be SLOW.

2) Can you please capture the actual query plan and post that up?

3)...

drew.allen (1/9/2017)

---

You do have a partial CROSS JOIN somewhere. Looking at the query plan, the hash match join has inputs with 297K and 907K rows, but the output is...

Chris Harshman (1/9/2017)

---

halifaxdal (1/9/2017)

---

I did some extraction before hand, and the new query is much faster now, however, still the result gets exploded. Here is what I did:

1. I generated...

After I created two interim tables in advance, the final query actually finished in only 8 seconds! So the lesson I learn here is for cases that involve big table,...

I think I have the answer here now:

It turns out the [WBS Element] and vendor is NOT a 1:1 relationship, it is 1:n, there are cases one [WBS Element]...

I did some extraction before hand, and the new query is much faster now, however, still the result gets exploded. Here is what I did:

1. I generated two perm tables...

This is expected

2) Can you please capture the actual query plan and post...

Thank you for your reply, I do not have access to the server in weekend but I will post the needed plan here on Monday.

ChrisM@Work (1/5/2017)

---

https://msdn.microsoft.com/en-us/library/ms188774.aspx

and

https://msdn.microsoft.com/en-us/library/ms190356.aspx

It only has to be reset once it's been set....

You got it, but can you elaborate this? wouldn't that mean potentially every query must do the rowcount reset?

Thanks.

I did run them at the same time, it's just the count(1) takes longer and the select * takes less than 1 second.