Hello,
I was wondering if there was a better way to write a SQL query for JOIN dim_vulnerability and dim_asset. My SQL query just takes too long and I was wondering if there was something more efficient.
SELECT asset_id, ip_address, host_name, dv.title FROM dim_vulnerability dv JOIN fact_asset_vulnerability_instance USING (vulnerability_id) JOIN dim_asset USING (asset_id)
Thanks in advance
Hi @jacob_horning,
You could try switching your join order. Something like this might be more performant since you’ll limit the scope of your join on fact_asset_vulnerability_instance to just the assets that you need.
SELECT asset_id, ip_address, host_name, dv.title
FROM dim_asset
JOIN fact_asset_vulnerability_instance USING (asset_id)
JOIN dim_vulnerability USING (vulnerability_id)
Alternatively, if you do not need details like the port/service for a particular vulnerability instance, you could use the fact_asset_vulnerability_finding table instead of fact_asset_vulnerability_instance since it will have less rows and thus, be more performant.
Hope that helps!
Thank you so much it works so much faster now