September 10, 2015 at 7:42 am
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
September 10, 2015 at 7:51 am
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
September 10, 2015 at 8:12 am
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
September 10, 2015 at 8:20 am
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
September 10, 2015 at 8:20 am
I have added two more tables which has only 800 Records in it.
September 10, 2015 at 8:24 am
Please post your new code that uses the DelimitedSplit8K function and attach the actual execution plan, as Gail asked.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2015 at 8:29 am
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
September 10, 2015 at 8:46 am
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
September 10, 2015 at 8:47 am
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
September 10, 2015 at 8:51 am
i am using group by to get distinct data.
September 10, 2015 at 8:52 am
What would be alternative for not using functions in where, having and ON clause.
September 10, 2015 at 8:58 am
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
Change is inevitable... Change for the better is not.
September 10, 2015 at 9:01 am
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
Change is inevitable... Change for the better is not.
September 10, 2015 at 9:07 am
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.
September 10, 2015 at 9:09 am
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