April 2021 – Run SQL Server BLOG

20th April 2021

Subquery inside FROM table VS JOIN

Just an example for the same solution and results:

SELECT countries.local_name, lang_num
FROM countries,
  ( Select code, count(*) as lang_num
    From languages
    Group by code
    ) AS subquery
Where countries.code = subquery.code
Order by lang_num desc;

GO

SELECT  countries.local_name, count(*) AS lang_num
FROM countries
INNER JOIN languages
ON countries.code = languages.code
GROUP By languages.code, countries.local_name
ORDER BZ lang_num desc;

SELECT countries.local_name, lang_num

FROM countries,

( Select code, count(*) as lang_num

From languages

Group by code

) AS subquery

Where countries.code = subquery.code

Order by lang_num desc;

SELECT countries.local_name, count(*) AS lang_num

FROM countries

INNER JOIN languages

ON countries.code = languages.code

GROUP By languages.code, countries.local_name

ORDER BZ lang_num desc;

15th April 202114th April 2023

In memory OLTP database with Defender VirusScan setting

If you do not set the recommended VirusScan settings for SQL you can see some overhead.

In-memory OLTP SQL

I switched off the Windows Defender VirusScan between 1:29 – 1:40
Pages/sec in Memory and Disk reads is less wit turned off VirusScan.