Query is taking very long to execute

• akash_singh

  Ten Centuries

  Points: 1089

  More actions

  September 10, 2015 at 7:42 am

  #320517

  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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 10, 2015 at 7:51 am

  #1825666

  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

• akash_singh

  Ten Centuries

  Points: 1089

  More actions

  September 10, 2015 at 8:12 am

  #1825680

  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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 10, 2015 at 8:20 am

  #1825684

  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

• akash_singh

  Ten Centuries

  Points: 1089

  More actions

  September 10, 2015 at 8:20 am

  #1825685

  I have added two more tables which has only 800 Records in it.

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  September 10, 2015 at 8:24 am

  #1825687

  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)
  

• akash_singh

  Ten Centuries

  Points: 1089

  More actions

  September 10, 2015 at 8:29 am

  #1825690

  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

• Grant Fritchey

  SSC Guru

  Points: 398882

  More actions

  September 10, 2015 at 8:46 am

  #1825693

  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

• Grant Fritchey

  SSC Guru

  Points: 398882

  More actions

  September 10, 2015 at 8:47 am

  #1825694

  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

• akash_singh

  Ten Centuries

  Points: 1089

  More actions

  September 10, 2015 at 8:51 am

  #1825697

  i am using group by to get distinct data.

• akash_singh

  Ten Centuries

  Points: 1089

  More actions

  September 10, 2015 at 8:52 am

  #1825698

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

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  September 10, 2015 at 8:58 am

  #1825703

  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)
  

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  September 10, 2015 at 9:01 am

  #1825705

  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)
  

• akash_singh

  Ten Centuries

  Points: 1089

  More actions

  September 10, 2015 at 9:07 am

  #1825707

  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.

• akash_singh

  Ten Centuries

  Points: 1089

  More actions

  September 10, 2015 at 9:09 am

  #1825712

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