All,

I am running into a performance problem, which I seem to run into over and over on different queries. I will have a query that joins 10 tables that executes in 2 seconds. I add a join to a table that has 2 rows in it and the query runs forever seemingly.

Here is an example:

This runs in 2 seconds

SELECT COUNT(*)
FROM batch
INNER JOIN dw__vuln_result_head vuln_result ON vuln_result.vuln_result_id = batch.fact_pk_id AND vuln_result.data_source_id = batch.data_source_id
INNER JOIN dw__host host ON host.host_id = vuln_result.host_id AND host.data_source_id = vuln_result.data_source_id
INNER JOIN dw__vuln_rule rule ON rule.vuln_rule_id = vuln_result.rule_id AND rule.data_source_id = vuln_result.data_source_id
INNER JOIN dw__audit scan ON scan.audit_id = vuln_result.audit_id AND scan.data_source_id = vuln_result.data_source_id
INNER JOIN dw__network n ON n.network_id = scan.network_id AND n.data_source_id = scan.data_source_id
INNER JOIN dw__vuln vuln ON vuln.vuln_id = vuln_result.vuln_id AND vuln.data_source_id = vuln_result.data_source_id

Adding this makes it run for an hour:

INNER JOIN dw_vuln_protocol vuln_protocol ON vuln_protocol.protocol_id = vuln_result.protocol_id AND vuln_protocol.data_source_Id = vuln_result.data_source_id

Am I joining these tables together wrong or something?