How Cartesian products affects Sybase IQ performance?

[former_member213461]

In my IQ message log, I have the following message:

I. 04/21 08:28:22. 0001847919 Exception Thrown from dfo_Root.cxx:822, Err# 0, tid 8 origtid 8
I. 04/21 08:28:22. 0001847919 O/S Err#: 0, ErrID: 9216 (df_Exception); SQLCode: -1005015, SQLState: 'QTA15', Severity: 14
I. 04/21 08:28:22. 0001847919 [20169]: The optimizer was unable to find a query plan that avoided cartesian product joins larger than the Max_Cartesian_Result setting
-- (dfo_Root.cxx 822) 

I don´t know if

this situation may affect the overall performance

Thank you

[tayeb_hadjou · Accepted solution]

Hi Jairo,

In this specific case, the query failed because the cartesian product exceeded the Max_Cartesian_Result value. Join conditions are a key in performance. They save space, time and memory. Thus recommendations would be :

- Review and rewrite the query accordingly. Add  suitable join conditions.

- Review the tables scheme. Add Primary Key, Foreing keys, suitable indexes. They help to reduce Many-To-Many joins and use effeciently IQ Optmizer. You can enable query Index_Advisor option to get suggestions printed with the query plan.

If you need, for testing and Dev, allow the query finishes normally and see its real resources usage, you can disable temporary the Cartesian_Result limitaion or increase its value :

set temporary option Max_Cartesian_Result=<new_values> /* ( 0 : unlimited) */;

-- then execute the query

select ....

;

[former_member213461 · Accepted solution]

Thanks Tayeb,

Do you know, what kind of resources consume this cartesian product for example: main memory, temporary memory, temporary dbspace?

What resource area is the most affected with this operation?

Best regards

[tayeb_hadjou · Accepted solution]

Hi Jairo,

As Cartesian product (no join conditions) selects large number of rows, it requires more exec time, temp space (if order by, group by, etc,..) , temp cache, main cache, threads.

To get close estimations about query resources consumtion, you can generate plans without execution using option NoExec.

Query plan option recommended are : quary_name, query_plan, query_detail, query_plan_as_html, query_plan_as_html_directory, dml_options10.

See Generating Query Plans

Note that other resource monitoring options can be enabled/used , but they require effectivel query exection. The info are collected once execution is finished with success. Eg. query_plan_after_run and query_timing.

Regards,

Tayeb.

[former_member185199 · Accepted solution]

You should read some basics of relation algebra:

cartesian product means that every record of Table A is joined with every other record of Table B so you get expotentially resultsets:

Table A | Table B | Reultset

100       | 100       | 10000

1000     | 1000     | 1000000

and so on and so on...

you can figure out that this gets big really fast, that why there is something like Max_Cartesian_Result.

regards

dj

[former_member213461 · Accepted solution]

Hi Dirk

Thanks for your response

I know the concept of cartesian product but my question was oriented to identify  the IQ area most affected with this operation

regards

[Former Member]

Cartesian product joins are horribly bad for performance regardless of DBMS or data warehouse.  You may want to hunt down the developer and ever so politely inform the individual that join clauses are required.