Hi,

I am getting performance issue when using cross joins. Table A having 5K records and Table B having 5K records. when i use cross joins it will take more time to retrieve the results. Please provide any suggestion to implement this task quickly and more efficiently.

• Edited by Guru SQL 22 hours 8 minutes ago
• Moved by ArthurZMVP 17 hours 51 minutes ago Does not appear SSIS related

If you can explain your problem in more details including your tables, input and desired output, you may get more help. Cross JOIN is a slow operation, but getting back 25K rows should not be too slow. 

A cross join of two tables with 5,000 rows each will generate 5000 * 5000 rows or 25 million rows, not 25 thousand.

Guru SQL:  We do need more information from you to be of much help.  Probably you don't want a cross join.  In most cases where a cross join is appropriate, at least one of the tables has a very small number (typically 10 or fewer) of rows.

Tom

Well, I was not 100% sure that 5K*5K = 25K :) But was too lazy to re-check. I guess it shows that my math is definitely falling :(

Are you talking about the time it takes to display in SSMS or actual performance in an ETL (or similar) process? If in SSMS, how wide is your data? SSMS can be comparatively slow so assuming a cross join is what you really need, have you reduced the number of columns you're returning?

Like Tom said, you've turned a total of 10k rows into 25M rows. There's not going to be anything fast about that.

If you goal is to compare each row in table A to every row in table B, you may be able to do a "triangular join" instead of the cross join. Triangular joins are still expensive but far less expensive than a full cross join.

The syntax looks like this...

SELECT 
	*
FROM 
	TableA a
	JOIN TableB b
		ON a.ID < b.ID

That'll take you from 25M rows down to 12.49M rows...