SQL Server Performance Forum – Threads Archive

Improving SP performance

I have a query in which the user can specify from the app several fields/variables (@Quality_num, @Caliber_min, @Caliber_max, @Image, @Tansport_cost, @Maturation_num, @Avail_amount_min and @Price). If user specify @Quality_num (for example @Quality_num = 3) and nothing else then the SP performance seems fast, but if user specify for example @Price (for example @Price = 2,59) then the performance is incredibly slow. The code in green is â€˜retrieving#%92 data from Offers_quality table, and the code in blue is â€˜retrieving#%92 data from Offers_Maturation table. Select Offer_id, User_num, OfferDate, Offer_Title, Company_name, city_name
From Offers As o Where (@Quality_num = 0 Or EXISTS (SELECT * FROM Offers_quality As of_qual WHERE :confused:

ffer_id = of_qual.Offer_num And (of_qual.Quality_num = @Quality_num))) And ((@Image = 0) Or EXISTS (SELECT * FROM Offers_quality As of_qual WHERE ( :confused:

ffer_id = of_qual.Offer_num) And (of_qual.Quality_num = @Quality_num Or @Quality_num = 0) And (of_qual.Image_name Is Not Null And @Image = 1)))
And (@Tansport_cost = 0 Or EXISTS (SELECT * FROM Offers_quality As of_qual WHERE ( :confused:

ffer_id = of_qual.Offer_num) And (of_qual.Quality_num = @Quality_num Or @Quality_num = 0) And ((of_qual.Image_name Is Not Null And @Image = 1) Or @Image = 0) And (of_qual.Tansport_cost = @Tansport_cost)))
And ((@Maturation_num = 0) Or EXISTS (SELECT * FROM Offers_quality As of_qual INNER JOIN Offers_Maturation As of_mat on of_mat.Offer_qual_num = of_qual.Offer_qual_id WHERE ( :confused:

ffer_id = of_qual.Offer_num) And (of_qual.Quality_num = @Quality_num Or @Quality_num = 0) And (of_mat.Maturation_num = @Maturation_num)))
And (@Avail_amount_min = 0 Or EXISTS (SELECT * FROM Offers_quality As of_qual INNER JOIN Offers_Maturation As of_mat on of_mat.Offer_qual_num = of_qual.Offer_qual_id WHERE ( :confused:

Perhaps in this way is better to read [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] <pre id="code"> Select Offer_id, User_num, OfferDate, Offer_Title, Company_name, city_name From Offers As o Where (@Quality_num = 0 Or EXISTS (SELECT * FROM Offers_quality As of_qual WHERE :confused:

ffer_id = of_qual.Offer_num And (of_qual.Quality_num = @Quality_num))) And ((@Image = 0) Or EXISTS (SELECT * FROM Offers_quality As of_qual WHERE ( :confused:

ffer_id = of_qual.Offer_num) And (of_qual.Quality_num = @Quality_num Or @Quality_num = 0) And (of_qual.Image_name Is Not Null And @Image = 1))) And (@Tansport_cost = 0 Or EXISTS (SELECT * FROM Offers_quality As of_qual WHERE ( :confused:

ffer_id = of_qual.Offer_num) And (of_qual.Quality_num = @Quality_num Or @Quality_num = 0) And ((of_qual.Image_name Is Not Null And @Image = 1) Or @Image = 0) And (of_qual.Tansport_cost = @Tansport_cost))) And ((@Maturation_num = 0) Or EXISTS (SELECT * FROM Offers_quality As of_qual INNER JOIN Offers_Maturation As of_mat on of_mat.Offer_qual_num = of_qual.Offer_qual_id WHERE ( :confused:

ffer_id = of_qual.Offer_num) And (of_qual.Quality_num = @Quality_num Or @Quality_num = 0) And (of_mat.Maturation_num = @Maturation_num))) And (@Avail_amount_min = 0 Or EXISTS (SELECT * FROM Offers_quality As of_qual INNER JOIN Offers_Maturation As of_mat on of_mat.Offer_qual_num = of_qual.Offer_qual_id WHERE ( :confused:

ffer_id = of_qual.Offer_num) And(of_qual.Quality_num = @Quality_num Or @Quality_num = 0) And ((of_qual.Image_name Is Not Null And @Image = 1) Or @Image = 0) And (of_qual.Tansport_cost = @Tansport_cost Or @Tansport_cost = 0) And (of_mat.Maturation_num = @Maturation_num Or @Maturation_num = 0) And (((@Amount_measure = 1) And (of_mat.Available_amount >= @Avail_amount_min * 1000 And of_mat.Amount_measure = 2)) Or ((@Amount_measure = 2) And (of_mat.Available_amount >= @Avail_amount_min / 1000 And of_mat.Amount_measure = 1)) Or (of_mat.Available_amount >= @Avail_amount_min And of_mat.Amount_measure = @Amount_measure)))) And (@Price = 0 Or EXISTS (SELECT * FROM Offers_quality As of_qual INNER JOIN Offers_Maturation As of_mat on of_mat.Offer_qual_num = of_qual.Offer_qual_id WHERE ( :confused:

ffer_id = of_qual.Offer_num) And (of_qual.Quality_num = @Quality_num Or @Quality_num = 0) And ((of_qual.Image_name Is Not Null And @Image = 1) Or @Image = 0) And (of_qual.Tansport_cost = @Tansport_cost Or @Tansport_cost = 0) And (of_mat.Maturation_num = @Maturation_num Or @Maturation_num = 0) And (((@Amount_measure = 1) And (of_mat.Available_amount >= @Avail_amount_min * 1000 And of_mat.Amount_measure = 2)) Or ((@Amount_measure = 2) And (of_mat.Available_amount >= @Avail_amount_min / 1000 And of_mat.Amount_measure = 1)) Or (of_mat.Available_amount >= @Avail_amount_min And of_mat.Amount_measure = @Amount_measure) Or @Avail_amount_min = 0) And (of_mat.Price <= @Price And of_mat.Price_measure = @Price_measure))) Order by OfferDate Desc, Offer_id Desc</pre id="code">

SELECT T1.* FROM
(Select Offer_id, User_num, OfferDate, Offer_Title, Company_name, city_nameFrom Offers As o
Where (@Quality_num = 0 Or EXISTS (SELECT * FROM Offers_qualityAs of_qual WHERE :confused:

ffer_id = of_qual.Offer_num And (of_qual.Quality_num [email protected]_num)))And ((@Image = 0) Or EXISTS (SELECT * FROM Offers_quality Asof_qual WHERE ( :confused:

ffer_id = of_qual.Offer_num) And (of_qual.Quality_num = @Quality_num [email protected]_num = 0) And (of_qual.Image_name Is Not Null And @Image = 1)))
) AS T1
INNER JOIN
(Select Offer_id, User_num, OfferDate, Offer_Title, Company_name, city_nameFrom Offers As o
WHERE (@Tansport_cost = 0 Or EXISTS (SELECT * FROM Offers_qualityAs of_qual WHERE ( :confused:

I think the SP I posted works much faster written in this way: (This is the whole SP) Select Offer_id, User_num, OfferDate, Offer_Title, Company_name, city_name
From Offers As o Where EXISTS (SELECT * FROM Offers_quality As of_qual
INNER JOIN Offers_Maturation As of_mat on of_mat.Offer_qual_num = of_qual.Offer_qual_id
WHERE ( :confused:

ffer_id = of_qual.Offer_num) And (of_qual.Quality_num = @Quality_num Or
@Quality_num = 0) And ((of_qual.Image_name Is Not Null And @Image = 1) Or @Image = 0)
And (of_qual.Tansport_cost = @Tansport_cost Or @Tansport_cost = 0) And
(of_mat.Maturation_num = @Maturation_num Or @Maturation_num = 0) And (((@Amount_measure =
1) And (of_mat.Available_amount >= @Avail_amount_min * 1000 And of_mat.Amount_measure =
2)) Or ((@Amount_measure = 2) And (of_mat.Available_amount >= @Avail_amount_min / 1000
And of_mat.Amount_measure = 1)) Or (of_mat.Available_amount >= @Avail_amount_min And
of_mat.Amount_measure = @Amount_measure) Or @Avail_amount_min = 0) And ((of_mat.Price <=
@Price And of_mat.Price_measure = @Price_measure) Or @Price = 0)) What do you think? The only problem now is the Offers table records aren#%92 t returned if there are no records in Offers_quality table with its foreign key (Offer_num). I mean, if none parameter is defined I want all the records form Offers table are returned, and not only the ones that are linked to Offers_quality through â€˜Offer_num#%92 column foreign key in Offers_quality table.
How can I avoid this? With first code posted this did not happen.. Thanks

You may need to use Left Outer Join than Inner Join Madhivanan Failing to plan is Planning to fail

Hi, I think the problem is here: EXISTS (SELECT * FROM Offers_quality As of_qual In this way only will be returned records form Offers table when EXISTS is true, therefore only for records found in Offers_quality table ‘EXISTS (SELECT * FROM Offers_quality As of_qual’. How can I also return all records form Offers table indifferently the records in Offers_quality table?

Did you try the syntax from my previous post?

Hi, About your code example, indeed I don#%92 t understand the idea of complete code I have to
implement. My first post is only an example of my complete SP. The real SP consists in
three â€˜Selects#%92 like my sample (but with ten more filters/parameters and with seven table
joins after every â€˜Select#%92). So, my SP consists in three selects like this: (more or less)
Select Offer_id, User_num, OfferDate, Offer_Title, Company_name, city_name…
From Offers As o JOIN Users As u
On …
LEFT JOIN Products As prod
On …
LEFT JOIN Families As fam
On …
JOIN Cities As c
On …
JOIN States As s
On …
LEFT JOIN DOP_IGP_product As DI_prod
On …
LEFT JOIN DOP_IGP As DI
On … Where … More filters here …
And (@Quality_num = 0 Or EXISTS (SELECT * FROM Offers_quality
As of_qual WHERE :confused:

ffer_id = of_qual.Offer_num And (of_qual.Quality_num =
@Quality_num))) And ((@Image = 0) Or EXISTS (SELECT * FROM Offers_quality As
of_qual WHERE ( :confused:

ffer_id = of_qual.Offer_num) And (of_qual.Quality_num = @Quality_num Or
@Quality_num = 0) And (of_qual.Image_name Is Not Null And @Image = 1)))
… And the same as my first post until de end.
Perhaps apply your system to my SP will be very long to write and later to understand and to maintain. I don#%92 t know.. Do you really think my solution will be worst than yours? If so, why?

If you translate each of the criteria clauses into a derived table, SQL Server doesn’t have to build the complete recordset for the JOINs and then apply the criteria – it applies the criteria before building the JOINs. Or at least you have a better chance of SQL Server doing it in that order.

About my solution, do you know which is the best way to bypass my EXISTS clause in case the parameters used in that clause are equal to 0? (@Quality_num, @Image, @Tansport_cost, @Maturation_num, @Avail_amount_min, @Price, …) In this way is very slow: (In bold) Select Offer_id, User_num, OfferDate, Offer_Title, Company_name, city_name
From Offers As o Where ((@Quality_num = 0 and @Image = 0 and @Tansport_cost = 0 and @Maturation_num and
@Avail_amount_min = 0 and @Price = 0…) Or EXISTS (SELECT * FROM Offers_quality As of_qual
INNER JOIN Offers_Maturation As of_mat on of_mat.Offer_qual_num = of_qual.Offer_qual_id
WHERE ( :confused:

..It is very slow when there are parameters different to 0, for example @Quality_num = 3

Better way would be to build dynamic sql.

If you think Dynamic SQL is the best option, tell me how the string would be (more or less) with my simple code sample:
Declare @sql1 nvarchar(4000) Select @sql1 = ‘…..
And which are the advantages?

I don’t really have time right now to write code, but code would be more efficient if you remove all exists(…) for parameters with zero value. Off course that code could be further optimized, but I don’t have time to investigate it right now.

Then look at your actual data, and consider putting an index on each field for which you supply the parameters. There is a trade-off, as adding indexes usually slows down the response time after an insert, update or delete.

Have you considered using dynamic sql where you do ‘if’ checks on your input values, and then assign values to strings such as:
if @QualityNum > 0 then
set @QualityClause = ‘ And(of_qual.Quality_num = @Quality_num)’
else
set @QualityClause = ” Then you begin constructing the select clause
set @query = Select Offer_id, User_num, OfferDate, Offer_Title, Company_name, city_name From Offers As o And if you have any actual numbers you add a where clause and append the clauses ie ‘if @QualityNum > 0 or @ImageNum > 0 or ….. then’ Then when you are constructing the larger and larger queries at the end you can use something like:
set @query = @query + ‘where exists(SELECT * FROM Offers_quality As of_qualINNER JOIN Offers_Maturation As of_mat on of_mat.Offer_qual_num = of_qual.Offer_qual_id WHERE ( :confused:

ffer_id = of_qual.Offer_num) ‘ + @QualityClause + @ImageClause etc. + ‘)’ The idea being that you only have to build and execut one exists statement and it will be based on only the fields that you actually cared about. As it appears now if the user inputs multiple values you will have to execute virtually the same statement over and over. Whereas, by building the clause you only implement 1 single exists clause that is constructed for exactly what you are looking for. Just a suggestion for an alternative method,
Dalton

]]>