locked
CROSS Join Function not moving RRS feed

  • Question

  • I have a query where 14 mil data query is having a CROSS JOIN function. The query query get stuck here for hours and I don't see any change in data Logical read of Physical read change. 

    Is it good idea to use CROSS JOIN for such a large data set ? What could be the best approach to fix this ?

    Thanks for your time.

    Friday, July 3, 2020 1:48 PM

All replies

  • Hi Atul,

    Assuming 14 mil records is the output of the query that you are trying to execute, following are top 2 recos from my experience:

    1. If possible avoid cross join as it generates more outputs than inputs. If this situation is something where you cannot avoid, recommendation is to pre-aggregate (use GROUP BY clause to aggregate) the data before using Cross-Join. This will save resource cost and in turn will improve performance.
    2. As cross join with huge data needs more resources to run, make sure you have enough resources in the machine you are running the query.

    Hope this helps. 


    SP


    • Edited by spolna Monday, July 6, 2020 11:33 AM
    Monday, July 6, 2020 11:28 AM