I Work on sql server i have slow transfer data when make select into
small number of rows
it take too much time
my execution plan
https://www.brentozar.com/pastetheplan/?id=r1o3p8NOt
my query as below :
SELECT
d.PartID ,
d.Code ,
d.CodeTypeID ,
tr.RevisionID ,
tr.ZPLID,
tr.partlevel,
d.FeatureName,
d.FeatureValue
INTO ExtractReports.dbo.TEqualCodes
from ExtractReports.dbo.TAllData d with(nolock)
inner join parts.tradecodes tr with(nolock) on d.partid=tr.partid and d.codetypeid=tr.codetypeid and tr.partlevel=0 and d.code=tr.code and tr.zplid=4239
left join [ExtractReports].[dbo].[TradeCodesInsert] i with(nolock) on i.partid=tr.partid and i.codetypeid=tr.codetypeid and i.partlevel=tr.partlevel and i.partlevel=0 and tr.zplid=i.zplid
where i.partid is null
table structure
CREATE TABLE [Parts].[TradeCodes](
[TradeCodesID] [int] IDENTITY(1,1) NOT NULL,
[PartID] [int] NOT NULL,
[Code] [varchar](20) NOT NULL,
[CodeTypeID] [int] NOT NULL,
[SourceTypeID] [bigint] NULL,
[RevisionID] [bigint] NULL,
[ModifiedDate] [datetime] NULL,
[CreatedDate] [datetime] NOT NULL,
[Modifiedby] [int] NULL,
[CreatedBy] [int] NULL,
[PartLevel] [tinyint] NULL,
[ZPLID] [int] NULL,
[MappingDoneFlag] [int] NOT NULL,
[MappingValueId] [int] NOT NULL,
CONSTRAINT [PK__TradeCod__FEFAF27527F7A1C3] PRIMARY KEY CLUSTERED
(
[TradeCodesID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UC_PartCode] UNIQUE NONCLUSTERED
(
[PartID] ASC,
[CodeTypeID] ASC,
[PartLevel] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
ALTER TABLE [Parts].[TradeCodes] ADD CONSTRAINT [DF__TradeCode__Creat__215D38B9] DEFAULT (getdate()) FOR [CreatedDate]
GO
ALTER TABLE [Parts].[TradeCodes] ADD DEFAULT ((0)) FOR [MappingDoneFlag]
GO
ALTER TABLE [Parts].[TradeCodes] ADD DEFAULT ((0)) FOR [MappingValueId]
GO
so how to enhance my query to be faster
November 19, 2021 at 11:16 am
Where is the DDL of ExtractReports.dbo.TAllData and [ExtractReports].[dbo].[TradeCodesInsert] ? I only see [Parts].[TradeCodes]
Is there any sample data to work on?
November 19, 2021 at 1:45 pm
You are getting a scan of TAllData (please tell you don't have a naming standard where you name all tables T*). It doesn't have filter criteria, but it does have JOIN criteria to the tradecodes table. Maybe an index on three columns used in the join; partid, codetypeid, code. I'm not sure in what order because I can't see your data. Most selective first is usually best.
You also have two different key lookup operations. Eliminating them through the use of an INCLUDE on the nonclustered index seeks that you're getting could help as well. I'd focus first on that table scan though.
You're also getting a late filter operation on the TradeCodeInsert table. Probably, and I can't see your structures, so guessing, partid is the second column in that index? Maybe, testing is your friend, on all this, change the order of those columns... Maybe not. Again, test. Alternatively, put partid in it's own index and possibly get an index join... but, testing, experimentation, testing.
Focus on the scan.
The query itself doesn't have any outstanding code smells. Painting NOLOCK all over it could lead to issues. Is it OK if occasionally you get duplicate rows or miss rows? Much more likely because of that scan too.
"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
Below are 2 alternative ways to write the same query, but without the LEFT JOIN. They may have an improvement in performance, so test, test, test.
SELECT d.PartID
, d.Code
, d.CodeTypeID
, tr.RevisionID
, tr.ZPLID
, tr.partlevel
, d.FeatureName
, d.FeatureValue
INTO ExtractReports.dbo.TEqualCodes
FROM ExtractReports.dbo.TAllData AS d WITH ( NOLOCK )
INNER JOIN parts.tradecodes AS tr WITH ( NOLOCK )
ON d.partid = tr.partid
AND d.codetypeid = tr.codetypeid
AND d.code = tr.code
AND tr.partlevel = 0
AND tr.zplid = 4239
WHERE NOT EXISTS ( SELECT 1
FROM ExtractReports.dbo.TradeCodesInsert AS i WITH ( NOLOCK )
WHERE i.partid = tr.partid
AND i.codetypeid = tr.codetypeid
AND i.partlevel = tr.partlevel
AND i.partlevel = 0
AND i.zplid = tr.zplid );
WITH cteData AS (
SELECT TOP (9223372036854775807)
d.PartID
, d.Code
, d.CodeTypeID
, tr.RevisionID
, tr.ZPLID
, tr.partlevel
, d.FeatureName
, d.FeatureValue
FROM ExtractReports.dbo.TAllData AS d WITH ( NOLOCK )
INNER JOIN parts.tradecodes AS tr WITH ( NOLOCK )
ON d.partid = tr.partid
AND d.codetypeid = tr.codetypeid
AND d.code = tr.code
WHERE tr.zplid = 4239
AND tr.partlevel = 0
)
SELECT cte.PartID
, cte.Code
, cte.CodeTypeID
, cte.RevisionID
, cte.ZPLID
, cte.partlevel
, cte.FeatureName
, cte.FeatureValue
INTO ExtractReports.dbo.TEqualCodes
FROM cteData AS cte
WHERE EXISTS ( SELECT cte.partid, cte.codetypeid, cte.partlevel, cte.zplid
EXCEPT
SELECT i.partid, i.codetypeid, i.partlevel, i.zplid
FROM ExtractReports.dbo.TradeCodesInsert AS i WITH ( NOLOCK )
WHERE i.partlevel = 0 );
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply