1

I need help to optmize the SQL logic in one of my functions. Please, note that I am not able to use store procedure.

Here is my table. It will be initialized using @MainTable that contains a lot of records.

DECLARE TABLE @ResultTable
(
    ResultValue INT
)

These are tables that stores some parameters - they can be emty too.

DECLARE TABLE @ParameterOne (ParameterOne INT)
DECLARE TABLE @ParameterTwo (ParameterOne NVARCHAR(100))
...
DECLARE TABLE @ParameterN(ParameterN TINYINT)

Now, I need to join a lot of tables to my @MainTable in order to select from it only some of its records. The selected records depend on the information stored in the parameters table.

So, my current solution is:

INSERT INTO  ResultTable(ResultValue)
SELECT ResultValue 
FROM MainTable M
INNER JOIN @MainOne MO
    ON M.ID=MO.ID
....
INNER JOIN @MainN MN
    ON M.IDN=MN.ID
WHERE (EXISTS (SELECT 1 FROM @ParameterOne WHERE ParameterOne=MO.ID) OR NOT EXISTS (SELECT 1 FROM @ParameterOne))
       AND 
      ...
       AND
      (EXISTS (SELECT 1 FROM @ParameterN WHERE ParameterN=MN.Name) OR NOT EXISTS (SELECT 1 FROM @ParameterN ))

So, the idea is to add the records only if they match the current criteria from the parameters tables.

Because I am not able to use procedure to build dynamic query I am using the WHERE clause with combinations of EXISTS and NOT EXISTS for each parameter table.

The problem is that it works slower when I am adding more and more parameters table. Is there an other way to do this without using a lot of IF/ELSE statements checking what parameter table has records - it will make the function a lot bigger and difficult for read.

And ideas and advices are welcomed.

1 Answer 1

1

Good question.

Try the following one:

INSERT INTO  ResultTable(ResultValue)
SELECT ResultValue 
FROM MainTable M
INNER JOIN (SELECT * FROM @MainOne WHERE (EXISTS (SELECT 1 FROM @ParameterOne WHERE [email protected]) OR NOT EXISTS (SELECT 1 FROM @ParameterOne))) MO
    ON M.ID=MO.ID
....
INNER JOIN (SELECT * FROM @MainN WHERE (EXISTS (SELECT 1 FROM @ParameterN WHERE [email protected] OR NOT EXISTS (SELECT 1 FROM @ParameterN))) MO
    ON M.IDN=MN.ID

Advantages:

  1. Result of the JOIN is more quickly, because it does not process all data (it is already filtered)
  2. It looks more simple for adjusting
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for the answer, I am going to try this tomorrow and will inform you about the result.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.