I've restored a database by the standard menu (the backup was a .bak file).
It worked really well, but now I can't use the database, but there is a "(Restoring...)" standing to the right of the database-name in the sql server management studio. I thought it should work like this and therefore was sitting and waiting, but hous later the status didn't change.
What shell I do?
I've restored a database by the standard menu (the backup was a .bak file).
It worked really well, but now I can't use the database, but there is a "(Restoring...)" standing to the right of the database-name in the sql server management studio. I thought it should work like this and therefore was sitting and waiting, but hous later the status didn't change.
What shell I do?
Could some body in microsoft database team explain this behavior? Problem is predominant when cardinality of a column is very high and a where clause is specified on that column. Both use the same index.
select a12.DATE_INVOICE_ID DATE_INVOICE_ID,
a11.CUSTOMER_ID CUSTOMER_ID,
sum(a11.EXTENDED_PRICE) WJXBFS1,
sum(a11.TOTAL_COST) WJXBFS2,
(sum(a11.EXTENDED_PRICE) - sum(a11.TOTAL_COST)) WJXBFS3
from FACT_SALES_LINE a11
join RLTN_V_SL_INVOICE_YTD a12
on (a11.DATE_INVOICE_ID = a12.DATE_INVOICE_CYTD_ID)
join LKP_V_SL_EXPENSE_CODE a13
on (a11.EXPENSE_CODE_ID = a13.EXPENSE_CODE_ID)
join LKP_V_SL_LAST_STATUS a14
on (a11.LAST_STATUS_ID = a14.LAST_STATUS_ID)
join LKP_V_SL_NEXT_STATUS a15
on (a11.NEXT_STATUS_ID = a15.NEXT_STATUS_ID)
join LKP_V_SL_ORDER_TYPE a16
on (a11.ORDER_TYPE_ID = a16.ORDER_TYPE_ID)
where (a11.CUSTOMER_ID in (1523364, 1522717, 1523004, 1523728, 1523809, 1523012)
and a12.DATE_INVOICE_ID = 106365
and a16.ORDER_TYPE_SRCCD not in ( 'ST','SG','SI','SU','SK','DL','S1','YU')
and a11.BUSINESS_UNIT_ID in (461100, 461400, 461600)
and a13.EXPENSE_CODE_SRCCD <> 'LC'
and a15.NEXT_STATUS_SRCCD = '999'
and a14.LAST_STATUS_SRCCD in ( '620','914') )
group by a12.DATE_INVOICE_ID,
a11.CUSTOMER_ID
OPTION ( FORCE ORDER )
PLAN without force order:
select a12.DATE_INVOICE_ID DATE_INVOICE_ID, a11.CUSTOMER_ID CUSTOMER_ID, sum(a11.EXTENDED_PRICE) WJXBFS1, sum(a11.TOTAL_COST) WJXBFS2, (sum(a11.EXTENDED_PRICE) - sum(a11.TOTAL_COST)) WJXBFS3 from FACT_SALES_LINE a11 join RLTN_V_SL_INVOICE_YTD a12 on (a11.DATE_INVOICE_ID = a12.DATE_INVOICE_CYTD_ID) join LKP_V_SL_EXPENSE_CODE a13 on (a11.EXPENSE_CODE_ID = a13.EXPENSE_CODE_ID) join LKP_V_SL_LAST_STATUS a14 on (a11.LAST_STATUS_ID = a14.LAST_STATUS_ID) join LKP_V_SL_NEXT_STATUS a15 on (a11.NEXT_STATUS_ID = a15.NEXT_STATUS_ID) join LKP_V_SL_ORDER_TYPE a16 on (a11.ORDER_TYPE_ID = a16.ORDER_TYPE_ID) where (a11.CUSTOMER_ID in (1523364, 1522717, 1523004, 1523728, 1523809, 1523012) and a12.DATE_INVOICE_ID = 106365 and a16.ORDER_TYPE_SRCCD not in ( 'ST','SG','SI','SU','SK','DL','S1','YU') and a11.BUSINESS_UNIT_ID in (461100, 461400, 461600) and a13.EXPENSE_CODE_SRCCD <> 'LC' and a15.NEXT_STATUS_SRCCD = '999' and a14.LAST_STATUS_SRCCD in ( '620','914') ) group by a12.DATE_INVOICE_ID, a11.CUSTOMER_ID 1 1 0 NULL NULL 1 NULL 1.138269 NULL NULL NULL 2.716851 NULL NULL SELECT 0 NULL
|--Compute Scalar(DEFINE[Expr1022]=[Expr1020]-[Expr1021])) 1 2 1 Compute Scalar Compute Scalar DEFINE[Expr1022]=[Expr1020]-[Expr1021]) [Expr1022]=[Expr1020]-[Expr1021] 1.138269 0 1.138269E-07 66 2.716851 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1020], [Expr1021], [Expr1022] NULL PLAN_ROW 0 1
|--Compute Scalar(DEFINE[Expr1020]=CASE WHEN [Expr1023]=(0) THEN NULL ELSE [Expr1024] END, [Expr1021]=CASE WHEN [Expr1025]=(0) THEN NULL ELSE [Expr1026] END)) 1 3 2 Compute Scalar Compute Scalar DEFINE[Expr1020]=CASE WHEN [Expr1023]=(0) THEN NULL ELSE [Expr1024] END, [Expr1021]=CASE WHEN [Expr1025]=(0) THEN NULL ELSE [Expr1026] END) [Expr1020]=CASE WHEN [Expr1023]=(0) THEN NULL ELSE [Expr1024] END, [Expr1021]=CASE WHEN [Expr1025]=(0) THEN NULL ELSE [Expr1026] END 1.138269 0 1.273072E-06 49 2.716851 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1020], [Expr1021] NULL PLAN_ROW 0 1
|--Stream Aggregate(GROUP BY[a11].[CUSTOMER_ID]) DEFINE[Expr1023]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1024]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1025]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [Expr1026]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=ANY([JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]))) 1 4 3 Stream Aggregate Aggregate GROUP BY[a11].[CUSTOMER_ID]) [Expr1023]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1024]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1025]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [Expr1026]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=ANY([JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]) 1.138269 0 1.273072E-06 49 2.716851 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1023], [Expr1024], [Expr1025], [Expr1026] NULL PLAN_ROW 0 1
|--Sort(ORDER BY[a11].[CUSTOMER_ID] ASC)) 1 5 4 Sort Sort ORDER BY[a11].[CUSTOMER_ID] ASC) NULL 1.17323 0.01126126 0.0001004628 41 2.71685 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 0 1
|--Nested Loops(Inner Join, OUTER REFERENCES[a11].[DATE_INVOICE_ID])) 1 6 5 Nested Loops Inner Join OUTER REFERENCES[a11].[DATE_INVOICE_ID]) NULL 1.17323 0 8.671883E-06 41 2.705488 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 0 1
|--Nested Loops(Inner Join, OUTER REFERENCES[a11].[EXPENSE_CODE_ID])) 1 7 6 Nested Loops Inner Join OUTER REFERENCES[a11].[EXPENSE_CODE_ID]) NULL 2.074613 0 8.671883E-06 41 2.702026 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 0 1
| |--Nested Loops(Inner Join, OUTER REFERENCES[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID])) 1 8 7 Nested Loops Inner Join OUTER REFERENCES[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]) NULL 2.074613 0 8.671883E-06 45 2.697204 [a11].[CUSTOMER_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 0 1
| | |--Stream Aggregate(GROUP BY[a11].[FSL_SEQ_NO]) DEFINE[a11].[CUSTOMER_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[CUSTOMER_ID] as [a11].[CUSTOMER_ID]), [a11].[EXPENSE_CODE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), [a11].[TOTAL_COST]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [a11].[EXTENDED_PRICE]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [a11].[DATE_INVOICE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]), [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]))) 1 9 8 Stream Aggregate Aggregate GROUP BY[a11].[FSL_SEQ_NO]) [a11].[CUSTOMER_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[CUSTOMER_ID] as [a11].[CUSTOMER_ID]), [a11].[EXPENSE_CODE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), [a11].[TOTAL_COST]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [a11].[EXTENDED_PRICE]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [a11].[DATE_INVOICE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]), [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]) 2.074613 0 0.1950575 49 2.692634 [a11].[CUSTOMER_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID], [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 0 1
| | | |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[ORDER_TYPE_ID]) OPTIMIZED) 1 10 9 Nested Loops Inner Join OUTER REFERENCES[a11].[ORDER_TYPE_ID]) OPTIMIZED NULL 390113 0 1.630672 53 2.497577 [a11].[FSL_SEQ_NO], [a11].[CUSTOMER_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID], [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 0 1
| | | |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[LAST_STATUS_ID])) 1 12 10 Nested Loops Inner Join OUTER REFERENCES[a11].[LAST_STATUS_ID]) NULL 2.074613 0 9.189784E-06 53 0.0406176 [a11].[FSL_SEQ_NO], [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 0 1
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[NEXT_STATUS_ID])) 1 13 12 Nested Loops Inner Join OUTER REFERENCES[a11].[NEXT_STATUS_ID]) NULL 2.198513 0 0.0001337915 57 0.03713583 [a11].[FSL_SEQ_NO], [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[LAST_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 0 1
| | | | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_06] AS [a11]), SEEK[a11].[CUSTOMER_ID]=(1522717) OR [a11].[CUSTOMER_ID]=(1523004) OR [a11].[CUSTOMER_ID]=(1523012) OR [a11].[CUSTOMER_ID]=(1523364) OR [a11].[CUSTOMER_ID]=(1523728) OR [a11].[CUSTOMER_ID]=(1523809)), WHERE[JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461100) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461400) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461600)) ORDERED FORWARD) 1 14 13 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_06] AS [a11]), SEEK[a11].[CUSTOMER_ID]=(1522717) OR [a11].[CUSTOMER_ID]=(1523004) OR [a11].[CUSTOMER_ID]=(1523012) OR [a11].[CUSTOMER_ID]=(1523364) OR [a11].[CUSTOMER_ID]=(1523728) OR [a11].[CUSTOMER_ID]=(1523809)), WHERE[JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461100) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461400) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461600)) ORDERED FORWARD [a11].[FSL_SEQ_NO], [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[BUSINESS_UNIT_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] 32.00753 0.0268287 0.00104035 66 0.02786905 [a11].[FSL_SEQ_NO], [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[BUSINESS_UNIT_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 0 1
| | | | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[LU_NEXT_STATUS].[IX_LU_NEXT_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_SRCCD]=N'999' AND [JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[NEXT_STATUS_ID] as [a11].[NEXT_STATUS_ID]) ORDERED FORWARD) 1 15 13 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[LU_NEXT_STATUS].[IX_LU_NEXT_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_SRCCD]=N'999' AND [JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[NEXT_STATUS_ID] as [a11].[NEXT_STATUS_ID]) ORDERED FORWARD NULL 1 0.003125 0.0001581 9 0.008185391 NULL NULL PLAN_ROW 0 32.00753
| | | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[LU_LAST_STATUS].[IX_LU_LAST_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'620' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID] OR [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'914' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID]) ORDERED FORWARD) 1 16 12 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[LU_LAST_STATUS].[IX_LU_LAST_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'620' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID] OR [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'914' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID]) ORDERED FORWARD NULL 1 0.003125 0.0001581 9 0.003472585 NULL NULL PLAN_ROW 0 2.198513
| | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_01]), SEEK[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] as [a11].[ORDER_TYPE_ID]) ORDERED FORWARD) 1 17 10 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_01]), SEEK[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] as [a11].[ORDER_TYPE_ID]) ORDERED FORWARD [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] 188041.3 0.2616435 0.2070025 11 0.8262868 [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 0 2.074613
| | |--Clustered Index Seek(OBJECT[JDE_DATA].[dbo].[LU_ORDER_TYPE].[PK__LU_ORDER_TYPE__265B8A40] AS ), SEEK.[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), WHERE[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'DL' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'S1' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SG' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SI' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SK' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'ST' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SU' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'YU') ORDERED FORWARD) 1 31 8 Clustered Index Seek Clustered Index Seek OBJECT[JDE_DATA].[dbo].[LU_ORDER_TYPE].[PK__LU_ORDER_TYPE__265B8A40] AS ), SEEK.[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), WHERE[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'DL' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'S1' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SG' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SI' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SK' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'ST' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SU' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'YU') ORDERED FORWARD .[ORDER_TYPE_SRCCD] 1 0.003125 0.0001581 14 0.004555401 .[ORDER_TYPE_SRCCD] NULL PLAN_ROW 0 2.074613
| |--Clustered Index Seek(OBJECT[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[PK__LU_EXPENSE_CODE__2843D2B2]), SEEK[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), WHERE[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD]<>N'LC') ORDERED FORWARD) 1 32 7 Clustered Index Seek Clustered Index Seek OBJECT[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[PK__LU_EXPENSE_CODE__2843D2B2]), SEEK[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), WHERE[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD]<>N'LC') ORDERED FORWARD [JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD] 1 0.003125 0.0001581 16 0.004812614 [JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD] NULL PLAN_ROW 0 2.074613
|--Index Seek(OBJECT[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[IX_REL_CALENDAR_YEAR_TO_DATE_01]), SEEK[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=(106365) AND [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID_CYTD]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]) ORDERED FORWARD) 1 33 6 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[IX_REL_CALENDAR_YEAR_TO_DATE_01]), SEEK[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=(106365) AND [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID_CYTD]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]) ORDERED FORWARD [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] 1 0.003125 0.0001581 11 0.003452996 [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 0 2.074613
PLAN WITH FORCE ORDER:
select a12.DATE_INVOICE_ID DATE_INVOICE_ID, a11.CUSTOMER_ID CUSTOMER_ID, sum(a11.EXTENDED_PRICE) WJXBFS1, sum(a11.TOTAL_COST) WJXBFS2, (sum(a11.EXTENDED_PRICE) - sum(a11.TOTAL_COST)) WJXBFS3 from FACT_SALES_LINE a11 join RLTN_V_SL_INVOICE_YTD a12 on (a11.DATE_INVOICE_ID = a12.DATE_INVOICE_CYTD_ID) join LKP_V_SL_EXPENSE_CODE a13 on (a11.EXPENSE_CODE_ID = a13.EXPENSE_CODE_ID) join LKP_V_SL_LAST_STATUS a14 on (a11.LAST_STATUS_ID = a14.LAST_STATUS_ID) join LKP_V_SL_NEXT_STATUS a15 on (a11.NEXT_STATUS_ID = a15.NEXT_STATUS_ID) join LKP_V_SL_ORDER_TYPE a16 on (a11.ORDER_TYPE_ID = a16.ORDER_TYPE_ID) where (a11.CUSTOMER_ID in (1523364, 1522717, 1523004, 1523728, 1523809, 1523012) and a12.DATE_INVOICE_ID = 106365 and a16.ORDER_TYPE_SRCCD not in ( 'ST','SG','SI','SU','SK','DL','S1','YU') and a11.BUSINESS_UNIT_ID in (461100, 461400, 461600) and a13.EXPENSE_CODE_SRCCD <> 'LC' and a15.NEXT_STATUS_SRCCD = '999' and a14.LAST_STATUS_SRCCD in ( '620','914') ) group by a12.DATE_INVOICE_ID, a11.CUSTOMER_ID OPTION ( FORCE ORDER ) 1 1 0 NULL NULL 1 NULL 1.08425 NULL NULL NULL 9.249098 NULL NULL SELECT 0 NULL
|--Compute Scalar(DEFINE[Expr1022]=[Expr1020]-[Expr1021])) 1 2 1 Compute Scalar Compute Scalar DEFINE[Expr1022]=[Expr1020]-[Expr1021]) [Expr1022]=[Expr1020]-[Expr1021] 1.08425 0 1.08425E-07 66 9.249098 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1020], [Expr1021], [Expr1022] NULL PLAN_ROW 0 1
|--Compute Scalar(DEFINE[Expr1020]=CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1036] END, [Expr1021]=CASE WHEN [Expr1037]=(0) THEN NULL ELSE [Expr1038] END)) 1 3 2 Compute Scalar Compute Scalar DEFINE[Expr1020]=CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1036] END, [Expr1021]=CASE WHEN [Expr1037]=(0) THEN NULL ELSE [Expr1038] END) [Expr1020]=CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1036] END, [Expr1021]=CASE WHEN [Expr1037]=(0) THEN NULL ELSE [Expr1038] END 1.08425 0 1.204394E-06 49 9.249098 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1020], [Expr1021] NULL PLAN_ROW 0 1
|--Stream Aggregate(GROUP BY[a11].[CUSTOMER_ID]) DEFINE[Expr1035]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1036]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1037]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [Expr1038]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=ANY([JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]))) 1 4 3 Stream Aggregate Aggregate GROUP BY[a11].[CUSTOMER_ID]) [Expr1035]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1036]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1037]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [Expr1038]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=ANY([JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]) 1.08425 0 1.204394E-06 49 9.249098 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1035], [Expr1036], [Expr1037], [Expr1038] NULL PLAN_ROW 0 1
|--Sort(ORDER BY[a11].[CUSTOMER_ID] ASC)) 1 5 4 Sort Sort ORDER BY[a11].[CUSTOMER_ID] ASC) NULL 1.103783 0.01126126 0.0001002863 41 9.249096 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 0 1
|--Parallelism(Gather Streams) 1 6 5 Parallelism Gather Streams NULL NULL 1.103783 0 0.02850539 41 9.237735 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
|--Merge Join(Inner Join, MANY-TO-MANY MERGE[a11].[ORDER_TYPE_ID])=(.[ORDER_TYPE_ID]), RESIDUAL[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_ID] as .[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] as [a11].[ORDER_TYPE_ID])) 1 7 6 Merge Join Inner Join MANY-TO-MANY MERGE[a11].[ORDER_TYPE_ID])=(.[ORDER_TYPE_ID]), RESIDUAL[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_ID] as .[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] as [a11].[ORDER_TYPE_ID]) NULL 1.103783 0.0004695 0.001520579 41 9.209229 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
|--Sort(ORDER BY[a11].[ORDER_TYPE_ID] ASC)) 1 8 7 Sort Sort ORDER BY[a11].[ORDER_TYPE_ID] ASC) NULL 1.103783 0.002815315 2.507257E-05 45 0.09645625 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| |--Bitmap(HASH[a11].[ORDER_TYPE_ID]), DEFINE[Bitmap1034])) 1 9 8 Bitmap Bitmap Create HASH[a11].[ORDER_TYPE_ID]) [Bitmap1034] 1.103783 0 0.02850667 45 0.09361587 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[a11].[ORDER_TYPE_ID])) 1 10 9 Parallelism Repartition Streams PARTITION COLUMNS[a11].[ORDER_TYPE_ID]) NULL 1.103783 0 0.02850667 45 0.09361587 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[NEXT_STATUS_ID])) 1 11 10 Nested Loops Inner Join OUTER REFERENCES[a11].[NEXT_STATUS_ID]) NULL 1.103783 0 4.031072E-06 45 0.06510919 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[LAST_STATUS_ID])) 1 12 11 Nested Loops Inner Join OUTER REFERENCES[a11].[LAST_STATUS_ID]) NULL 3.857486 0 3.344787E-05 49 0.06137029 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[NEXT_STATUS_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| | |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[EXPENSE_CODE_ID])) 1 13 12 Nested Loops Inner Join OUTER REFERENCES[a11].[EXPENSE_CODE_ID]) NULL 32.00753 0 3.344787E-05 53 0.05315145 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| | | |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[DATE_INVOICE_ID], [Expr1033]) WITH UNORDERED PREFETCH) 1 14 13 Nested Loops Inner Join OUTER REFERENCES[a11].[DATE_INVOICE_ID], [Expr1033]) WITH UNORDERED PREFETCH NULL 32.00753 0 3.344787E-05 57 0.03554453 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| | | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_06] AS [a11]), SEEK[a11].[CUSTOMER_ID]=(1522717) OR [a11].[CUSTOMER_ID]=(1523004) OR [a11].[CUSTOMER_ID]=(1523012) OR [a11].[CUSTOMER_ID]=(1523364) OR [a11].[CUSTOMER_ID]=(1523728) OR [a11].[CUSTOMER_ID]=(1523809)), WHERE[JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461100) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461400) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461600)) ORDERED FORWARD) 1 16 14 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_06] AS [a11]), SEEK[a11].[CUSTOMER_ID]=(1522717) OR [a11].[CUSTOMER_ID]=(1523004) OR [a11].[CUSTOMER_ID]=(1523012) OR [a11].[CUSTOMER_ID]=(1523364) OR [a11].[CUSTOMER_ID]=(1523728) OR [a11].[CUSTOMER_ID]=(1523809)), WHERE[JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461100) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461400) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461600)) ORDERED FORWARD [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[BUSINESS_UNIT_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] 32.00753 0.0268287 0.0002600876 62 0.02708879 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[BUSINESS_UNIT_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 1 1
| | | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[IX_REL_CALENDAR_YEAR_TO_DATE_01]), SEEK[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=(106365) AND [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID_CYTD]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]) ORDERED FORWARD) 1 17 14 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[IX_REL_CALENDAR_YEAR_TO_DATE_01]), SEEK[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=(106365) AND [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID_CYTD]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]) ORDERED FORWARD [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] 1 0.003125 0.0001581 11 0.008185391 [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 32.00753
| | | |--Clustered Index Seek(OBJECT[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[PK__LU_EXPENSE_CODE__2843D2B2]), SEEK[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), WHERE[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD]<>N'LC') ORDERED FORWARD) 1 18 13 Clustered Index Seek Clustered Index Seek OBJECT[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[PK__LU_EXPENSE_CODE__2843D2B2]), SEEK[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), WHERE[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD]<>N'LC') ORDERED FORWARD [JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD] 1 0.003125 0.0001581 16 0.01755811 [JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD] NULL PLAN_ROW 1 32.00753
| | |--Index Seek(OBJECT[JDE_DATA].[dbo].[LU_LAST_STATUS].[IX_LU_LAST_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'620' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID] OR [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'914' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID]) ORDERED FORWARD) 1 19 12 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[LU_LAST_STATUS].[IX_LU_LAST_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'620' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID] OR [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'914' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID]) ORDERED FORWARD NULL 1 0.003125 0.0001581 9 0.008185391 NULL NULL PLAN_ROW 1 32.00753
| |--Index Seek(OBJECT[JDE_DATA].[dbo].[LU_NEXT_STATUS].[IX_LU_NEXT_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_SRCCD]=N'999' AND [JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[NEXT_STATUS_ID] as [a11].[NEXT_STATUS_ID]) ORDERED FORWARD) 1 20 11 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[LU_NEXT_STATUS].[IX_LU_NEXT_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_SRCCD]=N'999' AND [JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[NEXT_STATUS_ID] as [a11].[NEXT_STATUS_ID]) ORDERED FORWARD NULL 1 0.003125 0.0001581 9 0.003734868 NULL NULL PLAN_ROW 1 3.857486
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS.[ORDER_TYPE_ID]), ORDER BY.[ORDER_TYPE_ID] ASC), WHEREPROBE([Bitmap1034])=TRUE)) 1 21 7 Parallelism Repartition Streams PARTITION COLUMNS.[ORDER_TYPE_ID]), ORDER BY.[ORDER_TYPE_ID] ASC), WHEREPROBE([Bitmap1034])=TRUE) NULL 20.5 0 0.0285224 11 9.11078 .[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Merge Join(Inner Join, MERGE.[ORDER_TYPE_ID])=([JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), RESIDUAL[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_ID] as .[ORDER_TYPE_ID])) 1 22 21 Merge Join Inner Join MERGE.[ORDER_TYPE_ID])=([JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), RESIDUAL[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_ID] as .[ORDER_TYPE_ID]) NULL 20.5 0 0.001586888 11 9.082257 .[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS.[ORDER_TYPE_ID]), ORDER BY.[ORDER_TYPE_ID] ASC)) 1 23 22 Parallelism Repartition Streams PARTITION COLUMNS.[ORDER_TYPE_ID]), ORDER BY.[ORDER_TYPE_ID] ASC) NULL 328 0 0.03033649 11 0.03529974 .[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
| |--Clustered Index Scan(OBJECT[JDE_DATA].[dbo].[LU_ORDER_TYPE].[PK__LU_ORDER_TYPE__265B8A40] AS ), WHERE[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'DL' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'S1' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SG' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SI' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SK' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'ST' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SU' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'YU') ORDERED FORWARD) 1 24 23 Clustered Index Scan Clustered Index Scan OBJECT[JDE_DATA].[dbo].[LU_ORDER_TYPE].[PK__LU_ORDER_TYPE__265B8A40] AS ), WHERE[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'DL' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'S1' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SG' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SI' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SK' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'ST' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SU' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'YU') ORDERED FORWARD .[ORDER_TYPE_ID] 328 0.004606482 0.00013165 18 0.004738132 .[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Stream Aggregate(GROUP BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID])) 1 25 22 Stream Aggregate Aggregate GROUP BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]) NULL 27 0 1.6875E-05 11 9.045368 [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), ORDER BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] ASC)) 1 26 25 Parallelism Repartition Streams PARTITION COLUMNS[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), ORDER BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] ASC) NULL 108 0 0.0291047 11 9.045351 [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Stream Aggregate(GROUP BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID])) 1 27 26 Stream Aggregate Aggregate GROUP BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]) NULL 108 0 0.634653 11 9.016247 [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Index Scan(OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_01]), ORDERED FORWARD) 1 28 27 Index Scan Index Scan OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_01]), ORDERED FORWARD [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] 5077116 6.985347 1.396246 11 8.381594 [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
I have 3 tables, that appear as follows (insignificant fields are not mentioned for brevity):
RETAIL(code, CurrentLocation) ~ 2.6 million records
LOCAUDIT(code, Date, Time, Location) ~ 3.6 million records
STAFF(ID, NAME) ~ 40K records
Each record in the RETAIL table represents a document. The LOCAUDIT table maintains history information for documents: locations they've been to. A location can be represented by a staff (from STAFF table), or an unlimited range of different names - not enumerated in a table.
The query we run tries to find the currentlocation for each document in the RETAIL table (if any). Since a document may have been to many location, I'm interested in the last location which has the max Date,Time.
To perform the query, I created two views:
HISTORY
=======
CREATE VIEW HISTORY
AS
SELECT CODE, "DATE", TIME, CAST("DATE" + ' ' + TIME AS datetime) AS UpdateDateTime, LOCATION
FROM LOCAUDIT
LASTHISTORY
==========
CREATE VIEW LASTHISTORY
AS
SELECT CODE, Max(UpdateDateTime) AS LastUpdated
FROM HISTORY
GROUP BY CODE
UPDATE RETAIL
SET CURRENTLOCATION = (CASE WHEN t3.NAME IS NULL THEN t2.LOCATION ELSE t3.NAME END)
FROM RETAIL AS t4
LEFT JOIN LASTHISTORY AS t1 ON (t4.CODE = t1.CODE)
LEFT JOIN HISTORY AS t2 ON (t1.ITEM = t2.ITEM AND t1.LastUpdated = t2.UpdateDateTime)
LEFT JOIN STAFF AS t3 ON (t2.LOCATION = t3.ID)
What the query does is update the current location of each document. If the current location is a staff, we find the name of the staff member (hence the case).
In addition to clustered indexes on the primary keys, I've also created an index on (Code, Date, Time) on LOCAUDIT.
However, the query still seems to take up to 3 hours sometimes to run on a server with 4 CPU's and a whole bunch of memory. Can anyone suggest some way to improve this, add more effective indexes, or rewrite the queries all together. Any help is appreciated..
Recently my system encounter some problem when retrieving certain record from MSSQL.
For an example i have a database which contains 1.5 million of members. so i have a perl scripts that will execute to query based on certain range.
the schedule like below:
1 script - 1-250k (Query finish less than 5 mins)
<interval 5 mins>
1 script - 250k-500k (Query finish less than 5 mins)
<interval 5 mins>
1 script - 500k-750k (Query finish less than 5 mins)
<interval 5 mins>
1 script - 750k-1M (Query finish in 1++ hours)
<interval 5 mins>
1 script - 1M-1.25M (Query finish in 1++ hours)
<interval 5 mins>
1 script - 1.25M-1.50M (Query finish in 1++ hours)
END
After the 4th query, the query seems to work very slow, and this problem only raise on windows 2003 with mssql 2005, current server that run smoothly is win2k with mssql2000.
anyone have any idea on this problem either cause by operating system and database or related to something else?
hello
I have been assigned the task to port some reports from Business Objects 5.0 to Reporting Services 2005.
The BO reports used data regions based on multiple datasets, which is not feasible in SSRS.
So I have to get everything in one big query. The problem is that the query runs for 2 hours instead of a couple of minutes with our old BO solution which got it's data from an access database !
is there a better way to do this ?
here is the query
Code Snippet
ALTER PROCEDURE [dbo].[MERCKGEN_HDMCKQ02_DoctorBrickDoctorGroups_values]
-- Add the parameters for the stored procedure here
@TimePeriodDesc varchar(255),
@TotalMarket varchar(255),
@datasetname varchar(255),
@VisibleProducts varchar(500),
@DoctorSubTerritory varchar(255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT xpospecialty.specialtydesc,
a.doctorsubterritory ,
a.doctorbrick ,
a.doctorgroup ,
xpomarket.productdesc ,
s.totalmarketrank ,
s.prod1ranktotal ,
s.prod2ranktotal ,
SUM
(
CASE
WHEN (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN xpomeasures.rxvalues
ELSE 0
END
) AS totalrxvalues,
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc <> 'Non -')
AND (XpoMarketSize.MarketSizeDesc <> 'Non - MKT')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS rxrcount,
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'High')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [high],
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'Medium')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [medium],
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'Low')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [low],
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'Very Low')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [very low],
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'Very High')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [very high],
(SELECT SUM(xpomeasures.rxvalues) AS totalrxvalues
FROM dbo.xpotimeperiod
INNER JOIN dbo.xpomeasures
ON xpotimeperiod.timeperiodid = xpomeasures.timeperiodid
INNER JOIN dbo.xpomarket
ON xpomeasures.xpomarketid = xpomarket.xpomarketid
INNER JOIN dbo.xpogeography
ON xpomeasures.geographyid = xpogeography.geographyid
WHERE (xpomarket.productdesc = @TotalMarket)
AND (xpomeasures.datasetname = @DatasetName)
AND (xpotimeperiod.datasetname = @DatasetName)
AND (xpomarket.datasetname = @DatasetName)
AND (xpogeography.datasetname = @DatasetName)
AND (xpogeography.doctorgroup = a.doctorgroup)
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
) AS rxvaluesdoctorgroup
FROM dbo.xpotimeperiod
INNER JOIN dbo.xpomeasures
ON xpotimeperiod.timeperiodid = xpomeasures.timeperiodid
INNER JOIN dbo.XpoMarketSize
ON XpoMeasures.XpoMarketSizeId = XpoMarketSize.XpoMarketSizeId
INNER JOIN dbo.xpoproductsize
ON xpomeasures.xpoproductsizeid = xpoproductsize.xpoprodsizeid
INNER JOIN dbo.xpomarket
ON xpomeasures.xpomarketid = xpomarket.xpomarketid
INNER JOIN dbo.xpogeography a
ON xpomeasures.geographyid = a.geographyid
INNER JOIN dbo.xpospecialty
ON xpomeasures.xpospecialtyid = xpospecialty.xpospecialtyid
INNER JOIN dbo.Fn_mvparamsorted (@VisibleProducts,',' )
ON xpomarket.productdesc = fn_mvparamsorted.parame
LEFT JOIN (
SELECT xpogeography.doctorgroup,
SUM(CASE WHEN xpomarket.productdesc = @TotalMarket THEN xpomeasures.rxvalues ELSE 0 END) AS totalrxvalues,
ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = @TotalMarket THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS TotalMarketRank,
ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = 'PPI Total' AND xpogeography.doctorsubterritory = @DoctorSubTerritory THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS 'Prod2RankTotal',
ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = 'Merck-Generics Total' AND xpogeography.doctorsubterritory = @DoctorSubTerritory THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS 'Prod1RankTotal'
FROM dbo.xpotimeperiod
INNER JOIN dbo.xpomeasures ON xpotimeperiod.timeperiodid = xpomeasures.timeperiodid
INNER JOIN dbo.xpomarket ON xpomeasures.xpomarketid = xpomarket.xpomarketid
INNER JOIN dbo.xpogeography ON xpomeasures.geographyid = xpogeography.geographyid
WHERE xpomeasures.datasetname = @DatasetName
and (xpomarket.datasetname = @DatasetName)
and (xpogeography.datasetname = @DatasetName)
AND xpotimeperiod.timeperioddesc = @TimePeriodDesc
GROUP BY xpogeography.doctorgroup
) AS s ON s.doctorgroup = a.doctorgroup
WHERE (xpomeasures.datasetname = @DatasetName)
AND (xpomarketsize.datasetname = @DatasetName)
AND (xpoproductsize.datasetname = @DatasetName)
AND (xpotimeperiod.datasetname = @DatasetName)
AND (a.datasetname = @DatasetName)
AND (XpoMarket.datasetname = @DatasetName)
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
AND (a.doctorsubterritory = @DoctorSubTerritory)
GROUP BY xpospecialty.specialtydesc,
a.doctorsubterritory ,
a.doctorbrick ,
a.doctorgroup ,
fn_mvparamsorted.parame ,
fn_mvparamsorted.sortid ,
xpomarket.productdesc ,
s.totalmarketrank ,
s.prod1ranktotal ,
s.prod2ranktotal
ORDER BY s.totalmarketrank,
a.doctorgroup ,
fn_mvparamsorted.sortid
END
Hello -
I need to call a SQL Server stored procedure, which takes over five hours to run, from an asp.NET web page. This procedure then calls a DTS package which is what takes 5+ hours to run. I need the user to be able to click on the 'run' button and have the page kick off the stored procedure (or the DTS Package if that will work instead) and display a message saying the load has begun and to check a 'status' link.
Right now I get a page timeout because it's waiting for results.
Any help would be greatly appreciated!
Thanks,
cat72
Sorry i think i may have posted this in the incorrect forum before - if i have done it again here can someone tell me where i should post this please, thanks:
Hi,
we are having problems with a server Intel RZeon 3ghz, 3gb ram running 2003 service pack 2 with a 70gb drive and and 400 gb drive all with adequate free space. There are 6 hard disks in total and i assume operating at least RAID 5. We have SQL2000 server with a few standard sized databases and a connection to one other server.
A few months ago the back up of SQL server databases started taking 4- 5 hours when before it took 20 minutes. We had actually lost one of our disks in the RAID array and it before this was spotted by our engineers we reindexed the sql databases and defragged both 70gbC: and D: 400gb drives hoping to correct this slow down. Unfortunately the new disk had not been correctly seated and this was why it was taking 4-5 hours. After fixing the disk the backups took 12 minutes again but then started taking 2-3 hours after a few days.
The reindex/defrag did seem to improve the speed of the backups to 12 minutes (from 20 minutes) when the backup did function correctly (also the sql databases' performance improved). However the backups only take 12 minutes after a server reboot - this can last from only 2, up to 5 backups(days) in a row before a slow down to 2-3 hours and again only a reboot will sort out this problem.
NB this intermittent slowdown only occurred after the disk failure.
We have tried monitoring SQL server and can find no CPU/RAM intensive clashes or long running jobs interferring with the back up. Does anyone know what might be going on here? and if there are any server monitoring tools that may help us discover what is causing this problem ?
thanks for your help
best regards
kevin
Hi,
How much time will it take to restore a DB 300MB long in SQL Server 2005 Express?
Full Restore with recovery option?
The thing is that I made a restore process and its been running for about an hour now.
When I open the Management Studio on the database folder it shows "Restoring..."
Is this normal?
Tnx
Had a problem in a database and wanted to restore from disk as of 11/5/2007 (full backup). This particular database is only really updated about once a month, and changes were made on the sixth, so I thought this would be safe.
So (be kind - not an SQL guru), I ran something similar to:
RESTORE DATABASE myDatabase
FROM DISK = '\serverNamesqlbackupsmyDatabasemyDatabase_backup_200711052320.bak'
WITH NORECOVERY, STOPAT = 'Nov 05, 2007 11:21 PM'
myDatabase_backup_200711052320.bak is 42,084kb and has been running since 9:30 this morning (it's 2:20ish now). I've tried to take offline, stop/start sql services, restart server. Nothing works. It just keeps reading myDatabase (restoring).
Anybody help me out? How do I get it to stop and then do a clean restore?
Thanks so much,
Janet
Had a problem in a database and wanted to restore from disk as of 11/5/2007 (full backup). This particular database is only really updated about once a month, and changes were made on the sixth, so I thought this would be safe.
So (be kind - not an SQL guru), I ran something similar to:
RESTORE DATABASE myDatabase
FROM DISK = '\serverNamesqlbackupsmyDatabasemyDatabase_backup_200711052320.bak'
WITH NORECOVERY, STOPAT = 'Nov 05, 2007 11:21 PM'
myDatabase_backup_200711052320.bak is 42,084kb and has been running since 9:30 this morning (it's 2:20ish now). I've tried to take offline, stop/start sql services, restart server. Nothing works. It just keeps reading myDatabase (restoring).
Anybody help me out? How do I get it to stop and then do a clean restore?
Thanks so much,
Janet
I have SQL 2014. When I try to restore a user database using SSMS GUI, the Restore Database Pop up box never pops up. This happens for any database on this server at any time. Sometimes I get the pop up, some times I dont get.
So I tried to click on Databases on Top and Restore Database, and then select the db that I need to restore from Drop down, then it shows "creating restore plan selecting backups" but it takes forever.
We have full backup and trn log backups every 30 mins. So is it trying to get all these backup files in the background causing this issue? If yes then how to overcome this?
I want to display Days Hours Mins Format.
I am Having two columns Like below,
Col1 (in days) col2 (In Hours : Mins)
3days 4:5
In this first have to add Col1 and Col2 (Here one day is equals to 9 hours ) so the addition is 31.5
From this 31.5 I should display 3 Days 4 Hours 30 Mins because 31.5 contains 3 (9 hours) days 4 Hours and .5 is equals to 30 mins.
I have data coming from a telephony system that keeps track of when anemployee makes a phone call to conduct a survey and which project numberis being billed for the time the employee spends on that phone call in aMS SQL Server 2000 database (which I don't own).The data is being returned to me in a view (see DDL for w_HR_Call_Logbelow). I link to this view in MS access through ODBC to create alinked table. I have my own view in Access that converts the integernumbers for start and end date to Date/Time and inserts some otherinformation i need.This data is eventually going to be compared with data from someelectronic timesheets for purposes of comparing entered hours vs hoursactually spent on the telephone, and the people that will be viewing thedata need the total time on the telephone as wall as that total brokendown by day/evening and weekend. Getting weekend durations is easyenough (see SQL for qryTelephonyData below), but I was wondering ifanyone knew of efficient set-based methods for doing a day/eveningbreakdown of some duration given a start date and end date (with theday/evening boundary being 17:59:59)? My impression is that to do thiscorrectly (i.e., handle employees working in different time zones,adjusting for DST, and figuring out what the boundary is for switchingfrom evening back to day) will require procedural code (probably inVisual Basic or VBA).However, if there are set-based algorithms that can accomplish it inSQL, I'd like to explore those, as well. Can anyone give any pointers?Thanks.--DDL for view in MS SQL 2000 database:CREATE VIEW dbo.w_HR_Call_LogASSELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username ASInitials, dbo.billing.startdate, dbo.billing.startdate +dbo.billing.duration AS EndDate,dbo.billing.duration, dbo.projects.name ASPrjName, dbo.w_GetCallTrackProject6ID(dbo.projects.descript ion) AS ProjID6,dbo.w_GetCallTrackProject10ID(dbo.projects.descrip tion) AS ProjID10,dbo.billing.interactionidFROM dbo.projects INNER JOINdbo.projectsphone INNER JOINdbo.users INNER JOINdbo.TRCUsers ON dbo.users.userid =dbo.TRCUsers.UserID INNER JOINdbo.billing ON dbo.users.userid =dbo.billing.userid ON dbo.projectsphone.projectid =dbo.billing.projectid ONdbo.projects.projectid = dbo.projectsphone.projectidWHERE (dbo.billing.userid 0)ORDER BY dbo.billing.startdateI don't have acess to the tables, but the fields in the view comethrough as the following data types:WinsID - varchar(10)Initials - varchar(30)startdate - long integer (seconds since 1970-01-01 00:00:00)enddate - long integer (seconds since 1970-01-01 00:00:00)duration - long integer (enddate - startdate)ProjID10 - varchar(15)interactionid - varchar(255) (the identifier for this phone call)MS Access SQL statement for qryTelephonyData (based on the view,w_HR_Call_Log):SELECT dbo_w_HR_Call_Log.WinsID, dbo_w_HR_Call_Log.ProjID10,FORMAT(CDATE(DATEADD('s',startdate-(5*60*60),'01-01-197000:00:00')),"yyyy-mm-dd") AS HoursDate,CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970 00:00:00')) ASStartDT,CDATE(DATEADD('s',enddate-(5*60*60),'01-01-1970 00:00:00')) AS EndDT,DatePart('w',[StartDT]) AS StartDTDayOfWeek, Duration,IIf(StartDTDayOfWeek=1 Or StartDTDayOfWeek=7,Duration,0) ASWeekendSeconds,FROM dbo_w_HR_Call_LogWHERE WinsID<>'0'
View 3 Replies View RelatedI have a float variable that holds a decimal number of hours.
So 1.5 equals 1 hour 30 minutes.
I need to change this to the format 1:30
Any idea how to do this?
I have a Stored Procedure that has a query in it and it take 0 second and then a stored procedure that takes 16 seconds. From what I can tell they shoul be the same.
It doesn't recompile when i run the stored procedure, I checked that.
Hello everybody
I created stand by server and do restore of database 'Alex' with standby
every 15 min
database 'Alex' on stand by server is set to read only
I allow user to run queries using database 'Alex' on stand by server
I noticed in my job history foolwing
Job started job run
---------- -------
10:00:00 00:01:00
10:30:00 00:19:23 --- at this time users did select on stand by server
11:00:00 00:02:00
So I am mising the job at 10:45:00 but I did not get any errors
and then job goes back to every 15 min
what happen to job at 10:45:00 did it run at 11:00 ?
job created from DTS and include
a) back of transaction log from production database
b) moving log stand by server
c) restoring log "with stand by" on stand by server
What is best solution if I want used stand by server for reports ?
Thank you.
While migrating Report services in SQL Server 2005 to 2014, I am trying to restore the Encryption Key in RS Configuration Manager in2014. But I cannot click the 'Restore' button in RS Configuration Manager. So if I should be grant more right to do so or any other action?
View 2 Replies View RelatedIn Windows Server 2012. How do I do a System Restore to a previous restore point?I need to install the 64 bit and 32 bit Oracle Client Install for connections in SSIS and to create Oracle Linked Servers.
If you make a mistake it is not fun removing it. Sometimes it corrupts the machine and it is difficult to uninstall since there is not an Oracle Universal installer for Oracle 11g.If you install the 32 bit before the 64 you mess up the machine.how to create a restore point.
I am looking for a SQL Backup/Restore tools which can restore multiple environments. Here is high level requirements.
1. We have 4 DBs, range from 1 TB - 1.5 TB Each Database. When we restore to QA, DEV, or Staging, we usually restore 4 of them.
2. I am looking for the speed to complete restoring between 1 - 2 hours for 4 DBs.
I am evaluating the Dephix Software but the setup is very complex and its given us a lot of issues with Windows Authentions, and failure in the middle of the backup. I used Guess Software many years ago but can't find it on the web site any more. Speed is very important for us mean complete restoring as fast as possible. We are on SQL 2012 and SQL 2008 R2.We are currently using NETAPP Technology and I have Redgate Backup Tool but I am mainly looking for fast Restore Process.
hi, this stored procedure below takes 25 mins to execute. is this normal? and is there a faster way to do this? its not like i have millions of records, i only have about 170.
Create Procedure sp_PayrollComplete
AS
declare @weDate nvarchar(10)
Select @weDate= we FROM cbetts.tblArchive
WHERE we = (select max(convert(datetime,we)) from cbetts.tblarchive)
declare @uid int
declare @job nvarchar(10)
declare @name nvarchar(30)
declare @Location nvarchar(30)
declare @Direct float
declare @LBO float
declare @g1099 float
declare @agency float
declare @nonbill float
declare @holiday float
declare @vacation float
declare @total float
delete cbetts.tblpayroll
Declare payroll_cursor CURSOR FOR
SELECT distinct userid FROM cbetts.v_PayrollTotals
where (we = @weDate)
ORDER BY userid
OPEN payroll_cursor
-- Perform the first fetch.
FETCH NEXT FROM payroll_cursor into @uid
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
select @location=location from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
select @name = name from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
select @Direct =
sum(totals) from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
and employeetype='direct'
and left(jobno,3) != '900'
select @LBO =
sum(totals) from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
and employeetype='LBO'
select @g1099 =
sum(totals)from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
and employeetype='1099'
select @agency =
sum(totals) from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
and employeetype='Agency'
select @nonbill=
sum(totals) from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
and left(jobno,3)='900'
select @holiday =
sum(totals) from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
and (jobno='90010' or jobno='90011')
select @vacation =
sum(totals) from cbetts.v_payrolltotals
where userid = @uid
and we = @weDate
and jobno='90020'
set @total = ISNULL(@direct,0) + ISNULL(@lbo,0) + ISNULL(@g1099,0) + ISNULL(@agency,0) + ISNULL(@nonbill,0) + ISNULL(@holiday,0) + ISNULL(@vacation,0)
set @nonbill = ISNULL(@nonbill,0) - (ISNULL(@holiday,0) + ISNULL(@vacation,0))
insert into cbetts.tblpayroll (Name,userid,location,direct,lbo,g1099,agency,nonbill,holiday,vacation)
values (@name,@uid,@location,ISNULL(@direct,0), ISNULL(@lbo,0), ISNULL(@g1099,0), ISNULL(@agency,0),ISNULL(@nonbill,0), ISNULL(@holiday,0), ISNULL(@vacation,0))
FETCH NEXT FROM payroll_cursor into @uid
END
CLOSE payroll_cursor
DEALLOCATE payroll_cursor
-- get rid of the 0 hour entries
delete cbetts.tblpayroll
where total = 0
GO
i have a table which contains a text field
and basically i have to check if a text or phrase exist in that text
select count(*) from MYtable where TxtField like '%MYPHRASE%'
there are 700,000 records in that table and whenever i query it takes 9 seconds to give me the recordcount.
what i am doing wrong
The database is about 5 Gb and the transaction log is about 13 M
and it takes 11 hours to do a full back up.
I am not sure why it takes that long? Is there any thing I can check?
IT is SQL v7.
Thank you
Hi all,
I have a stored procedure that is called from a VB.NET application that takes an enormously long time to execute. In the QA it only takes 10sec but in the application it takes ages. The stored procedure is as follows:
PROCEDURE NAME IS SPTOPTWENTYUSERS
SELECT TOP 20 STRUSERNAME,SUM(INTBYTESRECVD) AS INTDOWNLOAD FROM TBLISAWEBLOGS
WHERE DTELOGDATE BETWEEN @BEGINDATE AND @ENDDATE
GROUP BY STRUSERNAME
ORDER BY INTDOWNLOAD DESC
The code that runs it is as follows:
sSQLString = SPTOPTWENTYUSERS
Using cnn As New SqlConnection(GetPath)
Try
Dim cmd As New SqlCommand(sSQLString, cnn)
Dim dr As SqlDataReader
With cmd
.CommandType = CommandType.StoredProcedure
.CommandTimeout = 0
.Parameters.Add("@BEGINDATE", SqlDbType.DateTime)
.Parameters.Add("@ENDDATE", SqlDbType.DateTime)
.Parameters("@BEGINDATE").Value = dtpStartDate.Value
.Parameters("@ENDDATE").Value = dtpEndDate.Value
End With
cnn.Open()
dr = cmd.ExecuteReader
Any help on why this happens would be much appreciated.
thanks
Hi, Is there any way to audit or record in SQL Server 2000 what queries are the ones that consume more resources in the server so I can focus and improve them?
Thanks
We have a re-indexing all DBs schedule job in our SQL 2000 box,normally it took 7 hours to complete but all of the sudden now ittakes more than 20 hours.What do you think it cause this problem? We have no clue.
View 2 Replies View RelatedHiI a am executing a stored procedure from C code using SQLExecute . Ittakes some time to Execute the procedure. However when I see theprocessor usage during that time it shows 100% cpu usage.Can anyone tell me why SQLExecute uses 100% CPU when running thestored proceudre.Thanksvishal
View 2 Replies View RelatedThe Error:
An attempt to attach an auto-named database for file C:UsersCodeFreakDocumentsVisual Studio 2005WebSitesPersonalSiteApp_Dataaspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
---------------------------------------------------------------------------------------------------------------
I've tried allowing permissions I'VE EVEN TRIED ALLOWING EVERYONE! I've tried right clicking the datasource under the datasource explorer and clicking 'detatch.' It works Locally but fails when i pusblish with IIS. Whats with these SQL databases that just dont want to work with IIS 7 ?
Published with: IIS 7
I've tried removing the "user instance."
This is User roles and all that which uses SQL Server 2005.
So my question is, How do i publish a website with SQL Server 2005 database?
Please give it to me step by step as im still quite a newbie.
Thanks in advance
Dear all, I have MsAccess97 database. The size of this database is 450 MB, the same database i converted into SQL Compact Editon database. But the size reaches to 1.35 GB even after i did compact process. It reduces to 1.33 GB. I don't know why it increases from 450 MB to 1.3 GB? anybody knows the reason please reply then how do i reduce the size? waiting for the reply. Thanks Gops India gopalan@sofist.com
View 4 Replies View Related
I could use a little help here. We have a stored procedure that runs on SQL2000 and for a large dataset only takes 1-2 minutes. On SQL2005 however, it takes around 25 minutes. Any advice or insight anyone could give would be great.
Here's the stored procedure:
CREATE PROCEDURE daa_upd_relationship_balance_hist
AS
begin tran
insert fldarts..daa_relationship_bal_hist
select <-- list snipped -->
from daa_relationship_bal drb, daa_user_review dur
where
drb.acct_no = dur.acct_no and
drb.control_2 = dur.control_2 and
drb.nb_gl_cost_ctr = dur.nb_gl_cost_ctr and
drb.nb_dda_sav_type = dur.nb_dda_sav_type and
drb.acct_no+drb.control_2+drb.nb_gl_cost_ctr+drb.nb_dda_sav_type+convert(char(10),dur.activity_date, 101)
not in
(select acct_no+control_2+nb_gl_cost_ctr+nb_dda_sav_type+convert(char(10), activity_date, 101)
from fldarts..daa_relationship_bal_hist)
if @@error = 0
commit tran
else
begin
rollback tran
print '!!!Error (daa_relationship_bal_hist) : Relationship Balance History not updated'
end
return
GO
So we have three tables. Here's a schema for each and the indexes on them. I've omitted columns from the tables that are not utilized in this query.
daa_relationship_bal:
CREATE TABLE [daa_relationship_bal] (
[control_2] [char] (3) NOT NULL ,
[nb_gl_cost_ctr] [char] (7) NOT NULL ,
[acct_no] [char] (14) NOT NULL ,
[nb_dda_sav_type] [char] (3) NOT NULL
)
index:
idx_upd_balance_hist nonclustered located on PRIMARY acct_no, control_2, nb_gl_cost_ctr, nb_dda_sav_type
daa_user_review:
CREATE TABLE [daa_user_review] (
[control_2] [char] (3) NOT NULL ,
[nb_gl_cost_ctr] [char] (7) NOT NULL ,
[acct_no] [char] (14) NOT NULL ,
[nb_dda_sav_type] [char] (1) NOT NULL ,
[activity_date] [datetime] NULL
)
index:
PK_daa_user_review_1__37 nonclustered, unique, primary key located on INDEXES control_2, nb_gl_cost_ctr, acct_no, nb_dda_sav_type
daa_relationship_bal_hist:
CREATE TABLE [daa_relationship_bal_hist] (
[control_2] [char] (3) NOT NULL ,
[nb_gl_cost_ctr] [char] (7) NOT NULL ,
[acct_no] [char] (14) NOT NULL ,
[nb_dda_sav_type] [char] (3) NOT NULL ,
[activity_date] [datetime] NOT NULL
)
index:
PK_daa_rel_bal_hist_1__37 nonclustered, unique, primary key located on PRIMARY control_2, nb_gl_cost_ctr, acct_no, nb_dda_sav_type, activity_date
Any help on this would be great. If more information is needed, please let me know.
Hi all,
I have a package which updates a table which contains a large amount of rows, when this package is runnning the process consumes 100% CPU and other services are crashing (SSAS, SPS, SSRS).
Is there any parameter which could be set to avoid SSIS to consume 100% of the CPU during the execution of this package ?
We have a few Servers that have space issues that need full DB Backups every day. As a result, we have to carefully watch how many DB Backup files we keep on the Server. On the Maintenance Plan, if I set the 'Delete files older than:' to '1' 'Day', will this delete anything over 24 hours old? What I have noticed, is that it tends to delete on Tuesday anything that was created on Sunday, but not Monday. Therefore, I am assuming that instead of '1 Day' being 24 hours, it is anything less than 47 hours and 59 minutes...
Is my assumption correct or do I need to look at something else. Thanks!
Billy
Hi,
cube processing is taking more time in a new server while same cubes takes less time in another server.
the cubes are processed through DTS package
can anybody help finding out the possible reasons for this.
Regards
Naseem