SQL Server Performance

How can I optimize the multiple case statement in this stored procedure?

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by yaltasource, Jan 21, 2009.

  1. yaltasource New Member

    Hi there
    Can I please get opinions on whether the below stored procedure is a silly way of sorting/has too many cases? And generally how I can improve it?

    CREATE PROCEDURE dbo.ContactDetailsSet_GetContactDetailsSet
    (@SortColumn nvarchar(50))
    AS
    BEGIN
    SET NOCOUNT ON;
    SELECT ContactDetailsSetID, ContactDetailsSetNamesID, OrganisationsID, ContactDetailsSetJobTitle, ContactDetailsSetEmail, ContactDetailsSetMobilePhone, ContactDetailsSetPhone, ContactDetailsSetPhoneOther1, ContactDetailsSetPhoneOther1Purpose, ContactDetailsSetPhoneOther2, ContactDetailsSetPhoneOther2Purpose, ContactDetailsSetFax, ContactDetailsSetAddressLine1, ContactDetailsSetAddressLine2, ContactDetailsSetAddressLine3, ContactDetailsSetSuburb, CountriesID, StatesID, CitiesID, ContactDetailsSetCountry, ContactDetailsSetState, ContactDetailsSetCity, ContactDetailsSetPostcode, ContactDetailsSetActive, ContactDetailsSetDateCreated, ContactDetailsSetCreatedBy, ContactDetailsSetLastModifiedBy, ContactDetailsSetDateLastModified FROM ContactDetailsSet ORDER BY
    CASE
    WHEN @SortColumn ='ContactDetailsSetIDAsc' THEN ContactDetailsSetID
    END ASC,
    CASE
    WHEN @SortColumn ='ContactDetailsSetIDDesc' THEN ContactDetailsSetID
    END DESC,
    CASE
    WHEN @SortColumn ='ContactDetailsSetActiveAsc' THEN ContactDetailsSetActive
    END ASC,
    CASE
    WHEN @SortColumn ='ContactDetailsSetActiveDesc' THEN ContactDetailsSetActive
    END DESC,
    CASE
    WHEN @SortColumn ='ContactDetailsSetDateCreatedAsc' THEN ContactDetailsSetDateCreated
    WHEN @SortColumn ='ContactDetailsSetDateLastModifiedAsc' THEN ContactDetailsSetDateLastModified
    END ASC,
    CASE
    WHEN @SortColumn ='ContactDetailsSetDateCreatedDesc' THEN ContactDetailsSetDateCreated
    WHEN @SortColumn ='ContactDetailsSetDateLastModifiedDesc' THEN ContactDetailsSetDateLastModified
    END DESC,
    CASE
    WHEN @SortColumn ='StatesIDAsc' THEN StatesID
    WHEN @SortColumn ='CountriesIDAsc' THEN CountriesID
    WHEN @SortColumn ='ContactDetailsSetNamesIDAsc' THEN ContactDetailsSetNamesID
    WHEN @SortColumn ='ContactDetailsSetCreatedByAsc' THEN ContactDetailsSetCreatedBy
    WHEN @SortColumn ='ContactDetailsSetLastModifiedByAsc' THEN ContactDetailsSetLastModifiedBy
    WHEN @SortColumn ='CitiesIDAsc' THEN CitiesID
    WHEN @SortColumn ='OrganisationsIDAsc' THEN OrganisationsID
    END ASC,
    CASE
    WHEN @SortColumn ='StatesIDDesc' THEN StatesID
    WHEN @SortColumn ='CountriesIDDesc' THEN CountriesID
    WHEN @SortColumn ='ContactDetailsSetNamesIDDesc' THEN ContactDetailsSetNamesID
    WHEN @SortColumn ='ContactDetailsSetCreatedByDesc' THEN ContactDetailsSetCreatedBy
    WHEN @SortColumn ='ContactDetailsSetLastModifiedByDesc' THEN ContactDetailsSetLastModifiedBy
    WHEN @SortColumn ='CitiesIDDesc' THEN CitiesID
    WHEN @SortColumn ='OrganisationsIDDesc' THEN OrganisationsID
    END DESC,
    CASE
    WHEN @SortColumn ='ContactDetailsSetStateAsc' THEN ContactDetailsSetState
    WHEN @SortColumn ='ContactDetailsSetCityAsc' THEN ContactDetailsSetCity
    WHEN @SortColumn ='ContactDetailsSetPhoneAsc' THEN ContactDetailsSetPhone
    WHEN @SortColumn ='ContactDetailsSetCountryAsc' THEN ContactDetailsSetCountry
    WHEN @SortColumn ='ContactDetailsSetPostcodeAsc' THEN ContactDetailsSetPostcode
    WHEN @SortColumn ='ContactDetailsSetJobTitleAsc' THEN ContactDetailsSetJobTitle
    WHEN @SortColumn ='ContactDetailsSetAddressLine2Asc' THEN ContactDetailsSetAddressLine2
    WHEN @SortColumn ='ContactDetailsSetMobilePhoneAsc' THEN ContactDetailsSetMobilePhone
    WHEN @SortColumn ='ContactDetailsSetEmailAsc' THEN ContactDetailsSetEmail
    WHEN @SortColumn ='ContactDetailsSetAddressLine1Asc' THEN ContactDetailsSetAddressLine1
    WHEN @SortColumn ='ContactDetailsSetPhoneOther2PurposeAsc' THEN ContactDetailsSetPhoneOther2Purpose
    WHEN @SortColumn ='ContactDetailsSetFaxAsc' THEN ContactDetailsSetFax
    WHEN @SortColumn ='ContactDetailsSetPhoneOther1PurposeAsc' THEN ContactDetailsSetPhoneOther1Purpose
    WHEN @SortColumn ='ContactDetailsSetPhoneOther1Asc' THEN ContactDetailsSetPhoneOther1
    WHEN @SortColumn ='ContactDetailsSetSuburbAsc' THEN ContactDetailsSetSuburb
    WHEN @SortColumn ='ContactDetailsSetPhoneOther2Asc' THEN ContactDetailsSetPhoneOther2
    WHEN @SortColumn ='ContactDetailsSetAddressLine3Asc' THEN ContactDetailsSetAddressLine3
    END ASC,
    CASE
    WHEN @SortColumn ='ContactDetailsSetStateDesc' THEN ContactDetailsSetState
    WHEN @SortColumn ='ContactDetailsSetCityDesc' THEN ContactDetailsSetCity
    WHEN @SortColumn ='ContactDetailsSetPhoneDesc' THEN ContactDetailsSetPhone
    WHEN @SortColumn ='ContactDetailsSetCountryDesc' THEN ContactDetailsSetCountry
    WHEN @SortColumn ='ContactDetailsSetPostcodeDesc' THEN ContactDetailsSetPostcode
    WHEN @SortColumn ='ContactDetailsSetJobTitleDesc' THEN ContactDetailsSetJobTitle
    WHEN @SortColumn ='ContactDetailsSetAddressLine2Desc' THEN ContactDetailsSetAddressLine2
    WHEN @SortColumn ='ContactDetailsSetMobilePhoneDesc' THEN ContactDetailsSetMobilePhone
    WHEN @SortColumn ='ContactDetailsSetEmailDesc' THEN ContactDetailsSetEmail
    WHEN @SortColumn ='ContactDetailsSetAddressLine1Desc' THEN ContactDetailsSetAddressLine1
    WHEN @SortColumn ='ContactDetailsSetPhoneOther2PurposeDesc' THEN ContactDetailsSetPhoneOther2Purpose
    WHEN @SortColumn ='ContactDetailsSetFaxDesc' THEN ContactDetailsSetFax
    WHEN @SortColumn ='ContactDetailsSetPhoneOther1PurposeDesc' THEN ContactDetailsSetPhoneOther1Purpose
    WHEN @SortColumn ='ContactDetailsSetPhoneOther1Desc' THEN ContactDetailsSetPhoneOther1
    WHEN @SortColumn ='ContactDetailsSetSuburbDesc' THEN ContactDetailsSetSuburb
    WHEN @SortColumn ='ContactDetailsSetPhoneOther2Desc' THEN ContactDetailsSetPhoneOther2
    WHEN @SortColumn ='ContactDetailsSetAddressLine3Desc' THEN ContactDetailsSetAddressLine3
    END DESC
    END
    Thanks
    Sam
  2. Adriaan New Member

    Why not let the client application do the sorting on the resultset? This might also reduce the number of repetetive calls to SQL Server for basically the same data.
  3. Adriaan New Member

    --duplicate post--
  4. Adriaan New Member

    Else, why not use dynamic SQL ...
    DECLARE @SQL VARCHAR(MAX)
    SET @SQL = 'SELECT ContactDetailsSetID, ContactDetailsSetNamesID, OrganisationsID, ContactDetailsSetJobTitle, ContactDetailsSetEmail, ContactDetailsSetMobilePhone, ContactDetailsSetPhone, ContactDetailsSetPhoneOther1, ContactDetailsSetPhoneOther1Purpose, ContactDetailsSetPhoneOther2, ContactDetailsSetPhoneOther2Purpose, ContactDetailsSetFax, ContactDetailsSetAddressLine1, ContactDetailsSetAddressLine2, ContactDetailsSetAddressLine3, ContactDetailsSetSuburb, CountriesID, StatesID, CitiesID, ContactDetailsSetCountry, ContactDetailsSetState, ContactDetailsSetCity, ContactDetailsSetPostcode, ContactDetailsSetActive, ContactDetailsSetDateCreated, ContactDetailsSetCreatedBy, ContactDetailsSetLastModifiedBy, ContactDetailsSetDateLastModified FROM ContactDetailsSet ORDER BY '
    IF RIGHT(@SortColumn, 4) = 'DESC'
    SET @SQL = @SQL + LEFT(@SortColumn, LEN(@SortColumn) - 4) + ' DESC'
    ELSE
    SET @SQL = @SQL + LEFT(@SortColumn, LEN(@SortColumn) - 3) + ' ASC'

    EXEC (@SQL)
    ... or even avoid the whole conditional string manipulation by making the client application insert a space in the @SortColumn value before ASC or DESC.
  5. RedDevils New Member

    It would be preety good idea to use Dynamic SQL in this case using CASE statement instead.
    -Abhijit, MCP

Share This Page