Query is taking very long to execute

  • Hi,

    I have a table which has 39 columns with a primary key. I am performing single select operation which looks like below query. This table has close to 200K records only but still taking 25-30 seconds to execute.

    I have joined the table to a table valued function which returns comma separated fund IDs into a single column.

    Select <10-12 Columns> Into #Temp

    From Dbo.[VDSMeetingInformation] VMI INNER JOIN dbo.[vdsfn_SplitString](@fund_id,',') fnsplt

    ON VMI.FundID = (CASE WHEN fnsplt.splitdata IS NULL THEN VMI.FundID ELSE fnsplt.splitdata END)

    Please let me know if anything else is required from my side.

    Thanks,

    Akash

  • Start by replacing the split function (which is probably an inefficient one) with the DelimitedSplit8K[/url] from this site.

    If the query is still slow after doing that, please post table definitions, index definitions and execution plan.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi,

    Please find the attached execution plan and table structure for your reference. After replacing the tabular function, still there is no improvement.

    Thanks,

    Akash

  • Please post the execution plan, not a picture of part of the plan.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I have added two more tables which has only 800 Records in it.

  • Please post your new code that uses the DelimitedSplit8K function and attach the actual execution plan, as Gail asked.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • INSERT INTO #MeetingInformation(MeetingID,FundID,VoteString,ClientVoteID,VoteFlag,MeetingDate,RecordDate,CompanyName,Ticker

    ,CompressedName,MeetingTypeID,MeetingType,SecurityID

    ,SecurityType,VotingPolicyID,VotingPolicy,CountryId,Country,Latitude,Longitude,ItemOnAgendaID,BallotID,ClientVote,MgtRecVote,CompanyID,

    SixDigitGICS,SixDigitSectorType)

    SELECTVMI.MeetingID,

    VMI.FundID,

    VMI.VoteString,

    VMI.ClientVoteID,

    CASE WHEN ((VMI.VoteString IS NULL) OR RTRIM(LTRIM(REPLACE(REPLACE(VMI.VoteString,'D',''),'N',''))) = '' )THEN 0 ELSE 1 END AS VoteFlag,

    VMI.MeetingDate,

    VMI.RecordDate,

    VMI.CompanyName As CompanyName,

    VMI.Ticker As Ticker,

    VMI.CompressedName As CompressedName,

    VMI.MeetingTypeID,

    VMI.MeetingType,

    VMI.SecurityID,

    VMI.SecurityType,

    VMI.VotingPolicyID,

    VMI.VotingPolicy,

    VMI.CountryID,

    VMI.Country,

    VMI.Latitude,

    VMI.Longitude,

    VMI.ItemOnAgendaID,

    VMI.BallotID,

    VMI.ClientVote AS ClientVote,

    VMI.MgtRecVote,

    VMI.CompanyID,

    VMI.SixDigitGICS,

    VMI.SixDigitSectorType

    FROMDbo.[VDSMeetingInformation] VMI

    INNER JOIN dbo.[DelimitedSplit8K](@fund_id,',') fnsplt ON VMI.FundID =(CASE WHEN fnsplt.item IS NULL THEN VMI.FundID ELSE fnsplt.item END)

    WHERE VMI.customerID = @customer_id

    AND VMI.MeetingDate BETWEEN @Start_date AND @End_date

    AND VMI.MeetingID > 0

    AND VMI.Country IS NOT NULL

    AND ( COALESCE(VMI.CompanyName,VMI.CompanyName ) LIKE COALESCE(@Value1,VMI.CompanyName,VMI.CompanyName ) +'%'

    OR

    COALESCE(VMI.ticker,VMI.Ticker) LIKE COALESCE(@Value1,VMI.ticker,VMI.Ticker)+'%'

    OR

    VMI.SecurityID LIKE COALESCE(@value1,VMI.SecurityID) +'%'

    )

    GROUP BYVMI.MeetingID,

    VMI.FundID,

    VMI.VoteString,

    VMI.ClientVoteID,

    CASE WHEN ((VMI.VoteString IS NULL) OR RTRIM(LTRIM(REPLACE(REPLACE(VMI.VoteString,'D',''),'N',''))) = '' )THEN 0 ELSE 1 END ,

    VMI.MeetingDate,

    VMI.RecordDate,

    VMI.CompanyName ,

    VMI.Ticker ,

    VMI.CompressedName ,

    VMI.MeetingTypeID,

    VMI.MeetingType,

    VMI.SecurityID,

    VMI.SecurityType,

    VMI.VotingPolicyID,

    VMI.VotingPolicy,

    VMI.CountryID,

    VMI.Country,

    VMI.Latitude,

    VMI.Longitude,

    VMI.ItemOnAgendaID,

    VMI.BallotID,

    VMI.ClientVote ,

    VMI.MgtRecVote,

    VMI.CompanyID,

    VMI.SixDigitGICS,

    VMI.SixDigitSectorType

  • First thing I'd do is replace all this mess with OR clauses:

    ( COALESCE(VMI.CompanyName,VMI.CompanyName )

    That is going to absolutely destroy any chance of good index use. Don't use functions against columns in WHERE, HAVING or ON conditions.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Why is there a GROUP BY when you're not doing any aggregations that I can see?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • i am using group by to get distinct data.

  • What would be alternative for not using functions in where, having and ON clause.

  • Grant is spot on. Column names encapsulated in formulas and OR in the WHERE clause is likely the larger problem.

    A part of the problem may be due to the nature of some inline table valued functions. Although they're well worth using due to the performance, you sometimes do have to use them like they want to be used. I've seen some places (like here) where someone doesn't actually need the ItemNumber column from the function and so they don't included it, which can sometimes kill performance depending on how the optimizer is feeling that day.

    Since you don't actually need to return the ItemNumber column, include WHERE fnsplit.ItemNumber > 0 to your code. Considering all the non-SARGable predicates and the ORs in the WHERE clause, not to mention that you also have a conditional GROUP BY, that may not be of any help here.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • akash_singh (9/10/2015)


    i am using group by to get distinct data.

    That would also be a part of the problem. It would seem that you might have an accidental many-to-many join going on and you're trying to make up for it using the equivalent of a DISTINCT.

    akash_singh (9/10/2015)


    What would be alternative for not using functions in where, having and ON clause.

    The answer to both question would be to figure out what's causing the need for the GROUP BY and then, perhaps, use some "Divide'n'Conquer" methods (perhaps an intermediate Temp Table) to prevent that problem.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi,

    I have changed the query to

    SELECTVMI.MeetingID,

    VMI.FundID,

    VMI.VoteString,

    VMI.ClientVoteID,

    CASE WHEN ((VMI.VoteString IS NULL) OR RTRIM(LTRIM(REPLACE(REPLACE(VMI.VoteString,'D',''),'N',''))) = '' )THEN 0 ELSE 1 END AS VoteFlag,

    VMI.MeetingDate,

    VMI.RecordDate,

    VMI.CompanyName As CompanyName,

    VMI.Ticker As Ticker,

    VMI.CompressedName As CompressedName,

    VMI.MeetingTypeID,

    VMI.MeetingType,

    VMI.SecurityID,

    VMI.SecurityType,

    VMI.VotingPolicyID,

    VMI.VotingPolicy,

    VMI.CountryID,

    VMI.Country,

    VMI.Latitude,

    VMI.Longitude,

    VMI.ItemOnAgendaID,

    VMI.BallotID,

    VMI.ClientVote AS ClientVote,

    VMI.MgtRecVote,

    VMI.CompanyID,

    VMI.SixDigitGICS,

    VMI.SixDigitSectorType

    FROM Dbo.[VDSMeetingInformation] VMI

    INNER JOIN dbo.[DelimitedSplit8K](@fund_id,',') fnsplt ON VMI.FundID =(CASE WHEN fnsplt.item IS NULL THEN VMI.FundID ELSE fnsplt.item END)

    WHERE VMI.customerID = @customer_id

    AND VMI.MeetingDate BETWEEN @Start_date AND @End_date

    AND VMI.MeetingID > 0

    AND VMI.Country IS NOT NULL

    AND (

    VMI.CompanyName LIKE COALESCE(@Value1,VMI.CompanyName ) +'%'

    OR

    VMI.ticker LIKE COALESCE(@Value1,VMI.Ticker)+'%'

    OR

    VMI.SecurityID LIKE COALESCE(@value1,VMI.SecurityID) +'%'

    )

    GROUP BY VMI.MeetingID,

    VMI.FundID,

    VMI.VoteString,

    VMI.ClientVoteID,

    CASE WHEN ((VMI.VoteString IS NULL) OR RTRIM(LTRIM(REPLACE(REPLACE(VMI.VoteString,'D',''),'N',''))) = '' )THEN 0 ELSE 1 END ,

    VMI.MeetingDate,

    VMI.RecordDate,

    VMI.CompanyName ,

    VMI.Ticker ,

    VMI.CompressedName ,

    VMI.MeetingTypeID,

    VMI.MeetingType,

    VMI.SecurityID,

    VMI.SecurityType,

    VMI.VotingPolicyID,

    VMI.VotingPolicy,

    VMI.CountryID,

    VMI.Country,

    VMI.Latitude,

    VMI.Longitude,

    VMI.ItemOnAgendaID,

    VMI.BallotID,

    VMI.ClientVote ,

    VMI.MgtRecVote,

    VMI.CompanyID,

    VMI.SixDigitGICS,

    VMI.SixDigitSectorType

    But still cannot see any improvements. COALESCE i am using because it is the requirement for search criteria.

  • should i use distinct and remove table valued function. And try to use alternatives of COALESCE.

Viewing 15 posts - 1 through 15 (of 15 total)

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