January 4, 2018 at 1:34 pm
Hamid-Sadeghian - Thursday, January 4, 2018 6:47 AM...unfortunately I cannot change the query...
If it is on an Enterprise Edition you can try using a Plan Guide - https://docs.microsoft.com/en-us/sql/relational-databases/performance/plan-guides
If I were you I would try this hint - @hints = N'OPTION(HASH JOIN)'. It should give the better performance with this number of records compare to the NESTED LOOPS.
January 4, 2018 at 1:37 pm
Hamid-Sadeghian - Thursday, January 4, 2018 8:20 AMThe plan is create on single thread but subtree cost is more than 5 but I don't know why the plan is not parallel?
Try to force a hash join as I described above.
January 4, 2018 at 3:05 pm
[Code]Create Index IXTEST2 on SDE_Geometry9 (sde_State_ID)
with(drop_existing=on)[/code]
You missed the second column - Geometry_ID - in this index
_____________
Code for TallyGenerator
January 4, 2018 at 4:34 pm
Hamid-Sadeghian - Thursday, January 4, 2018 1:51 AMI cannot change the query because this is send from ARCGIS application.
There's a pretty good chance that no amount of indexing will fix the query. My recommendation is to rewrite the code (possibly adding indexes) and send it to ARCGIS to have them incorporate the change IF you think its running too slow.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 4, 2018 at 8:25 pm
Evgeny - Thursday, January 4, 2018 1:34 PMHamid-Sadeghian - Thursday, January 4, 2018 6:47 AM...unfortunately I cannot change the query...If it is on an Enterprise Edition you can try using a Plan Guide - https://docs.microsoft.com/en-us/sql/relational-databases/performance/plan-guides
If I were you I would try this hint - @hints = N'OPTION(HASH JOIN)'. It should give the better performance with this number of records compare to the NESTED LOOPS.
This query send from application and I cannot change it.because I don't have access to source code of application. Also this query is adhoc and not sp.
January 4, 2018 at 8:26 pm
Sergiy - Thursday, January 4, 2018 3:05 PM[Code]Create Index IXTEST2 on SDE_Geometry9 (sde_State_ID)with(drop_existing=on)[/code]You missed the second column - Geometry_ID - in this index
Thank you. with add this field to index the plan is the same as before.
January 5, 2018 at 5:57 am
Hamid-Sadeghian - Thursday, January 4, 2018 8:26 PMSergiy - Thursday, January 4, 2018 3:05 PM[Code]Create Index IXTEST2 on SDE_Geometry9 (sde_State_ID)with(drop_existing=on)[/code]You missed the second column - Geometry_ID - in this indexThank you. with add this field to index the plan is the same as before.
Because the problem is with the code. You're going to have to find a way to attack that. Indexing and statistics are not going to fix the fundamental issue of a poorly structured query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 5, 2018 at 6:04 am
Grant Fritchey - Friday, January 5, 2018 5:57 AMHamid-Sadeghian - Thursday, January 4, 2018 8:26 PMSergiy - Thursday, January 4, 2018 3:05 PM[Code]Create Index IXTEST2 on SDE_Geometry9 (sde_State_ID)with(drop_existing=on)[/code]You missed the second column - Geometry_ID - in this indexThank you. with add this field to index the plan is the same as before.
Because the problem is with the code. You're going to have to find a way to attack that. Indexing and statistics are not going to fix the fundamental issue of a poorly structured query.
Thanks
Yes I know that the problem is from the code. but I don't know how to resolve that. Do you have any suggestion?
January 5, 2018 at 6:11 am
Hamid-Sadeghian - Friday, January 5, 2018 6:04 AMThanks
Yes I know that the problem is from the code. but I don't know how to resolve that. Do you have any suggestion?
It's code generated from a product. You need to go to the product, the developers and users of the product, and work it from that angle. Most (unfortunately not all) systems have more than one way to get things done. The way that the system you're working with currently is generating code is causing substantial problems. You need to understand how that tool works and other mechanisms available within the tool that can enable to you directly address what the underlying issue is.
Looking it up, ArcGis, which you state is the product, does have mechanisms for inputting T-SQL directly. There's your answer.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 5, 2018 at 6:23 am
Grant Fritchey - Friday, January 5, 2018 6:11 AMHamid-Sadeghian - Friday, January 5, 2018 6:04 AMThanks
Yes I know that the problem is from the code. but I don't know how to resolve that. Do you have any suggestion?It's code generated from a product. You need to go to the product, the developers and users of the product, and work it from that angle. Most (unfortunately not all) systems have more than one way to get things done. The way that the system you're working with currently is generating code is causing substantial problems. You need to understand how that tool works and other mechanisms available within the tool that can enable to you directly address what the underlying issue is.
Looking it up, ArcGis, which you state is the product, does have mechanisms for inputting T-SQL directly. There's your answer.
Thank you a lot.
January 5, 2018 at 6:25 am
What is this query doing?
Can the process of getting data into the tables be changed so that the delete is not necessary?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2018 at 6:33 am
Hamid-Sadeghian - Thursday, January 4, 2018 8:12 AMChrisM@Work - Thursday, January 4, 2018 7:33 AMHamid-Sadeghian - Thursday, January 4, 2018 5:50 AMthank you,
This index already exist on this table. but I think has a problem with statistics of this index.
at network_v3 table, field objectID has a 1493760 distinct values but histogram for this column is :
RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
1 0 1 0 1
1493760 1493758 1 1493758 1
Is this statistics correct?Can you please post the CREATE INDEX statement?
These are indexes that I create on all tables in query.
Create unique index IXTEST on a14(objectid,SDE_State_id)
Create Index IXTEST2 on SDE_Geometry9 (Geometry_ID,sde_State_ID)
Create Index IXTEST3 on SDE_Geometry9 (Geometry_ID)
Create unique Index IXTEST4 on network_v3 (objectid)
Delete gm
FROM AliTest2.DBO.SDE_GEOMETRY9 as gm
WHERE NOT EXISTS ( SELECT objectid,0
FROM AliTest2.DBO.NETWORK_V3 as v3
WHERE v3.OBJECTID = gm.GEOMETRY_ID
AND gm.SDE_STATE_ID = 0
UNION ALL
SELECT a.OBJECTID,a.SDE_STATE_ID
FROM AliTest2.DBO.a14 as a
WHERE a.OBJECTID = gm.GEOMETRY_ID
AND a.SDE_STATE_ID = gm.SDE_STATE_ID)
I think this query is different from your sample because more than one table is used and two fields of tables used in where clause.
Edit: Here's an awesome reference:
https://www.red-gate.com/simple-talk/sql/learn-sql-server/operator-of-the-week-index-spool/
/Edit
IF 0 = 1 BEGIN
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 5, 2018 at 9:14 am
ChrisM@Work - Friday, January 5, 2018 6:33 AMHamid-Sadeghian - Thursday, January 4, 2018 8:12 AMChrisM@Work - Thursday, January 4, 2018 7:33 AMHamid-Sadeghian - Thursday, January 4, 2018 5:50 AMthank you,
This index already exist on this table. but I think has a problem with statistics of this index.
at network_v3 table, field objectID has a 1493760 distinct values but histogram for this column is :
RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
1 0 1 0 1
1493760 1493758 1 1493758 1
Is this statistics correct?Can you please post the CREATE INDEX statement?
These are indexes that I create on all tables in query.
Create unique index IXTEST on a14(objectid,SDE_State_id)
Create Index IXTEST2 on SDE_Geometry9 (Geometry_ID,sde_State_ID)
Create Index IXTEST3 on SDE_Geometry9 (Geometry_ID)
Create unique Index IXTEST4 on network_v3 (objectid)
Delete gm
FROM AliTest2.DBO.SDE_GEOMETRY9 as gm
WHERE NOT EXISTS ( SELECT objectid,0
FROM AliTest2.DBO.NETWORK_V3 as v3
WHERE v3.OBJECTID = gm.GEOMETRY_ID
AND gm.SDE_STATE_ID = 0
UNION ALL
SELECT a.OBJECTID,a.SDE_STATE_ID
FROM AliTest2.DBO.a14 as a
WHERE a.OBJECTID = gm.GEOMETRY_ID
AND a.SDE_STATE_ID = gm.SDE_STATE_ID)
I think this query is different from your sample because more than one table is used and two fields of tables used in where clause.Here's a more accurate model of your tables and of the query too. Essentially the results are the same: creating a couple of indexes which are sympathetic to the query results in elimination of the index spools - as you would expect - and a 50x reduction in execution time:Edit: Here's an awesome reference:
https://www.red-gate.com/simple-talk/sql/learn-sql-server/operator-of-the-week-index-spool/
/Edit
IF 0 = 1 BEGINIF OBJECT_ID('tempdb..#NETWORK_V3') IS NOT NULL DROP TABLE #NETWORK_V3;
WITH Ten AS (SELECT n FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),
Hundred AS (SELECT n = 0 FROM Ten a, Ten b),
TenThou AS (SELECT n = 0 FROM Hundred a, Hundred b)
SELECT TOP(1493760)
RowID = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
OBJECTID = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
SDE_STATE_ID = 0,
Filler1 = NEWID(),
Filler2 = NEWID(),
Filler3 = NEWID()
INTO #NETWORK_V3
FROM TenThou a, TenThou bCREATE UNIQUE CLUSTERED INDEX ucx_RowID ON #NETWORK_V3 (RowID)
-- CREATE INDEX [ix_OBJECTID] ON #NETWORK_V3 (OBJECTID) -- DROP INDEX [ix_OBJECTID] ON #NETWORK_V3;
IF OBJECT_ID('tempdb..#SDE_GEOMETRY9') IS NOT NULL DROP TABLE #SDE_GEOMETRY9;
SELECT TOP(186880)
RowID,
GEOMETRY_ID = OBJECTID,
SDE_STATE_ID,
Filler1,
Filler2,
Filler3
INTO #SDE_GEOMETRY9
FROM #NETWORK_V3CREATE CLUSTERED INDEX ucx_RowID ON #SDE_GEOMETRY9 (RowID)
-- CREATE INDEX ix_SDE_STATE_ID_GEOMETRY_ID ON #SDE_GEOMETRY9 (SDE_STATE_ID, GEOMETRY_ID)
IF OBJECT_ID('tempdb..#a14') IS NOT NULL DROP TABLE #a14;
SELECT TOP(180224)
RowID,
OBJECTID,
SDE_STATE_ID = 1,
Filler1,
Filler2,
Filler3
INTO #a14
FROM #NETWORK_V3CREATE CLUSTERED INDEX ucx_RowID ON #a14 (RowID)
-- CREATE INDEX [ix_SDE_STATE_ID_OBJECTID] ON #a14 (SDE_STATE_ID, OBJECTID) -- DROP INDEX [ix_SDE_STATE_ID_OBJECTID] ON #a14;END
SET STATISTICS IO, TIME ON
SELECT c1.GEOMETRY_ID, c1.SDE_STATE_ID
FROM #SDE_GEOMETRY9 c1
WHERE NOT EXISTS (
SELECT c2.OBJECTID, 1
FROM #NETWORK_V3 c2
WHERE c2.OBJECTID = c1.GEOMETRY_ID
AND c1.SDE_STATE_ID = 0
UNION ALL
SELECT c3.OBJECTID, c3.SDE_STATE_ID
FROM #a14 c3
WHERE c3.OBJECTID = c1.GEOMETRY_ID
AND c3.SDE_STATE_ID = c1.SDE_STATE_ID
)
SET STATISTICS IO, TIME OFF
-- without indexes 5000 ms (with filter & index spools)
-- with indexes 100 ms (with filter, no index spools)
Thank you for your code. with your sample and compare to original tables , I could find problem. If you add a geometry field on network_v3 table, your index does not work and index spool will appear.
In my table , I create a spatial index on this column but the plan does not change.
January 5, 2018 at 9:49 am
Hamid-Sadeghian - Friday, January 5, 2018 9:14 AMChrisM@Work - Friday, January 5, 2018 6:33 AMHamid-Sadeghian - Thursday, January 4, 2018 8:12 AMChrisM@Work - Thursday, January 4, 2018 7:33 AMHamid-Sadeghian - Thursday, January 4, 2018 5:50 AMthank you,
This index already exist on this table. but I think has a problem with statistics of this index.
at network_v3 table, field objectID has a 1493760 distinct values but histogram for this column is :
RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
1 0 1 0 1
1493760 1493758 1 1493758 1
Is this statistics correct?Can you please post the CREATE INDEX statement?
These are indexes that I create on all tables in query.
Create unique index IXTEST on a14(objectid,SDE_State_id)
Create Index IXTEST2 on SDE_Geometry9 (Geometry_ID,sde_State_ID)
Create Index IXTEST3 on SDE_Geometry9 (Geometry_ID)
Create unique Index IXTEST4 on network_v3 (objectid)
Delete gm
FROM AliTest2.DBO.SDE_GEOMETRY9 as gm
WHERE NOT EXISTS ( SELECT objectid,0
FROM AliTest2.DBO.NETWORK_V3 as v3
WHERE v3.OBJECTID = gm.GEOMETRY_ID
AND gm.SDE_STATE_ID = 0
UNION ALL
SELECT a.OBJECTID,a.SDE_STATE_ID
FROM AliTest2.DBO.a14 as a
WHERE a.OBJECTID = gm.GEOMETRY_ID
AND a.SDE_STATE_ID = gm.SDE_STATE_ID)
I think this query is different from your sample because more than one table is used and two fields of tables used in where clause.Here's a more accurate model of your tables and of the query too. Essentially the results are the same: creating a couple of indexes which are sympathetic to the query results in elimination of the index spools - as you would expect - and a 50x reduction in execution time:Edit: Here's an awesome reference:
https://www.red-gate.com/simple-talk/sql/learn-sql-server/operator-of-the-week-index-spool/
/Edit
IF 0 = 1 BEGINIF OBJECT_ID('tempdb..#NETWORK_V3') IS NOT NULL DROP TABLE #NETWORK_V3;
WITH Ten AS (SELECT n FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),
Hundred AS (SELECT n = 0 FROM Ten a, Ten b),
TenThou AS (SELECT n = 0 FROM Hundred a, Hundred b)
SELECT TOP(1493760)
RowID = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
OBJECTID = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
SDE_STATE_ID = 0,
Filler1 = NEWID(),
Filler2 = NEWID(),
Filler3 = NEWID()
INTO #NETWORK_V3
FROM TenThou a, TenThou bCREATE UNIQUE CLUSTERED INDEX ucx_RowID ON #NETWORK_V3 (RowID)
-- CREATE INDEX [ix_OBJECTID] ON #NETWORK_V3 (OBJECTID) -- DROP INDEX [ix_OBJECTID] ON #NETWORK_V3;
IF OBJECT_ID('tempdb..#SDE_GEOMETRY9') IS NOT NULL DROP TABLE #SDE_GEOMETRY9;
SELECT TOP(186880)
RowID,
GEOMETRY_ID = OBJECTID,
SDE_STATE_ID,
Filler1,
Filler2,
Filler3
INTO #SDE_GEOMETRY9
FROM #NETWORK_V3CREATE CLUSTERED INDEX ucx_RowID ON #SDE_GEOMETRY9 (RowID)
-- CREATE INDEX ix_SDE_STATE_ID_GEOMETRY_ID ON #SDE_GEOMETRY9 (SDE_STATE_ID, GEOMETRY_ID)
IF OBJECT_ID('tempdb..#a14') IS NOT NULL DROP TABLE #a14;
SELECT TOP(180224)
RowID,
OBJECTID,
SDE_STATE_ID = 1,
Filler1,
Filler2,
Filler3
INTO #a14
FROM #NETWORK_V3CREATE CLUSTERED INDEX ucx_RowID ON #a14 (RowID)
-- CREATE INDEX [ix_SDE_STATE_ID_OBJECTID] ON #a14 (SDE_STATE_ID, OBJECTID) -- DROP INDEX [ix_SDE_STATE_ID_OBJECTID] ON #a14;END
SET STATISTICS IO, TIME ON
SELECT c1.GEOMETRY_ID, c1.SDE_STATE_ID
FROM #SDE_GEOMETRY9 c1
WHERE NOT EXISTS (
SELECT c2.OBJECTID, 1
FROM #NETWORK_V3 c2
WHERE c2.OBJECTID = c1.GEOMETRY_ID
AND c1.SDE_STATE_ID = 0
UNION ALL
SELECT c3.OBJECTID, c3.SDE_STATE_ID
FROM #a14 c3
WHERE c3.OBJECTID = c1.GEOMETRY_ID
AND c3.SDE_STATE_ID = c1.SDE_STATE_ID
)
SET STATISTICS IO, TIME OFF
-- without indexes 5000 ms (with filter & index spools)
-- with indexes 100 ms (with filter, no index spools)Thank you for your code. with your sample and compare to original tables , I could find problem. If you add a geometry field on network_v3 table, your index does not work and index spool will appear.
In my table , I create a spatial index on this column but the plan does not change.
I'm unable to replicate this behaviour. I've added a geometry column to the network_v3 table:
IF OBJECT_ID('tempdb..#NETWORK_V3') IS NOT NULL DROP TABLE #NETWORK_V3;
WITH Ten AS (SELECT n FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),
Hundred AS (SELECT n = 0 FROM Ten a, Ten b),
TenThou AS (SELECT n = 0 FROM Hundred a, Hundred b),
Results AS (
SELECT TOP(1493760)
RowID = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
OBJECTID = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
GeomCol1 = geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0),
Filler1 = NEWID(),
Filler2 = NEWID(),
Filler3 = NEWID(),
Filler4 = NEWID(),
Filler5 = NEWID(),
Filler6 = NEWID()
FROM TenThou a, TenThou b)
SELECT *,
SDE_STATE_ID = CASE WHEN RowID <= 186880 THEN 0 ELSE 1 END
INTO #NETWORK_V3
FROM Results
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 5, 2018 at 10:08 am
There's a rowcount Top operator in your plan with only one reason I can think of: SET ROWCOUNT issued before the query.
When I issue SET ROWCOUNT 1 before running my query, it runs forever and I get a rowcount Top operator.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply