October 21, 2007 at 1:37 pm
Why would you think that that would remain unquestioned, when it was simply a repeat of the personal attack, that again, has no place in this forum?
Sorry, David, I sure didn't mean for it to be personal... Both statements fit into the idea that those types of tools are provided as a convenience for folks that can't figure out the T-SQL to do it. I just said it the wrong way and once I realized that people took it the wrong way, I took the time to explain it what I really meant... and that I wouldn't do that again...
I'll have a Pivot table example and test data on the 2k side of the house tomorrow...
--Jeff Moden
October 21, 2007 at 1:37 pm
Jeff Moden (10/21/2007)
Lowell, here's the code, one more time... would you and David please do the honors? Thanks, guys...
I simply added the line for the starttime to the original code, as I didn't want to have to reinsert CR/LFs all over again (the code window here doesn't carry them over on a copy on my machine, guessing they are just returning linefeeds, perhaps, but not going to pull out the hex editor now to find out). I ran it a couple of times.
ETA: Tests were run in SSMS 2005 using SQL Server Standard Edition, build 3054.
Creating test table...
(1000000 row(s) affected)
00:00:11:343 Duration (hh:mi:ss:mmm)
==============================================================================
INNER JOIN method...
(1000000 row(s) affected)
00:00:30:000 Duration (hh:mi:ss:mmm)
==============================================================================
OverParition method...
(1000000 row(s) affected)
00:00:30:483 Duration (hh:mi:ss:mmm)
==============================================================================
Creating test table...
(1000000 row(s) affected)
00:00:11:767 Duration (hh:mi:ss:mmm)
==============================================================================
INNER JOIN method...
(1000000 row(s) affected)
00:00:29:293 Duration (hh:mi:ss:mmm)
==============================================================================
OverParition method...
(1000000 row(s) affected)
00:00:29:377 Duration (hh:mi:ss:mmm)
==============================================================================
October 21, 2007 at 1:42 pm
There was a feeling that something is not right.
Now we have 2 point something times difference.
About the value I expected.
But it still should be under 2 times because there is a common part in both queries.
Over 2 times difference shows that even "old good" query engine is implemented in SQL2k5 worse than in SQL2k.
_____________
Code for TallyGenerator
October 21, 2007 at 1:49 pm
Heh... jury is still out on 2 times... it's starting to look a lot like the hardware or the installation may also make a huge difference. Davids times came out almost exactly the same... let's see what Lowell's run comes up with.
Lowell and David, if you get the chance and have the inclination, would you post your hardware configs? Thanks, guys.
David, they are, indeed "line feeds". I get around it by pasting the code into MS Word and replacing ^l (circumflex with lower case "L") with ^p (circumflex with lower case "P"). Still doesn't keep leading spaces but that's no biggee...
--Jeff Moden
October 21, 2007 at 1:56 pm
Jeff Moden (10/21/2007)
Heh... jury is still out on 2 times... it's starting to look a lot like the hardware or the installation may also make a huge difference. Davids times came out almost exactly the same... let's see what Lowell's run comes up with.
I believe Lowell pointed on the reason - different drivers using to return recordsets from different servers.
Pure test should insert data into temp table, not to return to fromt end.
_____________
Code for TallyGenerator
October 21, 2007 at 2:06 pm
Sergiy (10/21/2007)
Jeff Moden (10/21/2007)
Heh... jury is still out on 2 times... it's starting to look a lot like the hardware or the installation may also make a huge difference. Davids times came out almost exactly the same... let's see what Lowell's run comes up with.I believe Lowell pointed on the reason - different drivers using to return recordsets from different servers.
Pure test should insert data into temp table, not to return to fromt end.
That would be the last bit of code I posted... David, did you run the first code (select to screen) or the last code (insert into temp table)? In the tests that Lowell did, that made a huge difference...
--Jeff Moden
October 21, 2007 at 2:10 pm
BTW... Sergiy, do you have convenient access to a 2k5 box that you could run these tests on? Would be real good if we had a 3rd set of numbers...
--Jeff Moden
October 21, 2007 at 5:12 pm
My Dev Server for testing:
Windows 2000 Advanced Server SP4
1 Gig ram
AMD Athlon 64 Processor 3400+
======================================
SQL2000/QA
Creating test table...
00:00:21:467 Duration (hh:mi:ss:mmm)
======================================
INNER JOIN method...
00:00:05:737 Duration (hh:mi:ss:mmm)
======================================
OverParition method...
[Not Applicable]
======================================
SQL2000/QA Second Pass
Creating test table...
00:00:09:403 Duration (hh:mi:ss:mmm)
======================================
INNER JOIN method...
00:00:03:000 Duration (hh:mi:ss:mmm)
======================================
SQL2005/QA
Creating test table...
00:00:09:030 Duration (hh:mi:ss:mmm)
======================================
INNER JOIN method...
00:00:03:533 Duration (hh:mi:ss:mmm)
======================================
OverParition method...
00:00:08:907 Duration (hh:mi:ss:mmm)
======================================
SQL2005/QA Second Pass
Creating test table...
00:00:07:953 Duration (hh:mi:ss:mmm)
======================================
INNER JOIN method...
00:00:02:437 Duration (hh:mi:ss:mmm)
======================================
OverParition method...
00:00:07:670 Duration (hh:mi:ss:mmm)
======================================
SQL2005/SSMS
Creating test table...
00:00:48:500 Duration (hh:mi:ss:mmm)
======================================
INNER JOIN method...
00:00:03:360 Duration (hh:mi:ss:mmm)
======================================
OverParition method...
00:00:08:797 Duration (hh:mi:ss:mmm)
======================================
SQL2005/SSMS repeated, because
the temp table took so long:
Creating Temp Table
00:00:09:017 Duration (hh:mi:ss:mmm)
======================================
INNER JOIN method...
00:00:02:437 Duration (hh:mi:ss:mmm)
======================================
OverParition method...
00:00:07:593 Duration (hh:mi:ss:mmm)
======================================
Lowell
October 21, 2007 at 6:12 pm
Awesome, Lowell... thanks!
Sergiy was right... he expected about 2 times faster... sometimes, almost 3 times faster for the Inner Join.
You guys are the best! I'll start working on a pivot table example tomorrow when I get home.
--Jeff Moden
October 21, 2007 at 8:06 pm
Jeff Moden (10/21/2007)
That would be the last bit of code I posted... David, did you run the first code (select to screen) or the last code (insert into temp table)? In the tests that Lowell did, that made a huge difference...
I ran the first code, and I believe I stated as such. That's exactly how it is used on our end, not sent to temp tables, as the stored procs in which we use the Over() function are the datasources for various reports. In that situation, both methods run in the same timeframe. In our real world uses, the run time is under a second. If my requirements are to write something to temp tables, I'll write what I consider the best ("best" again being subjective and highly dependent on the situation, as I noted earlier) code for that. In the situations where we use Over(), it works like a charm, is easily modifiable, quicker to code, and less prone to typos. My original code that I threw together had zero typos the first time I ran it. Sergiy's example had three separate errors, which is not a slam on Sergiy, as I have typos, miss columns, forget group bys, etc., all of the time. To be fair, two of the three errors would have been just as likely in the Over() query (I just didn't happen to make them, but I certainly have in the past), but not the third, the Group By. It's not necessarily the complexity of code that causes errors to be introduced, sometimes it's just the amount of code. Same results, same timeframe, less code? I'm in.
October 22, 2007 at 12:55 am
Jeff Moden (10/21/2007)
BTW... Sergiy, do you have convenient access to a 2k5 box that you could run these tests on? Would be real good if we had a 3rd set of numbers...
I'm sick in bed with my old laptop.
Don't have access to any server at all.
:blink:
And it was long weekend here!
Bloody hell!!! :crazy:
_____________
Code for TallyGenerator
October 22, 2007 at 1:23 am
David McFarland (10/21/2007)
I ran the first code, and I believe I stated as such. That's exactly how it is used on our end, not sent to temp tables, as the stored procs in which we use the Over() function are the datasources for various reports. In that situation, both methods run in the same timeframe. In our real world uses, the run time is under a second. If my requirements are to write something to temp tables, I'll write what I consider the best ("best" again being subjective and highly dependent on the situation, as I noted earlier) code for that. In the situations where we use Over(), it works like a charm, is easily modifiable, quicker to code, and less prone to typos. My original code that I threw together had zero typos the first time I ran it. Sergiy's example had three separate errors, which is not a slam on Sergiy, as I have typos, miss columns, forget group bys, etc., all of the time. To be fair, two of the three errors would have been just as likely in the Over() query (I just didn't happen to make them, but I certainly have in the past), but not the third, the Group By. It's not necessarily the complexity of code that causes errors to be introduced, sometimes it's just the amount of code. Same results, same timeframe, less code? I'm in.
Ummm... understood... but would you mind running the last rendition just so we're not making the mistake of single machine bias? Thanks, Dave.
--Jeff Moden
October 22, 2007 at 1:28 am
Sergiy (10/22/2007)
I'm sick in bed with my old laptop.Don't have access to any server at all.
:blink:
And it was long weekend here!
Bloody hell!!! :crazy:
Heh... I'm sick too! Sick of working on weekends because of other people's poor planning 😛 Just now finishing a gotta-have-it-now project that's due in 5 1/2 hours. And they didn't even give me the requirements until late on Friday... I don't think I'm going to let them do this to me again...:D
--Jeff Moden
October 22, 2007 at 1:43 am
Jeff Moden (10/22/2007)
Heh... I'm sick too! Sick of working on weekends because of other people's poor planning 😛 Just now finishing a gotta-have-it-now project that's due in 5 1/2 hours. And they didn't even give me the requirements until late on Friday... I don't think I'm going to let them do this to me again...:D
Lucky bastard! 😛
I'm still getting the requirements, and it's 2 weeks after the project is deployed to PRODUCTION!!!
(there was Staging for more than 2 month...)
:w00t:
_____________
Code for TallyGenerator
October 22, 2007 at 7:00 am
HEH!! Now, THAT's entertainment 😀
--Jeff Moden
Viewing 15 posts - 91 through 105 (of 172 total)
You must be logged in to reply to this topic. Login to reply