Quote:
quote:Originally posted by Mitch
Jeff, All my tables have indexes, however when I went back to compair them and the ones in Access some of the fields in MS SQL are not indexed like they are in Access. I can create thoese that are missing.
And, I am not sure what you mean by: "all referential (FOREIGN KEY) constraints are defined?" I know what a "foreign key" is (at least I think I do) but not how to define it.
|
A FOREIGN KEY constraint (aka referential integrity constraint) defines a data relationship that exists between two tables. A foreign key in one table is typically a primary key in another. I say typically because in fact a foreign key constraint only need be a unique value (i.e. a column which has a unique index defined on it) in the other table. Of course, a primary key by definition is unique, so usually the constraints are set up referring to primary keys, but they don't have to be.
Anyway, the presence of a foreign key constraint insures that values in one table are guaranteed to be found in another.
The constraint is usually implemented (internally) by creating a unique index on the columns involved in the relationship. Then, the query processor can validate any update by doing a simple index lookup.
A side effect of this is that JOINs are made faster by the presence of these indexes. Since most often you construct JOINS based on such foreign key relationships, when you declare the relationship you get better performing JOINs "for free".
You can declare a foreign key relationship most easily by using the database diagram and clicking on a table's primary key column and dragging it to the referring table. A dialog will pop up that lets you define the columns involved in the relationship.
You can also use the ALTER TABLE ADD CONSTRAINT command, as, for example:
Code:
ALTER TABLE yourtable ADD
CONSTRAINT FK_yourconstraintname FOREIGN KEY
(someforeignkeycolumn)
REFERENCES someothertable
(someothertableprimarykey);
I have found that the upsizing wizard does not always do the best possible job defining indexes and constraints - you need to look the resultant table definitions over carefully to insure that all the indexes that you need have been properly defined.
Run the query in query analyzer again, and before you do, click Query->Show Execution Plan. After the query runs, inspect the tab labeled "Execution Plan". This shows what operations the query processor does to execute your query. In your case it will no doubt be somewhat complex. Look for operations labeled "table scan". These are sequential reads throughout the entire contents of a table (and obviously can be slow if the table is large). Look at the columns involved in the scan and consider defining an index on them.
You may also want to consider adding indexes on columns used in WHERE clauses, as this may help the process of selecting which rows are involved in JOINs. This can be overdone, as there is overhead involved in maintaining an index whenever a row is inserted, updated, or deleted.
You could also try running the "Index tuning wizard" located in the "query" menu in Query Analyzer. I have had mixed results using this tool, but it may be worth a try...
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Query runs forever
