I have a query that executes against a table that can grow to millions of rows. The query comes out of a QA tool that we use that is outside the standard functionality of the DB (as far as what is indexed and how and why). The query is:
SELECT id FROM thisTable t WHERE col = 'val' AND ((not exists (SELECT 1 FROM thisTable WHERE refid = t.id) and refbool = 0) or refbool = 1) ORDER BY newid()
Basically, let's say the table has
col columns. So you could have data as follows:
id | refid | refbool | col ------------------------------------ 1 | NULL | 0 | val 2 | NULL | 0 | val 3 | NULL | 0 | val 4 | 2 | 1 | val 5 | NULL | 0 | val 6 | 1 | 1 | val
The query should never pick the rows for id in (1, 2) because they are referred to by other rows. It should only grab rows where
refbool = 1, OR
refbool = 0 AND that row's id is not any other row's
refid. This statement is horribly non-performant, but I'm not sure what a better query would look like for this. Assume that no indexes, views, stored procedures, or other underlying machinations can be added - it must be a query.
The overall query is significantly larger,
JOINS to two additional tables and gathers quite a bit of data. However, I've narrowed it does to this particular bit as commenting out this line takes the query execution time from 16s to <1s.
I'm also reordering the rows by
newid() as I need to randomly select a sample item. Removing the
ORDER BY also make the query significantly faster even leaving the third row in. It appears that the two operations combined causes the slowness. I've tried designing a CTE, but have failed to increase performance doing so.
I've looked at the execution plan. There are indices that would be added that would improve this query. However, performance of internal QA tools do not take precedence over performance in client production environments, and making changes to the structure in a QA environment for the utility in relation to indices, etc. invalidates it's usefulness as a QA environment as it will likely perform differently than a production environment.
I could certainly write a query that would perform worse than my current query by changing the logic of the query itself. I'm sure we all could. I'm asking to apply that sort of reasoning to improve the performance of the query instead.