Insert/update/delete Transaction

Hi,

I have an unbound DataGridView and I have load it with a set of records from a Data base.

I modify existing rows, delete rows and add new rows to DataGridView control. I have to send a new modified dataset back to the data base.

Please any suggestions how to solve the problem?

Thanks in advance

George

Hi George,

I think you'll have more success posting your question on the Visual Studio forums - this is the T-SQL forum which is primarily used for back-end SQL questions, rather than user interface coding problems like DataGridViews.

Hope that helps :)

Menthos
|||Thanks :)sql

Insert thru a view to a table with an IDENTITY property

Why doesn't my identity property function normally when I try to insert
through a view?
--I create base table with identity property
CREATE TABLE _t
(id int identity
,num int)
--then insert a value
INSERT _t(num) VALUES (1)
--create view on base table
CREATE VIEW t
AS
SELECT * FROM _t
--create trigger to insert from view into the base table
CREATE TRIGGER trg
ON t
INSTEAD OF INSERT, UPDATE
AS
INSERT _t(num)
SELECT num
FROM inserted
--now try to insert into view (w/o specifying an ident value)
INSERT t(num) VALUES (3)
--and get this error
-- Server: Msg 233, Level 16, State 2, Line 1
-- The column 'id' in table 't' cannot be null.
--now try to insert into view (w specifying an ident value)
INSERT t(id, num) VALUES (7,3)
SELECT * FROM t
--and this is the result
-- id num
-- -- --
-- 1 1
-- 2 3
Does anyone have any idea why the IDENTITY property is not functioning
properly?
IOW why it asking me to supply a value for the IDENTITY column in order to
do the insert and then when I supply it, it is ignored.
What am I missing?This is interesting...
As a workaround, omit the IDENTITY column from the view's query:
CREATE VIEW t
AS
SELECT num FROM _t
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:05338885-9863-42AE-A2FE-3BC68F94AD30@.microsoft.com...
> Why doesn't my identity property function normally when I try to insert
> through a view?
> --I create base table with identity property
> CREATE TABLE _t
> (id int identity
> ,num int)
> --then insert a value
> INSERT _t(num) VALUES (1)
> --create view on base table
> CREATE VIEW t
> AS
> SELECT * FROM _t
>
> --create trigger to insert from view into the base table
> CREATE TRIGGER trg
> ON t
> INSTEAD OF INSERT, UPDATE
> AS
> INSERT _t(num)
> SELECT num
> FROM inserted
>
> --now try to insert into view (w/o specifying an ident value)
> INSERT t(num) VALUES (3)
> --and get this error
> -- Server: Msg 233, Level 16, State 2, Line 1
> -- The column 'id' in table 't' cannot be null.
> --now try to insert into view (w specifying an ident value)
> INSERT t(id, num) VALUES (7,3)
> SELECT * FROM t
> --and this is the result
> -- id num
> -- -- --
> -- 1 1
> -- 2 3
>
> Does anyone have any idea why the IDENTITY property is not functioning
> properly?
> IOW why it asking me to supply a value for the IDENTITY column in order to
> do the insert and then when I supply it, it is ignored.
> What am I missing?|||Dave
Yes, there is an issue with instead of trigger on view. scop_identity
function returns NULL
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:05338885-9863-42AE-A2FE-3BC68F94AD30@.microsoft.com...
> Why doesn't my identity property function normally when I try to insert
> through a view?
> --I create base table with identity property
> CREATE TABLE _t
> (id int identity
> ,num int)
> --then insert a value
> INSERT _t(num) VALUES (1)
> --create view on base table
> CREATE VIEW t
> AS
> SELECT * FROM _t
>
> --create trigger to insert from view into the base table
> CREATE TRIGGER trg
> ON t
> INSTEAD OF INSERT, UPDATE
> AS
> INSERT _t(num)
> SELECT num
> FROM inserted
>
> --now try to insert into view (w/o specifying an ident value)
> INSERT t(num) VALUES (3)
> --and get this error
> -- Server: Msg 233, Level 16, State 2, Line 1
> -- The column 'id' in table 't' cannot be null.
> --now try to insert into view (w specifying an ident value)
> INSERT t(id, num) VALUES (7,3)
> SELECT * FROM t
> --and this is the result
> -- id num
> -- -- --
> -- 1 1
> -- 2 3
>
> Does anyone have any idea why the IDENTITY property is not functioning
> properly?
> IOW why it asking me to supply a value for the IDENTITY column in order to
> do the insert and then when I supply it, it is ignored.
> What am I missing?|||On Wed, 26 Oct 2005 17:48:02 -0700, Dave wrote:

>Why doesn't my identity property function normally when I try to insert
>through a view?
Hi Dave,
That's because SQL Server checks if the NOT NULL constraint is violated
BEFORE the INSTEAD OF trigger is fired.
<speculation>
I *think* that this has an architectural reason. The new row(s) have to
be present in the "inserted" pseudo-table. This table has the same
structure as the table or view that the INSTEAD OF trigger is defined
for - up to and including nullability. That meanst that if a column
can't be NULL in the table (or view), there will be no space in the data
structure to represent whether a real value or a NULL was inserted.
And since SQL Server can't faithfully represent a NULL in the inserted
table that is passed to the INSTEAD OF trigger, it takes the safe route
and generates an error message.
</speculation>

>Does anyone have any idea why the IDENTITY property is not functioning
>properly?
It has nothing to do with the IDENTITY property, as explained above. If
you check Books Online, you'll find an example where a bogus value has
to be passed for a computed value in the view.

>IOW why it asking me to supply a value for the IDENTITY column in order to
>do the insert and then when I supply it, it is ignored.
>What am I missing?
You missed the discussion of a similar situation in Books Online, under
the heading "INSTEAD OF INSERT Triggers".
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

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
>