What could explain that the SQL Server Express Edition is much faster than the Web / Standard Edition?
These are multiple VMs where hosting is possible. They all have the same problem. Some are the same problem on SQL Server 2012 others on 2017. All machines have the latest updates.
The machine is good enough for the job 8G 4 cores and the DBs are small and have a data volume of less than 1 GB. Same machines. I even tried running Express and Mi-Edition on the same computer with only one instance enabled and the same problem.
It does not matter if the web application runs locally on the same computer or remotely accesses the SQL server over the network.
The DBs have exactly the same data.
The number of requests in forms and their complexity is about half as high as in the web edition. This is a repeatable problem that does not occur only once.
It is not specific to the Web Edition and server type because it was replicated to development computers in Express VS Developer Edition.
The SQL Server settings seem to be identical.
Several people have looked at it and we can not figure out why.
I was told to give an example:
Select Top (500) * by UserLog u Join the staff at s.Username = u.username Join Forms f on f.PageFile = u.PageFile left outer join client c to c.CID = u.CID order by u.id desc
Execution plan are exactly the same.
Over several attempts, Express is 50% faster for this very simple query.
Is there a minimum DB size for the non-Express edition? (that would not make sense …)