March 6, 2003 at 2:35 am
Hi,
Have any idea about this following :
I. How to create correct KEY and INDEX ?
Example :
TABLE : STOCK_BALANCE
-YEAR
-MONTH
-PRODCODE
-BARCODE
-QTY_BEGINNING
-QTY_IN
-QTY_OUT
-UNITCOST
... etc
Currently, the keys that I have created are 1 unique primary key (with 4 fields)
and 1 index key for this table.
Primary key
1.Year
2.month
3.Prodcode
4.Barcode
Index key (idx_ProdCode)
1.Prodcode
2.Barcode
My Question are :
1.Is it correct to create primary key with many fields ?
2.Is it better, if i split to a few index keys ?
for example :
--> a.create 2 key/index
--->a.1.Primary key (non unique)
---->1.prodcode
---->2.barcode
--->a.2.Idx_StockPeriod
---->1.year
---->2.month
or,
--> b.create 4 index
b.1. idx_year
- year
b.2.idx_month
- month
b.3.idx_prodcode
- prodcode
b.4.idx_barcode
- barcode
3.have any good idea/ suggestion?
II. Is it need to create key for searching ?
Example :
TABLE : PRODUCT
-PRODCODE
-Barcode
-Title1
-Title2
-DEPARTMENT
-BRAND
-CATEGORY
-COMMODITY
-STATUS
... ETC
My application provide the searching feature with several fields.
Because of the data keep on increasing, the performance become slower.
Searching/filter list by
-PRODCODE
-Barcode
-Title1
-Title2
-DEPARTMENT
-BRAND
-CATEGORY
-COMMODITY
-STATUS
I only have 1 primary key (Unique)
1.ProdCode
2.Barcode
Is it necessary for me to create more Index keys to support all fields that i use for
searching or filtering ? Or just leave it let SQL server handle it!
for example :
add 7 index key (i think should be slower, or no need create key?)
1. Idx_title1
- title1
2. idx_title2
- title2
3. idx_department
- department
4. idx_brand
- brand
5. ... etc
Thx,
Jonny
March 6, 2003 at 5:05 am
With regards to a wide index. It is not bad if it is a clustered index and the only index in the table. If no the only then keep in mind all non-clustered indexes reference the key values in the clustered index, so the wider the clustered index the wider the non-clustereds will be.
Also, You don't want to have many wide non-clustered indexes simple because you are repeating the data. And you do not want to use the same columns in multiple indexes (rememeber SQL can do index intersection, that is use multiple indexes to determine best results).
Now as for giving what I think may best help you, can you first tell me what the STOCK_BALANCE table does. What is it's purpose and what data does it collect for what periods?
March 7, 2003 at 2:03 am
Stock_Balace table is the history table which use to keep all the inventory transacation and stock balance information.
usually i use it to create many kind of inventory reports.
Thx,
Jonny
March 7, 2003 at 8:14 am
quote:
...Primary key (non unique)---->1.prodcode
---->2.barcode
...
It is not possible to have a non-unique primary key.
Suggestion:
Think about having an IDENTITY field PRIMARY KEY, and putting a UNIQUE CONSTRAINT across the fields required for integrity of the data. Then, after monitoring performance of stored procs and query plans, put indexes on those fields which execution plans are producing the most tables scans. Take a close look at the search arguments (WHERE clauses) in your procedures and consider indexes on those fields most frequently filtered on.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply