November 13, 2007 at 6:17 am
Peter Larsson (11/13/2007)
Jason, would you mind run the tests again with this index for testdata table?CREATE UNIQUE NONCLUSTERED INDEX [ixu_testdata_ndex_incl_tenpower_twodigit] ON [dbo].[testData]
(
[nDex] ASC
)
INCLUDE ( [tenPower], twodigit )
WITH (INDEX (ixu_testdata_ndex_incl_tenpower_twodigit))
Without the above index AND HAVING A CLUSTERED INDEX on tally:
SET STATISTICS IO ON
SELECT ndex, twodigit, tenPower, 1
FROM dbo.testData as t1
CROSS JOIN tally as t2
WHERE t2.N <= t1.tenPower;
SET STATISTICS IO OFF
(5498798 row(s) affected) 1:30 execution time
Table 'tally'. Scan count 1000000, logical reads 3000000, all others 0
Table 'testData'. Scan count 3, logical reads 9836, all others 0
Table 'Worktable'. Scan count 0, logical reads 0, all others 0
With the index AND HAVING A CLUSTERED INDEX on tally:
SET STATISTICS IO ON
SELECT ndex, twodigit, tenPower, 1
FROM dbo.testData as t1 WITH (INDEX (ixu_testdata_ndex_incl_tenpower_twodigit))
CROSS JOIN tally as t2
WHERE t2.N <= t1.tenPower;
SET STATISTICS IO OFF
(5498798 row(s) affected) 1:28 execution time
Table 'tally'. Scan count 1000000, logical reads 3000000, all others 0
Table 'testData'. Scan count 3, logical reads 2720, *** Read Aheads 9 ***
Table 'Worktable'. Scan count 0, logical reads, all others 0
So as expected, the included columns do reduce the amount of logical reads.
So now the fun should really begin, how can we improve on this even more?
EDIT, I missed the read aheads when I posted this the first time.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 13, 2007 at 6:27 am
You're getting a "scan" on the Tally table? Did you remove the Clustered Index it had?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2007 at 6:29 am
Jason,
Did you test only the Tally table method? Sorry, I can't really tell from your post...
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2007 at 6:37 am
Yes, I used the tally table (or as you named it (myNumbers).
I didn't realize you wanted the Clustered index removed for the test.
So the new results with the PK removed:
SET STATISTICS IO ON
SELECT ndex, twodigit, tenPower, 1
FROM dbo.testData as t1 WITH (INDEX (ixu_testdata_ndex_incl_tenpower_twodigit))
CROSS JOIN tally as t2
WHERE t2.N <= t1.tenPower;
SET STATISTICS IO OFF
(5498798 row(s) affected)
Table 'tally'. Scan count 1, logical reads 1609, all others 0
Table 'testData'. Scan count 3, logical reads 2720, all others 0
Table 'Worktable'. Scan count 10, logical reads 2700451, all others 0
Interesting, it appears that without the clustered index on the tally(myNumber) table most of the work is moved into the "WorkTable"
Now onto the CTE tests ....
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 13, 2007 at 6:54 am
HOLY SON OF A .. %&@$#@ !!!!
I never imagined the recursive CTE would be perform so poorly...
The recursive CTE solution WITHOUT the Included Column index:
SET STATISTICS IO ON;
WITH
CTE_Expand
AS (SELECT nDex, twodigit, 1 AS tenPower
FROM dbo.testData
UNION ALL
SELECT c.nDex, c.twodigit, c.tenPower + 1
FROM
CTE_Expand AS c
INNER JOIN dbo.testData AS t
ON c.nDex = t.nDex AND c.tenPower < t.tenPower)
SELECT nDex, twodigit, 1 AS EngCount
FROM CTE_Expand
ORDER BY nDex, twoDigit
SET STATISTICS IO OFF
(5498798 row(s) affected) 4:55 execution time
Table 'Worktable'. Scan count 5,498,800, logical reads 65,959,902, physical reads 0
Table 'testData'. Scan count 2, logical reads 4,222, physical reads 0
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0
AND WITH the included Column Index and Hint:
SET STATISTICS IO ON;
WITH
CTE_Expand
AS (SELECT nDex, twodigit, 1 AS tenPower
FROM dbo.testData WITH (INDEX (ixu_testdata_ndex_incl_tenpower_twodigit))
UNION ALL
SELECT c.nDex, c.twodigit, c.tenPower + 1
FROM
CTE_Expand AS c
INNER JOIN dbo.testData AS t WITH (INDEX (ixu_testdata_ndex_incl_tenpower_twodigit))
ON c.nDex = t.nDex AND c.tenPower < t.tenPower)
SELECT nDex, twodigit, 1 AS EngCount
FROM CTE_Expand
ORDER BY nDex, twoDigit
SET STATISTICS IO OFF
(5498798 row(s) affected) 4:30 execution time
Table 'Worktable'. Scan count 2, logical reads 46,552,398
Table 'testData'. Scan count 1, logical reads 16,498,877
Table 'Worktable'. Scan count 0, logical reads 0
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 13, 2007 at 7:12 am
Hey all, I just realized how confusing my posts might have been, so I've went back and added the code I used to run my tests into my previous posts.
Hope this helps.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 13, 2007 at 7:26 am
Yes, I used the tally table (or as you named it (myNumbers).
I didn't realize you wanted the Clustered index removed for the test.
So the new results with the PK removed:
Heh... you didn't post the duration and "No"... didn't want the Clustered Index removed... someone else removed it in a test they did and I wanted to make sure you didn't remove it in kind. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2007 at 7:29 am
... and what happened to the Cross-Apply Peter was talking about?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2007 at 7:32 am
HOLY SON OF A .. %&@$#@ !!!!
I never imagined the recursive CTE would be perform so poorly...
Heh... my work is almost done here...
Peter, did you have some Cross-Apply code that you wanted to test? I think Jason is all geared up to do the test...
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2007 at 7:37 am
I found that CROSS APPLY was almost identical to CROSS JOIN.
In my early tests, I had a CLUSTERED INDEX on MyNumbers (tally) table.
SELECTt1.ndex,
t1.twodigit,
t1.tenPower,
1
FROMdbo.testData as t1 WITH (INDEX (ixu_testdata_ndex_incl_tenpower_twodigit))
CROSS APPLY(
SELECTt2.N
FROMMyNumbers AS t2
WHEREt2.N <= t1.tenPower
) AS t2
N 56°04'39.16"
E 12°55'05.25"
November 13, 2007 at 7:45 am
Jeff Moden (11/13/2007)
Yes, I used the tally table (or as you named it (myNumbers).
I didn't realize you wanted the Clustered index removed for the test.
So the new results with the PK removed:
Heh... you didn't post the duration and "No"... didn't want the Clustered Index removed... someone else removed it in a test they did and I wanted to make sure you didn't remove it in kind. 😀
I believe the execution time difference was negligible.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 13, 2007 at 8:00 am
OK, so the final results (at least from my tests...)
SET STATISTICS IO ON;
SELECT
t1.ndex,
t1.twodigit,
t1.tenPower,
1
FROM
dbo.testData as t1 WITH (INDEX (ixu_testdata_ndex_incl_tenpower_twodigit))
CROSS APPLY
(SELECTt2.N
FROMtally AS t2
WHEREt2.N <= t1.tenPower) AS t2
SET STATISTICS IO OFF
(5498798 row(s) affected) 1:30 execution time
Table 'tally'. Scan count 1000000, logical reads 3000000
Table 'testData'. Scan count 3, logical reads 2720
Table 'Worktable'. Scan count 0, logical reads 0
*Like Peter said....turns out to be identical to the CROSS JOIN with PK and Included Column Index:
SET STATISTICS IO ON
SELECT ndex, twodigit, tenPower, 1
FROM dbo.testData as t1 WITH (INDEX (ixu_testdata_ndex_incl_tenpower_twodigit))
CROSS JOIN tally as t2
WHERE t2.N <= t1.tenPower;
SET STATISTICS IO OFF
(5498798 row(s) affected) 1:28 execution time
Table 'tally'. Scan count 1000000, logical reads 3000000, all others 0
Table 'testData'. Scan count 3, logical reads 2720, all others 0
Table 'Worktable'. Scan count 0, logical reads, all others 0
BUT with the PK removed from the tally (myNumbers) table, the total logical reads and scan counts go down even though the execution time increases. *Using Cross Join*
(5498798 row(s) affected) 1:38 execution time
Table 'tally'. Scan count 1, logical reads 1609, all others 0
Table 'testData'. Scan count 3, logical reads 2720, all others 0
Table 'Worktable'. Scan count 10, logical reads 2700451, all others 0
Any thoughts on this last part?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 13, 2007 at 8:23 am
Sorry to jump in AFTER the final post - but unless I'm missing something - the CTE code posted is not even equivalent to either the cross join or the cross apply. It's not returning the same results at all.
I realize this is another "let's toss out the new feature" threads - but I'd at least like to see that they return the SAME thing before going there.
It just seems like an awfully dangerous trend - test one aspect, say "it sucks", and abandon the feature altogether.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 13, 2007 at 8:45 am
Matt, Why do you say it's not getting the same results. I get the same results on my box?
Oh, and a side note. I'm not necessarily jumping on the band wagon and bashing the new features. I've found times where they are beneficial (Not to start another discussion here, just go with me) I'm looking at this as a learning experience and nothing more.
One more note. I just noticed that the CTE solution had an ORDER by in it. I removed it and got slightly better performance (well it executed faster 😀 ):
SET STATISTICS IO ON;
WITH
CTE_Expand
AS (SELECT nDex, twodigit, 1 AS tenPower
FROM dbo.testData WITH (INDEX (ixu_testdata_ndex_incl_tenpower_twodigit))
UNION ALL
SELECT c.nDex, c.twodigit, c.tenPower + 1
FROM
CTE_Expand AS c
INNER JOIN dbo.testData AS t WITH (INDEX (ixu_testdata_ndex_incl_tenpower_twodigit))
ON c.nDex = t.nDex AND c.tenPower < t.tenPower)
SELECT nDex, twodigit, 1 AS EngCount
FROM CTE_Expand
--ORDER BY nDex, twoDigit
SET STATISTICS IO OFF
(5498798 row(s) affected) 3:05 execution time
Table 'Worktable'. Scan count 2, logical reads 46,552,398
Table 'testData'. Scan count 1, logical reads 16,498,877
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 13, 2007 at 8:49 am
It's odd - because the CTE is giving me nothing but 1's in the ENGCount (not incrementing).
And I don't doubt you're not since I've seen you use CTE's a lot, but we have others who are about to (use this to "gut" CTE's).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 31 through 45 (of 60 total)
You must be logged in to reply to this topic. Login to reply