You don't mention which provider you are using, but I had a similar problem recently trying to drag data from SQL 2008 into Excel 2010.

Setting up the connection worked fine,...

Performance Stats as promised

First my solution WITH and WITHOUT TABLOCKX on MVTS and #accepted:

And next a comparison with SwePeso's solution which shows an interesting variance between the two solutions for...

Look at us, all doing WHILE loops ! Someone should take a photograph to preserve the moment 😛

WayneS (1/24/2011)

---

I have to admit... the problem initially had me thinking "quirky update" until I realized that it wouldn't handle it. But the QU stuck in my head, and with...

Jeff Moden (1/24/2011)

---

BWAA-HAA!!!... I'm just the humble tester here. The OP posted...

Dave,

I haven't tested yours, but can see that it does not meet with Sir Jeff's strict guidelines....

Seriously, a simple test is this:

INSERT MVTS(IdMvt1,idMvt2,idMvt3)

SELECT 1,2,3 UNION ALL

SELECT 3,4,5 UNION ALL

SELECT 4,5,6

Rows...

TABLOCKX on mvts knock about 0.5 second off the time (14%) for a Pool of 10,000

I will add this tweak to the code when I run the performance tests later.

Good...

WayneS (1/24/2011)

---

mister.magoo (1/24/2011)

---

No, I didn't. Are...

No, I didn't. Are you suggesting...

Heh, I rather hoped you didn't - as I was timing them in profiler, pausing between each one so that I could test a few tweaks along the way and...

They are fine..?? if that's what you mean? The smaller pool sizes were ok anyway - adding the UNIQUEness to the...

New Code :

1. modified the index on the temp table #accepted to be unique

2. added FASTFIRSTROW table hint

3. replaced UNPIVOT with CROSS APPLY/SELECT UNION ALL (might be slightly quicker -...

Ok, had a brief moment...

I guess this is another "depends" moment re: unpivot vs cross apply/select union all.

in my initial tests unpivot was faster. i will re-test.

thanks Jeff

Dave Ballantyne (1/24/2011)

---

Yes , very sneaky 🙂

Ok , so that shouldn't be a problem....

Getting back to the original issue though , although there may be X millions rows , how...