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
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
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
The parts in bold italic are incompatible with each other, you'll get zero rows returned (which is what the pasted query plan indicates, all the row counts were zero). You'll need to fix that by removing one of those conditions, you can't have both.
As to why it's slow, how large are the tables being JOINed? If they have millions of rows it will take time to read them from disk. The TAllData table had no indexes used in this query, it's doing a full table scan. Adding an index with PartID, Code, and CodetypeID, and include FeatureName and FeatureValue, may speed it up.
Looks OK to me. It's a LEFT JOIN (vs an INNER JOIN). Checking one of the join columns for NULL -- in this case i.partid -- is the standard way to determine if a row was found or not.
No, because the i. table is on the left side of the join and the check is in the join condition. Checking for "i.partlevel = 0" in the WHERE clause would effectively make it an INNER JOIN, which is likely what you're thinking of.
As to your actual performance issue, would need to see the DDL for all tables, including all index definitions. Also, use sys.partitions to give us the rough row counts for all the tables.
the plan shows that you are doing 2 key lookups: One on [ExtractReports].[dbo].[TradeCodesInsert].[UC_PartCode].[i] and one on [Z2DataCore].[Parts].[TradeCodes].
Have you tried creating a covering indexes to avoid this?
A couple of other observations:
you have the database name in the query, so this isn't transferable if you create a backup called ExtractReports_bak to test something on.
select into to create a table is usually slower than creating the table ahead of time. You could create the table in source control, then simply truncate it before running the query.
