Hello experts,
Is there a free or a paid tool/software that can be used to provide t-sql recommendations or that can provide corrections to the t-sql statement to optimize the code for e.g see below...suggesting some changes to use "in" instead of "exists". I knew of a tool that was used to do this in the past and it was "Lecco SQL Expert". However, this isn't available anymore. Is there an alternative SQL tool that can be a paid tool or free which can do this.
SELECT
some_data
FROM details_table D
WHERE EXISTS (SELECT
1
FROM lookup_table L
WHERE L.lookup_column = D.lookup_column
AND L.matching_column = 'Yes')
can be written as
SELECT /*+ FIRST_ROWS */
some_data
FROM details_table D
WHERE lookup_column IN (SELECT
L.lookup_column
FROM lookup_table L
WHERE L.matching_column = 'Yes')
As a bit of a sidebar, if the lookup column on the lookup table is unique and has an index on it, both of the methods might suck compare to a good ol' fashioned INNER JOIN. If you need to be able to answer "No" when not found, then both will be better than an outer join and, depending on the data and table structure, could be a tie with each other for performance.
I guess my question here is... you seem to have some practical knowledge... why not just do the test yourself instead of using software that you'd have to test the recommendations of anyway?
Unfortunately, my experience on T-SQL side is very minimal and somehow code logic is not something that I am really good at...hence the need. In case if I ask someone in the forum to help me out with a problem that I am struggling with they would come back and say that we aren't paid to help you fix the code which is understandable but what other means do I have if there is no help and I can't do it myself....not every DBA is exceptional in T-SQL.