Problem with Tuning of Delete Statement

Question

Problem with Tuning of Delete Statement

Viewing 5 posts - 31 through 34 (of 34 total)

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

Hamid-Sadeghian SSC Eights! Points: 890 More actions · Answer 1

ChrisM@Work - Friday, January 5, 2018 9:49 AM
Hamid-Sadeghian - Friday, January 5, 2018 9:14 AM
ChrisM@Work - Friday, January 5, 2018 6:33 AM
Hamid-Sadeghian - Thursday, January 4, 2018 8:12 AM
ChrisM@Work - Thursday, January 4, 2018 7:33 AM
Hamid-Sadeghian - Thursday, January 4, 2018 5:50 AM
thank 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 BEGIN
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)
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 b
CREATE 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_V3
CREATE 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_V3
CREATE 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

Please use this script that create database and sample data.
At my test, different geometry data on this field cause this problem . with this sample data, clustered index has about 26000 pages but if value of the shape field is the same for all records, index spool will not appear.
Also I don't enable rowcount on my session .

Sergiy SSC Guru Points: 110206 More actions · Answer 2

Keep in mind - if a modified index was not used in the cached plan then recreating it won't necessarily change the query behaviour.

Stored plan will be used until it's invalidated by significant change in statistics, clearing cache or some other relevant event.

_____________
Code for TallyGenerator

Hamid-Sadeghian SSC Eights! Points: 890 More actions · Answer 3

Sergiy - Friday, January 5, 2018 3:13 PM
Keep in mind - if a modified index was not used in the cached plan then recreating it won't necessarily change the query behaviour.Stored plan will be used until it's invalidated by significant change in statistics, clearing cache or some other relevant event.

Thank you for your hint. I run this query from SSMS with recompile option.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 4

There's an error in the sample datascript:

Create Database TestData

Go

Use testData

Go

r

ALTER TABLE [dbo].[SDE_GEOMETRY9] ADD CONSTRAINT [geomdef9] DEFAULT ((0)) FOR [SDE_STATE_ID]

GO

Can you post up whatever should replace "r" please?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Hamid-Sadeghian SSC Eights! Points: 890 More actions · Answer 5

ChrisM@home - Saturday, January 6, 2018 3:52 AM
There's an error in the sample datascript:
Create Database TestData
Go
Use testData
Go
r
ALTER TABLE [dbo].[SDE_GEOMETRY9] ADD CONSTRAINT [geomdef9] DEFAULT ((0)) FOR [SDE_STATE_ID]
GO
Can you post up whatever should replace "r" please?

Sorry for mistake

CREATE TABLE [dbo].[SDE_GEOMETRY9]( [GEOMETRY_ID] [int] NOT NULL, [CAd] [varbinary](max) NULL, [SDE_STATE_ID] [bigint] NOT NULL, CONSTRAINT [PK_sde_geometry9] PRIMARY KEY CLUSTERED ( [GEOMETRY_ID] ASC, [SDE_STATE_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO