Assuming that there is no Network/Application Server problem going on, could be a problem with the execution plan that is different in the application from the one is being used in the SQL Server Management studio...

Assuming that there is no Network/Application Server problem going on, could be a problem with the execution plan that is different in the application from the one is being used in the SQL Server Management studio...

If the execution plan is exactly the same as it is being running from the platform, the SET options are the same, there is no parameter-sniffing problem and you are executing the query from the same place your application is, I fail to see why the times are different.

Owain, please check make sure it's not database/network latency, or some concurrent database backup/maintenance that is interfering with the performance.

Some query plans get optimized if you inline the parameters. For example WHERE Name LIKE @Search has very poor performance, but if you inline a value like WHERE Name LIKE 'Leonardo%', Sql Server might now use indexes because the first characters of the name are known. If possible, try to rule out this hypothesis.

Also, could you tell us if the table is consumed via integration, or was it created in OutSystems? And are you using aggregates or writing SQL queries?

It is an aggregate on a OutSystems created tables with a "Max Records" setting of 1 so it is not a volume of data issue. The query ultimately is to see if there are any records found meeting the criteria. Using an aggregate it does query for a lot of fields when I don't require the values so is there a way to reduce the number of columns returned? I do not think this is the main cause of the issue, but it could offer a performance improvement.

It is a join of 4 tables where all of the joins are on ID fields

There are 4 filters on ID, Boolean, ID, Datetime

After creating this post I found through experimentation in SQL Managent Studio that one set of test values in the query in particular takes a long time and it is not clear to me why. I am trying to get a session with OutSystems support to review this specific case. I will update this thread with a resolution if we arrive at one.

The Platform optimizer will take care of the number of fields returned. In general, you should not copy the output of the query to a structure of list that has the same structures/entities, since this forces all data to be retrieved. I guess that's not what you do (you probably only check the List.Empty?), so that can't be the case here.

If it's a specific set of values that makes it slow it might be an execution plan gone awry. Especially filters on date/time can cause this we've noticed. In SQL queries we typically add hints for this.

The one query that is slower (and it is much slower) is simply varying one of the IDs in the filter. It is difficult to see why this would be the case, but using my knowledge the application and of the actual data it could be that one of the inner joins would come up with an empty result prior to the filters being applied. I have no idea why this would make it slower. It is difficult to explain exactly in this post, which is why I am hoping a shared session with support will more quickly review this specific scenario and might help uncover the underlying cause.