SQL Server Performance

Modify SP to perform search with paged results

Discussion in 'T-SQL Performance Tuning for Developers' started by ClassyThemes, Jun 23, 2003.

  1. ClassyThemes New Member

    Hi all!

    I got some wonderful help in here the other day to get some paged query results out of my sp. Now I'd like to use the same idea and modify the sp to use on my search page to return paged results from whatever criteria the uses enters into a search box.

    However, I'm getting an error around the search phrase everytime. I would appreciate you taking a moment and letting me know what I'm doing wrong.

    Thanks!

    ********************************************************************
    (
    @Page int,
    @RecsPerPage int,
    @Search nvarchar(75)
    )
    AS

    SET NOCOUNT ON

    DECLARE
    @FirstRec int,
    @LastRec int,
    @row_count int,
    @error int

    -- calculating start and end row
    SELECT @FirstRec = (((@Page - 1) * @RecsPerPage) + 1)
    SELECT @LastRec = @FirstRec + @RecsPerPage - 1

    --Create a temporary table
    CREATE TABLE #TempItems
    (
    seq_id int IDENTITY primary key,
    ProductID int -- I assume this is the PK of the table.
    )

    -- Insert the rows from tblItems into the temp.
    INSERT INTO #TempItems (ProductID)
    EXEC('SELECT ProductID FROM Products WHERE
    ModelNumber LIKE %' + @Search + '% AND Disable IS NULL
    OR
    ModelName LIKE %' + @Search + '% AND Disable IS NULL
    OR
    Description LIKE %' + @Search + '% AND Disable IS NULL
    OR
    ThemeDesigner Like %' + @Search + '% AND Disable IS NULL
    OR
    ProductID LIKE %' + @Search + '% AND Disable IS NULL
    OR
    Keywords LIKE %' + @Search + '% AND Disable IS NULL
    ORDER BY ModelName ' )

    SELECT @row_count = @@ROWCOUNT, @error = @@ERROR
    -- @row_count now holds the number of rows inserted (meaning number or rows corrisponding to the conditions above)
    -- add error control here.

    SELECT theme, t.ProductID, ModelName, ThemeHomePage, Description, UnitCost, UnitSaleCost, ProductImage, ModelNumber,ThemeDesigner, Yourid, PMid,
    MoreRecords =
    (
    SELECT COUNT(*)
    FROM #TempItems
    WHERE seq_id > @LastRec
    )

    FROM #TempItems t WITH (NOLOCK)
    INNER JOIN Products p
    ON p.ProductID = t.ProductID
    WHERE seq_id BETWEEN @FirstRec AND @LastRec

    -- calculating number or rows and pages
    IF @@ROWCOUNT > 0
    SELECT @row_count as ROW_COUNT,
    @row_count/@RecsPerPage + case (@row_count % @RecsPerPage) when 0 then 0 ELSE 1 end as PageCount
    ELSE
    SELECT 0 ROW_COUNT, 0 PageCount

    -- cleanup
    DROP TABLE #TempItems

    SET NOCOUNT OFF

  2. gaurav_bindlish New Member

  3. bambola New Member

    I cannot understand your dymanic query. You are comparing different columns to the same value, and I think you don't really mean to return all rows

    WHERE productID = %25% -- this will return error converting to int
    OR Description = %25%
    etc...

    Besides, this query will result an error when datatype are different and conversion between datatypes need to be explicit.

    But maybe I understand what you are trying to achieve. And considering that you are aware that the current query will ALWAYS perform a table scan, you can do this:



    CREATE PROCEDURE my_sproc
    (
    , @ProductID int = NULL
    , @ModelName varchar(50) = NULL
    , @Description varchar(50) = NULL
    , @ModelNumber varchar(50) = NULL
    , @ThemeDesigner varchar(50) = NULL
    , @Keywords varchar(50) = NULL
    , @Page int = 1
    , @RecsPerPage int = 15
    )
    AS
    .
    .
    .
    -- for all varchars
    SET @ModelNumber = '%' + @ModelNumber + '%'
    .
    .
    .
    INSERT INTO #TempItems
    SELECT ProductID
    FROM Products
    WHERE ProductID = @ProductID
    OR
    ModelNumber LIKE COALESCE(@ModelNumber, ModelNumber) AND Disable IS NULL
    OR
    .
    .
    .
    Now you can call your procedure with the parameters you want:

    EXEC dbo.my_sproc @ProductID = 1, @ModelNumber = 'xxx'

    Since you are no longer doing insert exec, you should also consider using table datatype.

    Let me know if I'm in the right direction.

    Bambola.


  4. ClassyThemes New Member

    hmmm......

    All I'm trying to do in that exec statement is selectany productId where any part of the product description or whatever matches/includes the search criteria.

    The user could enter a product ID or just a keyword and the search would return all matches.

    Perhaps where I am going wrong is trying to migrate the existing select statement I am using into the SP. The select statement works on it's own....it's just when used in conjunction with the sp that it bombs.
  5. ClassyThemes New Member

    Soo....Bambola....


    With your code suggestion I can give the users the option to search by product id, model name, description etc.....very interesting....

    but what I'm trying to do is just a general keywords search...most users wouldn't know what field to search.
  6. gaurav_bindlish New Member

  7. ClassyThemes New Member

    Thanks. I'll check that link.

    This sp works:

    (
    @Search nvarchar(255)
    )
    AS
    SELECT
    *
    FROM
    Products
    WHERE
    ModelNumber LIKE '%' + @Search + '%' AND Disable IS NULL
    OR
    ModelName LIKE '%' + @Search + '%' AND Disable IS NULL
    OR
    Description LIKE '%' + @Search + '%' AND Disable IS NULL
    OR
    ThemeDesigner Like '%' + @Search + '%' AND Disable IS NULL
    OR
    ProductID LIKE '%' + @Search + '%' AND Disable IS NULL
    OR
    Keywords LIKE '%' + @Search + '%' AND Disable IS NULL
    ORDER BY ModelName
  8. gaurav_bindlish New Member

    Please post the error that you are getting in the first SP.


    Gaurav
  9. bambola New Member

    Could you post the call to the procedure, the error message and the table structure?
    It would also be helpful if you post example of search strings you wish to run.

    In the meantime, try to print the string you are constructing (from the application and sproc), see what you have there. Maybe try to run it in QA if you cannot figure out what the error is.

    If you stick with the exec (string), I'd recoomand using sp_execute(sql) with parameters. it is more secure and should perform better.

    Bambola.
  10. gaurav_bindlish New Member

    Very true Bambola, sp_executesql() performs better than EXECUTE as its execution plan is cached while in the case of Execute, it is not.

    Gaurav

    Edit: Corrected the mistake as suggested by Bambola
  11. bambola New Member

    I obviously meant sp_executesql.

    Bambola.
  12. ClassyThemes New Member

    calling the procedure this way:

    SaleSQL = "sp_simplesearch " & currentpage &", "& iRecordsPerPage &", '"& SearchText&"'"

    Error:
    Line 2 = Incorrect syntax near 'WhateverWordEnteredInSearch'
  13. gaurav_bindlish New Member

    Does this procedure runs fine from query analyzer?

    Gaurav
  14. ClassyThemes New Member

    no. Same error either through the query analyzer or the script.
  15. gaurav_bindlish New Member

    Post the complete SP and the input data for the same. I think we can work this out.

    Gaurav
  16. bambola New Member

    Check to see if you have ' in the search string. It is also possible that one of the int variable is empty.<br />If not, post the actualy string, what you run in QA, otherwise it is hard to tell what the problem is. (replace whatever it is that you don't want us to see <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Bambola.
  17. ClassyThemes New Member

    That is the whole SP.

    I use the following to test

    @Page = 1
    @RecsPerPage =10
    @Search = business

    I know it's probably something simply you all can see....but it's had me going in circles for 3 days!
  18. gaurav_bindlish New Member

    Very crude question, are you passing business in quotes or not?

    If the ProdID is int as you have stored in temporary table, then u'll have to cast it to varchar and then concatenate with % for the comparison. So the query becomes
    INSERT INTO #TempItems (ProductID)
    EXEC('SELECT ProductID FROM Products WHERE
    ModelNumber LIKE %' + @Search + '% AND Disable IS NULL
    OR
    ModelName LIKE %' + @Search + '% AND Disable IS NULL
    OR
    Description LIKE %' + @Search + '% AND Disable IS NULL
    OR
    ThemeDesigner Like %' + @Search + '% AND Disable IS NULL
    OR
    ProductID LIKE %' + Convert(varchar(6),@Search) + '% AND Disable IS NULL
    OR....
    This brings us to the original point made by Bambola, if there is a datatype mismatch while comparison,u'll have to cast them. So if rest of the datatypes are also not the same, cast them before using. Makke this change and ket us know.

    Gaurav
  19. bambola New Member

    I assume it is @Search = 'business', otherwise it would have given an invalid column name error. So uhmm... you must be missing some key word.

    Try this:

    replace this:


    INSERT INTO #TempItems (ProductID)
    EXEC('SELECT ProductID FROM Products WHERE
    ModelNumber LIKE %' + @Search + '% AND Disable IS NULL
    OR
    ModelName LIKE %' + @Search + '% AND Disable IS NULL
    OR
    Description LIKE %' + @Search + '% AND Disable IS NULL
    OR
    ThemeDesigner Like %' + @Search + '% AND Disable IS NULL
    OR
    ProductID LIKE %' + @Search + '% AND Disable IS NULL
    OR
    Keywords LIKE %' + @Search + '% AND Disable IS NULL
    ORDER BY ModelName ' )

    with this



    declare @str varchar(8000)
    select @str = 'SELECT ProductID FROM Products WHERE
    ModelNumber LIKE %' + @Search + '% AND Disable IS NULL
    OR
    ModelName LIKE %' + @Search + '% AND Disable IS NULL
    OR
    Description LIKE %' + @Search + '% AND Disable IS NULL
    OR
    ThemeDesigner Like %' + @Search + '% AND Disable IS NULL
    OR
    ProductID LIKE %' + @Search + '% AND Disable IS NULL
    OR
    Keywords LIKE %' + @Search + '% AND Disable IS NULL
    ORDER BY ModelName '

    PRINT @str
    RETURN

    Now call it from QA and let me know what did it print.

    Bambola.



  20. ClassyThemes New Member

    Bambola....replacing the code as directed returns

    "The stored procedure executed successfully but did not return any records"

    I've tried passing the search word with quotes and without.
  21. gaurav_bindlish New Member

    What was the outut of the statement PRINT @str? And if it is not showing any output, then probably you passed @Search as NULL.

    Gaurav
  22. bambola New Member

    It is not supposed to return records.
    ok, replace PRINT @sql with SELECT @sql

    Bambola.
  23. ClassyThemes New Member

    There was no output....not that I could see. How could I possibly be passing the @Search as NULL??
  24. ClassyThemes New Member

    Ok Bambola....that gives me:

    SELECT ProductID FROM Products WHERE
    ModelNumber LIKE %business% AND Disable IS NULL
    OR
    ModelName LIKE %business% AND Disable IS NULL
    OR
    Description LIKE %business% AND Disable IS NULL
    OR
    ThemeDesigner Like %business% AND Disable IS NULL
    OR
    ProductID LIKE %business% AND Disable IS NULL
    OR
    Keywords LIKE %business% AND Disable IS NULL
    ORDER BY ModelName
  25. bambola New Member

    You are missing the quotes. it should be

    SELECT ProductID FROM Products WHERE
    ModelNumber LIKE '%business%' AND Disable IS NULL
    OR
    ModelName LIKE '%business%' AND Disable IS NULL
    OR
    Description LIKE '%business%' AND Disable IS NULL
    OR
    ThemeDesigner Like '%business%' AND Disable IS NULL
    OR
    ProductID LIKE '%business%' AND Disable IS NULL
    OR
    Keywords LIKE '%business%' AND Disable IS NULL
    ORDER BY ModelName

    Copy this from here into QA, and change back your sproc. Does it still give you an error?

    Bambola.
  26. ClassyThemes New Member

    GENIUS!<br /><br />[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />This did the trick:<br /><br />INSERT INTO #TempItems (ProductID)<br />EXEC('SELECT ProductID FROM Products WHERE<br />ModelNumber LIKE ''%' + @Search + '%'' AND Disable IS NULL<br />OR<br />ModelName LIKE ''%' + @Search + '%'' AND Disable IS NULL<br />OR<br />Description LIKE ''%' + @Search + '%'' AND Disable IS NULL<br />OR<br />ThemeDesigner Like ''%' + @Search + '%'' AND Disable IS NULL<br />OR<br />ProductID LIKE ''%' + @Search + '%'' AND Disable IS NULL<br />OR<br />Keywords LIKE ''%' + @Search + '%'' AND Disable IS NULL<br />ORDER BY ModelName ' ) <br /><br /><br />I cannot thank you enough for your help! You've taught me some great things about debugging today, and I really appreciate it!
  27. ClassyThemes New Member

    The only problem now becomes if they enter more than one word into the search.

    Any ideas???
  28. bambola New Member

    That problem can be solved. You can write a function that returns a table datatype, and join it in your query. Inside the function, parse the string accorsing to a delimiter, and insert each value into the table datatype.

    Something like

    CREATE FUNCTION SplitString (@string varchar(8000), @delimiter char(1))
    RETURNS @elements TABLE (Element varchar(8000)) -- or any other datatype
    AS
    BEGIN
    -- here write your code to loop over the string and insert each value into
    -- the table datatype. You can check for duplications if you want (or add it as
    -- a parameter of the function)

    RETURN
    END

    Now your query will look something like:

    SELECT columns...
    FROM my_table INNER JOIN dbo.SplitString(@str, ',')
    ON column_to_compare = Element

    OR

    SELECT columns...
    FROM my_table
    WHERE column_to_compare IN (SELECT Element FROM dbo.SplitString(@str, ',') )

    Bambola.


  29. ClassyThemes New Member

    Thanks!

    I really appreciate the help. I couldn't have done it without you.

Share This Page