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.
Showing posts with label entity. Show all posts
Showing posts with label entity. Show all posts
Monday, March 26, 2012
entity relationship diagram
I have VS 2005 and SQL Server 2005 Express installed and I created 4 tables and setup the primary and foreign keys and can view the individual foreign key relationships by right clicking on the foreign key.
Is there a way to view a table relationship diagram (fields with primary and foreign keys), such as Access provides. Does VS 2005 or SQL Server 2005 have that capability?
Thanks
If you right-click on the folder named Database Diagrams, you can create a databse diagram. You can create a diagram here. To view the folder, click the plus sign next to the database that you want to create the diagram for.Entity Deletion Strategy
Hi,
I'm wondering what the standard practise is for dealing with the
following very common scenario:
You have users who can use your application and they are identified by
email address. Sometimes you want to delete one of these users. You
want to keep some reference to them in the DB for auditing purposes.
Also, you want to be able to free up that email address so it can be
used again.
It seems to me there are two options:
Keep the user in the User table but set its "status" to "deleted". The
problem thought is that now many of the queries against the user table
will have to check status.
Delete the user from the User table, but have it stored in some other
table, a UserAudit table for example.
Is there a standard way of doing this? If so, how is it done? Thanks.Hi
As long as the email address does not have a unique index or is the primary
key you can use the status flag. Depending on how many deleted users you have
(or if you see a significant degredation of performance) then you may or may
not want to partition the table (or create a partitioned view). To remove the
need to add the status check to every where clause you can create an active
users view (and keep table name) of the table. To implement a partioned view
would be the flip side of this (create an new table and transfer the table
name to the partitoned view), once you are using the active users table/view
there would be no T-SQL code change involved with changing over to the other
model.
John
"nickgieschen@.gmail.com" wrote:
> Hi,
> I'm wondering what the standard practise is for dealing with the
> following very common scenario:
> You have users who can use your application and they are identified by
> email address. Sometimes you want to delete one of these users. You
> want to keep some reference to them in the DB for auditing purposes.
> Also, you want to be able to free up that email address so it can be
> used again.
> It seems to me there are two options:
> Keep the user in the User table but set its "status" to "deleted". The
> problem thought is that now many of the queries against the user table
> will have to check status.
> Delete the user from the User table, but have it stored in some other
> table, a UserAudit table for example.
> Is there a standard way of doing this? If so, how is it done? Thanks.
>|||John Bell wrote:
> Hi
> As long as the email address does not have a unique index or is the primary
> key you can use the status flag. Depending on how many deleted users you have
> (or if you see a significant degredation of performance) then you may or may
> not want to partition the table (or create a partitioned view). To remove the
> need to add the status check to every where clause you can create an active
> users view (and keep table name) of the table. To implement a partioned view
> would be the flip side of this (create an new table and transfer the table
> name to the partitoned view), once you are using the active users table/view
> there would be no T-SQL code change involved with changing over to the other
> model.
> John
> "nickgieschen@.gmail.com" wrote:
> > Hi,
> >
> > I'm wondering what the standard practise is for dealing with the
> > following very common scenario:
> >
> > You have users who can use your application and they are identified by
> > email address. Sometimes you want to delete one of these users. You
> > want to keep some reference to them in the DB for auditing purposes.
> > Also, you want to be able to free up that email address so it can be
> > used again.
> >
> > It seems to me there are two options:
> >
> > Keep the user in the User table but set its "status" to "deleted". The
> > problem thought is that now many of the queries against the user table
> > will have to check status.
> >
> > Delete the user from the User table, but have it stored in some other
> > table, a UserAudit table for example.
> >
> > Is there a standard way of doing this? If so, how is it done? Thanks.
> >
> >
You can create a trigger and when you will delete rows deleted rows
will be inserted to history table. So you can create a primary key or
unique key on email address.
Regards
Amish Shah
http://shahamishm.tripod.com
I'm wondering what the standard practise is for dealing with the
following very common scenario:
You have users who can use your application and they are identified by
email address. Sometimes you want to delete one of these users. You
want to keep some reference to them in the DB for auditing purposes.
Also, you want to be able to free up that email address so it can be
used again.
It seems to me there are two options:
Keep the user in the User table but set its "status" to "deleted". The
problem thought is that now many of the queries against the user table
will have to check status.
Delete the user from the User table, but have it stored in some other
table, a UserAudit table for example.
Is there a standard way of doing this? If so, how is it done? Thanks.Hi
As long as the email address does not have a unique index or is the primary
key you can use the status flag. Depending on how many deleted users you have
(or if you see a significant degredation of performance) then you may or may
not want to partition the table (or create a partitioned view). To remove the
need to add the status check to every where clause you can create an active
users view (and keep table name) of the table. To implement a partioned view
would be the flip side of this (create an new table and transfer the table
name to the partitoned view), once you are using the active users table/view
there would be no T-SQL code change involved with changing over to the other
model.
John
"nickgieschen@.gmail.com" wrote:
> Hi,
> I'm wondering what the standard practise is for dealing with the
> following very common scenario:
> You have users who can use your application and they are identified by
> email address. Sometimes you want to delete one of these users. You
> want to keep some reference to them in the DB for auditing purposes.
> Also, you want to be able to free up that email address so it can be
> used again.
> It seems to me there are two options:
> Keep the user in the User table but set its "status" to "deleted". The
> problem thought is that now many of the queries against the user table
> will have to check status.
> Delete the user from the User table, but have it stored in some other
> table, a UserAudit table for example.
> Is there a standard way of doing this? If so, how is it done? Thanks.
>|||John Bell wrote:
> Hi
> As long as the email address does not have a unique index or is the primary
> key you can use the status flag. Depending on how many deleted users you have
> (or if you see a significant degredation of performance) then you may or may
> not want to partition the table (or create a partitioned view). To remove the
> need to add the status check to every where clause you can create an active
> users view (and keep table name) of the table. To implement a partioned view
> would be the flip side of this (create an new table and transfer the table
> name to the partitoned view), once you are using the active users table/view
> there would be no T-SQL code change involved with changing over to the other
> model.
> John
> "nickgieschen@.gmail.com" wrote:
> > Hi,
> >
> > I'm wondering what the standard practise is for dealing with the
> > following very common scenario:
> >
> > You have users who can use your application and they are identified by
> > email address. Sometimes you want to delete one of these users. You
> > want to keep some reference to them in the DB for auditing purposes.
> > Also, you want to be able to free up that email address so it can be
> > used again.
> >
> > It seems to me there are two options:
> >
> > Keep the user in the User table but set its "status" to "deleted". The
> > problem thought is that now many of the queries against the user table
> > will have to check status.
> >
> > Delete the user from the User table, but have it stored in some other
> > table, a UserAudit table for example.
> >
> > Is there a standard way of doing this? If so, how is it done? Thanks.
> >
> >
You can create a trigger and when you will delete rows deleted rows
will be inserted to history table. So you can create a primary key or
unique key on email address.
Regards
Amish Shah
http://shahamishm.tripod.com
Entity Deletion Strategy
Hi,
I'm wondering what the standard practise is for dealing with the
following very common scenario:
You have users who can use your application and they are identified by
email address. Sometimes you want to delete one of these users. You
want to keep some reference to them in the DB for auditing purposes.
Also, you want to be able to free up that email address so it can be
used again.
It seems to me there are two options:
Keep the user in the User table but set its "status" to "deleted". The
problem thought is that now many of the queries against the user table
will have to check status.
Delete the user from the User table, but have it stored in some other
table, a UserAudit table for example.
Is there a standard way of doing this? If so, how is it done? Thanks.Hi
As long as the email address does not have a unique index or is the primary
key you can use the status flag. Depending on how many deleted users you hav
e
(or if you see a significant degredation of performance) then you may or may
not want to partition the table (or create a partitioned view). To remove th
e
need to add the status check to every where clause you can create an active
users view (and keep table name) of the table. To implement a partioned view
would be the flip side of this (create an new table and transfer the table
name to the partitoned view), once you are using the active users table/view
there would be no T-SQL code change involved with changing over to the other
model.
John
"nickgieschen@.gmail.com" wrote:
> Hi,
> I'm wondering what the standard practise is for dealing with the
> following very common scenario:
> You have users who can use your application and they are identified by
> email address. Sometimes you want to delete one of these users. You
> want to keep some reference to them in the DB for auditing purposes.
> Also, you want to be able to free up that email address so it can be
> used again.
> It seems to me there are two options:
> Keep the user in the User table but set its "status" to "deleted". The
> problem thought is that now many of the queries against the user table
> will have to check status.
> Delete the user from the User table, but have it stored in some other
> table, a UserAudit table for example.
> Is there a standard way of doing this? If so, how is it done? Thanks.
>|||John Bell wrote:
[vbcol=seagreen]
> Hi
> As long as the email address does not have a unique index or is the primar
y
> key you can use the status flag. Depending on how many deleted users you h
ave
> (or if you see a significant degredation of performance) then you may or m
ay
> not want to partition the table (or create a partitioned view). To remove
the
> need to add the status check to every where clause you can create an activ
e
> users view (and keep table name) of the table. To implement a partioned vi
ew
> would be the flip side of this (create an new table and transfer the table
> name to the partitoned view), once you are using the active users table/vi
ew
> there would be no T-SQL code change involved with changing over to the oth
er
> model.
> John
> "nickgieschen@.gmail.com" wrote:
>
You can create a trigger and when you will delete rows deleted rows
will be inserted to history table. So you can create a primary key or
unique key on email address.
Regards
Amish Shah
http://shahamishm.tripod.comsql
I'm wondering what the standard practise is for dealing with the
following very common scenario:
You have users who can use your application and they are identified by
email address. Sometimes you want to delete one of these users. You
want to keep some reference to them in the DB for auditing purposes.
Also, you want to be able to free up that email address so it can be
used again.
It seems to me there are two options:
Keep the user in the User table but set its "status" to "deleted". The
problem thought is that now many of the queries against the user table
will have to check status.
Delete the user from the User table, but have it stored in some other
table, a UserAudit table for example.
Is there a standard way of doing this? If so, how is it done? Thanks.Hi
As long as the email address does not have a unique index or is the primary
key you can use the status flag. Depending on how many deleted users you hav
e
(or if you see a significant degredation of performance) then you may or may
not want to partition the table (or create a partitioned view). To remove th
e
need to add the status check to every where clause you can create an active
users view (and keep table name) of the table. To implement a partioned view
would be the flip side of this (create an new table and transfer the table
name to the partitoned view), once you are using the active users table/view
there would be no T-SQL code change involved with changing over to the other
model.
John
"nickgieschen@.gmail.com" wrote:
> Hi,
> I'm wondering what the standard practise is for dealing with the
> following very common scenario:
> You have users who can use your application and they are identified by
> email address. Sometimes you want to delete one of these users. You
> want to keep some reference to them in the DB for auditing purposes.
> Also, you want to be able to free up that email address so it can be
> used again.
> It seems to me there are two options:
> Keep the user in the User table but set its "status" to "deleted". The
> problem thought is that now many of the queries against the user table
> will have to check status.
> Delete the user from the User table, but have it stored in some other
> table, a UserAudit table for example.
> Is there a standard way of doing this? If so, how is it done? Thanks.
>|||John Bell wrote:
[vbcol=seagreen]
> Hi
> As long as the email address does not have a unique index or is the primar
y
> key you can use the status flag. Depending on how many deleted users you h
ave
> (or if you see a significant degredation of performance) then you may or m
ay
> not want to partition the table (or create a partitioned view). To remove
the
> need to add the status check to every where clause you can create an activ
e
> users view (and keep table name) of the table. To implement a partioned vi
ew
> would be the flip side of this (create an new table and transfer the table
> name to the partitoned view), once you are using the active users table/vi
ew
> there would be no T-SQL code change involved with changing over to the oth
er
> model.
> John
> "nickgieschen@.gmail.com" wrote:
>
You can create a trigger and when you will delete rows deleted rows
will be inserted to history table. So you can create a primary key or
unique key on email address.
Regards
Amish Shah
http://shahamishm.tripod.comsql
Entity ’ in SQL Server 2000 output
I have an application that queries a SQL Server 2000 db through an ODBC driver on my Win2000 machine.
The DB contains some french text e.g. ltranger stored in a nvarchar column.
When I query the DB the result contains the entity &# 8217; rather than the character itself. I don't want this and it doesn't happen in SQL Server 7.
Tried changing the collation from SQL_Latin1_General_Cp1_CI_AS but with no luck.
Does anyone have experience with this?
ThanksHi,
Did you change collation on the entire dB or on table and column ?
Which collation do you have as standard ?
Regards
Tommy|||Hi,
We've changed the collation of the individual dB/table, but not
the collation of the server (which is still SQL_Latin1_General_CP1_CI_AS), so as not to affect other dBs.
Is it likely to be a collation issue?|||Hi,
I'm not sure about that. I've tried to insert that text into a table with collation SQL_Latin1_General_CP1_CI_AS and the result in query analyzer is correct, have you tried select "column" from "table".
Is the result the same ?
Where did the dB data come from ?
You may check how it's inserted into the dB.
Let me know !
Tommy|||Hi,
Thanks for taking an interest.
The original data is probably generated in some Microsoft applications i.e. Word. I have no control over that.
Yes, the results look okay in query analyser, the problem occurs when I do SQL calls through an ODBC driver from a remote machine.
I don't think it's a driver problem because I get the correct characters when testing on SQL Server 7.
What has changed between 7 and 2000?
Help|||Ok,
That's a tricky one..
One of the things that changed in SQL 2000 is collations (the possibility to set collations on dB, tables and columns not just on the server).
and sometimes just change the collation on the table or columns or only the dB just ain't enough.
Some things you can do is:
Check the application, how/what does it when querying the dB, how do the question look like. (run SQL profiler) if you can't look in the code.
The collation on SQL 7 ?
How did you do the switch from SQL 7 to 2000 ?
Regional settings on the client. (? ? I'm not sure about this)
Anyone else have a succession ?
I can send you a proc that handles collations if you'll like.
Regards
Tommy|||Hi,
Basically we just send an SQLExecute command with a SELECT statement.
Of course I can parse out the & #8217 from the results but surely I shouldn't need to? (And also what about any other entities I may encounter?)
Anyway, thanks for the suggestions
The DB contains some french text e.g. ltranger stored in a nvarchar column.
When I query the DB the result contains the entity &# 8217; rather than the character itself. I don't want this and it doesn't happen in SQL Server 7.
Tried changing the collation from SQL_Latin1_General_Cp1_CI_AS but with no luck.
Does anyone have experience with this?
ThanksHi,
Did you change collation on the entire dB or on table and column ?
Which collation do you have as standard ?
Regards
Tommy|||Hi,
We've changed the collation of the individual dB/table, but not
the collation of the server (which is still SQL_Latin1_General_CP1_CI_AS), so as not to affect other dBs.
Is it likely to be a collation issue?|||Hi,
I'm not sure about that. I've tried to insert that text into a table with collation SQL_Latin1_General_CP1_CI_AS and the result in query analyzer is correct, have you tried select "column" from "table".
Is the result the same ?
Where did the dB data come from ?
You may check how it's inserted into the dB.
Let me know !
Tommy|||Hi,
Thanks for taking an interest.
The original data is probably generated in some Microsoft applications i.e. Word. I have no control over that.
Yes, the results look okay in query analyser, the problem occurs when I do SQL calls through an ODBC driver from a remote machine.
I don't think it's a driver problem because I get the correct characters when testing on SQL Server 7.
What has changed between 7 and 2000?
Help|||Ok,
That's a tricky one..
One of the things that changed in SQL 2000 is collations (the possibility to set collations on dB, tables and columns not just on the server).
and sometimes just change the collation on the table or columns or only the dB just ain't enough.
Some things you can do is:
Check the application, how/what does it when querying the dB, how do the question look like. (run SQL profiler) if you can't look in the code.
The collation on SQL 7 ?
How did you do the switch from SQL 7 to 2000 ?
Regional settings on the client. (? ? I'm not sure about this)
Anyone else have a succession ?
I can send you a proc that handles collations if you'll like.
Regards
Tommy|||Hi,
Basically we just send an SQLExecute command with a SELECT statement.
Of course I can parse out the & #8217 from the results but surely I shouldn't need to? (And also what about any other entities I may encounter?)
Anyway, thanks for the suggestions
Subscribe to:
Posts (Atom)