Performance issue for a query with order by clause in SQL 2005 SP2

Question

Post reply

Performance issue for a query with order by clause in SQL 2005 SP2

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Bob Fazio SSChampion Points: 10794 More actions · Answer 1

It says you need the trace enabled for hotfix. Not sure about CU4

Using the SQL I posted earlier, I see the "No Join Predicate". I add the option(force order) and it changes the plan...

However in my case

-- No hint or trace.

CPU time = 0 ms, elapsed time = 96 ms.

-- OPTION(FORCE ORDER) but no trace enabled

CPU time = 0 ms, elapsed time = 205 ms.

-- No Hint but both traces enabled

CPU time = 0 ms, elapsed time = 215 ms.

-- OPTION(FORCE ORDER) and both traces enabled.

CPU time = 0 ms, elapsed time = 231 ms.

I am going to guess then that you might not want to enable the traces.

oops! Can't observe something without changing it (Forget who said that).... Forgot I had generate explain plan on (they all came back in 1ms)

So not sure if it matters. I'll try and make it more complicated.

Bob Fazio SSChampion Points: 10794 More actions · Answer 2

using the following;

;WITH table1(col1) AS (

SELECT objects.[object_id] FROM sys.objects

)

SELECT DISTINCT TOP(2000) Table1.col1,Table2.col1,Table3.col1

FROM table1,table1 TABLE2,table1 table3

ORDER BY table3.col1

-- option(force order)

The above comes back in 151ms

after I uncomment the option(force order) trace or no trace. I gave up waiting after 30 seconds.... 🙂

Cliff Jones SSChampion Points: 10547 More actions · Answer 3

I get similar results after applying CU4 and enabling the 2 trace flags. It does not seem to help my problem queries with outer joins. I still have the same 'No Join Predicate' warning and same execution plan after as before. Same cartesian product on one of my tables.

SQL 2005 with CU and no trace flags:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'EpsOrganizations'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'OrganizationLevels'. Scan count 1, logical reads 52821, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Organizations'. Scan count 2, logical reads 1254, physical reads 0, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Residents'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'EntDemographics'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Entities'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 21, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'EntOrganizations'. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Episodes'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'MiscCodes'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 28656 ms, elapsed time = 28834 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

SQL 2005 with CU4 and Trace Flags enabled:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'EpsOrganizations'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'OrganizationLevels'. Scan count 1, logical reads 52821, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Organizations'. Scan count 2, logical reads 1254, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Residents'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'EntDemographics'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Entities'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 19, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'EntOrganizations'. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Episodes'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'MiscCodes'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 29313 ms, elapsed time = 29490 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

SQL 2000:

Table 'MiscCodes'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.

Table 'EntPhones'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.

Table 'ZipCodeInfo'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.

Table 'Entities'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.

Table 'Organizations'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.

Table 'OrganizationLevels'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.

Table 'EntAddresses'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.

Table 'EpsOrganizations'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.

Table 'EntDemographics'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.

Table 'EntOrganizations'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.

Table 'Residents'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.

Table 'Episodes'. Scan count 1, logical reads 3, physical reads 3, read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 30 ms.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Bob Fazio (3/7/2008)
using the following;
;WITH table1(col1) AS (
SELECT objects.[object_id] FROM sys.objects
)
SELECT DISTINCT TOP(2000) Table1.col1,Table2.col1,Table3.col1
FROM table1,table1 TABLE2,table1 table3
ORDER BY table3.col1
-- option(force order)
The above comes back in 151ms
after I uncomment the option(force order) trace or no trace. I gave up waiting after 30 seconds.... 🙂

Not sure what the difference is... 0 ms for me... when I uncomment the option, it's slower but still takes less than 1.5 seconds.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)