Is there some problem with parametrs in sql select query on Azure SQL?

Question

I'm trying to find where is the problem with this select query on Azure SQL. It runs for about 700ms.

DECLARE @LanguageIds1 INT = 11;
DECLARE @LanguageIds2 INT = 14;

SELECT
[G].[Id],
[G].[Name],
COUNT(*) AS [AudiobookCount]
FROM [Genre] AS [G]
INNER JOIN [Audiobook2Genre] AS [A2G] ON [A2G].[GenreId] = [G].[Id]
INNER JOIN [Audiobook] AS [A] ON [A].[Id] = [A2G].[AudiobookId]
WHERE [A].[LanguageId] IN (@LanguageIds1,@LanguageIds2)
GROUP BY [G].[Id], [G].[Name]
ORDER BY [G].[Name]

When I changed it to this (remove parameters and directly put values).

SELECT
[G].[Id],
[G].[Name],
COUNT(*) AS [AudiobookCount]
FROM [Genre] AS [G]
INNER JOIN [Audiobook2Genre] AS [A2G] ON [A2G].[GenreId] = [G].[Id]
INNER JOIN [Audiobook] AS [A] ON [A].[Id] = [A2G].[AudiobookId]
WHERE [A].[LanguageId] IN (11,14)
GROUP BY [G].[Id], [G].[Name]
ORDER BY [G].[Name]

It runs only for about 80ms. So difference in running time is about 620ms and I don't understand why.

I tried copy database from Azure to local MSSQL 2017 Developer Edition via bacpac and run same queries. Running times was approximately the same.

Looks to be classic [parameter sniffing](https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/). Add the `OPTION(RECOMPILE)` hint to the end of the query and I would expect the same performance. — Dan Guzman, Mar 27 '19 at 11:59

score 1 · Accepted Answer · answered Mar 28 '19 at 06:25

The reason is SQL Server doesn't always select the best execution plan for your queries and thankfully there are several different hints that can be used to force SQL Server into using one execution plan over another. One issue that you may be faced with is when using parameters in your WHERE clause, sometimes the query runs great and other times it runs really slow.

As mentioned above, SQL Server offers many hints that can be used to force how an execution plan is put together. The option that we are interested in is the OPTIMIZE FOR option. This will allow us to specify what parameter value we want SQL Server to use when creating the execution plan.

Please reference this tutorial: Optimize Parameter Driven Queries with SQL Server OPTIMIZE FOR Hint.

Maybe you can modify the code like this and test again.

DECLARE @LanguageIds1 INT = 11;
DECLARE @LanguageIds2 INT = 14;

SELECT
[G].[Id],
[G].[Name],
COUNT(*) AS [AudiobookCount]
FROM [Genre] AS [G]
INNER JOIN [Audiobook2Genre] AS [A2G] ON [A2G].[GenreId] = [G].[Id]
INNER JOIN [Audiobook] AS [A] ON [A].[Id] = [A2G].[AudiobookId]
WHERE [A].[LanguageId] IN (@LanguageIds1,@LanguageIds2)
GROUP BY [G].[Id], [G].[Name]
ORDER BY [G].[Name]
OPTION (OPTIMIZE FOR (@LanguageIds1= 11,@LanguageIds2= 14))

For more details: you can see Hints (Transact-SQL) - Query.

You also can reference this case: Why does a parameterized query produces vastly slower query plan vs non-parameterized query.

Hope this helps.

Is there some problem with parametrs in sql select query on Azure SQL?

1 Answers1