RI vs Speed

Hello,

We are in the process of setting up RI (mostly foreign keys, some sp's) on our SQL Server 2000 DBs. One of the questions that has come into play is how bad the performance hit will be once the RI has been put into place. We are using VFP 7 and C# for our front ends, BTW.

I was just wondering if there was anyone out there who has seen any performance difference between an application with RI and one without. If so, how bad of a hit was it, and what type of RI did you use (FKs, SPs, triggers, etc...). If anyone has any advice on how to reduce the performance hit, that would be appreciated as well!

Thanks!

-Justinthe question should not be stated in terms of performance of RI versus performance of not having RI

the question should be performance of RI versus performance of doing the same RI actions in application code

the answer should be, let the database run the RI actions

if you are considering giving up RI, you are saying you are willing to forego having valid data, and i don't believe that's a performance question, but an integrity question

let's consider a (somewhat contrived) analogy -- what if your bank said to you "every time we transfer money from your checking account to your savings account, do you want us to make sure the money actually arrived in the savings account? or would you like to give up that assurance in order to get your card out of the ATM a few milliseconds sooner?"

rudy
http://rudy.ca/|||We recently converted all of our data to SQL Server - currently we have NO RI in place. Some of the RI we have planned to put in place is critical, and will go no matter what the impact - we just want to be prepared. Some of it is somewhat redundant and not as necessary, and the dreaded management wants to weigh the pros and cons of putting this RI into place. This why I am asking the performance question.|||i understand, and sympathize :)

having myself been a clueless pointy-haired boss in the past, i can understand where they're coming from

however, i think you still need to make the point about the purpose of RI, which is to ensure data integrity

now, granted, if it's an existing application which is being modified to use a new database, then you have an interesting situation

presumably the existing app already takes care of RI concerns itself (e.g. not inserting an order for a customer that doesn't exist) so adding RI in these circumstances seems rather pointless

in other words, there's little to be gained (in a bottom-line cost-benefit sense) from adding RI constraints to a database for an app that's already working

when framed in this context, i would suggest setting up the database without RI to start with, taking several volume test performance measures, applying RI, and running the performance tests again

i predict the RI overhead will not be noticeable, or at least no more than a 5% increase

your mileage may vary, since so much depends on the actual tables and relationships involved

rudy|||Thanks for your reply :)

The application was actually written in VFP 6/7 and was designed to use the native FoxPro tables. once some of the DBCs (there are about 30) started nearing the 2 GB limit, we had to 'tweak' (word used by mgmt, it was more like rewrite) the program to be SQL Server compliant. In the midst of all this our DBA ran screaming for the hills, so I recently got the position. The hired guns who did the data conversion neglected to convert the extensive VFP RI on the old DBs to SQL (along with other insignificant things like memos and field descriptions), and now I am stuck in cleanup mode. Since the RI has to be rebuilt from the ground up, mgmt is taking the oppurtunity to sacrifice data integrity for customer-visible speed.

I've been doing some testing on mass updates with RI hooked and then unhooked, and have noticed about a 7% hit for the essential RI. Not a big deal considering we got a 238% speed increase once we converted to SQL Server DBs (the application goes over a WAN). I haven't wriiten the SPs and triggers for the 'nonessential' portion yet, and mgmnt doesn't want me to until they are comfortable it would 'not be a waste of my' + underpaid + 'time.'

Review of DB Design - Normalized, Contraints, Foreign Keys, etc.

First of all, this is my initial thread here on dbforums. I come from the land of Broadband Reports and would like to say, Hello fellow DB enthusiasts. :)

I'm not a novice to relational databases (Access MDBs), but new to implementing a db via SQL SERVER (2000 in this case) and using Access Data Projects.

My partial db schema is as follows:

participants
--DID (pk) char(1)
--LID (fk - schools) char(4)
--studentLast varchar(50)
--studentFirst varchar(25)

Sample Data would be
010191M001 | 5671 | SPARKS | JONATHAN
030495F283 | 5671 | DYLAN | CYNTHIA
=====================================

enrollhist (insert/update trigger for enrollactive)
--EID (pk - autonumber) bigint(8)
--EMID (fk - enrollmode) int(4)
--DID (fk - participants) char(10)
--LID (fk - schools) char(4)
--enrollactive bit(1)

Sample Data would be
38173 | 4 | 030495F283 | 9003 | 0
38266 | 3 | 010191M001 | 5671 | 0
39022 | 6 | 030495F283 | 9003 | 0
39036 | 5 | 030495F283 | 9003 | 0
39044 | 4 | 030495F283 | 5671 | 1
39117 | 4 | 010191M001 | 5671 | 1
=====================================

enrollmode
--EMID (pk) int(4)
--mode varchar(25)

Sample Data would be
1 | RECEIVED
2 | WAITING
3 | PENDING
4 | ENROLLED
5 | DROPPED
6 | TRANSFERRED
10 | ORPHANED
11 | DENIED
=====================================

schools
--LID (pk) varchar(4)
--CTID (fk - caltracks) char(1)
--AID (fk - agencies) char(1)
--SDID (fk - schooldist) char(1)
--COID (fk - countydist) char(1)
--sitename varchar(25)
--sitetitle varchar(75)

Sample Data would be
5671 | 3 | 2 | 1 | 4 | ASCOT | ASCOT AVENUE
9003 | 2 | 1 | 4 | 1 | ROWAN | ROWAN AVENUE
2865 | 1 | 3 | 2 | 3 | BRIGHT | BIRDELEE BRIGHT
=====================================

caltracks
--CTID (pk) char(1)
--legend char(4)
--trktitle varchar(15)
--trkcnt int(4)

Sample Data would be
1 | 9030 | 90/30 | 4
2 | CON6 | CONCEPT-6 | 3
3 | SNGL | SINGLE TRACK | 1
=====================================

agencies
--AID (pk) char(1)
--legend varchar(4)
--agencytitle varvhar(50)

Sample Data would be
1 | CRYS | CRYSTAL STAIRS
2 | MAOF | MEXICAN AMERICAN FOUNDATION
3 | PATH | PATHWAYS
4 | CCRC | CHILD CARE RESOURCE CENTER
5 | CHSC | CHILDREN'S HOME SOCIETY OF CALIFORNIA
==========================================

THE REMAINING "FKs" FROM SCHOOL ARE SIMILAR, as is other tables and their relationships. The design of the foreign keys were made using sql and the keyword "REFERENCES" and "FOREIGN KEY."

My questions are: :confused:
(1) Is the use of FK as a Constraint any different than using an INDEX and how?
(2) Should I Alter the Tables to include CASCADING Up/Down?
(3) Are the use of CHARs Ok for the Keys?
(4) Have I over/under-normalized any of the relationships?not to avoid your good questions, but i'll grab at the low hanging fruit...

the PK of 'participants' won't allow you to have many participants.
256 tops, it would appear.

ditto all your char(1) PKs|||You PKs are each 1 character? Doesn't seem too scalable to me...|||(oddity) Sorry about the TYPO, the PK for table.participants is CHAR(10) as shown as a FK in table.enrollhist

(blindman) The instances when I use CHAR(1) is truly not scalable, but the chances of the keys needing 2+ chars are slim to none. But, in the optimum chance of meeting that slim probability, I would have to agree with you.

I also have an additional question regarding the schema I have for the enrollhist table. Does anyone see any flaws in its design.

The implementation I want is to have the enrollmode and effective date (forgot the column for effective date in the schema listed [effdate datetime]) stored whenever there is a change in the status of the participant. The previous database had each of the modes as a column of the participants table. This is a no-no in my book!

thanks so far...
-bigEz|||Its not a no-no if each state can be achieved at most one time, but since this is probably not the case then your implementation is much more robust.

Reverse enginnering in VISIO 2002 (problem with sp_primarykey)

I have problem if I want to do reverse engineering in VISIO 2002.
The database uses primary key created with sp_primarykey an foreign keys with sp_foreingnkey

In VISIO - Database - Options - Drivers I had set the DDL script generationas follows:
Preffered version - 6.0
Generate primary key using - sp_primarykey
Generate foregin key using - sp_foreignkey

The process had passed without errors, but the relations didn't showed.
I tried option "Show related tables" but nothing didn't happend.

thanks.That could possibily be because of the loss of dependensies. The happens when you drop and recreate few objects in the database, which does ot re-establish the dependensies.

Try recompiling the objects or establishing the foreign keys again.

Thanks.

reverse engineering MS SQL server 2k5

Hi Alan
I can certainly import a database and foreign keys in SQL 2005, for instance
if I choose the northwind database and go through the wizard making sure that
FKs are imported and I choose the option to add the tables later to a
diagram. I can then sekect the customers tables from the tables and views box
and drag it onto the diagram. Then right clicking show related tables will
bring in the orders and customercustomerdemo tables with their foreign keys.
John
"Alain R." wrote:

> Hi,
> I got a DB with more than 100 tables.
> i tried to get it structure by Visio and its reverse engineering method
> but Visio does not draw "links" among tables representing PK and FK
> connections.
> So do you know any other software which could do the job ?
> maybe a simple freeware is enough, it is just to have an overview on
> this database and its objects.
> thanks a lot,
> A.
>
I had that issue also, I installed SP3 for Visio 2003 and it started working
after that . Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John Bell" wrote:
[vbcol=seagreen]
> Hi Alan
> I can certainly import a database and foreign keys in SQL 2005, for instance
> if I choose the northwind database and go through the wizard making sure that
> FKs are imported and I choose the option to add the tables later to a
> diagram. I can then sekect the customers tables from the tables and views box
> and drag it onto the diagram. Then right clicking show related tables will
> bring in the orders and customercustomerdemo tables with their foreign keys.
> John
> "Alain R." wrote:
|||Hi
I was using Microsoft Office Visio for Enterprise Architects (11.7218.8132)
SP2
John
"Mohit K. Gupta" wrote:
[vbcol=seagreen]
> I had that issue also, I installed SP3 for Visio 2003 and it started working
> after that . Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "John Bell" wrote: