I have stored procedure like below
if you guys ask for the detailed stored procedure, it is what it is, the procedure is just like the query below, i just simplified the query and mask the table and column name
if you guys ask what inside the table, it contains millions of data of each tables related with @x and each tables is already indexed, i only want to knows why the only "select 'test'" query is running so slow inside this Stored procedure. when i exec sp_a 999, i saw the activity monitor seems like they check every tables inside the "IF" and not going to the @x 999 directly
SP_A with only one parameter @x as int
CREATE PROCEDURE [dbo].[SP_A]
@TYPx INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @x AS INT
SET @x = @TYPX
if @x = 1
begin
select col1,col2 from table_A
end
if @x = 2
begin
select col1,col2 from table_b
end
...
if @x = 70
begin
select col1,col2 from table_x70
end
if @x = 999
begin
select 'test'
end
END
It's continued until i have around 70 types of @x inside this procedure and then I tried to make @x = 999 with only select 'test' and I do exec SP_A 999
Why with this simple query it runs more than 4 minutes and not done yet?