Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
duhaas
Constraint Violating Yak Guru
310 Posts |
 Posted - 2009-08-19 : 16:10:48
|
Looking for some advice on how I can possibly speed up the procedure:ASWITH MarksRnkCnt AS( SELECT ltrim(isnull(pmname.Firstname,'') + ' ' + isnull(pmname.Lastname,'')) as pm,substring(ap.accountnumber,3,9)as accountnumber, ap.[1YReturn],ap.reportdt, RANK() OVER(ORDER BY ap.[1yreturn]) AS rnk, COUNT(*) OVER() AS cnt FROM abacuspmperformance ap left join account_detail ad on substring(ap.accountnumber,3,9) = ad.account_number left join dbo.InvestmentOfficers pmname on ad.portfolio_manager = pmname.initials whereap.[1yreturn] is not nulland ap.[1yreturn] <> '1' and ap.perfclass = 'totl' and reportdt = @date and ltrim(isnull(pmname.Firstname,'') + ' ' + isnull(pmname.Lastname,'')) = @pm)SELECT pm,accountnumber,round([1yReturn],2) as y1Return, cast(round(1.*(rnk-1)/(cnt-1),2) as decimal(18,2)) AS percentilerankFROM MarksRnkCnt where (abs((1.*(rnk-1)/(cnt-1)) -(0.05))) = (select min(abs( (1.*(rnk-1)/(cnt-1)) - 0.05)) from MarksRnkCnt)or (abs((1.*(rnk-1)/(cnt-1)) -(0.95))) = (select min(abs( (1.*(rnk-1)/(cnt-1)) - 0.95)) from MarksRnkCnt) order by percentilerank asc @date is a static date, and @pm is a value pulled from a lookup table. It took about 54 seconds to run just now, and the abacusperformance table has about 200K rows in it. Any help or suggestions would be greatly appreciated |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-08-19 : 16:24:54
|
| I'm guessing this isn't helping:left join account_detail ad on substring(ap.accountnumber,3,9) = ad.account_number Any index on ap.accountnumber will be ignored since you are using a substring of it to correlate with ad.account_numbersimilar problem could be happengin with the =@pm part. about how many rows are returned in your CTE and how many are being filtered by the final WHERE clause?What indexes are available and can you post the execution planBe One with the OptimizerTG |
 |
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2009-08-19 : 16:52:12
|
quote:
Originally posted by TG
I'm guessing this isn't helping:left join account_detail ad on substring(ap.accountnumber,3,9) = ad.account_number Any index on ap.accountnumber will be ignored since you are using a substring of it to correlate with ad.account_numbersimilar problem could be happengin with the =@pm part. about how many rows are returned in your CTE and how many are being filtered by the final WHERE clause?What indexes are available and can you post the execution planBe One with the OptimizerTG
Let me actually just update the table where I have to do the substring on to only include the 9 digits I need first, then eliminating the need to do that substring and I will follow up with the rest. |
|
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2009-08-19 : 17:28:24
|
quote:
Originally posted by duhaas
quote:
Originally posted by TG
I'm guessing this isn't helping:left join account_detail ad on substring(ap.accountnumber,3,9) = ad.account_number Any index on ap.accountnumber will be ignored since you are using a substring of it to correlate with ad.account_numbersimilar problem could be happengin with the =@pm part. about how many rows are returned in your CTE and how many are being filtered by the final WHERE clause?What indexes are available and can you post the execution planBe One with the OptimizerTG
Let me actually just update the table where I have to do the substring on to only include the 9 digits I need first, then eliminating the need to do that substring and I will follow up with the rest.
So doing the update on the table first to get rid of the substring didnt help, here is the execution plan: seems like the has match ona left outer join is costing me 67%, not sure where to pin that down to, here is what i ran to get the plan:WITH MarksRnkCnt AS( SELECT ltrim(isnull(pmname.Firstname,'') + ' ' + isnull(pmname.Lastname,'')) as pm,ap.accountnumber as accountnumber, ap.[1YReturn],ap.reportdt, RANK() OVER(ORDER BY ap.[1yreturn]) AS rnk, COUNT(*) OVER() AS cnt FROM abacuspmperformance ap left join account_detail ad on ap.accountnumber = ad.account_number left join dbo.InvestmentOfficers pmname on ad.portfolio_manager = pmname.initials whereap.[1yreturn] is not nulland ap.[1yreturn] <> '1' and ap.perfclass = 'totl' and reportdt = '08/17/2009' and ltrim(isnull(pmname.Firstname,'') + ' ' + isnull(pmname.Lastname,'')) = 'Gerry Ciszewski')SELECT pm,accountnumber,round([1yReturn],2) as y1Return, cast(round(1.*(rnk-1)/(cnt-1),2) as decimal(18,2)) AS percentilerankFROM MarksRnkCnt where (abs((1.*(rnk-1)/(cnt-1)) -(0.05))) = (select min(abs( (1.*(rnk-1)/(cnt-1)) - 0.05)) from MarksRnkCnt)or (abs((1.*(rnk-1)/(cnt-1)) -(0.95))) = (select min(abs( (1.*(rnk-1)/(cnt-1)) - 0.95)) from MarksRnkCnt) order by percentilerank asc |
|
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2009-08-19 : 17:32:40
|
bigger pic |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-08-19 : 17:34:03
|
| Your Execution plan is not clear. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-08-19 : 17:35:25
|
Don't think you answered these questions:quote:
about how many rows are returned in your CTE and how many are being filtered by the final WHERE clause?What indexes are available and can you post the execution plan
For the execution plan, we obviously can't read what you posted so use this to get the text version:set showplan_text onthen run the statement. set showplan_text offBe One with the OptimizerTG |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-08-19 : 17:40:32
|
| [code]Set statistics profile onYour QuerySet statistics profile off[/code] |
|
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2009-08-19 : 22:39:03
|
Sorry about that:StmtText----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------WITH MarksRnkCnt AS( SELECT ltrim(isnull(pmname.Firstname,'') + ' ' + isnull(pmname.Lastname,'')) as pm,ap.accountnumber as accountnumber, ap.[1YReturn],ap.reportdt, RANK() OVER(ORDER BY ap.[1yreturn]) AS rnk, COUNT(*) OVER() AS cnt FROM (1 row(s) affected)StmtText---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |--Parallelism(Gather Streams, ORDER BY:([Expr1038] ASC)) |--Sort(ORDER BY:([Expr1038] ASC)) |--Compute Scalar(DEFINE:([Expr1038]=CONVERT(decimal(18,2),round(((1.)*CONVERT_IMPLICIT(numeric(19,0),[Expr1008]-(1),0))/CONVERT_IMPLICIT(numeric(10,0),[Expr1009]-(1),0),(2)),0))) |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Expr1008], [Expr1009])) |--Sort(ORDER BY:([Expr1008] ASC, [Expr1009] ASC)) | |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:([Expr1008], [Expr1009])) | |--Nested Loops(Inner Join) | |--Table Spool | | |--Segment | | |--Compute Scalar(DEFINE:([Expr1010]=ltrim((isnull([IMCWharehouse].[dbo].[InvestmentOfficers].[FirstName] as [pmname].[FirstName],N'')+N' ')+isnull([IMCWharehouse].[dbo].[InvestmentOfficers].[LastName] as [pmn | | |--Sequence Project(DEFINE:([Expr1008]=rank)) | | |--Segment | | |--Segment | | |--Parallelism(Gather Streams, ORDER BY:([ap].[1YReturn] ASC)) | | |--Sort(ORDER BY:([ap].[1YReturn] ASC)) | | |--Filter(WHERE:([Expr1039]=N'Gerry Ciszewski')) | | |--Compute Scalar(DEFINE:([Expr1039]=ltrim((isnull([IMCWharehouse].[dbo].[InvestmentOfficers].[FirstName] as [pmname].[FirstName],N'')+N' ')+isnull([IMCWharehouse].[dbo].[Inv | | |--Hash Match(Right Outer Join, HASH:([pmname].[Initials])=([Expr1040]), RESIDUAL:([Expr1040]=[IMCWharehouse].[dbo].[InvestmentOfficers].[Initials] as [pmname].[Initials | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([pmname].[Initials])) | | | |--Index Scan(OBJECT:([IMCWharehouse].[dbo].[InvestmentOfficers].[_dta_index_InvestmentOfficers_5_11863109__K2_K1_3] AS [pmname])) | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Expr1040])) | | |--Compute Scalar(DEFINE:([Expr1040]=CONVERT_IMPLICIT(nvarchar(5),[IMCWharehouse].[dbo].[account_detail].[portfolio_manager] as [ad].[portfolio_manager],0))) | | |--Hash Match(Left Outer Join, HASH:([ap].[AccountNumber])=([Expr1041]), RESIDUAL:([IMCWharehouse].[dbo].[AbacusPMPerformance].[AccountNumber] as [ap].[Ac | | |--Bitmap(HASH:([ap].[AccountNumber]), DEFINE:([Bitmap1053])) | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([ap].[AccountNumber])) | | | |--Index Seek(OBJECT:([IMCWharehouse].[dbo].[AbacusPMPerformance].[_dta_index_AbacusPMPerformance_5_2111346586__K1_K5_K11_K3] AS [ap]), SEE | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Expr1041]), WHERE:(PROBE([Bitmap1053])=TRUE)) | | |--Compute Scalar(DEFINE:([Expr1041]=CONVERT_IMPLICIT(nvarchar(10),[IMCWharehouse].[dbo].[account_detail].[account_number] as [ad].[account_numb | | |--Index Scan(OBJECT:([IMCWharehouse].[dbo].[account_detail].[_dta_index_account_detail_5_1103342995__K2_K8] AS [ad])) | |--Nested Loops(Inner Join, WHERE:((1))) | |--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[Expr1057],0))) | | |--Stream Aggregate(DEFINE:([Expr1057]=Count(*))) | | |--Table Spool | |--Table Spool |--Row Count Spool |--Concatenation |--Filter(WHERE:([Expr1022]=abs(((1.)*CONVERT_IMPLICIT(numeric(19,0),[Expr1008]-(1),0))/CONVERT_IMPLICIT(numeric(10,0),[Expr1009]-(1),0)-(0.05)))) | |--Stream Aggregate(DEFINE:([Expr1022]=MIN(abs(((1.)*CONVERT_IMPLICIT(numeric(19,0),[Expr1019]-(1),0))/CONVERT_IMPLICIT(numeric(10,0),[Expr1020]-(1),0)-(0.05))))) | |--Nested Loops(Inner Join) | |--Table Spool | | |--Segment | | |--Sequence Project(DEFINE:([Expr1019]=rank)) | | |--Segment | | |--Segment | | |--Sort(ORDER BY:([ap].[1YReturn] ASC)) | | |--Filter(WHERE:([Expr1042]=N'Gerry Ciszewski')) | | |--Compute Scalar(DEFINE:([Expr1042]=ltrim((isnull([IMCWharehouse].[dbo].[InvestmentOfficers].[FirstName] as [pmname].[FirstName],N'')+N' ')+isnull([IMCWharehouse].[dbo].[Inv | | |--Hash Match(Right Outer Join, HASH:([pmname].[Initials])=([Expr1043]), RESIDUAL:([Expr1043]=[IMCWharehouse].[dbo].[InvestmentOfficers].[Initials] as [pmname].[Initials | | |--Table Scan(OBJECT:([IMCWharehouse].[dbo].[InvestmentOfficers] AS [pmname])) | | |--Compute Scalar(DEFINE:([Expr1043]=CONVERT_IMPLICIT(nvarchar(5),[IMCWharehouse].[dbo].[account_detail].[portfolio_manager] as [ad].[portfolio_manager],0))) | | |--Hash Match(Left Outer Join, HASH:([ap].[AccountNumber])=([Expr1044]), RESIDUAL:([IMCWharehouse].[dbo].[AbacusPMPerformance].[AccountNumber] as [ap].[Account | | |--Index Seek(OBJECT:([IMCWharehouse].[dbo].[AbacusPMPerformance].[_dta_index_AbacusPMPerformance_5_2111346586__K1_K5_K11_K3] AS [ap]), SEEK:([ap].[Report | | |--Compute Scalar(DEFINE:([Expr1044]=CONVERT_IMPLICIT(nvarchar(10),[IMCWharehouse].[dbo].[account_detail].[account_number] as [ad].[account_number],0))) | | |--Index Scan(OBJECT:([IMCWharehouse].[dbo].[account_detail].[_dta_index_account_detail_5_1103342995__K2_K8] AS [ad])) | |--Nested Loops(Inner Join, WHERE:((1))) | |--Compute Scalar(DEFINE:([Expr1020]=CONVERT_IMPLICIT(int,[Expr1061],0))) | | |--Stream Aggregate(DEFINE:([Expr1061]=Count(*))) | | |--Table Spool | |--Table Spool |--Filter(WHERE:([Expr1035]=abs(((1.)*CONVERT_IMPLICIT(numeric(19,0),[Expr1008]-(1),0))/CONVERT_IMPLICIT(numeric(10,0),[Expr1009]-(1),0)-(0.95)))) |--Stream Aggregate(DEFINE:([Expr1035]=MIN(abs(((1.)*CONVERT_IMPLICIT(numeric(19,0),[Expr1032]-(1),0))/CONVERT_IMPLICIT(numeric(10,0),[Expr1033]-(1),0)-(0.95))))) |--Nested Loops(Inner Join) |--Table Spool | |--Segment | |--Sequence Project(DEFINE:([Expr1032]=rank)) | |--Segment | |--Segment | |--Sort(ORDER BY:([ap].[1YReturn] ASC)) | |--Filter(WHERE:([Expr1045]=N'Gerry Ciszewski')) | |--Compute Scalar(DEFINE:([Expr1045]=ltrim((isnull([IMCWharehouse].[dbo].[InvestmentOfficers].[FirstName] as [pmname].[FirstName],N'')+N' ')+isnull([IMCWharehouse].[dbo].[Inv | |--Hash Match(Right Outer Join, HASH:([pmname].[Initials])=([Expr1046]), RESIDUAL:([Expr1046]=[IMCWharehouse].[dbo].[InvestmentOfficers].[Initials] as [pmname].[Initials | |--Table Scan(OBJECT:([IMCWharehouse].[dbo].[InvestmentOfficers] AS [pmname])) | |--Compute Scalar(DEFINE:([Expr1046]=CONVERT_IMPLICIT(nvarchar(5),[IMCWharehouse].[dbo].[account_detail].[portfolio_manager] as [ad].[portfolio_manager],0))) | |--Hash Match(Left Outer Join, HASH:([ap].[AccountNumber])=([Expr1047]), RESIDUAL:([IMCWharehouse].[dbo].[AbacusPMPerformance].[AccountNumber] as [ap].[Account | |--Index Seek(OBJECT:([IMCWharehouse].[dbo].[AbacusPMPerformance].[_dta_index_AbacusPMPerformance_5_2111346586__K1_K5_K11_K3] AS [ap]), SEEK:([ap].[Report | |--Compute Scalar(DEFINE:([Expr1047]=CONVERT_IMPLICIT(nvarchar(10),[IMCWharehouse].[dbo].[account_detail].[account_number] as [ad].[account_number],0))) | |--Index Scan(OBJECT:([IMCWharehouse].[dbo].[account_detail].[_dta_index_account_detail_5_1103342995__K2_K8] AS [ad])) |--Nested Loops(Inner Join, WHERE:((1))) |--Compute Scalar(DEFINE:([Expr1033]=CONVERT_IMPLICIT(int,[Expr1065],0))) | |--Stream Aggregate(DEFINE:([Expr1065]=Count(*))) | |--Table Spool |--Table Spool(82 row(s) affected) Let me get those other questions in one sec |
|
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2009-08-19 : 22:45:52
|
| CTE Returns 155 rows, after the where clause just 2 |
|
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2009-08-20 : 11:49:04
|
I actually changed some things around and now performance is much better:Basically just replaced cte with a temp tableUSE [IMCWharehouse]GO/****** Object: StoredProcedure [dbo].[AbacusPerformanceOverviewPMPercetile1Y] Script Date: 08/20/2009 10:48:33 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO----This will create a SP that will show you all the service types and the total number of accounts----and the tototal sum of those accounts market valuesALTER proc [dbo].[AbacusPerformanceOverviewPMPercetile1Y] @pm nvarchar(50), @date smalldatetimeASSELECT ltrim(isnull(pmname.Firstname,'') + ' ' + isnull(pmname.Lastname,'')) as pm, ap.accountnumber as accountnumber, ap.[1YReturn], ap.reportdt, RANK() OVER(ORDER BY ap.[1yreturn]) AS rnk, COUNT(*) OVER() AS cntinto #MarksRnkCntFROM abacuspmperformance ap -- select * from abacuspmperformance left join account_detail ad on ap.accountnumber = ad.account_number left join dbo.InvestmentOfficers pmname on ad.portfolio_manager = pmname.initials -- select ltrim(isnull(Firstname,'') + ' ' + isnull(Lastname,'')),* from InvestmentOfficerswhere ap.[1yreturn] is not null and ap.[1yreturn] <> '1' and ap.perfclass = 'totl' and reportdt = @date and ltrim(isnull(pmname.Firstname,'') + ' ' + isnull(pmname.Lastname,'')) = @pmcreate clustered index px_marksrnk on #MarksRnkCnt(accountnumber)create index ix_marksrnk on #MarksRnkCnt(rnk,cnt)--------/**WITH MarksRnkCnt AS( SELECT ltrim(isnull(pmname.Firstname,'') + ' ' + isnull(pmname.Lastname,'')) as pm,ap.accountnumber as accountnumber, ap.[1YReturn],ap.reportdt, RANK() OVER(ORDER BY ap.[1yreturn]) AS rnk, COUNT(*) OVER() AS cnt FROM abacuspmperformance ap left join account_detail ad on ap.accountnumber = ad.account_number left join dbo.InvestmentOfficers pmname on ad.portfolio_manager = pmname.initials whereap.[1yreturn] is not nulland ap.[1yreturn] <> '1' and ap.perfclass = 'totl' and reportdt = @date and ltrim(isnull(pmname.Firstname,'') + ' ' + isnull(pmname.Lastname,'')) = @pm)**/SELECT pm,accountnumber,round([1yReturn],2) as y1Return, cast(round(1.*(rnk-1)/(cnt-1),2) as decimal(18,2)) AS percentilerankFROM #MarksRnkCntwhere (abs((1.*(rnk-1)/(cnt-1)) -(0.05))) = ( select min(abs( (1.*(rnk-1)/(cnt-1)) - 0.05)) from #MarksRnkCnt)or (abs((1.*(rnk-1)/(cnt-1)) -(0.95))) = ( select min(abs( (1.*(rnk-1)/(cnt-1)) - 0.95)) from #MarksRnkCnt) order by percentilerank asc |
|
|
|
|
|
|
|
|
SP Performance...