entity relationships and performance

I have large, very heavily used database (SQL 2000). Performance is critical. I have a job that runs on a nightly basis to delete records no longer need from specific tables (as to keep them manageable).

My question, does have entity relationships on these tables adversely affect performance? The cleanup batch job is taking so long now that I'd like to delete table relationships to speed it up.

I understand the value for maintaining data integerity, but any insight as to how relationships affect performance?

Thanks.Well, I delete the relationships. The batch job that deleted records went from 6-10 hours to 22 minutes.

So, while I don't think there is a major performance hit by having relationships established and doing selects/inserts/updates, there obviously is when it comes to deletes.|||whether you are doing inserts, updates or deletes there wil lalways be a "HIT" due to RI. Insert or Update 10,000 records at one time, both with and without RI and you will see a diffrence. The question is does removal of RI justify the speed over potentialy corrupted data.|||Obviously, the performance gain was substantial. Are there any performance gains via relationships, i.e. does the query optimizer leverage relationships when creating execution plans?|||No. The optimizer looks at indexes, statistics and hints to decide the best query plan.
Referential integrity is there solely to enforce the quality of the data.

Enterprise Manager runs slow on some computers

I have a network of WinXP SP1 machines who all connect to
SQL Server 2000 SP3 on a Win2k server. When running
enterprise manager, most of the computers take at least 4
minutes to expand the database tree. Working on databases
(retrieving properties etc) is also painfully slow. On
two computers, however, Enterprise Manager shows no
slowdown and work as I would expect. Can anyone assist me
in diagnosing the problem?I would guess that one of the snap ins on the slower machines might be
causing a problem, I had something similar happen to me only for me the
enterprise manager wouldn't open at all.
On one of the slower machines try opening the Microsoft Management Console
program (C:\windows\system32\mmc.exe) and click on file and add/remove snap
ins. CLick the Add button and then select the Microsoft SQL Enterprise
manager. Click Ok and then try to open the database tree to see if its any
quicker. If so you can then click save as and use this console file instead
of the enterprise manager. If not then I'm afraid i can;t help you.
Nick
"G. Sutherland" <g.sutherland@.REMOVEkildrummy.co.uk> wrote in message
news:5dbc01c474b6$da345870$a501280a@.phx.gbl...
> I have a network of WinXP SP1 machines who all connect to
> SQL Server 2000 SP3 on a Win2k server. When running
> enterprise manager, most of the computers take at least 4
> minutes to expand the database tree. Working on databases
> (retrieving properties etc) is also painfully slow. On
> two computers, however, Enterprise Manager shows no
> slowdown and work as I would expect. Can anyone assist me
> in diagnosing the problem?|||Check to ensure that the AutoClose properties of the databases are not set
in EM on the slow machines.
Andrew J. Kelly SQL MVP
"G. Sutherland" <g.sutherland@.REMOVEkildrummy.co.uk> wrote in message
news:5dbc01c474b6$da345870$a501280a@.phx.gbl...
> I have a network of WinXP SP1 machines who all connect to
> SQL Server 2000 SP3 on a Win2k server. When running
> enterprise manager, most of the computers take at least 4
> minutes to expand the database tree. Working on databases
> (retrieving properties etc) is also painfully slow. On
> two computers, however, Enterprise Manager shows no
> slowdown and work as I would expect. Can anyone assist me
> in diagnosing the problem?|||Check for a file called SQL.LOG on the slow machines. If it's there and is
growing, you've got ODBC Tracing turned on.
Otherwise, run a network trace and compare the protocols being used from
the slow vs. fast.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Also, in EM, you can click on File|Options... and choose to delete the
history of changes to the console files. This can speed up performance as
well.
Jacco Schalkwijk
SQL Server MVP
"G. Sutherland" <g.sutherland@.REMOVEkildrummy.co.uk> wrote in message
news:5dbc01c474b6$da345870$a501280a@.phx.gbl...
>I have a network of WinXP SP1 machines who all connect to
> SQL Server 2000 SP3 on a Win2k server. When running
> enterprise manager, most of the computers take at least 4
> minutes to expand the database tree. Working on databases
> (retrieving properties etc) is also painfully slow. On
> two computers, however, Enterprise Manager shows no
> slowdown and work as I would expect. Can anyone assist me
> in diagnosing the problem?|||Yes you fixed my prob-, SQL TRACING WAS TURNED ON.
I have a very fast computer and it still made E-P-M very slow alymost
unuseable... Found c:\sql.log file 2.5 GB's in size ha ha...
Turned Tracing off works very fast now. cool!
Hummm, Now to find out how it got turned on in the first place ? Who
did it did you ? why you little #&*&#?! get over here!
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Enterprise Manager runs slow on some computers

I have a network of WinXP SP1 machines who all connect to
SQL Server 2000 SP3 on a Win2k server. When running
enterprise manager, most of the computers take at least 4
minutes to expand the database tree. Working on databases
(retrieving properties etc) is also painfully slow. On
two computers, however, Enterprise Manager shows no
slowdown and work as I would expect. Can anyone assist me
in diagnosing the problem?
I would guess that one of the snap ins on the slower machines might be
causing a problem, I had something similar happen to me only for me the
enterprise manager wouldn't open at all.
On one of the slower machines try opening the Microsoft Management Console
program (C:\windows\system32\mmc.exe) and click on file and add/remove snap
ins. CLick the Add button and then select the Microsoft SQL Enterprise
manager. Click Ok and then try to open the database tree to see if its any
quicker. If so you can then click save as and use this console file instead
of the enterprise manager. If not then I'm afraid i can;t help you.
Nick
"G. Sutherland" <g.sutherland@.REMOVEkildrummy.co.uk> wrote in message
news:5dbc01c474b6$da345870$a501280a@.phx.gbl...
> I have a network of WinXP SP1 machines who all connect to
> SQL Server 2000 SP3 on a Win2k server. When running
> enterprise manager, most of the computers take at least 4
> minutes to expand the database tree. Working on databases
> (retrieving properties etc) is also painfully slow. On
> two computers, however, Enterprise Manager shows no
> slowdown and work as I would expect. Can anyone assist me
> in diagnosing the problem?
|||Check to ensure that the AutoClose properties of the databases are not set
in EM on the slow machines.
Andrew J. Kelly SQL MVP
"G. Sutherland" <g.sutherland@.REMOVEkildrummy.co.uk> wrote in message
news:5dbc01c474b6$da345870$a501280a@.phx.gbl...
> I have a network of WinXP SP1 machines who all connect to
> SQL Server 2000 SP3 on a Win2k server. When running
> enterprise manager, most of the computers take at least 4
> minutes to expand the database tree. Working on databases
> (retrieving properties etc) is also painfully slow. On
> two computers, however, Enterprise Manager shows no
> slowdown and work as I would expect. Can anyone assist me
> in diagnosing the problem?
|||Check for a file called SQL.LOG on the slow machines. If it's there and is
growing, you've got ODBC Tracing turned on.
Otherwise, run a network trace and compare the protocols being used from
the slow vs. fast.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Also, in EM, you can click on File|Options... and choose to delete the
history of changes to the console files. This can speed up performance as
well.
Jacco Schalkwijk
SQL Server MVP
"G. Sutherland" <g.sutherland@.REMOVEkildrummy.co.uk> wrote in message
news:5dbc01c474b6$da345870$a501280a@.phx.gbl...
>I have a network of WinXP SP1 machines who all connect to
> SQL Server 2000 SP3 on a Win2k server. When running
> enterprise manager, most of the computers take at least 4
> minutes to expand the database tree. Working on databases
> (retrieving properties etc) is also painfully slow. On
> two computers, however, Enterprise Manager shows no
> slowdown and work as I would expect. Can anyone assist me
> in diagnosing the problem?
|||Yes you fixed my prob-, SQL TRACING WAS TURNED ON.
I have a very fast computer and it still made E-P-M very slow alymost
unuseable... Found c:\sql.log file 2.5 GB's in size ha ha...
Turned Tracing off works very fast now. cool!
Hummm, Now to find out how it got turned on in the first place ? Who
did it did you ? why you little #&*&#?! get over here!
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

enterprise manager for SQL 2000

Hello,
I have been using SQL 7 for some time and so have the
Enterprise manager to match. I have just started work on a
remote SQL server, which runs SQL 2000 - and so my
Enterprise manager can't communicate with it.
I have looked everywhere, but can't see a way of
controlling the remote server without upgrading my SQL
server to version 2000. This seems a bit of an overkill
when all I want to do is create and import a few tables on
the remote server.
Do I really have to pay for a whole system upgrade, even
though I don't want to use SQL 2000 here, just so I can
import some tables? I can't seem to locate an alternative
Microsoft solution.
Thanks.
DCIf you upgrade your CLient Utilites to SQL2k you will be able to communicate
with the SQL7 and SQL2K boxes. It's backward compatible.
--
HTH
Ryan Waight, MCDBA, MCSE
"david cox" <davidcox@.mail.com> wrote in message
news:044401c37d08$f422d640$a001280a@.phx.gbl...
> Hello,
> I have been using SQL 7 for some time and so have the
> Enterprise manager to match. I have just started work on a
> remote SQL server, which runs SQL 2000 - and so my
> Enterprise manager can't communicate with it.
> I have looked everywhere, but can't see a way of
> controlling the remote server without upgrading my SQL
> server to version 2000. This seems a bit of an overkill
> when all I want to do is create and import a few tables on
> the remote server.
> Do I really have to pay for a whole system upgrade, even
> though I don't want to use SQL 2000 here, just so I can
> import some tables? I can't seem to locate an alternative
> Microsoft solution.
> Thanks.
> DC