Jeff Moden perhaps that is the reason why Option A is sometimes faster and why Option B is sometimes faster my knowledge of DBA aspects is not as deep...

Ok... I just did some extensive testing using Brent's code both as is and with some mods to give the test table a bit more bulk.

Using a MAXDOP of 1,...

It's been my expectation that SQL Server will run slower because of all the stuff they're doing to figure out if a plan should be reverted and a bunch of...

My motto is...

I know you get some reduced performance from running on a virtual machine by I thought this would be maybe 20% slower, the creation of the clustered index...

Okay to comment on the (A) CREATE TABLE with INDEX then INSERT INTO versus the (B) SELECT INTO then create INDEX there is no set solution for which is...

You don't really need to partition the table if you (1) cluster the table by date first and (2) the lookup queries specify a WHERE clause on a date...

@bob-2... you made the comment in your first posts of...

Did you check for blocking?  If so, is there blocking associated with this issue?

Also another means of getting around all those WITH (NOLOCKS) is to set at the top of the stored procedure either:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

-- OR --

SET TRANSACTION ISOLATION...

Note I teach/tutor for free and have an online Discord server with 80 plus students at verying levels of expertise. My first motto is (and all my students know...

You and I have know each other for a...

Perhaps a better thing to do is to partition the table?

• This reply was modified 2 years, 10 months ago by Jeff Moden.

Post deleted... I misread what was being done.

• This reply was modified 2 years, 10 months ago by Jeff Moden. Reason: Post deleted... I misread what was being done

Strong second here on Scott's synonym suggestion.

Thanks, Steve.