SQL Server Performance

Improving SP performance

Discussion in 'T-SQL Performance Tuning for Developers' started by Cesar, Nov 7, 2005.

  1. Cesar New Member


    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: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


    How can I improve the SP so that is more efficient?

    Thank you,
    Cesar
  2. Cesar New Member

    Perhaps in this way is better to read [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />Select Offer_id, User_num, OfferDate, Offer_Title, Company_name, city_name<br />From Offers As o <br /> <br />Where <font color="green">(<b>@Quality_num</b> = 0 Or EXISTS (SELECT * FROM Offers_quality<br />As of_qual WHERE :confused:ffer_id = of_qual.Offer_num And (of_qual.Quality_num =<br />@Quality_num)))<br /></font id="green"><br /><br /><br />And <font color="green">((<b>@Image</b> = 0) Or EXISTS (SELECT * FROM Offers_quality As<br />of_qual WHERE (:confused:ffer_id = of_qual.Offer_num) And (of_qual.Quality_num = @Quality_num Or<br />@Quality_num = 0) And (of_qual.Image_name Is Not Null And @Image = 1)))<br /></font id="green"> <br /><br /><br />And <font color="green">(<b>@Tansport_cost</b> = 0 Or EXISTS (SELECT * FROM Offers_quality<br />As of_qual WHERE (:confused:ffer_id = of_qual.Offer_num) And (of_qual.Quality_num = @Quality_num<br />Or @Quality_num = 0) And ((of_qual.Image_name Is Not Null And @Image = 1) Or @Image = 0)<br />And (of_qual.Tansport_cost = @Tansport_cost)))<br /></font id="green"><br /><br /><br />And <font color="blue">((<b>@Maturation_num</b> = 0) Or EXISTS (SELECT * FROM Offers_quality As<br />of_qual INNER JOIN Offers_Maturation As of_mat on of_mat.Offer_qual_num =<br />of_qual.Offer_qual_id WHERE (:confused:ffer_id = of_qual.Offer_num) And (of_qual.Quality_num =<br />@Quality_num Or @Quality_num = 0) And (of_mat.Maturation_num = @Maturation_num)))<br /></font id="blue"> <br /><br /><br />And <font color="blue">(<b>@Avail_amount_min</b> = 0 Or EXISTS (SELECT * FROM Offers_quality As<br />of_qual INNER JOIN Offers_Maturation As of_mat on of_mat.Offer_qual_num =<br />of_qual.Offer_qual_id WHERE (:confused:ffer_id = of_qual.Offer_num) And(of_qual.Quality_num =<br />@Quality_num Or @Quality_num = 0) And ((of_qual.Image_name Is Not Null And @Image = 1)<br />Or @Image = 0) And (of_qual.Tansport_cost = @Tansport_cost Or @Tansport_cost = 0) And<br />(of_mat.Maturation_num = @Maturation_num Or @Maturation_num = 0) And (((@Amount_measure =<br />1) And (of_mat.Available_amount &gt;= @Avail_amount_min * 1000 And of_mat.Amount_measure =<br />2)) Or ((@Amount_measure = 2) And (of_mat.Available_amount &gt;= @Avail_amount_min / 1000<br />And of_mat.Amount_measure = 1)) Or (of_mat.Available_amount &gt;= @Avail_amount_min And<br />of_mat.Amount_measure = @Amount_measure)))) <br /></font id="blue"> <br /><br /><br />And <font color="blue">(<b>@Price</b> = 0 Or EXISTS (SELECT * FROM Offers_quality As of_qual<br />INNER JOIN Offers_Maturation As of_mat on of_mat.Offer_qual_num = of_qual.Offer_qual_id<br />WHERE (:confused:ffer_id = of_qual.Offer_num) And (of_qual.Quality_num = @Quality_num Or<br />@Quality_num = 0) And ((of_qual.Image_name Is Not Null And @Image = 1) Or @Image = 0)<br />And (of_qual.Tansport_cost = @Tansport_cost Or @Tansport_cost = 0) And<br />(of_mat.Maturation_num = @Maturation_num Or @Maturation_num = 0) And (((@Amount_measure =<br />1) And (of_mat.Available_amount &gt;= @Avail_amount_min * 1000 And of_mat.Amount_measure =<br />2)) Or ((@Amount_measure = 2) And (of_mat.Available_amount &gt;= @Avail_amount_min / 1000<br />And of_mat.Amount_measure = 1)) Or (of_mat.Available_amount &gt;= @Avail_amount_min And<br />of_mat.Amount_measure = @Amount_measure) Or @Avail_amount_min = 0) And (of_mat.Price &lt;=<br />@Price And of_mat.Price_measure = @Price_measure)))<br /></font id="blue"> <br /><br /><br /> Order by OfferDate Desc, Offer_id Desc</font id="code"></pre id="code"><br />
  3. Adriaan New Member

    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 =@Quality_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 Or@Quality_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:ffer_id = of_qual.Offer_num) And (of_qual.Quality_num = @Quality_numOr @Quality_num = 0) And ((of_qual.Image_name Is Not Null And @Image = 1) Or @Image = 0)And (of_qual.Tansport_cost = @Tansport_cost))) AS T2
    ON T1.Offer_Id = T2.Offer_Id
    INNER JOIN

    etc., etc.
  4. Cesar New Member

    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

  5. Madhivanan Moderator

    You may need to use Left Outer Join than Inner Join

    Madhivanan

    Failing to plan is Planning to fail
  6. Cesar New Member

    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?
  7. Adriaan New Member

    Did you try the syntax from my previous post?
  8. Cesar New Member

    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?
  9. Adriaan New Member

    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.
  10. Cesar New Member

    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: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)))

  11. Cesar New Member

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

    Better way would be to build dynamic sql.
  13. Cesar New Member

    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?
  14. mmarovic Active Member

    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.
  15. Adriaan New Member

    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.
  16. druer New Member

    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


Share This Page