January 18, 2019 at 6:30 pm
Team,
I have this query that runs quite a long time, actually the result is not been displayed at all.
The Query:
SELECT distinct o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM
CR2Copy..OBJECT_TO_ATTRIBUTE (NOLOCK) o2a WHERE ( 1=1 ) AND ( 1=1 )
AND o2a.STATUS_ID = 33000000000001 AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID in
( 82000000000018,82000000000092,82000000000095,82000000000096,82000000000316,82000000000320,82000000000329,82000000000331,82000000000334,82000000000335,
82000000000340,82000000000341,82000000000603,82000000000605,82000000000611,82000000000613,82000000000614,82000000000615,82000000000616,82000000000617,
82000000000618,82000000000620,82000000000621,82000000000623,82000000000625,82000000000626,82000000000638,82000000000639,82000000000640 )
I had to cancel the query after executing for more than 8 hours(no blockings seen, only CPU and IO values were shown high when running sp_who2 active)
I was unable to get the actual execution plan so attached is the estimated execution plan for the same.
The main table here OBJECT_TO_ATTRIBUTE has around 2 billion records.
I also couldn't see any fragmentation of the index as the query I use also keeps running for ever(I think if we rebuild index things should be faster)
I updated statistics for table OBJECT_TO_ATTRIBUTE, but still no luck.
All the indexes that are in the table are below for your reference:
-------------------
ALTER TABLE [dbo].[OBJECT_TO_ATTRIBUTE] ADD CONSTRAINT [OBJECT_TO_ATTRIBUTE_PK] PRIMARY KEY CLUSTERED
(
[OBJECT_TO_ATTRIBUTE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
-----------------------------
CREATE NONCLUSTERED INDEX [IX_ROOT_OBJECT_ID] ON [dbo].[OBJECT_TO_ATTRIBUTE]
(
[ROOT_OBJECT_ID] ASC,
[STATUS_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
GO
-----------------------------------
CREATE NONCLUSTERED INDEX [IX_PROPERTY_ID] ON [dbo].[OBJECT_TO_ATTRIBUTE]
(
[PROPERTY_ID] ASC,
[STATUS_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
GO
-----------------------------
CREATE NONCLUSTERED INDEX [IX_PROPERTY__SOURCE_ID] ON [dbo].[OBJECT_TO_ATTRIBUTE]
(
[PROPERTY_ID] ASC,
[STATUS_ID] ASC,
[SOURCE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
GO
--------------------------------
Any suggestions by experts that I can make use of will be highly appreciated. Let me know if you need any more details. thanks
January 18, 2019 at 7:09 pm
DISTINCT is going to be brutal slow.
AND o2a.STATUS_ID = 33000000000001 AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID
and then are those columns indexed? (maybe included in PK index?)
January 18, 2019 at 7:37 pm
I see what can be done to avoid distinct? Group by or something like that? Yeah those columns are included in the non clustered index
January 19, 2019 at 1:50 am
missing index as per explain plan might help a bit there.
you could try changing the index to IX_PROPERTY__SOURCE_ID be as follows
create nonclustered index IX_PROPERTY__SOURCE_ID on dbo.OBJECT_TO_ATTRIBUTE
(PROPERTY_ID asc
, STATUS_ID asc
, SOURCE_ID asc
)
include (VALUE) -- add include of missing column
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = off
, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
either drop existing and create as per above, or set drop_existing=on to create it.
Also and as you are trying to display an estimated 32 million rows ... change your query to insert into a temp table before querying the results
if object_id('tempdb..#tempvalues') is not null
drop table #tempvalues;
select distinct o2a.source_id source_id
, o2a.value extdata
into #tempvalues
from CR2Copy..OBJECT_TO_ATTRIBUTE(NOLOCK) o2a
where (1 = 1)
and (1 = 1)
and o2a.STATUS_ID = 33000000000001
and o2a.PROPERTY_ID = 1000000060338
and o2a.source_id in
(82000000000018, 82000000000092, 82000000000095, 82000000000096, 82000000000316, 82000000000320, 82000000000329, 82000000000331, 82000000000334, 82000000000335,
82000000000340, 82000000000341, 82000000000603, 82000000000605, 82000000000611, 82000000000613, 82000000000614, 82000000000615, 82000000000616, 82000000000617,
82000000000618, 82000000000620, 82000000000621, 82000000000623, 82000000000625, 82000000000626, 82000000000638, 82000000000639, 82000000000640)
on other note - get ride of that nolock and ensure you do explicitly reference the table schema
January 19, 2019 at 4:42 am
Thank you dear i’ll try the suggestions out..
January 19, 2019 at 7:21 am
8 hours sounds too much that simple query with that execution plan.
How many rows are on your table?
How big are the columns?
How many rows do you expect the query to retrieve?
What hardware are you running on?
January 20, 2019 at 2:37 am
frederico_fonseca - Saturday, January 19, 2019 1:50 AMmissing index as per explain plan might help a bit there.you could try changing the index to IX_PROPERTY__SOURCE_ID be as follows
create nonclustered index IX_PROPERTY__SOURCE_ID on dbo.OBJECT_TO_ATTRIBUTE(PROPERTY_ID asc
, STATUS_ID asc
, SOURCE_ID asc
)
include (VALUE) -- add include of missing column
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = off
, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)either drop existing and create as per above, or set drop_existing=on to create it.
Also and as you are trying to display an estimated 32 million rows ... change your query to insert into a temp table before querying the results
if object_id('tempdb..#tempvalues') is not null
drop table #tempvalues;select distinct o2a.source_id source_id
, o2a.value extdata
into #tempvalues
from CR2Copy..OBJECT_TO_ATTRIBUTE(NOLOCK) o2a
where (1 = 1)
and (1 = 1)
and o2a.STATUS_ID = 33000000000001
and o2a.PROPERTY_ID = 1000000060338
and o2a.source_id in
(82000000000018, 82000000000092, 82000000000095, 82000000000096, 82000000000316, 82000000000320, 82000000000329, 82000000000331, 82000000000334, 82000000000335,
82000000000340, 82000000000341, 82000000000603, 82000000000605, 82000000000611, 82000000000613, 82000000000614, 82000000000615, 82000000000616, 82000000000617,
82000000000618, 82000000000620, 82000000000621, 82000000000623, 82000000000625, 82000000000626, 82000000000638, 82000000000639, 82000000000640)on other note - get ride of that nolock and ensure you do explicitly reference the table schema
I am just curious to know what where (1 = 1) and (1 = 1) is doing in the query. Is where (1 = 1) and (1 = 1) mandatory for above mentioned query?
Saravanan
January 20, 2019 at 2:55 am
Seen this too many times:pinch:
😎
Distinct sort for 380.000.000+ rows in parallel operation will kill most servers!
Try this
;WITH SOURCE_IDS (SOURCE_ID) AS
(
SELECT X.SOURCE_ID FROM
(VALUES
(82000000000018)
,(82000000000092)
,(82000000000095)
,(82000000000096)
,(82000000000316)
,(82000000000320)
,(82000000000329)
,(82000000000331)
,(82000000000334)
,(82000000000335)
,(82000000000340)
,(82000000000341)
,(82000000000603)
,(82000000000605)
,(82000000000611)
,(82000000000613)
,(82000000000614)
,(82000000000615)
,(82000000000616)
,(82000000000617)
,(82000000000618)
,(82000000000620)
,(82000000000621)
,(82000000000623)
,(82000000000625)
,(82000000000626)
,(82000000000638)
,(82000000000639)
,(82000000000640)
) X(SOURCE_ID)
)
,BASE_DATA AS
(
SELECT
o2a.SOURCE_ID source_id
, o2a.VALUE extdata
FROM
CR2Copy..OBJECT_TO_ATTRIBUTE o2a
INNER JOIN SOURCE_IDS SI
ON o2a.SOURCE_ID = SI.SOURCE_ID
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
)
SELECT
DISTINCT
BD.source_id
,BD.extdata
FROM BASE_DATA BD
OPTION (MAXDOP 1);
January 20, 2019 at 6:36 am
Eirikur Eiriksson - Sunday, January 20, 2019 2:55 AMSeen this too many times:pinch:
😎
Distinct sort for 380.000.000+ rows in parallel operation will kill most servers!Try this
;WITH SOURCE_IDS (SOURCE_ID) AS
(
SELECT X.SOURCE_ID FROM
(VALUES
(82000000000018)
,(82000000000092)
,(82000000000095)
,(82000000000096)
,(82000000000316)
,(82000000000320)
,(82000000000329)
,(82000000000331)
,(82000000000334)
,(82000000000335)
,(82000000000340)
,(82000000000341)
,(82000000000603)
,(82000000000605)
,(82000000000611)
,(82000000000613)
,(82000000000614)
,(82000000000615)
,(82000000000616)
,(82000000000617)
,(82000000000618)
,(82000000000620)
,(82000000000621)
,(82000000000623)
,(82000000000625)
,(82000000000626)
,(82000000000638)
,(82000000000639)
,(82000000000640)
) X(SOURCE_ID)
)
,BASE_DATA AS
(
SELECT
o2a.SOURCE_ID source_id
, o2a.VALUE extdata
FROM
CR2Copy..OBJECT_TO_ATTRIBUTE o2a
INNER JOIN SOURCE_IDS SI
ON o2a.SOURCE_ID = SI.SOURCE_ID
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
)
SELECT
DISTINCT
BD.source_id
,BD.extdata
FROM BASE_DATA BD
OPTION (MAXDOP 1);
Thank you sir, I'll try out and let you know
January 20, 2019 at 6:38 am
saravanatn - Sunday, January 20, 2019 2:37 AMfrederico_fonseca - Saturday, January 19, 2019 1:50 AMmissing index as per explain plan might help a bit there.you could try changing the index to IX_PROPERTY__SOURCE_ID be as follows
create nonclustered index IX_PROPERTY__SOURCE_ID on dbo.OBJECT_TO_ATTRIBUTE(PROPERTY_ID asc
, STATUS_ID asc
, SOURCE_ID asc
)
include (VALUE) -- add include of missing column
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = off
, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)either drop existing and create as per above, or set drop_existing=on to create it.
Also and as you are trying to display an estimated 32 million rows ... change your query to insert into a temp table before querying the results
if object_id('tempdb..#tempvalues') is not null
drop table #tempvalues;select distinct o2a.source_id source_id
, o2a.value extdata
into #tempvalues
from CR2Copy..OBJECT_TO_ATTRIBUTE(NOLOCK) o2a
where (1 = 1)
and (1 = 1)
and o2a.STATUS_ID = 33000000000001
and o2a.PROPERTY_ID = 1000000060338
and o2a.source_id in
(82000000000018, 82000000000092, 82000000000095, 82000000000096, 82000000000316, 82000000000320, 82000000000329, 82000000000331, 82000000000334, 82000000000335,
82000000000340, 82000000000341, 82000000000603, 82000000000605, 82000000000611, 82000000000613, 82000000000614, 82000000000615, 82000000000616, 82000000000617,
82000000000618, 82000000000620, 82000000000621, 82000000000623, 82000000000625, 82000000000626, 82000000000638, 82000000000639, 82000000000640)on other note - get ride of that nolock and ensure you do explicitly reference the table schema
I am just curious to know what
where (1 = 1) and (1 = 1)is doing in the query. Is where (1 = 1) and (1 = 1) mandatory for above mentioned query?
thank you sir, will try out your suggestions
January 20, 2019 at 6:43 am
Jonathan AC Roberts - Saturday, January 19, 2019 7:21 AM8 hours sounds too much that simple query with that execution plan.
How many rows are on your table?
How big are the columns?
How many rows do you expect the query to retrieve?
What hardware are you running on?
Please see the details requested:
How many rows are on your table? => around 2 billion
How big are the columns? =>
COLUMN_NAME DATA_TYPE
OBJECT_TO_ATTRIBUTE_ID bigint
ROOT_OBJECT_ID bigint
OBJECT_ID bigint
PROPERTY_ID bigint
VALUE_ID bigint
VALUE nvarchar
WEIGHT int
SORT int
LANGUAGE_CONTEXT_ID bigint
SOURCE_ID bigint
STATUS_ID bigint
DESTINATION_ID bigint
CDATE datetime
UDATE datetime
SESSION_ID bigint
How many rows do you expect the query to retrieve? ==> not very sure as the query just keeps running, and since this is the very first time we trying.
What hardware are you running on? => 64 GB memory, 16 processor
January 20, 2019 at 9:26 am
Tomys - Sunday, January 20, 2019 6:43 AMJonathan AC Roberts - Saturday, January 19, 2019 7:21 AM8 hours sounds too much that simple query with that execution plan.
How many rows are on your table?
How big are the columns?
How many rows do you expect the query to retrieve?
What hardware are you running on?Please see the details requested:
How many rows are on your table? => around 2 billion
How big are the columns? =>
COLUMN_NAME DATA_TYPE
OBJECT_TO_ATTRIBUTE_ID bigint
ROOT_OBJECT_ID bigint
OBJECT_ID bigint
PROPERTY_ID bigint
VALUE_ID bigint
VALUE nvarchar
WEIGHT int
SORT int
LANGUAGE_CONTEXT_ID bigint
SOURCE_ID bigint
STATUS_ID bigint
DESTINATION_ID bigint
CDATE datetime
UDATE datetime
SESSION_ID bigint
How many rows do you expect the query to retrieve? ==> not very sure as the query just keeps running, and since this is the very first time we trying.
What hardware are you running on? => 64 GB memory, 16 processor
VALUE nvarchar
So it's nvarchar(1) or you haven't put the length?
It would be nice to know the average length of the data in this column.
January 21, 2019 at 6:45 am
Jonathan AC Roberts - Sunday, January 20, 2019 9:26 AMTomys - Sunday, January 20, 2019 6:43 AMJonathan AC Roberts - Saturday, January 19, 2019 7:21 AM8 hours sounds too much that simple query with that execution plan.
How many rows are on your table?
How big are the columns?
How many rows do you expect the query to retrieve?
What hardware are you running on?Please see the details requested:
How many rows are on your table? => around 2 billion
How big are the columns? =>
COLUMN_NAME DATA_TYPE
OBJECT_TO_ATTRIBUTE_ID bigint
ROOT_OBJECT_ID bigint
OBJECT_ID bigint
PROPERTY_ID bigint
VALUE_ID bigint
VALUE nvarchar
WEIGHT int
SORT int
LANGUAGE_CONTEXT_ID bigint
SOURCE_ID bigint
STATUS_ID bigint
DESTINATION_ID bigint
CDATE datetime
UDATE datetime
SESSION_ID bigint
How many rows do you expect the query to retrieve? ==> not very sure as the query just keeps running, and since this is the very first time we trying.
What hardware are you running on? => 64 GB memory, 16 processorVALUE nvarchar
So it's nvarchar(1) or you haven't put the length?
It would be nice to know the average length of the data in this column.
sorry for the delay in response: VALUE is nvarchar (max)
January 21, 2019 at 6:54 am
Tomys - Monday, January 21, 2019 6:45 AMJonathan AC Roberts - Sunday, January 20, 2019 9:26 AMTomys - Sunday, January 20, 2019 6:43 AMJonathan AC Roberts - Saturday, January 19, 2019 7:21 AM8 hours sounds too much that simple query with that execution plan.
How many rows are on your table?
How big are the columns?
How many rows do you expect the query to retrieve?
What hardware are you running on?Please see the details requested:
How many rows are on your table? => around 2 billion
How big are the columns? =>
COLUMN_NAME DATA_TYPE
OBJECT_TO_ATTRIBUTE_ID bigint
ROOT_OBJECT_ID bigint
OBJECT_ID bigint
PROPERTY_ID bigint
VALUE_ID bigint
VALUE nvarchar
WEIGHT int
SORT int
LANGUAGE_CONTEXT_ID bigint
SOURCE_ID bigint
STATUS_ID bigint
DESTINATION_ID bigint
CDATE datetime
UDATE datetime
SESSION_ID bigint
How many rows do you expect the query to retrieve? ==> not very sure as the query just keeps running, and since this is the very first time we trying.
What hardware are you running on? => 64 GB memory, 16 processorVALUE nvarchar
So it's nvarchar(1) or you haven't put the length?
It would be nice to know the average length of the data in this column.sorry for the delay in response: VALUE is nvarchar (max)
Now I'm wondering what the average length of the value column is. Can you run this query?
;with cte as
(select top(1000000) len(x.Value) lenText
from CR2Copy..OBJECT_TO_ATTRIBUTE x)
select avg(lenText) from cte
January 21, 2019 at 7:03 am
Jonathan AC Roberts - Monday, January 21, 2019 6:54 AMTomys - Monday, January 21, 2019 6:45 AMJonathan AC Roberts - Sunday, January 20, 2019 9:26 AMTomys - Sunday, January 20, 2019 6:43 AMJonathan AC Roberts - Saturday, January 19, 2019 7:21 AM8 hours sounds too much that simple query with that execution plan.
How many rows are on your table?
How big are the columns?
How many rows do you expect the query to retrieve?
What hardware are you running on?Please see the details requested:
How many rows are on your table? => around 2 billion
How big are the columns? =>
COLUMN_NAME DATA_TYPE
OBJECT_TO_ATTRIBUTE_ID bigint
ROOT_OBJECT_ID bigint
OBJECT_ID bigint
PROPERTY_ID bigint
VALUE_ID bigint
VALUE nvarchar
WEIGHT int
SORT int
LANGUAGE_CONTEXT_ID bigint
SOURCE_ID bigint
STATUS_ID bigint
DESTINATION_ID bigint
CDATE datetime
UDATE datetime
SESSION_ID bigint
How many rows do you expect the query to retrieve? ==> not very sure as the query just keeps running, and since this is the very first time we trying.
What hardware are you running on? => 64 GB memory, 16 processorVALUE nvarchar
So it's nvarchar(1) or you haven't put the length?
It would be nice to know the average length of the data in this column.sorry for the delay in response: VALUE is nvarchar (max)
Now I'm wondering what the average length of the value column is. Can you run this query?
;with cte as
(select top(1000000) len(x.Value) lenText
from CR2Copy..OBJECT_TO_ATTRIBUTE x)
select avg(lenText) from cte
Hi Sir, I ran the query the output is :6
Viewing 15 posts - 1 through 15 (of 55 total)
You must be logged in to reply to this topic. Login to reply