QUERY OPTIMISATION AS IT TAKES MORE THAN ONE MINUTE TO FETCH THE RECORDS

  • d

  • USE [Orderex]

    GO

    /****** Object: StoredProcedure [dbo].[GetReverseHoldingsByStockTIDMDetailed] Script Date: 11/30/2012 12:06:17 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    Alter PROCEDURE [dbo].[GetReverseHoldingsByStockTIDMDetailed]

    (

    @user-id as varchar(7),

    @LoginType as varchar(5),

    @AccType varchar(3),

    @AccountActive varchar(1),

    @SFAClientType varchar(20),

    @TIDM varchar(7),

    @SEDOL varchar(7),

    @responsibilityCode varchar(7),

    @pageNumber int,

    @pageSize int,

    @sortExpression varchar(32),

    @sortOrder varchar(4),

    @virtualCount int OUTPUT

    )

    WITH RECOMPILE

    AS

    BEGIN

    CREATE TABLE #CLIENTHOLDINGSSTOCKTIDMDETAILED(

    [CLIENTCODE] int NULL ,

    [FullName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [ShortName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [ExecCode] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [ExecName][varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [SFAClientType] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    ) ON [PRIMARY]

    INSERT #CLIENTHOLDINGSSTOCKTIDMDETAILED

    (CLIENTCODE,FullName,ShortName,ExecCode,ExecName,SFAClientType)

    SELECT

    CONVERT(INT,A.Acct_no),

    A.Client_name,

    A.ClientShortName,

    A.Remisier_code,

    B.Client_name,

    CASEWHEN SFAClientType = 'E' THEN '(E) Exec'

    WHEN SFAClientType = 'M' THEN '(M) Adv Mgd'

    WHEN SFAClientType = 'A' THEN '(A) Adv'

    WHEN SFAClientType = 'D' THEN '(D) Disc'

    WHEN SFAClientType = 'B' THEN '(B) Bus'

    WHEN SFAClientType = 'N' THEN '(N) Disc - N/F'

    WHEN SFAClientType = 'F' THEN '(F) F.Cli.'

    WHEN SFAClientType = 'X' THEN '(X) Exp'

    WHEN SFAClientType = 'I' THEN '(I) Ind Cust'

    WHEN SFAClientType = 'O' THEN '(O) Ord'

    WHEN SFAClientType = 'S' THEN '(S) STC'

    ELSE '(--) Udf' END AS SFAClientType

    FROM [Orderex].[dbo].[Client_oe_info] A

    INNER JOIN Awol.dbo.RR_CODES AS B ON A.Remisier_code = B.Acct_no

    WHERE CONVERT(INT,A.Acct_no) NOT IN (

    SELECT HoldingsInclNT.AccountNumber

    FROM (

    SELECT CONVERT(INT,ACC_NUMBER) AS AccountNumber, TIDM,SEDOL,CODE_ASCII FROM ALL_HOLDINGS

    inner join client_oe_info coe on CONVERT(INT,coe.Acct_no) = ACC_NUMBER where coe.Remisier_code = @responsibilityCode

    UNION

    SELECT CONVERT(INT,ACC_NUMBER) AS AccountNumber, TIDM,SEDOL,CODE_ASCII FROM ALL_NT_HOLDINGS

    ) HoldingsInclNT

    WHERE (TIDM = @TIDM OR SEDOL = @SEDOL)

    AND code_ascii = Cast(Ascii(@AccType) as VarChar(3))

    )

    AND (

    @LoginType = 'Staff'

    AND A.Remisier_code = (CASE WHEN @responsibilityCode = 'All' THEN A.Remisier_code ELSE @responsibilityCode END)

    AND A.ACCOUNTACTIVE = (CASE WHEN @AccountActive = 'A' THEN A.ACCOUNTACTIVE ELSE @AccountActive END)

    )

    OR

    (

    @LoginType = 'Assoc'

    AND (@responsibilityCode = 'All'

    AND (A.REMISIER_CODE IN (SELECT RR_Code FROM awol.dbo.RR_Matrix WHERE Alias_RR_Code = @user-id) OR A.REMISIER_CODE = @user-id)

    OR A.REMISIER_CODE = @responsibilityCode

    )

    AND A.ACCOUNTACTIVE = (CASE WHEN @AccountActive = 'A' THEN A.ACCOUNTACTIVE ELSE @AccountActive END)

    )

    /*

    Make sure that the page number is at least 1

    */

    IF @pageNumber < 1

    BEGIN

    SET @pageNumber = 1

    END

    SELECT @virtualCount = COUNT(CLIENTCODE)

    FROM [#CLIENTHOLDINGSSTOCKTIDMDETAILED]

    WHERE SFAClientType LIKE (CASE WHEN @SFAClientType = 'ALL' THEN '%' ELSE @SFAClientType END)

    DECLARE @lastKeyValue numeric(18,0)

    DECLARE @lastAscendingSortValue SQL_Variant

    DECLARE @lastDescendingSortValue SQL_Variant

    DECLARE @numberToIgnore int

    SET @numberToIgnore = (@pageNumber-1) * @pageSize

    IF @numberToIgnore > 0

    BEGIN

    /*

    Get the last available sort data and unique key

    value from the last page.

    */

    SET ROWCOUNT @numberToIgnore

    SELECT

    @lastKeyValue = [UniqueValue],

    @lastAscendingSortValue = [AscendingSort],

    @lastDescendingSortValue = [DescendingSort]

    FROM

    (

    SELECT CLIENTCODE As [UniqueValue],

    CASE

    WHEN

    UPPER(@sortOrder) = 'DESC'

    AND UPPER(@sortExpression) = 'CLIENTCODE'

    THEN

    CONVERT(SQL_Variant, CLIENTCODE)

    WHEN

    UPPER(@sortOrder) = 'DESC'

    AND UPPER(@sortExpression) = 'FullName'

    THEN

    CONVERT(SQL_Variant, FullName)

    WHEN

    UPPER(@sortOrder) = 'DESC'

    AND UPPER(@sortExpression) = 'ShortName'

    THEN

    CONVERT(SQL_Variant, ShortName)

    WHEN

    UPPER(@sortOrder) = 'DESC'

    AND UPPER(@sortExpression) = 'ExecCode'

    THEN

    CONVERT(SQL_Variant, ExecCode)

    WHEN

    UPPER(@sortOrder) = 'DESC'

    AND UPPER(@sortExpression) = 'ExecName'

    THEN

    CONVERT(SQL_Variant, ExecName)

    WHEN

    UPPER(@sortOrder) = 'DESC'

    AND UPPER(@sortExpression) = 'SFAClientType'

    THEN

    CONVERT(SQL_Variant, SFAClientType)

    ELSE

    NULL

    END AS [DescendingSort],

    CASE

    WHEN

    UPPER(@sortOrder) = 'ASC'

    AND UPPER(@sortExpression) = 'CLIENTCODE'

    THEN

    CONVERT(SQL_Variant, CLIENTCODE)

    WHEN

    UPPER(@sortOrder) = 'ASC'

    AND UPPER(@sortExpression) = 'FullName'

    THEN

    CONVERT(SQL_Variant, FullName)

    WHEN

    UPPER(@sortOrder) = 'ASC'

    AND UPPER(@sortExpression) = 'ShortName'

    THEN

    CONVERT(SQL_Variant, ShortName)

    WHEN

    UPPER(@sortOrder) = 'ASC'

    AND UPPER(@sortExpression) = 'ExecCode'

    THEN

    CONVERT(SQL_Variant, ExecCode)

    WHEN

    UPPER(@sortOrder) = 'ASC'

    AND UPPER(@sortExpression) = 'ExecName'

    THEN

    CONVERT(SQL_Variant, ExecName)

    WHEN

    UPPER(@sortOrder) = 'ASC'

    AND UPPER(@sortExpression) = 'SFAClientType'

    THEN

    CONVERT(SQL_Variant, SFAClientType)

    ELSE

    NULL

    END AS [AscendingSort]

    FROM #CLIENTHOLDINGSSTOCKTIDMDETAILED

    WHERE SFAClientType LIKE (CASE WHEN @SFAClientType = 'ALL' THEN '%' ELSE @SFAClientType END)

    ) AS Derived

    ORDER BY

    [AscendingSort] ASC,

    [DescendingSort] DESC,

    [UniqueValue] ASC

    END

    /*

    Select the first @pageSize records that come after the last sort

    data/unique value from the last page. If this is the first page,

    just get the first @pageSize records.

    */

    SET ROWCOUNT @pageSize

    SELECT CLIENTCODE, FullName,ShortName,ExecCode,ExecName, SFAClientType

    FROM

    (

    SELECT RIGHT('000000'+ CONVERT(VARCHAR,CLIENTCODE),7) AS CLIENTCODE,

    FullName,

    ShortName,

    ExecCode,

    ExecName,

    SFAClientType,

    CLIENTCODE As [UniqueValue],

    CASE

    WHEN

    UPPER(@sortOrder) = 'DESC'

    AND UPPER(@sortExpression) = 'CLIENTCODE'

    THEN

    CONVERT(SQL_Variant, CLIENTCODE)

    WHEN

    UPPER(@sortOrder) = 'DESC'

    AND UPPER(@sortExpression) = 'FullName'

    THEN

    CONVERT(SQL_Variant, FullName)

    WHEN

    UPPER(@sortOrder) = 'DESC'

    AND UPPER(@sortExpression) = 'ShortName'

    THEN

    CONVERT(SQL_Variant, ShortName)

    WHEN

    UPPER(@sortOrder) = 'DESC'

    AND UPPER(@sortExpression) = 'ExecCode'

    THEN

    CONVERT(SQL_Variant, ExecCode)

    WHEN

    UPPER(@sortOrder) = 'DESC'

    AND UPPER(@sortExpression) = 'ExecName'

    THEN

    CONVERT(SQL_Variant, ExecName)

    WHEN

    UPPER(@sortOrder) = 'DESC'

    AND UPPER(@sortExpression) = 'SFAClientType'

    THEN

    CONVERT(SQL_Variant, SFAClientType)

    ELSE

    NULL

    END AS [DescendingSort],

    CASE

    WHEN

    UPPER(@sortOrder) = 'ASC'

    AND UPPER(@sortExpression) = 'CLIENTCODE'

    THEN

    CONVERT(SQL_Variant, CLIENTCODE)

    WHEN

    UPPER(@sortOrder) = 'ASC'

    AND UPPER(@sortExpression) = 'FullName'

    THEN

    CONVERT(SQL_Variant, FullName)

    WHEN

    UPPER(@sortOrder) = 'ASC'

    AND UPPER(@sortExpression) = 'ShortName'

    THEN

    CONVERT(SQL_Variant, ShortName)

    WHEN

    UPPER(@sortOrder) = 'ASC'

    AND UPPER(@sortExpression) = 'ExecCode'

    THEN

    CONVERT(SQL_Variant, ExecCode)

    WHEN

    UPPER(@sortOrder) = 'ASC'

    AND UPPER(@sortExpression) = 'ExecName'

    THEN

    CONVERT(SQL_Variant, ExecName)

    WHEN

    UPPER(@sortOrder) = 'ASC'

    AND UPPER(@sortExpression) = 'SFAClientType'

    THEN

    CONVERT(SQL_Variant, SFAClientType)

    ELSE

    NULL

    END AS [AscendingSort]

    FROM #CLIENTHOLDINGSSTOCKTIDMDETAILED

    WHERE SFAClientType LIKE (CASE WHEN @SFAClientType = 'ALL' THEN '%' ELSE @SFAClientType END)

    ) Derived

    WHERE

    (

    @NumberToIgnore = 0

    )

    OR

    (

    @sortOrder='ASC'

    AND

    (

    (

    @lastAscendingSortValue IS NULL

    AND

    (

    ([UniqueValue] > @lastKeyValue AND [AscendingSort] IS NULL)

    OR

    [AscendingSort] IS NOT NULL

    )

    )

    OR

    (

    @lastAscendingSortValue IS NOT NULL

    AND

    (

    [AscendingSort] > @lastAscendingSortValue

    OR

    ([AscendingSort] = @lastAscendingSortValue AND [UniqueValue] > @lastKeyValue)

    )

    )

    )

    )

    OR

    (

    @sortOrder='DESC'

    AND

    (

    (

    @lastDescendingSortValue IS NOT NULL

    AND (

    ([DescendingSort] IS NOT NULL AND

    ([DescendingSort] = @lastDescendingSortValue AND [UniqueValue] > @lastKeyValue)

    OR

    ([DescendingSort] < @lastDescendingSortValue)

    )

    OR

    ([DescendingSort] IS NULL)

    )

    )

    OR

    (@lastDescendingSortValue IS NULL AND [DescendingSort] IS NULL AND [UniqueValue] > @lastKeyValue)

    )

    )

    ORDER BY

    [AscendingSort] ASC,

    [DescendingSort] DESC,

    [UniqueValue] ASC

    SET ROWCOUNT 0

    END

    GO

  • freecoder (11/30/2012)


    ok I tried your query and the result has not change at all. I change the responsibilitycode parameter to a specific one instead of 'ALL' the query works fine but it gives me a problem when I choose certain account types.

    I am including the execution plan for the query where it takes 1 min.

    I have indexed on the tables as well.

    You probably didn't understand me correctly.

    I have not asked you to change your parameter, but remove it completely!

    Try a query, which doesn't check input parameters at all. Code it as it would be for the most "relaxed" combination of input parameters. You may find that 1 min is mostly required to fetch a large volume of data in case of parameters set to 'ALL'.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • a

  • I have also noticed that your code is changes from post to post 😉

    Now I can see that you also trying to page it.

    Please note:

    SET ROWCOUNT is not the best way to do paging in SQL, consider changing it to use TOP and ROW_NUMBER().

    Actually, I think you might benefit a lot if you rewrite your sp to use dynamic sql...

    Unfortunately, I cannot download attached files at work, so cannot see your query plan...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • d

  • How many records does query return without parameters?

    Looking the latest version of your sp, I can tell that it has quite few things...

    1. Brackets in WHERE clause of your "temp table" populating query doesn't look right.

    You need to have additional set of brackets around two parts of OR

    2. Is your database a case sensitive? I doubt it as your checks for:

    AND UPPER(@sortExpression) = 'ExecCode'

    would never work.

    If not, then it's no point to UPPER your strings for comparison... It just adds more noise to the code.

    Actually because of wrong brackets, the query you run without parameters is not really equal to the original one with parameters set to "ALL", it may return much less records due to the OR condition

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • freecoder (11/30/2012)


    can u please help me! I have never written dynamic sql. And I dont have time to learn :(.

    Can you give it to someone else to do? Surely if your company uses SQL Server it should employ someone with skills...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply