ChrisM@Work (10/9/2015)
SQL_Surfer (10/8/2015)
I have two tables. Table A has 2162 rows and table B has 101 rows. There isn't any join keys that I can join on these two tables. But I need everything from table B and only one column (Col1) from table A. It will result in a cross join and returns 218362 (2162 * 101) rows, which is correct.[/b] But this takes about 30 seconds to complete. Any workaround to accomplish this? Optimizer shows no predicate joins on the Nested Loop and to me it is correct.Here is what I have so far.
SELECT B.*, A.col1
FROM TableB B
LEFT JOIN TABLE A A
ON 1 = 1
Write it as a CROSS JOIN. Examine the execution plan for tuning opportunities, which will be limited if you really do need all columns from TableB. Post the actual execution plan here if you are still unsure.
Other than ensuring the two temp tables contain only the columns you want for output, I don't see any way of optimising this - unless you specify an ORDER BY, which you haven't.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden