October 8, 2015 at 10:55 pm
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 retruns 218362 (2162 * 101) rows, which is correct. 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
October 9, 2015 at 2:08 am
Why not do a select a.*,b.col1 from tableA a Cross join tableB b?
How would a group of data in tableA relate to a group of data in table B?
Give a simple example of data in tableA, tableB and show us what you want the results to look like.
October 9, 2015 at 2:17 am
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.
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
October 9, 2015 at 5:41 am
Writing it as a cross join and I've attached a SQL plan.
There is no keys between these two tables that I could join on. But that is the intent. The larger table has CustomerNumber, and Smaller table has Product Information. I want each customer number from larger table to appear in all the result set of table B. Result is correct, just trying to see if there is anyway to speed it up.
October 9, 2015 at 5:44 am
Other than possibly a nonclustered index on B.NANUM, not really much you can do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 9, 2015 at 5:49 am
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
October 9, 2015 at 6:24 am
Is there a table which has both keys between productID and customerID?
Me as an example will not have data against every product in your product table.
I would maybe buy 1 or 2 things out of millions.
If there is no Customer_Product table which links the 2 already then unholster the gun and do cross join.
October 9, 2015 at 10:40 am
Since all of table "a" would seem to fit in a single page, flip the table order in the CROSS JOIN, i.e. FROM b CJ a rather than FROM a CJ b:
SELECT a.*,b.NANUM
FROM #tempSRONAM b
CROSS JOIN #a a
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 9, 2015 at 12:53 pm
Cross join and outer join with no predicate are not the same. If there are no rows in one of the tables, cross join returns no rows.
October 9, 2015 at 12:57 pm
CREATETABLE outertable (a INT);
CREATETABLE innertable (b INT);
INSERToutertable VALUES (1), (2);
INSERTinnertable VALUES (10), (22), (30), (45);
SELECTCOUNT(*) AS outer_join_outer_table_has_rows
FROM (SELECT* FROM innertable LEFT JOIN outertable ON 1 = 1) AS t;
SELECTCOUNT(*) AS cross_join_outer_table_has_rows
FROM (SELECT* FROM innertable CROSS JOIN outertable) AS t;
DELETEoutertable;
SELECTCOUNT(*) AS outer_join_outer_table_no_rows
FROM (SELECT* FROM innertable LEFT JOIN outertable ON 1 = 1) AS t;
SELECTCOUNT(*) AS cross_join_outer_table_no_rows
FROM (SELECT* FROM innertable CROSS JOIN outertable) AS t;
October 9, 2015 at 1:00 pm
Just to explain that horrible SQL script in m previous, something on the network is sniffing SQL and won't let me send it out (HTTP reset). That one finally made it through.
Anyway, add the obvious spaces back in and it's a script that demonstrates the difference between an outer join without a predicate and a cross join.
October 9, 2015 at 1:20 pm
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 retruns 218362 (2162 * 101) rows, which is correct. 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
30 seconds to complete... to complete what? A return to the screen? Are you actually going to use the query to return to the screen??? If so, who's going to read all 218,362 rows? That's the same as more than 2,700 typewritten pages using a 10 pt font.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 9, 2015 at 1:31 pm
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 retruns 218362 (2162 * 101) rows, which is correct. 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
Try adding an index on TABLEA.col1, just to see if the cross join operation will do an index scan instead of a full table scan.
Also try changing the syntax to use an actual "CROSS JOIN" clause instead of "LEFT JOIN". It helps SQL Server construct a better execution plan if explicitly state what you're trying to do rather than tease it with something like "LEFT JOIN ... ON 1 = 1".
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply