Issue with a complex select statement

Question

Post reply

Issue with a complex select statement

Christian Benvenuto-248082

SSC Enthusiast

Points: 163
More actions
June 5, 2006 at 10:32 am

#153018

OK, I'm having an issue with a complex select statement that was captured from a BO report. Like I said, it's complex. What's strange is this report runs on about 20 prod and test servers in about 3 minutes. I'm having issues with one of my prod and it's test server, it runs in 3 hrs 30 min. Towards the end of the select, you see this statement:

    and ( CASE

WHEN substring('Gross      ',1,6) = 'Gross ' THEN ( dbo.AccountMetrics.FinancialBalancePlusInterestDue )

WHEN substring('Gross      ',1,6) = 'NPS   ' THEN ( dbo.AccountMetrics.FinancialBalanceNetofPartSoldPlusFinancialBalanceInterestDueNetofPartSold )

WHEN substring('Gross      ',1,6) = 'BV    ' THEN ( dbo.AccountMetrics.BookValue )

WHEN substring('Gross      ',1,6) = 'BV NPS' THEN ( dbo.AccountMetrics.BookValueNetofPartSold )

END ) > 0

Notice the tables aftre the then, do not use the alias provided earlier (AM).

Which is strange, because if I change it from dbo.AccountMetrics.tablename to AM.tablename it runs in 38 seconds. Note: changing this works for the statement that will never be true ie Gross = BV. not Gross = Gross. Also note, when I comment out all 3 of the case statements that will never be true ie Gross = BV, it returns the data in 40 seconds.

OK

Here's the actual statement that is running:

SELECT

dbo.ReportControl.OrderLinkCode,

dbo.Dates.Date,

dbo.Association.BranchNumber,

dbo.Products.CommitmentTypeCode,

dbo.Products.CommitmentTypeDescription,

BORptLbl_CommitmentTyp.ReportLabelName,

left(dbo.CreditClass.SplitClassReportCode+ SPACE(5),4),

dbo.CreditClass.SplitClassDescription,

BORptLbl_SplitClass.ReportLabelName,

   sum(dbo.AccountMetrics.BookValueNetofPartSold),

dbo.ReportControl.ElementUsageCode,

CASE

WHEN substring('Gross      ',1,6) = 'Gross ' THEN ( SUM(dbo.AccountMetrics.FinancialBalancePlusInterestDue) )

WHEN substring('Gross      ',1,6) = 'NPS   ' THEN ( SUM(dbo.AccountMetrics.FinancialBalanceNetofPartSoldPlusFinancialBalanceInterestDueNetofPartSold) )

WHEN substring('Gross      ',1,6) = 'BV    ' THEN ( sum(dbo.AccountMetrics.BookValue) )

WHEN substring('Gross      ',1,6) = 'BV NPS' THEN ( sum(dbo.AccountMetrics.BookValueNetofPartSold) )

END

FROM

dbo.ReportControl,

dbo.Dates,

dbo.Association,

dbo.Products,

dbo.tlkpBOReportLabel BORptLbl_CommitmentTyp,

dbo.CreditClass,

dbo.tlkpBOReportLabel BORptLbl_SplitClass,

dbo.AccountMetrics

WHERE

( dbo.Association.AssociationIDInternal=dbo.AccountMetrics.AssociationIDInternal and dbo.Association.DateIDInternal=dbo.AccountMetrics.DateIDInternal )

AND ( dbo.AccountMetrics.CreditClassIDInternal=dbo.CreditClass.CreditClassIDInternal )

AND ( dbo.AccountMetrics.DateIDInternal=dbo.Dates.DateIDInternal )

AND ( dbo.Products.ProductIDInternal=dbo.AccountMetrics.ProductIDInternal )

AND ( dbo.ReportControl.DateIDInternal=dbo.Dates.DateIDInternal )

AND ( dbo.AccountMetrics.CreditClassRecordType IN (1,2) )

AND ( dbo.CreditClass.SplitClass=BORptLbl_SplitClass.PartnerCode and dbo.CreditClass.SplitClassDescription=BORptLbl_SplitClass.PartnerDescription AND BORptLbl_SplitClass.ReportFilterName = 'SplitClass_Fltr' )

AND ( dbo.Products.CommitmentTypeCode=BORptLbl_CommitmentTyp.PartnerCode and dbo.Products.CommitmentTypeDescription=BORptLbl_CommitmentTyp.PartnerDescription AND BORptLbl_CommitmentTyp.ReportFilterName = 'CommitmentTypeCode_Fltr' )

AND (

dbo.ReportControl.RptID = 2

AND dbo.ReportControl.RptSection = 'C'

AND dbo.AccountMetrics.MonthEndFlag = 'Y'

AND CASE

WHEN substring('Gross      ',1,6) = 'Gross ' THEN ( dbo.AccountMetrics.FinancialBalancePlusInterestDue )

WHEN substring('Gross      ',1,6) = 'NPS   ' THEN ( dbo.AccountMetrics.FinancialBalanceNetofPartSoldPlusFinancialBalanceInterestDueNetofPartSold )

WHEN substring('Gross      ',1,6) = 'BV    ' THEN ( dbo.AccountMetrics.BookValue )

WHEN substring('Gross      ',1,6) = 'BV NPS' THEN ( dbo.AccountMetrics.BookValueNetofPartSold )

END > 0

AND ( dbo.AccountMetrics.BranchNumber IN (SELECT DISTINCT AM.BranchNumber

  FROM   dbo.Dates D,dbo.AccountMetrics AM,dbo.Employee EM,dbo.Account AC,dbo.Association A

  WHERE

    dbo.Dates.DateIDInternal=D.DateIDInternal

    AND AM.DateIDInternal=D.DateIDInternal

    AND AC.DateIDInternal=D.DateIDInternal

    AND AM.DateIDInternal=AC.DateIDInternal

    AND AM.AccountIDInternal=AC.AccountIDInternal

    and A.AssociationIDInternal=AM.AssociationIDInternal

    and A.DateIDInternal=AM.DateIDInternal

    and AM.MonthEndFlag = 'Y'

    and ( CASE

WHEN substring('Gross      ',1,6) = 'Gross ' THEN ( dbo.AccountMetrics.FinancialBalancePlusInterestDue )

WHEN substring('Gross      ',1,6) = 'NPS   ' THEN ( dbo.AccountMetrics.FinancialBalanceNetofPartSoldPlusFinancialBalanceInterestDueNetofPartSold )

WHEN substring('Gross      ',1,6) = 'BV    ' THEN ( dbo.AccountMetrics.BookValue )

WHEN substring('Gross      ',1,6) = 'BV NPS' THEN ( dbo.AccountMetrics.BookValueNetofPartSold )

END ) > 0

    and AM.EmployeeIDInternal=EM.EmployeeIDInternal

    AND dbo.AccountMetrics.DateIDInternal=AM.DateIDInternal

    AND dbo.AccountMetrics.AssociationNumber=AM.AssociationNumber

    AND A.AssociationBranch IN ('028001       ' ,'028002       ' ,'028003       ' ,'028006       ' ,'028014       ' , '028080       ')

    AND EM.EmployeeID ='00503') )

)

GROUP BY

dbo.ReportControl.OrderLinkCode,

dbo.Dates.Date,

dbo.Association.BranchNumber,

dbo.Products.CommitmentTypeCode,

dbo.Products.CommitmentTypeDescription,

BORptLbl_CommitmentTyp.ReportLabelName,

left(dbo.CreditClass.SplitClassReportCode+ SPACE(5),4),

dbo.CreditClass.SplitClassDescription,

BORptLbl_SplitClass.ReportLabelName,

dbo.ReportControl.ElementUsageCode

Viewing 9 posts - 1 through 8 (of 8 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

noeld

SSC Guru

Points: 96590

June 5, 2006 at 10:50 am

#641788

Is it safe to assume that when you said:

dbo.AccountMetrics.tablename you actually meant: dbo.AccountMetrics.Columnname

About the difference in speed you will need to check your indexes usage, the statistics and the load on the "slow" server.

Cheers,

* Noel

Christian Benvenuto-248082 SSC Enthusiast Points: 163 More actions · Answer 2

Christian Benvenuto-248082

SSC Enthusiast

Points: 163

June 5, 2006 at 11:07 am

#641793

Yeah, sorry, column name. INDEXES are identical, the stats have been updated on both servers nightly. I'm concerned about the fully qualified name vs alais. Why would this make a difference, when the statement getting changed, would never be true anyway?

brendt hess Hall of Fame Points: 3256 More actions · Answer 3

I would look at RAM, RAM usage, Disk space free, and disk utilization. If this server and its test server have less RAM available for SQL Server, then you can have radical slowdowns in processes *if* the process needs more memory space than is available. Also, if these have badly fragmented disks, or disks with little free space (which hinders many defragmenters), the problem could be a fragmented swap file.

Christian Benvenuto-248082 SSC Enthusiast Points: 163 More actions · Answer 4

It doesn't seem to be server related, hardware. I can restore a database from the servers that are running OK on to the test server and it runs fine and vice versa. It still runs slow on the test boxes that run fine. What kind of database options would effect the alais? Like I said in the begining, when I change the tablename to the alais, it runs fine. I just changed one of the query's that ran OK to an alias and now that query runs slow. this doesn't make sense.

noeld SSC Guru Points: 96590 More actions · Answer 5

Christian,

IF you changed dbo.AccountMetrics to AM in the "IN" subquery you are creating a totally different query because there is an external dbo.AccountMetrics outside of the IN which is what the code is refering to!

If what you want is maybe that the change that you proposed effectively is removing the need on the IN subquery to know about the "external" vaules.

Hope is clear,

* Noel

Christian Benvenuto-248082 SSC Enthusiast Points: 163 More actions · Answer 6

I was looking at that a little while ago. Your right, it is a different query. Anyway, why would changing the alais (commenting out the statement give the same results) make any difference in the query? Gross = Gross should be the only case true. Why when I comment out

WHEN substring('Gross ',1,6) = 'NPS ' THEN ( dbo.AccountMetrics.FinancialBalanceNetofPartSoldPlusFinancialBalanceInterestDueNetofPartSold )

WHEN substring('Gross ',1,6) = 'BV ' THEN ( dbo.AccountMetrics.BookValue )

WHEN substring('Gross ',1,6) = 'BV NPS'

The query runs in under 1 minute?

Christian Benvenuto-248082 SSC Enthusiast Points: 163 More actions · Answer 7

Christian Benvenuto-248082

SSC Enthusiast

Points: 163

June 5, 2006 at 1:24 pm

#641817

FYI, the case statement I refer to is the one in the IN statement.

Christian Benvenuto-248082 SSC Enthusiast Points: 163 More actions · Answer 8

Another thing, Text plans differ from database to database:

Here's the plan for the slow running query:

|--Compute Scalar(DEFINE[Expr1024]=If (substring('Gross ', 1, 6)='Gross ') then [Expr1021] else If (substring('Gross ', 1, 6)='NPS ') then [Expr1022] else If (substring('Gross ', 1, 6)='BV ') then [Expr1023] else If (substring('Gross ', 1, 6)='BV NPS') then [Expr1020] else NULL))

|--Stream Aggregate(GROUP BY[ReportControl].[OrderLinkCode], [Dates].[Date], [Association].[BranchNumber], [Products].[CommitmentTypeCode], [Products].[CommitmentTypeDescription], [BORptLbl_CommitmentTyp].[ReportLabelName], [Expr1019], [CreditClass].[SplitClassDescription], [BORptLbl_SplitClass].[ReportLabelName], [ReportControl].[ElementUsageCode]) DEFINE[Expr1020]=SUM([AccountMetrics].[BookValueNetofPartSold]), [Expr1021]=SUM([AccountMetrics].[FinancialBalancePlusInterestDue]), [Expr1022]=SUM([AccountMetrics].[FinancialBalanceNetofPartSoldPlusFinancialBalanceInterestDueNetofPartSold]), [Expr1023]=SUM([AccountMetrics].[BookValue])))

|--Sort(ORDER BY[ReportControl].[OrderLinkCode] ASC, [Dates].[Date] ASC, [Association].[BranchNumber] ASC, [Products].[CommitmentTypeCode] ASC, [Products].[CommitmentTypeDescription] ASC, [BORptLbl_CommitmentTyp].[ReportLabelName] ASC, [Expr1019] ASC, [CreditClass].[SplitClassDescription] ASC, [BORptLbl_SplitClass].[ReportLabelName] ASC, [ReportControl].[ElementUsageCode] ASC))

|--Compute Scalar(DEFINE[Expr1019]=substring([CreditClass].[SplitClassReportCode]+space(5), 1, 4)))

|--Nested Loops(Left Semi Join, OUTER REFERENCES[Dates].[DateIDInternal], [AccountMetrics].[AssociationNumber], [AccountMetrics].[BranchNumber], [AccountMetrics].[DateIDInternal]))

|--Hash Match(Inner Join, HASH[BORptLbl_CommitmentTyp].[PartnerCode], [BORptLbl_CommitmentTyp].[PartnerDescription])=([Products].[CommitmentTypeCode], [Products].[CommitmentTypeDescription]), RESIDUAL[Products].[CommitmentTypeCode]=[BORptLbl_CommitmentTyp].[PartnerCode] AND [Products].[CommitmentTypeDescription]=[BORptLbl_CommitmentTyp].[PartnerDescription]))

| |--Clustered Index Scan(OBJECT[DATAMART].[dbo].[tlkpBOReportLabel].[PK_tlkpBOReportLabel] AS [BORptLbl_CommitmentTyp]), WHERE[BORptLbl_CommitmentTyp].[ReportFilterName]='CommitmentTypeCode_Fltr'))

| |--Hash Match(Inner Join, HASH[BORptLbl_SplitClass].[PartnerCode], [BORptLbl_SplitClass].[PartnerDescription])=([CreditClass].[SplitClass], [CreditClass].[SplitClassDescription]), RESIDUAL[CreditClass].[SplitClass]=[BORptLbl_SplitClass].[PartnerCode] AND [CreditClass].[SplitClassDescription]=[BORptLbl_SplitClass].[PartnerDescription]))

| |--Clustered Index Scan(OBJECT[DATAMART].[dbo].[tlkpBOReportLabel].[PK_tlkpBOReportLabel] AS [BORptLbl_SplitClass]), WHERE[BORptLbl_SplitClass].[ReportFilterName]='SplitClass_Fltr'))

| |--Nested Loops(Inner Join, OUTER REFERENCES[AccountMetrics].[DateIDInternal]))

| |--Nested Loops(Inner Join, OUTER REFERENCES[AccountMetrics].[ProductIDInternal]))

| | |--Nested Loops(Inner Join, OUTER REFERENCES[AccountMetrics].[DateIDInternal], [AccountMetrics].[AssociationIDInternal]) WITH PREFETCH)

| | | |--Bookmark Lookup(BOOKMARK[Bmk1009]), OBJECT[DATAMART].[dbo].[CreditClass]))

| | | | |--Nested Loops(Inner Join, OUTER REFERENCES[AccountMetrics].[CreditClassIDInternal]) WITH PREFETCH)

| | | | |--Nested Loops(Inner Join, OUTER REFERENCES[ReportControl].[DateIDInternal]))

| | | | | |--Index Seek(OBJECT[DATAMART].[dbo].[ReportControl].[PK_ReportControl]), SEEK[ReportControl].[RptID]=2 AND [ReportControl].[RptSection]='C') ORDERED FORWARD)

| | | | | |--Filter(WHEREIf (substring('Gross ', 1, 6)='Gross ') then [AccountMetrics].[FinancialBalancePlusInterestDue] else If (substring('Gross ', 1, 6)='NPS ') then [AccountMetrics].[FinancialBalanceNetofPartSoldPlusFinancialBalanceInterestDueNetofPartSold] else If (substring('Gross ', 1, 6)='BV ') then [AccountMetrics].[BookValue] else If (substring('Gross ', 1, 6)='BV NPS') then [AccountMetrics].[BookValueNetofPartSold] else NULL>0.00))

| | | | | |--Clustered Index Seek(OBJECT[DATAMART].[dbo].[AccountMetrics].[IX_AccountMetrics_1]), SEEK[AccountMetrics].[DateIDInternal]=[ReportControl].[DateIDInternal] AND [AccountMetrics].[CreditClassRecordType]=1 OR [AccountMetrics].[DateIDInternal]=[ReportControl].[DateIDInternal] AND [AccountMetrics].[CreditClassRecordType]=2), WHEREIf (substring('Gross ', 1, 6)='Gross ') then [AccountMetrics].[FinancialBalancePlusInterestDue] else If (substring('Gross ', 1, 6)='NPS ') then [AccountMetrics].[FinancialBalanceNetofPartSoldPlusFinancialBalanceInterestDueNetofPartSold] else If (substring('Gross ', 1, 6)='BV ') then [AccountMetrics].[BookValue] else If (substring('Gross ', 1, 6)='BV NPS') then [AccountMetrics].[BookValueNetofPartSold] else NULL>0.00 AND [AccountMetrics].[MonthEndFlag]='Y') ORDERED FORWARD)

