Force Order Query Hint
Jun 8, 2001We are discovering that adding Force Order to a query is substantially increasing performance. Any issues around using this ?
Craig
ADVERTISEMENT
Force The ORDER BY To Be In Ascending Order??
Jul 5, 2007I noticed the StockDate is not sorted in proper order, like ascending order...
Code:
select top 1000 CONVERT(char, StockDate, 101) AS StockDate, timestamp from tblpurchaseraw where accountid = '119' order by stockdate desc
I noticed that StockDate is a datetime datatype so why does the month get ordered 1st, then day get ordered 2nd and year get ordered 3rd...
The sample data is MM/DD/YYYY...
So, how do I get it ordered propery by Year, Month then Day??
Force Clomn Name In Order BY Instead Of Expression Name
Feb 21, 2008hi,
I can not get the right result from the following query
SELECT Equipment.AreaID AS AreaNo, Equipment.EquipmentNo
FROM Area INNER JOIN
Equipment ON Area.AreaID = Equipment.AreaID
ORDER BY Area.AreaNo, Equipment.EquipmentNo
i want to force Area.AreaNo in Order BY i.e. the resultset is sorted on the values from AreaNo column in Table Area, but it seems that Equipment.AreaID AS AreaNo values become Area.AreaNo and resultset is sorted on AreaId rather than AreaNo.
How can i force it to sort on AreaNo values in the Area table without changing Equipment.AreaID AS AreaNo to something else.
Warning - The Join Order Has Been Enforced Because A Local Join Hint Is Used
Dec 23, 2014I have two select statements, in between select statement taking UNION ALL . I need to avoid the error
Warning: The join order has been enforced because a local join hint is used.
Subselect In A Query With Like Hint
Sep 26, 2013Is possible to use like hint with subselect? , i mean i want to find all rows in table A that contains a word in a field(CALLED CONTENT) in table B, concretely in a field called content too, i show you the idea although the syntax is incorrect.
select
' + char (39) + @country + char (39) + ' as PAIS,
A.ID,
A.IDUSUARIO MSISDN,
NULL AS MSISDN_COD,
convert(char(19),A.FECHA_ALVENTO, 121) AS FECHA_MO_LOCAL,
NULL AS FECHA_MO_LOCAL_D,
[code]...
Replication :: Ability To Use Query Hint In Transactional Filtered Articles
Jul 22, 2015I am setting up filtered replication(tran repl) for a table that is 2+ TB in size. The filtered rows are less than 1% of total size.When I run the snapshot, sometimes it complete in 27 mins(I have already optimized the snapshot agent profile) and sometimes it runs for ever. Mostly it runs forever, so the chances of it finishing soon are really very less.I have infact created filtered index just to cater to the query in the filter i.e. on the filter predicates. The reason its flipping flopping is due to the incorrect plan that it chooses. I can force the right index and the filtered query runs just fine but. I cannot use the query hints when I am adding the filter in replication as its not allowed.
I then went ahead and created the plan guides to force the best plan. But the plan guide was not used because even if there is a single space in the actual query that replication runs in the background the plan guide will not be used(testing on plan guides has proved this). The query needs to be ditto same including whitespaces and carriage returns. I then ran profiler and found that there is a view that replication creates and executes to extract the filtered rows and BCP it. Got the text of the view and created the plan guide on it but it was still not used because sp_helptext output seems to be different than what is actually being fired by replication internally.
Further, I collected the verbose log with level 2 and tried to find out if the main query shows up there with no success.
(a) I can't use the plan guide because I do not know the exact syntax(I mean I have the query but I do not know the white spaces, carriage returns, etc) and
(b) I cannot use query hint while filtering the article.
1. I cannot afford to have downtime.
2. I cannot use backup and restore option either.
With OPTION ( FORCE ORDER ), SQL Takes 1 Second And Without It Takes 2 Hours Before Cancelled
Apr 9, 2007Could 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
How Can I Force The Query To Return Values?
May 23, 2007I want the following query to return a row even when table 'X' is empty. How would I do this?
SELECT TOP 1 @Var1, @Var2, @Var3 from X
The parameters @Var1, @Var2 and @Var3 are passed to the stored procedure in which the above query is included.
When table is empty, it reurn nothing. It only return a row when table is not empty.
Force A Parallel Query Execution
Jun 2, 2007I have another post here regarding SQL 2005 running a query 50% slower than on 2000. It was discovered that 2005 runs the query in series whereas 2000 runs it in parallel.
Even with "Cost Threshold For Parallelism" set to a default value – 0, 2005 still executes my query in series. Does anyone know how to force a query to run in parallel in SQL 2005. I specifically want to set it at the database level.
How To Force The Query Engine To Use An Index With An OR Clause?
Nov 16, 2007I have the following table:
Create Table Item(
I_Code NVarChar(40) Primary Key NOT NULL,
I_MatID NVarChar(40),
I_Name NVarChar(160),
I_BC nvarchar(20),
I_Company nvarchar(20),
I_CompanyFound nvarchar(20),
I_Info1 nvarchar(55),
I_Acquired nvarchar(35),
I_Info2 nvarchar(55),
I_Info3 nvarchar(55),
I_Date DateTime DEFAULT GetDate()
);
Create Index ind_Item_Name on Item(I_Name);
Create Index ind_Item_BC on Item(I_BC);
Create Index ind_Item_Company on Item(I_Company);
Create Index ind_Item_CompanyFound on Item(I_CompanyFound);
create Index ind_Item_i1 on Item(I_Company,I_CompanyFound);
create Index ind_Item_i2 on Item(I_CompanyFound,I_Company);
Now this query DOES NOT use index:
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' or I_CompanyFound='102' )
While this one use:
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' )
UNION
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_CompanyFound='102' )
Both return the same rows. Is this a bug? I found the following:
http://support.microsoft.com/kb/223423
Some feedback?
Thanks
Force To Complete Query, Ignore Errors
Nov 9, 2006Hi,
I have a big table and want to make a plausibility check of it´s data.
Problem is, that my query stops, if there is an unexpected datatype in one of the rows. But that is it, what i want to filter out of my table with that query and save the result as new correct table.
How can i write a parameter to my query SQL Code, that if a error occurs, the querry resumes and the error line will not displayed in my final querry overview?
In my books and on the net, i don´t found something to this theme ;-(.
Thx in advance.
SQL 2012 :: How To Force Server To Generate A New Query Plan
Oct 30, 2015Select A.* from A inner join B on ( A.ID= B.ID )
I know there is some key word that you use to force SQL server to generate a new query plan ?What can that be ?
Performance Tuning Using Index. How To Force The Query Engine To Use It?
Nov 14, 2007Hello all.
I have the following table
Create Table Item(
I_AssetCode NVarChar(40) Primary Key NOT NULL,
I_Name NVarChar(160),
I_BC nvarchar(20),
I_Company nvarchar(20)
);
Create Index ind_Item_Name on Item(I_Name);
Create Index ind_Item_BC on Item(I_BC);
Create Index ind_Item_Company on Item(I_Company);
It is populated with 50 000 records.
Searching on indexed columns is fast, but I've run into the following problem:
I need to get all distinct companies in the table.
I've tried with these two queries, but they both are very slow!
1. "select I_Company from item group by I_Company " - This one takes 19 seconds
2. "select distinct(I_Company) from item" -This one takes 29 secons
When I ran them through the SQL Management Studio and checked the performance plan, I saw that the second one doesn't use index at all ! So I focused on the first...
The first one used index (it took it 15% of the time), but then it ran the "stream aggregate" which took 85% of the time !
Actully 15% of 19 seconds - about 2 seconds is pretty much enough for me. But it looks that aggregate function is run for nothing!
So is it possible to force the query engine of the SSCE not to run it, since there is actually no aggregate functions in my select clause?
According to SQL CE Books online:
Group By
"Specifies the groups (equivalence classes) that output rows are to be placed in. If aggregate functions are included in the SELECT clause <select list>, the GROUP BY clause calculates a summary value for each group."
It seems the aggregate is run every time, not only when there is an aggregate function.
Is this a bug?
Thanks in advance,
TipoMan
SQL 7.0 Security Question. How Do I Force My SQL Server To Query The SAM Database On The NT Server O
Aug 7, 2001My SQL 7.0 server is currently querying the SAM database on the PDC for Windows NT authentication. How can I force it to use the SAM database on the server(BDC) that I specify?
View 1 Replies View RelatedBCP - Hint Exceeds
Jan 3, 2007Query hints exceed maximum command buffer size of 1023 bytes(1029 bytes input).
i tried a long query in BCP because i have to insert a character to one of its fields.
hope somebody had encountered and solved this (sql server 2000). thanks in advance..
Possible To Use Like Hint With Sub-select?
Sep 26, 2013Is possible to use like hint with subselect? , i mean i want to find all rows in table A that contains a word in a field(CALLED CONTENT) in table B, concretely in a field called content too, i show you the idea although the syntax is incorrect.
select
' + char (39) + @country + char (39) + ' as PAIS,
A.ID,
A.IDUSUARIO MSISDN,
NULL AS MSISDN_COD,
convert(char(19),A.FECHA_ALVENTO, 121) AS FECHA_MO_LOCAL,
NULL AS FECHA_MO_LOCAL_D,
[code]....
When To Use The NOLOCK Hint.
Jul 20, 2005Background:I am currently working on a mission critical web based applicationthat is accessed 24 hours a day by users from just about every timezone. We use MS SQL Server as our database and we have lots ofproblems with time-outs. We used to have lots of problems with locksuntil my management decided that we would use the WITH (NOLOCK) hinton EVERY select statement and WITH (ROWLOCK) on EVERY updatestatement. I have argued since the beginning that the NOLOCK hintshould be the exception and not the rule. Meanwhile we continue tohave problems related to time-outs.Problem:I'm the one that they call when there are time-out errors.I am a programmer first and a DBA when I have to be. I'd really liketo hear from some of you who are the opposite. I realize that thereare many factors that contribute to slow response from a databaseserver (indexes, RAM, disk speed, etc.), but what I really need tohear from an expert is whether or not using NOLOCK on **EVERY** queryin a 30GB database that has 344 tables is a bad idea.Thanks in advance,Stephen McMahonJoin Bytes!
View 2 Replies View RelatedWhy Does My Query Timeout Unless Force Join To Hash Join?
Jul 25, 2007I'm using SQL Server 2005.
A piece of software I wrote starting timing out on a query that left outer joins a table to a view. Both the table and view have approximately the same number of rows (about 170000).
The table has 2 very similar columns, one is a varchar(1) and another is varchar(100). Neither are included in any index and beyond the size difference, the columns have the same properties. One of the employees here uses the varchar(1) column (called miscsearch) to tag large sets of rows to perform some action on. In this case, he had set 9000 rows miscsearch value to "g". The query then should join the table and view for all rows where miscsearch is set to g in the table. This query takes at least 20 minutes to run (I stopped it at this point).
If I remove the "where" clause and join all rows in the two tables, the query completes in about 20 seconds. If set the varchar(100) column (called descrip) to "g" for the same rows set via miscsearch, the query completes in about 20 seconds.
If I force the join type to a hash join, the query completes using miscsearch in about 30 seconds.
So, this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER HASH JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
and this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE descrip = 'g' ORDER BY balance DESC
But this does't:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
What should I be looking for here to understand why this is happening?
Thanks,
john
NOLOCK Hint On Views?
Nov 27, 2007Hi all
If i have a view:
CREATE VIEW vw_Users
AS
SELECT * FROM Users WITH(NOLOCK)
Is it suggested to use nolock in views?
And if i needed to use this view in stored procs is it then suggested to apply the nolock hint?
CREATE PROC [dbo] .[usp_GetCompanyUsers]
AS
SELECT * FROM Companies WITH(NOLOCK) JOIN
vw_Users WITH(NOLOCK) --<< ---is this suggested?
Lock Hint Question
Jul 20, 2005Hi All,I have a question about lock hint for you :If the first user currently run a select commandwith share lock and hold it. What kind oflock (lock hint) should be used by the second userin the select command (from the same table) so thatthis command will wait until the first user releasesthe lock ?I have tried using tablock, tablockx, xlock andupdlock hint in the select command for the seconduser, but it is not successful. Below is myunsuccessful test :The first user :Begin Transactionselect fprefix from ut1 where fprefix = '000'The second user :select * from ut1 with (tablockx)Please help meThanks in advanceAnita Hery*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies View RelatedNo Lock Hint - Across Servers
Aug 1, 2007Can I use the NO Lock hint when running a SELECT against a view that points to another server? Will the no lock "travel" to the other server through the view?
amk
Index Hint Syntax Help
Sep 28, 2007Can someone give me the right syntax for using an index hint in a delete statement as below?
DELETE table1 WITH (INDEX('indexname_IX'))
WHERE datecolumn < @datevariable
I get this error message:
Incorrect syntax near the keyword 'index'.
I'm using Query Analyzer in SQL 2000 Enterprise.
Thanks,
Gerald
Substring (Need Help) , Clue, Hint , Solution....?
Apr 10, 2002Hi there,
I need to get back the actual characters (without trailing blanks) contained in a char(43) field.
So i tried:
- substring(fieldname,1,len(fieldname)) which yields a 43 char column
- using a variable that contains the actual length in
substring(fieldname,1,@nchar)) the same.
- tricking by concatenating the resulting string with a dummy like "" didn't
work either.
So apparently I am at a dead end.
Anybody a clue, hint or solution????
Thanks to all contributors
Can You Place An Exclusive Row Lock Using A Hint?
Feb 21, 2000Hi,
Is it possible to place an exclusive row lock when running a SELECT query by using a lock hint (or otherwise).
Basically, when a select statement is run against a table I don't won't any other users to read that row until it has been updated - at some later stage.
Any suggestions on whether this is possible would be welcome.
Thanks,
Karl
Index Hint In Delete Statement?
Jul 12, 2006According to what I see in BOL, the following should work:
delete
from dbo.tbl1 WITH (INDEX(idx_un01))
where tbl1_no = 1
Yet when I syntax check this I get:
Msg 1069, Level 15, State 1, Line 2
Index hints are only allowed in a FROM clause.
(Please ignore the fact that index hints are unnecessary / a bad idea / etc.)
Renaming Databases With Grace, Any Hint ?
Aug 7, 2007Hi,
I want to implement the following pattern (pseudo-code follows):
[for index in x different databases, where x is rather big]
create database MyDatabase_#{index}_Temp
launch a dedicated ssis package on this database
if I detect no error, proudly rename MyDatabase_#{index}_Temp to MyDatabase_#{index}_Last_Known_Good (drop the last_known_good first if it exist)
Later on, a consolidation process collect the data in Last_Known_Good versions of the databases.
My question: what is the simpliest way to achieve the rename operation (step 3) ?
I have tried an "alter database #{old_database_name} modify name = #{new_database_name}", but it works only once: once renamed, the database keeps the old mdf and ldf filenames, and the next create database will choke on this.
I have also tried to rename the logical filenames with ALTER DATABASE XXX MODIFY FILE (NAME = YYY, NEWNAME = ZZZ) but it doesn't work either.
I'd like to avoid specifying absolute mdf and ldf filenames myself, is it possible ?
best,
Thibaut
NOLOCK Optimizer Hint On Iterator
Oct 25, 2006An interesting discussion yesterday. One of the programmers asked about the use of the NOLOCK optimizer hint with an iterator table aka table of numbers. His comment was that this optimizer hint was not efficient. Rather than give a knee-jerk response I thought it would be better to ask. The main circumstance is that the iterator table is completely static with a fill factor of 100%. My purpose is to eliminate lock contention if I can.
Are there reasons to not use the NOLOCK hint in this case to potentially improve performance?
Dave
Comment Thread For This Forum's Hint Sticky
Sep 27, 2005My God! What happens if I miss a step, or put it in a different order??? I know I can specify ORDER BY StepID, but what about a missing step? I do have "missing ranges" script, but posting questions filtered through the script process may become a full-time job in itself...What to do, what to do...
View 14 Replies View RelatedLocking Hint Problem On Multi-servers
Jul 20, 2005Hi All,I want to lock 2 tables on 2 servers using TABLOCKXhint. These tables function as semaphores in myapplication. It means when the tables are lockedthen other users will not be able to access themand automatically they can not continue their works.I have tried using the following code, but itdoes not work. I always got the error :"Cannot specify an index or locking hint fora remote data source" on the select command.begin transelect * from server1.accounting.dbo.semtabwith (tablockx) where fprefix = '000'select * from server2.accounting.dbo.semtabwith (tablockx) where fprefix = '000'--commit tranThe error will disappear if I remove the servername, like this :select * from accounting.dbo.semtabwith (tablockx) where fprefix = '000'But, it does not meet my requirement.It only locks one table on one server(default).Please help meThanks in advanceJohn S.*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
View 9 Replies View RelatedSelect Col42 From Tbl With (NOT INDEX (myidx)) - Hint Saying Do Not Use Possible?
Nov 28, 2007
Hello!
I would like when I compare query plans to be able to compare
2 queries where the 1. is the "normal" version and the 2. the version where I forced compiler
not to use a specific index (i don't want to force at table scan, so hint index(0) can't be used).
The only way I see how I could achieve something similar is to to drop the index and compare response time
before and after. But building index could be time consuming...
Is it possible?
select col42 from tbl;
select col42 from tbl with...
pressing CTRL+L
Greetings
Bjørn
Parent Child Relationship Column Hint?
Feb 22, 2007I've got a dilemma which I hope someone has a solution to.
Let's say we're building a data mining model to predict aircraft reliability. In the training table we've got a column (among many others) with a unique aircraft ID, and then a column for the type (737,747) and then a column for the series (100,200,300). I.E. A 737-800 series would be "737" and "800".
There is in essence a parent-child relationship between these 2 columns. 737's should share a common set of reliability factors, and then those factors might be further defined by the series number (for instance, the 737 might have very reliable radar except for the 500 series). The series is analogous to what model year a car is. What I want to make sure doesn't happen is for the system to correlate a 747-400 and a 737-400 because they are the same series. They are totally independent if the model number is different.
My only idea was to merge the columns and have a single value "737-100". But it would seem then that the model won't have any idea that a "737-100" and "737-200" should have a lot more in common than a "737-100" because the values will be completely different.
I was hoping to find some sort of parent-child hint in the column properties but found none.
What solutions have other people tried? It sure seems that there should be an elegant solution for something like, but I'm missing it.
Geof
SqlServer 2005 + Cluster =&&> Very Slow, Any Hint ?
Apr 25, 2007Hello
we have two SqlServer 2005 in cluster. The machines act very slow (although the CPU load is low) as soon as we achieve one of these operations:
- drop / create database
- restore database
We achieve those operations through the SqlServer Browser or via sqlcmd.
Everything else is running smoothly.
Is there any known reason on why it can be so slow ?
best regards
Thibaut
(hope this is not too OT, but could not find any cluster-specific forum).
Stuck With Login Failed For User XxxxASPNET Error, Need A Hint Please
Jul 11, 2006Hi,
I am stuck with that error when running my ASP.NET application on my production server for many weeks now and still don't have a complete solution to that problem.
I formerly posted that thread and read that other thread without success, so as I have made a few progress in delimiting the problem I am posting this new thread with the hope somebody will finally be able to help me.
To summarize:
1) I have developed an ASP.NET application,
- on a Windows XP Pro SP2 computer
- with VS studio 2005 Dev and included SQL 2005 engine
- this application works fine on the dev computer
2) I have deployed the application on a production server which is:
- a Windows 2000 SP 4 computer
- with SQL Express SP1 with Advance services installed
When I launch the prod application I have this error:
Login failed for user xxxxASPNET
I have found that I can bypass this error by doing the following:
1) manually attach my .mdf database with SQL Server Management Studio Express and specifying the field Attached as with myDatabaseName (in place of the default fullPhysicalPath/myDatabaseName.MDF) then detach it
2) change the auto generated connection string in web.config file from
<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|myDatabaseName.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
To
<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|myDatabaseName.mdf;User ID=sa;Password=mySAPassword;database=myDatabaseName" providerName="System.Data.SqlClient"/>
That would have been an acceptable substitution solution but unfortunately my
application also uses the new Login and Webparts features of VS 2005 which implies the automatic creation of an ASPNETDB.MDF database by VS.
As I have no mean (or at least don't know how) of modifying the associated Connection String for that database (it does not appear in web.config) I cannot bypass the problem for it and thus the error still appears when the application try to use this database.
Please I really need help on this matter, I can provide any other useful information as needed (logs, sources ...)