Do pre-defined joins in SQL Server slown down inserts/deletions?

I wanted to know if SQL Server 2000 does something behind the scenes in a transparent manner whenever records are inserted/deleted from two tablesbetween which a join is defined based on a primary key to foreign key relationship. So I have already defined a parent-child relationship through the 'Database Diagram' between these 2 tables. I know when a table is indexed then SQL Server will perform some actions behind the scenes in a transparent manner.
The reason I am asking this question is to know if its bad to define parent-child relatioship between 2 tables that will each contain thousands or millions of records.
Hi,
I do not know that is the exact control structure for an insert but ifyou have some constraints on your columns as well as foreign keys, therecord you are trying to insert is controlled by means of thoseconstraints.
For example, you have a foreign key on CityId column referencing Citiestable. Then the column value you are inserting for CityId must bepresent at Cities table. So this is a job to check if the cityid reallyexists in Cities table. Perhaps as simple as select * from cities wherecityid = @.cityid . But this brings an extra work during the insert.
This causes a delay. But maintains data integrity within your database.And data integrity is more important for me. Because you can improveperformance by many methods (by improving hardware, sql code, sqlserver parameters,etc) but if you lose your data integrity, it will notbe easy perhaps impossible to maintain it back.

Indexes are very useful. But if you use so many indexes on a tableespecially which is used only for insert and updates (not for so muchread), every insert will mean a new update or insert on each index usedon that table.
In short, we have the plus and minus side by side. The outcome depends how and where you use them.
Eralper
http://www.kodyaz.com

|||EDIT
I don't think DRI(declarative referential integrity) will slow down inserts and deletes because one thing it is not insert related it just guarantees updates and deletes will cascade up or down based on what you set up.I am assuming you marked the insert relationship also but that is just for data integrity by enforcing the relationship between tables. It is ANSI SQL if you say cascade on delete no action it will not affect the other table but if you say on delete cascade then it will affect the other table. If your tables are too big you should look into creating views that will be smaller. Run a search for cascade delete and cascade update in SQL Server BOL (books online). Hope this helps.sql

distributed transaction conundrum

The following statement joins two tables in databases on different
servers
(n.b. rose is the 'linked' server):
select top 10 hhid, c.s21_cust_num
from rose.customer.dbo.customer rc
join customer c
on rc.s21_cust_num = c.s21_cust_num
where c.store_num = 232
When I run it interactively from query analyzer, it executes without
error and produces a result set.
When I create a stored procedure and run it in the SQL debugger, I
receive the following set of DTC errors (which is the same thing that
happens when our program tries to update the table on the 'remote'
server).:
create procedure selectcustomer
as
select top 10 hhid, c.s21_cust_num
from rose.customer.dbo.customer rc
join customer c
on rc.s21_cust_num = c.s21_cust_num
where c.store_num = 232
go
Server: Msg 7391, Level 16, State 1, Procedure selectcustomer, Line 5
[Microsoft][ODBC SQL Server Driver][SQL Server]The operation could not
be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
There is a firewall between the two servers, but it seems strange that
the queries would run interactively but fail when running under
program control.
Is there an explanation for that?| The following statement joins two tables in databases on different
| servers
| (n.b. rose is the 'linked' server):
|
| select top 10 hhid, c.s21_cust_num
| from rose.customer.dbo.customer rc
| join customer c
| on rc.s21_cust_num = c.s21_cust_num
| where c.store_num = 232
|
| When I run it interactively from query analyzer, it executes without
| error and produces a result set.
|
| When I create a stored procedure and run it in the SQL debugger, I
| receive the following set of DTC errors (which is the same thing that
| happens when our program tries to update the table on the 'remote'
| server).:
|
| create procedure selectcustomer
|
| as
|
| select top 10 hhid, c.s21_cust_num
| from rose.customer.dbo.customer rc
| join customer c
| on rc.s21_cust_num = c.s21_cust_num
| where c.store_num = 232
|
| go
|
| Server: Msg 7391, Level 16, State 1, Procedure selectcustomer, Line 5
| [Microsoft][ODBC SQL Server Driver][SQL Server]The operation could not
| be performed because the OLE DB provider 'SQLOLEDB' was unable to
| begin a distributed transaction.
|
| [OLE/DB provider returned message: New transaction cannot enlist in
| the specified transaction coordinator. ]
|
| OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
| ITransactionJoin::JoinTransaction returned 0x8004d00a].
|
| There is a firewall between the two servers, but it seems strange that
| the queries would run interactively but fail when running under
| program control.
--
This Technet article might help:
HOWTO: Troubleshoot MS DTC Firewall Issues
http://support.microsoft.com/default.aspx?scid=kb;en-us;306843
Cheers,
--
Eric Cárdenas
SQL Server support|||"Eric Cardenas" <ecard@.anonymous.com> wrote in message
news:<MmeQtGrvDHA.3520@.cpmsftngxa07.phx.gbl>...
> This Technet article might help:
> HOWTO: Troubleshoot MS DTC Firewall Issues
> http://support.microsoft.com/default.aspx?scid=kb;en-us;306843
> Cheers,
> --
> Eric Cárdenas
> SQL Server support
Been there, and yes, I've had that problem. But I have another problem, too.
I created a linked server pointed to the SQL server on my workstation. In
the following code both tables t1 and t2 are in the same db, but in the
sproc I'm referencing one through a linked server (called SQL01). It runs
from a query analyzer window but fails in the debugger, with the same 7391
error. Can you tell me why does it fail in the debugger?
drop table t1
create table t1
(k1 int identity, nk1 int, att1 char(10), date1 datetime, varchar1 varchar
(30))
drop table t2
create table t2
(nk2 int, att2 char (10), date2 datetime, varchar2 varchar (30))
insert into t1
(nk1, att1, date1, varchar1)
values
(100, 'pot', getdate(), 'this is a test record')
insert into t1
(nk1, att1, date1, varchar1)
values
(200, 'pan', getdate(), 'this is another test record')
go
drop procedure movedata
go
create procedure movedata
as
set xact_abort on
insert into sql01.pos_rawdata.dbo.t2
(nk2, att2, date2)
select nk1, att1, date1 from t1
update rt2
set varchar2 = t1.varchar1
from t1, sql01.pos_rawdata.dbo.t2 rt2 where t1.nk1 = rt2.nk2 and nk1 = 100
go
select * from t2
Thanks for any insights you might have.|||This is just a thought, but doesn't the debugger have to begin a transaction while debugging in order to allow you teh rollback option? If this is the case, then everything you need in order to begin a DISTRIBUTED TRANSACTION needs to be in place. I notice that you included the SET XACT_ABORT ON command, which I know is one of the required conditions. The rest can be found in the BOL.
I hope this helps somehow.
Matthew Bando
BandoM@.(REmove)CSCTechnologies.com
>--Original Message--
>"Eric Cardenas" <ecard@.anonymous.com> wrote in message
>news:<MmeQtGrvDHA.3520@.cpmsftngxa07.phx.gbl>...
>> This Technet article might help:
>> HOWTO: Troubleshoot MS DTC Firewall Issues
>> http://support.microsoft.com/default.aspx?scid=3Dkb;en-
us;306843
>> Cheers,
>> --
>> Eric C=E1rdenas
>> SQL Server support
>Been there, and yes, I've had that problem. But I have another problem, too.
>I created a linked server pointed to the SQL server on my workstation. In
>the following code both tables t1 and t2 are in the same db, but in the
>sproc I'm referencing one through a linked server (called SQL01). It runs
>from a query analyzer window but fails in the debugger, with the same 7391
>error. Can you tell me why does it fail in the debugger?
>drop table t1
>create table t1
>(k1 int identity, nk1 int, att1 char(10), date1 datetime, varchar1 varchar
>(30))
>drop table t2
>create table t2
>(nk2 int, att2 char (10), date2 datetime, varchar2 varchar (30))
>insert into t1
>(nk1, att1, date1, varchar1)
>values
>(100, 'pot', getdate(), 'this is a test record')
>insert into t1
>(nk1, att1, date1, varchar1)
>values
>(200, 'pan', getdate(), 'this is another test record')
>go
>drop procedure movedata
>go
>create procedure movedata
>as
>set xact_abort on
>insert into sql01.pos_rawdata.dbo.t2
>(nk2, att2, date2)
>select nk1, att1, date1 from t1
>update rt2
>set varchar2 =3D t1.varchar1
>from t1, sql01.pos_rawdata.dbo.t2 rt2 where t1.nk1 =3D rt2.nk2 and nk1 =3D 100
>go
>select * from t2
>Thanks for any insights you might have.
>
>.
>|||I think that was a really good call.
"Matthew Bando" <anonymous@.discussions.microsoft.com> wrote in message
news:02af01c3bf4a$bf73fa80$a101280a@.phx.gbl...
This is just a thought, but doesn't the debugger have to
begin a transaction while debugging in order to allow you
teh rollback option? If this is the case, then
everything you need in order to begin a DISTRIBUTED
TRANSACTION needs to be in place. I notice that you
included the SET XACT_ABORT ON command, which I know is
one of the required conditions. The rest can be found in
the BOL.
I hope this helps somehow.
Matthew Bando
BandoM@.(REmove)CSCTechnologies.com