INSERT taking 18 minutes, base SELECT takes 11 seconds

I've got a bit of an odd situation here. I have a SELECT statement that
feeds an INSERT operation. The select executes in 11 seconds. The INSERT
takes more than 18 minutes. I'm only inserting 14000 rows, and it takes the
same amount of time even if I clear the target table first (TRUNCATE).
Similar INSERTS in the application run just fine - this is the only
slowdown.
The SELECT is fairly complex, but nothing too extreme. All of the source
data is on the same server, although in different databases. The target
table for the insert has only a single, non-clustered index, and running the
INSERT without the index doesn't change the time factor. Logging is set to
Simple. For the life of me, I can't figure out why the INSERT would be
bogging down this badly.
I've included the SP that contains the query below. As I mentioned above, if
you comment out the INSERT, it runs in 11 seconds. Any clues would be most
welcome - Thanks!
Stephen
create procedure usp_WRK_load_asof
@.start_date varchar(15),
@.end_date varchar(15),
@.bu integer
as
begin
PRINT 'Processing WRK_Load_ASOF'
-- Open Work Orders As Of a given date
INSERT INTO OneSite_Stage..FACT_Work_Orders
select
u.dim_time_string,
u.dim_time_key,
p.Number AS business_unit,
a.rr_number AS p_code,
s.ID AS w_o_id,
LEFT(s.UnitNumber,10) AS UnitID,
'' AS local_unit_type,
0 AS unitstatusid,
s.BuildingID AS u_bldg,
s.ServiceRequestDate AS report_date,
LEFT(s.RequestorName,30) AS reportby,
COALESCE(sp.ID, 'Other') AS f_code,
LEFT(sp.Description,70) AS descript,
LEFT(d.ServiceComments,40) AS solution,
LEFT(d.TechnicianName,20) AS assignto,
LEFT(d.TechnicianName,20) AS work_by,
COALESCE(d.ActualCompleteDate, s.ActualCompletionDate) AS complete,
d.TimeRangeStart AS timestar,
d.TimeRangeEnd AS timedone,
0 AS manhours,
LEFT(d.WorkDoneComments,40) AS material,
0 AS cost,
CASE s.Status
WHEN 'In Progress' THEN 'P'
WHEN 'Complete' THEN 'C'
END AS status,
LEFT(s.ResidentMemberPhone,20) AS phone,
1 as work_order_activity_id,
DateDiff(day, s.ServiceRequestDate, u.dim_time_key) as days_open,
ak.attributekey,
d.ID AS Detail_ID
from U2038689.dbo.servicerequest s
LEFT JOIN U2038689.dbo.ServiceRequestDetail d ON (s.ID =
d.ServiceRequestID AND s.propertyID = d.propertyID)
INNER join OneSite_Stage..HIST_property p on (p.propertyid =
s.propertyID AND p.dim_time_string = (SELECT MAX(dim_time_string) FROM
OneSite_Stage..HIST_Property hp WHERE hp.PropertyID = s.PropertyID))
LEFT JOIN U2038689..serviceproblem sp ON (d.serviceproblemid = sp.id
AND d.PropertyID = sp.PropertyID)
INNER join ods_stage.dbo.dim_adcp2 a ON (a.Business_Unit = p.number
AND a.OneSite_Flag = 1)
INNER JOIN ods_stage.dbo.dim_uploadday u ON (u.dim_time_string >=
@.start_date AND u.dim_time_string <= @.end_date)
INNER JOIN ods_stage.dbo.ODS_ADCP_History ak ON
(ak.rr_number = a.rr_number
and ak.year = year(u.dim_time_string)
and ak.period = (
select max(period)
from ods_stage.dbo.ODS_ADCP_History
where year = year(u.dim_time_string)
and period <= month(u.dim_time_string)
)
and ak.status = 'Active')
WHERE ods.dbo.udr_formatdate(s.ServiceRequestDate, 'YYYY-MM-DD') <
u.dim_time_string
AND (ods.dbo.udr_formatdate(COALESCE(d.ActualCompleteDate,
s.ActualCompletionDate), 'YYYY-MM-DD') >= u.dim_time_string
OR COALESCE(d.ActualCompleteDate, s.ActualCompletionDate) IS NULL)
AND u.dim_time_string >= ods.dbo.udr_formatdate(@.start_date,
'YYYY-MM-DD')
AND u.dim_time_string <= ods.dbo.udr_formatdate(@.end_date,
'YYYY-MM-DD')
AND (d.CancelDate IS NULL
OR ods.dbo.udr_formatdate(d.CancelDate, 'YYYY-MM-DD') >=
ods.dbo.udr_formatdate(@.end_date, 'YYYY-MM-DD'))
AND (p.number = @.bu OR @.bu = 0)
AND sp.id IS NOT NULL
endA couple of suggestions.
1. Do you have a trigger on the FACT_Work_Orders table?
2. Compare the execution plans of the INSERT statement and the SELECT
statement. Do you see any difference?
3. Check the locks while doing the inserts. You can use Erlands script at :
www.sommarskog.se.
4. This might be silly, but are you sure you are using the *exact* same
SELECT statement ( i.e. with parameterization, same values etc. ) when you
compare the time?
5. Finally a shot in the dark: Consider using a local variable and get the
result of the scalar UDF to the variable and use it in the WHERE clause.
Anith|||Stephen,
Are there any triggers on the table?
HTH
Jerry
"Stephen Cochran" <scochran666@.yahoo.com> wrote in message
news:O86VnSM1FHA.2792@.tk2msftngp13.phx.gbl...
> I've got a bit of an odd situation here. I have a SELECT statement that
> feeds an INSERT operation. The select executes in 11 seconds. The INSERT
> takes more than 18 minutes. I'm only inserting 14000 rows, and it takes
> the
> same amount of time even if I clear the target table first (TRUNCATE).
> Similar INSERTS in the application run just fine - this is the only
> slowdown.
> The SELECT is fairly complex, but nothing too extreme. All of the source
> data is on the same server, although in different databases. The target
> table for the insert has only a single, non-clustered index, and running
> the
> INSERT without the index doesn't change the time factor. Logging is set to
> Simple. For the life of me, I can't figure out why the INSERT would be
> bogging down this badly.
> I've included the SP that contains the query below. As I mentioned above,
> if
> you comment out the INSERT, it runs in 11 seconds. Any clues would be most
> welcome - Thanks!
> Stephen
>
> create procedure usp_WRK_load_asof
> @.start_date varchar(15),
> @.end_date varchar(15),
> @.bu integer
> as
> begin
> PRINT 'Processing WRK_Load_ASOF'
> -- Open Work Orders As Of a given date
> INSERT INTO OneSite_Stage..FACT_Work_Orders
> select
> u.dim_time_string,
> u.dim_time_key,
> p.Number AS business_unit,
> a.rr_number AS p_code,
> s.ID AS w_o_id,
> LEFT(s.UnitNumber,10) AS UnitID,
> '' AS local_unit_type,
> 0 AS unitstatusid,
> s.BuildingID AS u_bldg,
> s.ServiceRequestDate AS report_date,
> LEFT(s.RequestorName,30) AS reportby,
> COALESCE(sp.ID, 'Other') AS f_code,
> LEFT(sp.Description,70) AS descript,
> LEFT(d.ServiceComments,40) AS solution,
> LEFT(d.TechnicianName,20) AS assignto,
> LEFT(d.TechnicianName,20) AS work_by,
> COALESCE(d.ActualCompleteDate, s.ActualCompletionDate) AS complete,
> d.TimeRangeStart AS timestar,
> d.TimeRangeEnd AS timedone,
> 0 AS manhours,
> LEFT(d.WorkDoneComments,40) AS material,
> 0 AS cost,
> CASE s.Status
> WHEN 'In Progress' THEN 'P'
> WHEN 'Complete' THEN 'C'
> END AS status,
> LEFT(s.ResidentMemberPhone,20) AS phone,
> 1 as work_order_activity_id,
> DateDiff(day, s.ServiceRequestDate, u.dim_time_key) as days_open,
> ak.attributekey,
> d.ID AS Detail_ID
> from U2038689.dbo.servicerequest s
> LEFT JOIN U2038689.dbo.ServiceRequestDetail d ON (s.ID =
> d.ServiceRequestID AND s.propertyID = d.propertyID)
> INNER join OneSite_Stage..HIST_property p on (p.propertyid =
> s.propertyID AND p.dim_time_string = (SELECT MAX(dim_time_string) FROM
> OneSite_Stage..HIST_Property hp WHERE hp.PropertyID = s.PropertyID))
> LEFT JOIN U2038689..serviceproblem sp ON (d.serviceproblemid =
> sp.id
> AND d.PropertyID = sp.PropertyID)
> INNER join ods_stage.dbo.dim_adcp2 a ON (a.Business_Unit = p.number
> AND a.OneSite_Flag = 1)
> INNER JOIN ods_stage.dbo.dim_uploadday u ON (u.dim_time_string >=
> @.start_date AND u.dim_time_string <= @.end_date)
> INNER JOIN ods_stage.dbo.ODS_ADCP_History ak ON
> (ak.rr_number = a.rr_number
> and ak.year = year(u.dim_time_string)
> and ak.period = (
> select max(period)
> from ods_stage.dbo.ODS_ADCP_History
> where year = year(u.dim_time_string)
> and period <= month(u.dim_time_string)
> )
> and ak.status = 'Active')
> WHERE ods.dbo.udr_formatdate(s.ServiceRequestDate, 'YYYY-MM-DD') <
> u.dim_time_string
> AND (ods.dbo.udr_formatdate(COALESCE(d.ActualCompleteDate,
> s.ActualCompletionDate), 'YYYY-MM-DD') >= u.dim_time_string
> OR COALESCE(d.ActualCompleteDate, s.ActualCompletionDate) IS NULL)
> AND u.dim_time_string >= ods.dbo.udr_formatdate(@.start_date,
> 'YYYY-MM-DD')
> AND u.dim_time_string <= ods.dbo.udr_formatdate(@.end_date,
> 'YYYY-MM-DD')
> AND (d.CancelDate IS NULL
> OR ods.dbo.udr_formatdate(d.CancelDate, 'YYYY-MM-DD') >=
> ods.dbo.udr_formatdate(@.end_date, 'YYYY-MM-DD'))
> AND (p.number = @.bu OR @.bu = 0)
> AND sp.id IS NOT NULL
> end
>|||How long does it take if you do INTO mytesttable so as to test inserting
into the same database - dont do a # table because that will be in a
different database and subject to different logging.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Stephen Cochran" <scochran666@.yahoo.com> wrote in message
news:O86VnSM1FHA.2792@.tk2msftngp13.phx.gbl...
> I've got a bit of an odd situation here. I have a SELECT statement that
> feeds an INSERT operation. The select executes in 11 seconds. The INSERT
> takes more than 18 minutes. I'm only inserting 14000 rows, and it takes
> the
> same amount of time even if I clear the target table first (TRUNCATE).
> Similar INSERTS in the application run just fine - this is the only
> slowdown.
> The SELECT is fairly complex, but nothing too extreme. All of the source
> data is on the same server, although in different databases. The target
> table for the insert has only a single, non-clustered index, and running
> the
> INSERT without the index doesn't change the time factor. Logging is set to
> Simple. For the life of me, I can't figure out why the INSERT would be
> bogging down this badly.
> I've included the SP that contains the query below. As I mentioned above,
> if
> you comment out the INSERT, it runs in 11 seconds. Any clues would be most
> welcome - Thanks!
> Stephen
>
> create procedure usp_WRK_load_asof
> @.start_date varchar(15),
> @.end_date varchar(15),
> @.bu integer
> as
> begin
> PRINT 'Processing WRK_Load_ASOF'
> -- Open Work Orders As Of a given date
> INSERT INTO OneSite_Stage..FACT_Work_Orders
> select
> u.dim_time_string,
> u.dim_time_key,
> p.Number AS business_unit,
> a.rr_number AS p_code,
> s.ID AS w_o_id,
> LEFT(s.UnitNumber,10) AS UnitID,
> '' AS local_unit_type,
> 0 AS unitstatusid,
> s.BuildingID AS u_bldg,
> s.ServiceRequestDate AS report_date,
> LEFT(s.RequestorName,30) AS reportby,
> COALESCE(sp.ID, 'Other') AS f_code,
> LEFT(sp.Description,70) AS descript,
> LEFT(d.ServiceComments,40) AS solution,
> LEFT(d.TechnicianName,20) AS assignto,
> LEFT(d.TechnicianName,20) AS work_by,
> COALESCE(d.ActualCompleteDate, s.ActualCompletionDate) AS complete,
> d.TimeRangeStart AS timestar,
> d.TimeRangeEnd AS timedone,
> 0 AS manhours,
> LEFT(d.WorkDoneComments,40) AS material,
> 0 AS cost,
> CASE s.Status
> WHEN 'In Progress' THEN 'P'
> WHEN 'Complete' THEN 'C'
> END AS status,
> LEFT(s.ResidentMemberPhone,20) AS phone,
> 1 as work_order_activity_id,
> DateDiff(day, s.ServiceRequestDate, u.dim_time_key) as days_open,
> ak.attributekey,
> d.ID AS Detail_ID
> from U2038689.dbo.servicerequest s
> LEFT JOIN U2038689.dbo.ServiceRequestDetail d ON (s.ID =
> d.ServiceRequestID AND s.propertyID = d.propertyID)
> INNER join OneSite_Stage..HIST_property p on (p.propertyid =
> s.propertyID AND p.dim_time_string = (SELECT MAX(dim_time_string) FROM
> OneSite_Stage..HIST_Property hp WHERE hp.PropertyID = s.PropertyID))
> LEFT JOIN U2038689..serviceproblem sp ON (d.serviceproblemid =
> sp.id
> AND d.PropertyID = sp.PropertyID)
> INNER join ods_stage.dbo.dim_adcp2 a ON (a.Business_Unit = p.number
> AND a.OneSite_Flag = 1)
> INNER JOIN ods_stage.dbo.dim_uploadday u ON (u.dim_time_string >=
> @.start_date AND u.dim_time_string <= @.end_date)
> INNER JOIN ods_stage.dbo.ODS_ADCP_History ak ON
> (ak.rr_number = a.rr_number
> and ak.year = year(u.dim_time_string)
> and ak.period = (
> select max(period)
> from ods_stage.dbo.ODS_ADCP_History
> where year = year(u.dim_time_string)
> and period <= month(u.dim_time_string)
> )
> and ak.status = 'Active')
> WHERE ods.dbo.udr_formatdate(s.ServiceRequestDate, 'YYYY-MM-DD') <
> u.dim_time_string
> AND (ods.dbo.udr_formatdate(COALESCE(d.ActualCompleteDate,
> s.ActualCompletionDate), 'YYYY-MM-DD') >= u.dim_time_string
> OR COALESCE(d.ActualCompleteDate, s.ActualCompletionDate) IS NULL)
> AND u.dim_time_string >= ods.dbo.udr_formatdate(@.start_date,
> 'YYYY-MM-DD')
> AND u.dim_time_string <= ods.dbo.udr_formatdate(@.end_date,
> 'YYYY-MM-DD')
> AND (d.CancelDate IS NULL
> OR ods.dbo.udr_formatdate(d.CancelDate, 'YYYY-MM-DD') >=
> ods.dbo.udr_formatdate(@.end_date, 'YYYY-MM-DD'))
> AND (p.number = @.bu OR @.bu = 0)
> AND sp.id IS NOT NULL
> end
>|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23xTEvXM1FHA.268@.TK2MSFTNGP09.phx.gbl...
> A couple of suggestions.
> 1. Do you have a trigger on the FACT_Work_Orders table?
> 2. Compare the execution plans of the INSERT statement and the SELECT
> statement. Do you see any difference?
> 3. Check the locks while doing the inserts. You can use Erlands script at
:
> www.sommarskog.se.
> 4. This might be silly, but are you sure you are using the *exact* same
> SELECT statement ( i.e. with parameterization, same values etc. ) when you
> compare the time?
> 5. Finally a shot in the dark: Consider using a local variable and get the
> result of the scalar UDF to the variable and use it in the WHERE clause.
> --
> Anith
Thanks for looking at this, Anith (and Jerry).
1. No, there are no triggers on any of the tables.
2. Yes, the execution plans are identical.
3. I'll look into this - I've never really dealt with locks explicitly
before, so I don't know how they impact this situation. I do know that there
isn't any blocking occurring during the INSERT.
4. Yes, I test by commenting out the INSERT statement on the existing query.
5. Wouldn't work for most of the UDF calls. The date it operates on can
change from row to row. I've removed a couple of unnecessary UDF calls, but
so far it appears that there is no effect from this.
Thanks again for the reply!|||"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:OsL8KiM1FHA.3660@.TK2MSFTNGP15.phx.gbl...
> How long does it take if you do INTO mytesttable so as to test inserting
> into the same database - dont do a # table because that will be in a
> different database and subject to different logging.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
Thanks for taking a look. Just ran the test, and the times were identical.
BTW, I'm now using a cut-down date range, that puts the INSERT into the 4
minute range. The base select now takes 3 seconds and returns 3500 rows.
Certainly was worth a try, though.|||To add to what has been said. Have you looked at your hardware performance
during both? Insert of 14000 rows is not "trivial" though not tremendous,
but if you have your log on the same disk as your data and tempdb, and/or
your CPU was heavily used during the select it might be an issue.
Look at the plans of both and compare. There might be something there.
I would also reduce the number of rows inserted to very few and work your
way up until you see the breaking point. Watch all of the factors and see
what the problem is. This kinds of things seem very hardwarish to me,
though it could just be the plan.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Stephen Cochran" <scochran666@.yahoo.com> wrote in message
news:O86VnSM1FHA.2792@.tk2msftngp13.phx.gbl...
> I've got a bit of an odd situation here. I have a SELECT statement that
> feeds an INSERT operation. The select executes in 11 seconds. The INSERT
> takes more than 18 minutes. I'm only inserting 14000 rows, and it takes
> the
> same amount of time even if I clear the target table first (TRUNCATE).
> Similar INSERTS in the application run just fine - this is the only
> slowdown.
> The SELECT is fairly complex, but nothing too extreme. All of the source
> data is on the same server, although in different databases. The target
> table for the insert has only a single, non-clustered index, and running
> the
> INSERT without the index doesn't change the time factor. Logging is set to
> Simple. For the life of me, I can't figure out why the INSERT would be
> bogging down this badly.
> I've included the SP that contains the query below. As I mentioned above,
> if
> you comment out the INSERT, it runs in 11 seconds. Any clues would be most
> welcome - Thanks!
> Stephen
>
> create procedure usp_WRK_load_asof
> @.start_date varchar(15),
> @.end_date varchar(15),
> @.bu integer
> as
> begin
> PRINT 'Processing WRK_Load_ASOF'
> -- Open Work Orders As Of a given date
> INSERT INTO OneSite_Stage..FACT_Work_Orders
> select
> u.dim_time_string,
> u.dim_time_key,
> p.Number AS business_unit,
> a.rr_number AS p_code,
> s.ID AS w_o_id,
> LEFT(s.UnitNumber,10) AS UnitID,
> '' AS local_unit_type,
> 0 AS unitstatusid,
> s.BuildingID AS u_bldg,
> s.ServiceRequestDate AS report_date,
> LEFT(s.RequestorName,30) AS reportby,
> COALESCE(sp.ID, 'Other') AS f_code,
> LEFT(sp.Description,70) AS descript,
> LEFT(d.ServiceComments,40) AS solution,
> LEFT(d.TechnicianName,20) AS assignto,
> LEFT(d.TechnicianName,20) AS work_by,
> COALESCE(d.ActualCompleteDate, s.ActualCompletionDate) AS complete,
> d.TimeRangeStart AS timestar,
> d.TimeRangeEnd AS timedone,
> 0 AS manhours,
> LEFT(d.WorkDoneComments,40) AS material,
> 0 AS cost,
> CASE s.Status
> WHEN 'In Progress' THEN 'P'
> WHEN 'Complete' THEN 'C'
> END AS status,
> LEFT(s.ResidentMemberPhone,20) AS phone,
> 1 as work_order_activity_id,
> DateDiff(day, s.ServiceRequestDate, u.dim_time_key) as days_open,
> ak.attributekey,
> d.ID AS Detail_ID
> from U2038689.dbo.servicerequest s
> LEFT JOIN U2038689.dbo.ServiceRequestDetail d ON (s.ID =
> d.ServiceRequestID AND s.propertyID = d.propertyID)
> INNER join OneSite_Stage..HIST_property p on (p.propertyid =
> s.propertyID AND p.dim_time_string = (SELECT MAX(dim_time_string) FROM
> OneSite_Stage..HIST_Property hp WHERE hp.PropertyID = s.PropertyID))
> LEFT JOIN U2038689..serviceproblem sp ON (d.serviceproblemid =
> sp.id
> AND d.PropertyID = sp.PropertyID)
> INNER join ods_stage.dbo.dim_adcp2 a ON (a.Business_Unit = p.number
> AND a.OneSite_Flag = 1)
> INNER JOIN ods_stage.dbo.dim_uploadday u ON (u.dim_time_string >=
> @.start_date AND u.dim_time_string <= @.end_date)
> INNER JOIN ods_stage.dbo.ODS_ADCP_History ak ON
> (ak.rr_number = a.rr_number
> and ak.year = year(u.dim_time_string)
> and ak.period = (
> select max(period)
> from ods_stage.dbo.ODS_ADCP_History
> where year = year(u.dim_time_string)
> and period <= month(u.dim_time_string)
> )
> and ak.status = 'Active')
> WHERE ods.dbo.udr_formatdate(s.ServiceRequestDate, 'YYYY-MM-DD') <
> u.dim_time_string
> AND (ods.dbo.udr_formatdate(COALESCE(d.ActualCompleteDate,
> s.ActualCompletionDate), 'YYYY-MM-DD') >= u.dim_time_string
> OR COALESCE(d.ActualCompleteDate, s.ActualCompletionDate) IS NULL)
> AND u.dim_time_string >= ods.dbo.udr_formatdate(@.start_date,
> 'YYYY-MM-DD')
> AND u.dim_time_string <= ods.dbo.udr_formatdate(@.end_date,
> 'YYYY-MM-DD')
> AND (d.CancelDate IS NULL
> OR ods.dbo.udr_formatdate(d.CancelDate, 'YYYY-MM-DD') >=
> ods.dbo.udr_formatdate(@.end_date, 'YYYY-MM-DD'))
> AND (p.number = @.bu OR @.bu = 0)
> AND sp.id IS NOT NULL
> end
>|||Could point to indexing.
run DBCC SHOWCONTIG on the table and indexes and see if you have really bad
fragmentation.
My guess is that its doing significant page splitting.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Stephen Cochran" <scochran666@.yahoo.com> wrote in message
news:eGmvgoM1FHA.2312@.TK2MSFTNGP14.phx.gbl...
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:OsL8KiM1FHA.3660@.TK2MSFTNGP15.phx.gbl...
> Thanks for taking a look. Just ran the test, and the times were identical.
> BTW, I'm now using a cut-down date range, that puts the INSERT into the 4
> minute range. The base select now takes 3 seconds and returns 3500 rows.
> Certainly was worth a try, though.
>|||Thanks for everyone's input on this. Turns out it was something stupid on my
part - gee, that _never_ happens.
If you look at the bottom of the query, you will see the line "AND sp.id IS
NOT NULL". This was added recently to eliminate some superfluous rows from
the result. Seems pretty innocuous, doesn't it? Well, I just moved it up
into the JOIN condition, and everything is running well again. INSERTS now
take 3 seconds for 3500 rows.
It's amazing how something like this can throw everything into a tailspin.
Just serves as a reminder: Refactor, refactor, refactor - don't just keep
adding stuff without going back and reviewing the whole thing.
Thanks again, everyone. Sorry to have bothered you with this one.
Stephen|||I've found a MSDN article that explains how a large number of inserts into a
heap (non-clustered) table may run slower than into a clustered table.
PRB: Poor Performance on a Heap
http://support.microsoft.com/defaul...kb;en-us;297861
"Stephen Cochran" <scochran666@.yahoo.com> wrote in message
news:O86VnSM1FHA.2792@.tk2msftngp13.phx.gbl...
> I've got a bit of an odd situation here. I have a SELECT statement that
> feeds an INSERT operation. The select executes in 11 seconds. The INSERT
> takes more than 18 minutes. I'm only inserting 14000 rows, and it takes
> the
> same amount of time even if I clear the target table first (TRUNCATE).
> Similar INSERTS in the application run just fine - this is the only
> slowdown.
> The SELECT is fairly complex, but nothing too extreme. All of the source
> data is on the same server, although in different databases. The target
> table for the insert has only a single, non-clustered index, and running
> the
> INSERT without the index doesn't change the time factor. Logging is set to
> Simple. For the life of me, I can't figure out why the INSERT would be
> bogging down this badly.
> I've included the SP that contains the query below. As I mentioned above,
> if
> you comment out the INSERT, it runs in 11 seconds. Any clues would be most
> welcome - Thanks!
> Stephen
>
> create procedure usp_WRK_load_asof
> @.start_date varchar(15),
> @.end_date varchar(15),
> @.bu integer
> as
> begin
> PRINT 'Processing WRK_Load_ASOF'
> -- Open Work Orders As Of a given date
> INSERT INTO OneSite_Stage..FACT_Work_Orders
> select
> u.dim_time_string,
> u.dim_time_key,
> p.Number AS business_unit,
> a.rr_number AS p_code,
> s.ID AS w_o_id,
> LEFT(s.UnitNumber,10) AS UnitID,
> '' AS local_unit_type,
> 0 AS unitstatusid,
> s.BuildingID AS u_bldg,
> s.ServiceRequestDate AS report_date,
> LEFT(s.RequestorName,30) AS reportby,
> COALESCE(sp.ID, 'Other') AS f_code,
> LEFT(sp.Description,70) AS descript,
> LEFT(d.ServiceComments,40) AS solution,
> LEFT(d.TechnicianName,20) AS assignto,
> LEFT(d.TechnicianName,20) AS work_by,
> COALESCE(d.ActualCompleteDate, s.ActualCompletionDate) AS complete,
> d.TimeRangeStart AS timestar,
> d.TimeRangeEnd AS timedone,
> 0 AS manhours,
> LEFT(d.WorkDoneComments,40) AS material,
> 0 AS cost,
> CASE s.Status
> WHEN 'In Progress' THEN 'P'
> WHEN 'Complete' THEN 'C'
> END AS status,
> LEFT(s.ResidentMemberPhone,20) AS phone,
> 1 as work_order_activity_id,
> DateDiff(day, s.ServiceRequestDate, u.dim_time_key) as days_open,
> ak.attributekey,
> d.ID AS Detail_ID
> from U2038689.dbo.servicerequest s
> LEFT JOIN U2038689.dbo.ServiceRequestDetail d ON (s.ID =
> d.ServiceRequestID AND s.propertyID = d.propertyID)
> INNER join OneSite_Stage..HIST_property p on (p.propertyid =
> s.propertyID AND p.dim_time_string = (SELECT MAX(dim_time_string) FROM
> OneSite_Stage..HIST_Property hp WHERE hp.PropertyID = s.PropertyID))
> LEFT JOIN U2038689..serviceproblem sp ON (d.serviceproblemid =
> sp.id
> AND d.PropertyID = sp.PropertyID)
> INNER join ods_stage.dbo.dim_adcp2 a ON (a.Business_Unit = p.number
> AND a.OneSite_Flag = 1)
> INNER JOIN ods_stage.dbo.dim_uploadday u ON (u.dim_time_string >=
> @.start_date AND u.dim_time_string <= @.end_date)
> INNER JOIN ods_stage.dbo.ODS_ADCP_History ak ON
> (ak.rr_number = a.rr_number
> and ak.year = year(u.dim_time_string)
> and ak.period = (
> select max(period)
> from ods_stage.dbo.ODS_ADCP_History
> where year = year(u.dim_time_string)
> and period <= month(u.dim_time_string)
> )
> and ak.status = 'Active')
> WHERE ods.dbo.udr_formatdate(s.ServiceRequestDate, 'YYYY-MM-DD') <
> u.dim_time_string
> AND (ods.dbo.udr_formatdate(COALESCE(d.ActualCompleteDate,
> s.ActualCompletionDate), 'YYYY-MM-DD') >= u.dim_time_string
> OR COALESCE(d.ActualCompleteDate, s.ActualCompletionDate) IS NULL)
> AND u.dim_time_string >= ods.dbo.udr_formatdate(@.start_date,
> 'YYYY-MM-DD')
> AND u.dim_time_string <= ods.dbo.udr_formatdate(@.end_date,
> 'YYYY-MM-DD')
> AND (d.CancelDate IS NULL
> OR ods.dbo.udr_formatdate(d.CancelDate, 'YYYY-MM-DD') >=
> ods.dbo.udr_formatdate(@.end_date, 'YYYY-MM-DD'))
> AND (p.number = @.bu OR @.bu = 0)
> AND sp.id IS NOT NULL
> end
>