SQL Server Performance

Cross JOIN alone Vs Cross Join in UNION operation

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by atulgoswami, Mar 17, 2010.

  1. atulgoswami New Member

    When i run the cross join on huge tables, then obviously it takes time,
    But when i put this into UNION operation with one more query, which is returning less data, then this entire query runs too fast.
    How it is happening internally? What i know about UNION is, it searches for distinct data and in my case it returns the less data.
    For example, I ran one test cross join query and returned 50,00 rows and took 4 seconds, but when i put this query in UNION with one query (which returns 150 rows), then it takes less than a second and just 435 rows.
    I am just curious to know what happened internally.
    Thanks in advance.
  2. Adriaan New Member

    What is the point of the cross join query?
    Query returns less than 1% of the cartesian product when the distinct operator (UNION) is added.
  3. atulgoswami New Member

    [quote user="Adriaan"]
    What is the point of the cross join query?
    Query returns less than 1% of the cartesian product when the distinct operator (UNION) is added.
    [/quote]
    I agree there is no point in putting cross join query. I was just wondering, if run the cross join query alone it takes 5 min but when i add this query in UNION with one other non cross join query then it takes less than a second.

    UNION operator searches for DISTINCT values and returns few rows while putting with less data query.

    Now what i am assuming is when you UNION a cross join query and non cross join query then sql server does not perform cross join operation.
    I may be wrong here and experts, please correct me.

Share This Page