0

I'm new to SQL stored procedures (and also new to complex SQL queries). I'm creating a stored procedure which uses dynamic SQL to retrieve specific columns matching a specific filter from multiple tables. It looks something like this.

USE db_name
GO

CREATE PROCEDURE dbo.procedure_name 
(@User_Id INT,
@column1 nvarchar(100),
@column2 nvarchar(100),
@customFilter nvarchar(100))
AS
BEGIN
SET NOCOUNT ON

SET @globalFilter = getGlobalFilter() -- This looks something like User_Id = @User_Id

DECLARE @query NVARCHAR(MAX)

SET @query = 
'SELECT' + @column1 +',' +@column2 + 
'FROM tbl_1 JOIN tbl_2' +
--Multiple table joins happening here, including some complex ones with computed columns
+ 'WHERE ' + @globalSystemFilter
+ 'AND ' + @customFilter

EXEC sp_executesql @query, N'@User_Id INT', @User_Id = @User_Id
SET NOCOUNT OFF
END

This takes ~30s to execute, though just copying and pasting the query and executing it without using a stored procedure only takes ~3s. The weirder thing is that I unintentionally created the stored procedure without USE db_name at first, so the procedure was master.dbo.procedure_name. Under this schema, the procedure still worked and was executed as quickly as a regular query (3 seconds). It was only after changing the schema to db_name.dbo.procedure_name that the performance issues kicked in. How do I increase performance under this new schema? Also, any further advice on how to optimize the query in general would be welcome :) I'm using Microsoft SQL Server Management Studio.

EDIT: I've tried other solutions found on StackOverflow but they don't seem to work. I thought my issue may be different since the exact same stored procedure works quickly when using a different schema, but I'm not sure.

I tried adding OPTION (OPTIMIZE FOR (@User_Id UNKNOWN)) at the back of the query to prevent parameter sniffing - this helped shave off a couple of seconds but performance was still too slow.

I also tried SET ANSI_NULLS ON but that had no effect.

Recompiling the procedure every time it is run with CREATE PROCEDURE procedure_name WITH RECOMPILE slowed the query time. Doing this at execution EXEC procedure_name WITH RECOMPILE had no effect.

esja3224
  • 11
  • 3
  • Please, first search and read other question and then write question and say `i tried like blabla question but blabla problem` . try this answer for solve problem: https://stackoverflow.com/questions/440944/sql-server-query-fast-but-slow-from-procedure – Amirhossein Jun 24 '20 at 07:15
  • thanks for the feedback. I tried that post and a few others before creating this question, but didn't mention that - I've edited my post accordingly – esja3224 Jun 24 '20 at 08:20
  • `db_name.dbo.procedure_name` Unless you know what you are doing, you should not use 3 part names. The connection should determine the database that contains your objects (procedures, table, etc.). Next, it IS a best practice to schema qualify your object names. Your code doesn't - and that would be one thing to add. But take a look at this [kitchen sink discussion](https://www.sentryone.com/blog/aaronbertrand/backtobasics-updated-kitchen-sink-example) – SMor Jun 24 '20 at 11:59
  • @SMor thanks a lot! that discussion article was really useful. my issue with using a two-part identifier `dbo.procedure_name` was that the procedure wasn't recognized when I tried to execute it from my external python script – esja3224 Jun 24 '20 at 16:15

0 Answers0