| | | | |--Index Seek(OBJECT[DATAMART].[dbo].[CreditClass].[IX_CreditClass_Key]), SEEK[CreditClass].[CreditClassIDInternal]=[AccountMetrics].[CreditClassIDInternal]) ORDERED FORWARD)

| | | |--Index Seek(OBJECT[DATAMART].[dbo].[Association].[IX_Association_Key]), SEEK[Association].[AssociationIDInternal]=[AccountMetrics].[AssociationIDInternal]), WHERE[AccountMetrics].[DateIDInternal]=[Association].[DateIDInternal]) ORDERED FORWARD)

| | |--Clustered Index Seek(OBJECT[DATAMART].[dbo].[Products].[IX_Products_1]), SEEK[Products].[ProductIDInternal]=[AccountMetrics].[ProductIDInternal]) ORDERED FORWARD)

| |--Index Seek(OBJECT[DATAMART].[dbo].[Dates].[PK_Dates]), SEEK[Dates].[DateIDInternal]=[AccountMetrics].[DateIDInternal]) ORDERED FORWARD)

|--Row Count Spool

|--Nested Loops(Inner Join)

|--Index Seek(OBJECT[DATAMART].[dbo].[Dates].[IX_Dates_DateIDInternal] AS [D]), SEEK[D].[DateIDInternal]=[Dates].[DateIDInternal]) ORDERED FORWARD)

|--Nested Loops(Inner Join, WHERE[AM].[EmployeeIDInternal]=.[EmployeeIDInternal]))

|--Nested Loops(Inner Join, OUTER REFERENCES[AM].[AccountIDInternal]) WITH PREFETCH)

| |--Filter(WHERE[AM].[BranchNumber]=[AccountMetrics].[BranchNumber] AND [AM].[MonthEndFlag]='Y'))

| | |--Bookmark Lookup(BOOKMARK[Bmk1015]), OBJECT[DATAMART].[dbo].[AccountMetrics] AS [AM]))

| | |--Nested Loops(Inner Join, OUTER REFERENCES[A].[AssociationIDInternal]))

| | |--Bookmark Lookup(BOOKMARK[Bmk1018]), OBJECT[DATAMART].[dbo].[Association] AS [A]))

| | | |--Index Seek(OBJECT[DATAMART].[dbo].[Association].[IX_Association_2] AS [A]), SEEK[A].[DateIDInternal]=[Dates].[DateIDInternal] AND [A].[AssociationBranch]='057001 ' OR [A].[DateIDInternal]=[Dates].[DateIDInternal] AND [A].[AssociationBranch]='057004 ' OR [A].[DateIDInternal]=[Dates].[DateIDInternal] AND [A].[AssociationBranch]='057009 ' OR [A].[DateIDInternal]=[Dates].[DateIDInternal] AND [A].[AssociationBranch]='057014 ' OR [A].[DateIDInternal]=[Dates].[DateIDInternal] AND [A].[AssociationBranch]='057015 ' OR [A].[DateIDInternal]=[Dates].[DateIDInternal] AND [A].[AssociationBranch]='057080 ') ORDERED FORWARD)

| | |--Index Seek(OBJECT[DATAMART].[dbo].[AccountMetrics].[IX_AccountMetrics_3] AS [AM]), SEEK[AM].[AssociationIDInternal]=[A].[AssociationIDInternal] AND [AM].[DateIDInternal]=[AccountMetrics].[DateIDInternal]), WHERE[AM].[AssociationNumber]=[AccountMetrics].[AssociationNumber]) ORDERED FORWARD)

| |--Index Seek(OBJECT[DATAMART].[dbo].[Account].[IX_Account_Key] AS [AC]), SEEK[AC].[AccountIDInternal]=[AM].[AccountIDInternal] AND [AC].[DateIDInternal]=[AccountMetrics].[DateIDInternal]), WHERE[AC].[DateIDInternal]=[Dates].[DateIDInternal]) ORDERED FORWARD)

|--Table Spool

|--Index Seek(OBJECT[DATAMART].[dbo].[Employee].[IX_Employee_EmployeeID] AS ), SEEK.[EmployeeID]='00669') ORDERED FORWARD)