SQLTeam.com | Weblogs | Forums

My query select into very slow How to enhance it?

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 :slight_smile:

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 ?

You have an error in your JOIN condition:

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.

That's true, but the and i.partlevel=0 will effectively make it an INNER JOIN.

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.

so what i will do
can you write query after you modify here to know what exactly
mean

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:

  1. 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.
  2. 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.
  3. Is no lock really needed?