March 23, 2016 at 5:09 pm
Good Afternoon Guys,
I would like to ask is there a way for me To Optimize the way i insert My Data from A Temp Table To A Sql Table. i am currently Inserting Data to a Temp table using A Sql Query that i use and all is Fine And Good. Once I Tried To Insert The Data from the Temp Table To the Physical Table i am having a performance Issue With The Query I Am Running. I have Attached the following document For reference.
1. Create Script For Physical Table
2. Select Into Insert To the Physical Table
3. Indexes For the Physical Table
Any Suggestions from you guys so i can Speed Up The Process(I Am guessing An Additional Index) would be very Helpful
Best Regards,
Noel
March 23, 2016 at 7:34 pm
Stylez (3/23/2016)
Good Afternoon Guys,I would like to ask is there a way for me To Optimize the way i insert My Data from A Temp Table To A Sql Table. i am currently Inserting Data to a Temp table using A Sql Query that i use and all is Fine And Good. Once I Tried To Insert The Data from the Temp Table To the Physical Table i am having a performance Issue With The Query I Am Running. I have Attached the following document For reference.
1. Create Script For Physical Table
/****** Object: Table [dbo].[budCrewDetail] Script Date: 3/23/2016 3:46:34 PM ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[budCrewDetail](
[Co] [dbo].[bCompany] NOT NULL,
[Date] [dbo].[bDate] NOT NULL,
[EmployeeID] [dbo].[bEmployee] NULL,
[ID] [int] NOT NULL,
[Project] [dbo].[bProject] NOT NULL,
[UniqueAttchID] [uniqueidentifier] NULL,
[KeyID] [bigint] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](20) NULL,
[LastName] [varchar](20) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
2. Select Into Insert To the Physical Table
SELECT distinct JCDET.JCCo,JCDET.ActualDate
,JCDET.Employee
,ROW_NUMBER() OVER (
ORDER BY JCDET.Employee DESC) ID
,JCDET.Job
,PREH.FirstName
,PREH.LastName
INTO #ToInsert
FROM brvJCCDDetlDesc JCDET
INNER JOIN PREH ON
JCDET.Employee = PREH.Employee
AND JCDET.JCCo = PREH.PRCo
WHERE NOT EXISTS (
SELECT EmployeeID
FROM budCrewDetail
WHERE budCrewDetail.Co = JCDET.JCCo
AND budCrewDetail.Project = JCDET.Job
AND budCrewDetail.[Date] = JCDET.ActualDate
AND budCrewDetail.EmployeeID = JCDET.Employee
)
AND PREH.ActiveYN = 'Y'
AND CostType = '1'
AND JCDET.JCTransType = 'PR'
INSERT INTO dbo.budCrewDetail (
Co
,[Date]
,EmployeeID
,ID
,Project
,FirstName
,LastName
)
SELECT top 50000 JCCo
,ActualDate
,Employee
,ID
,Job
,FirstName
,LastName
FROM #ToInsert
drop table #ToInsert
3. Indexes For the Physical Table
/****** Object: Index [biudCrewDetail] Script Date: 3/23/2016 4:07:07 PM ******/DROP INDEX [biudCrewDetail] ON [dbo].[budCrewDetail] WITH ( ONLINE = OFF )
GO
/****** Object: Index [biudCrewDetail] Script Date: 3/23/2016 4:07:07 PM ******/
CREATE UNIQUE CLUSTERED INDEX [biudCrewDetail] ON [dbo].[budCrewDetail]
(
[Co] ASC,
[Project] ASC,
[Date] ASC,
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
Any Suggestions from you guys so i can Speed Up The Process(I Am guessing An Additional Index) would be very Helpful
Best Regards,
Noel
If this is the statement running slow:
INSERT INTO dbo.budCrewDetail (
Co
,[Date]
,EmployeeID
,ID
,Project
,FirstName
,LastName
)
SELECT top 50000 JCCo
,ActualDate
,Employee
,ID
,Job
,FirstName
,LastName
FROM #ToInsert
then it sounds like something else is going on because that query is dead simple. Are any constraints (foreign key or check) or triggers on dbo.budCrewDetail that you did not show us?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 23, 2016 at 9:36 pm
I noticed that you have a 4-column clustered index that consists entirely of user-defined data types. What are the underlying types of those? I'm just going to presume that it's a wide key, but the real kicker is that it probably isn't ever-increasing. I have to wonder if your suffering from page splits by inserting data that fits in the middle.
Like Orlando said, the insert statement itself is pretty simple, but I didn't think of the trigger idea and it could be very pertinent. My question is the same as his - Is there anything else going on in the procedure? I'm asking because you're selecting a bunch of rows into a temp table and then turning around and taking the top 50K into the physical table. Why the extra step and what about the rows other than the first 50K?
Another area of concern is the data types. You're creating the temp table from the data in your brvJCCDDetlDesc table. Do the data types in that table match the dbo.budCrewDetail table you're populating? I'm asking because implicit casts can slow down anything.
March 25, 2016 at 5:59 am
In addition, insert techniques matter as well.
1) If the target table is constructed as a heap, using an insert ... into ... from manner then hardly any log space is used. This reduces the number of writes. Primary keys and other can be applied afterwards.
You will find the need to use target column = isnull( source_column, somevalue ) for columns that are never null in order to make the column in the created table non nullable.
I have seen major speedups using this technique.
2) If however a table already exists (empty or not), try to sort the select part for the query according to the primary key in the target table.
3) Removing automatic update of statistics on a table can also help a bit if the target table contains few rows and are adding many more!
4) Same goes for indexes beyond the PK, they can be re-added later.
5) If a target table is guaranteed not going to be queried, you can also opt to claim a full table lock to reduce lock bookkeeping or simply always build the table from scratch in the most efficient manner using bulk operations (see option 1).
March 25, 2016 at 8:58 pm
...WHERE NOT EXISTS (
SELECT EmployeeID
FROM budCrewDetail
WHERE budCrewDetail.Co = JCDET.JCCo
AND budCrewDetail.Project = JCDET.Job
AND budCrewDetail.[Date] = JCDET.ActualDate....
Negation operators like NOT are nonsargable expressions.An index is not useful in nonsargable expressions.SQL Server cannot limit the search by using the index.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 26, 2016 at 12:29 pm
Sachin Nandanwar (3/25/2016)
...WHERE NOT EXISTS (SELECT EmployeeID
FROM budCrewDetail
WHERE budCrewDetail.Co = JCDET.JCCo
AND budCrewDetail.Project = JCDET.Job
AND budCrewDetail.[Date] = JCDET.ActualDate....
Negation operators like NOT are nonsargable expressions.An index is not useful in nonsargable expressions.SQL Server cannot limit the search by using the index.
The only NOT in this fragment is in the NOT EXISTS, and your comment does not apply to such a case. Depending on estimated rowcounts and estimates, I expect that this will be resolved in the execution plan as amn anti semi join, implemented either with the Nested Loops operator into an index seek, or with either the Merge Join or the Hash Match operator into an index or table scan.
In all these cases, the plan is pretty optimal for what the query tells it to do, and the NOT is not a problem. And as indicated above, if there is a good index on the equality columns in budCrewDetail, then it will almost certainly be used.
March 27, 2016 at 5:32 am
Stylez (3/23/2016)
Good Afternoon Guys,I would like to ask is there a way for me To Optimize the way i insert My Data from A Temp Table To A Sql Table. i am currently Inserting Data to a Temp table using A Sql Query that i use and all is Fine And Good. Once I Tried To Insert The Data from the Temp Table To the Physical Table i am having a performance Issue With The Query I Am Running. I have Attached the following document For reference.
1. Create Script For Physical Table
2. Select Into Insert To the Physical Table
3. Indexes For the Physical Table
Any Suggestions from you guys so i can Speed Up The Process(I Am guessing An Additional Index) would be very Helpful
Best Regards,
Noel
Quick question, how are the tempdb's file configurations and file locations?
😎
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply