EN VI

Sql - Stored procedures common sense pattern for dynamic query situation?

How to Sql - Stored procedures common sense pattern for dynamic query situation

I'm designing a table which could hold hundreds of thousands rows, as an example:

CREATE TABLE [dbo].[Messages](
    [MessageID] [uniqueidentifier] NOT NULL,
    [Number] [int] NOT NULL,
    [CreationDate] [datetime] NOT NULL,
    [Status] [tinyint] NOT NULL,
    [Message] [nvarchar](1022) NOT NULL,
    [Topic] [smallint] NOT NULL,
    [DestinataryID] [int] NULL
)

Almost all of those could be filtered by users at application runtime, destinataryID being the most frequent. I did create indexes, just not everywhere. So, my work now is design a SP to optimize that. I did something like this:

CREATE PROCEDURE GetFilteredData
@Topic INT = NULL,
@DestinataryID INT = NULL,
@CreationDateStart DATETIME = NULL,
@CreationDateEnd DATETIME = NULL,
@Status BIT = NULL
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT ID, Number, CreationDate, Status, Message, Topic, DestinataryID FROM YourTable WHERE 1=1';

IF @DestinataryID IS NOT NULL
    SET @SQL += ' AND DestinataryID = @DestinataryID';

IF @Topic IS NOT NULL
    SET @SQL += ' AND Topic = @Topic';

-- more ifs

EXEC sp_executesql @SQL, N'@Topic INT, @DestinataryID INT', @Topic, @DestinataryID;
END

My main concern is that it could start using bad execution plans, as it could be good to some requests and awful to others, when multiple possible contexts are being filtered.

Is my thing a regular way of doing these dynamic operations? Should I design a separate SP for each situation? Maybe using hints would help? Thanks in advance

Solution:

Is my thing a regular way of doing these dynamic operations?

Yes, this is the recommended way. This type of query is called a Kitchen Sink Query, and your solution is actually a very good one.

The issue with a monolithic query with lots of AND OR logic is that it completely messes up the plan, with the server having no way to serve so many different options at once (it only has a single plan for all possible options).

The dynamic solution as you are doing is actually much better, because it generates a new plan for every combination of filters. (Note "combination of filters" not "combination of values", this is why you need to parameterize it using sp_executesql). The plan is tailored to the exact type of query being done, so the right indexes will be used.

Consider using an ORM in whichever client language in order to build these dynamic filters, as they are much better at this than T-SQL.

It could start using bad execution plans, as it could be good to some requests and awful to others, when multiple possible contexts are being filtered.

Your concern about poor plans is not entirely unfounded, but you'd need to monitor your server to see what happens. Don't jump to anything until you have actual information. If you have decent indexing and mainly equality (not inequality or LIKE) predicates then you shouldn't have many issues.

Should I design a separate SP for each situation?

If you see that a certain combination comes out particularly poorly and you want to tailor it then yes, it could make sense to do so. Otherwise don't bother. There is no need for a separate procedure for every possible combination.

Often filtered indexes or indexed views are a better option than just customizing the query.

Maybe using hints would help?

That would depend on what the issue with the plan is, but generally hints should be avoided in favour of better indexing and general refactoring.

An example of that refactoring: your data types don't match your columns. Correcting that would improve

CREATE PROCEDURE GetFilteredData
  @Topic smallint = NULL,
  @DestinataryID INT = NULL,
  @CreationDateStart DATETIME = NULL,
  @CreationDateEnd DATETIME = NULL,
  @Status tinyint = NULL
AS