Composite Keys vs Single Columns

[benasumwa]

Hi friends!


I have a table with 12 months' payroll data for over 17,000 employees. The table has nearly 3 million rows.

The field

 has payroll items such as Basic Salary,
Bonus e.t.c. coded with numericals eg 001,100,738 and so on

The field [pfnumber] is employee's personal file no.

I have a primary key defined as follows:

[code]ADD CONSTRAINT c_period PRIMARY KEY CLUSTERED (YEAR,MONTH,CODE,PFNUMBER)[\code]

my query is as follows:

[code]SELECT PFNUMBER,CODE,AMOUNT,BALANCE,YEAR,MONTH FROM PROCESED WHERE YEAR=2004 AND CODE=738[\code]

It takes over 10min to process

If I modify it to include a payroll month as follows:

[code]SELECT PFNUMBER,CODE,AMOUNT,BALANCE,YEAR,MONTH FROM PROCESED WHERE YEAR=2004 AND MONTH =1 AND CODE=738[\code]

it takes 6 seconds. Pretty fast!

I need help on two things:

1. Somebody discouraged the use of composite keys 
   as in my case, I have never understood why. What
   are the pros & cons?
 
2. What can I do to speed my first query?
   (I do not need the month in it, I need records for
    the whole year)

(Please I am learning, the hard way, after 9years burried
in DBFs with no DBCs)

I am using VFP6 with MSDE 2000 on a Windows 98 box.

Thanks

Benson

 

[Mike Gagnon]

Read up on sys(3054) in the help file. Do some tests, and see how you can improve the speed of your query.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[Mike Lewis]

Benson,

What back end are you working with?

The reason I ask is that VFP doesn't support composite indexes, nor is there an ADD CONSTRAINT command, nor do we have clustered indexes.

The nearest equivalent of a composite index is an index on an expression. In order to optimise a query, the expression in the WHERE clause (or ON clause) has to match the expression that is being indexed.

To create an index, use INDEX ON rather than ADD CONSTRAINT. And don't use the keyword CLUSTERED -- it will generate an error.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[benasumwa]

Hi Mike

My Backend is MSDE 2000 (Read SQL Server). No VFP tables here.

... ADD CONSTRAINT c_period PRIMARY KEY CLUSTERED (YEAR,MONTH,CODE,PFNUMBER)[\code]

is the way i did it on SQL server. (shown in part)

By the way, could I have misunderstood the meaning of
composite keys? I thought it's a key defined with many columns in it's expression as above.


Carry on Mike

Thanks
Benson

 

[benasumwa]

Hi Mike (Yearwood)

Thanks Mike I think I have understood. Although
I really wanted to know if one should have indices
on all columns used in the WHERE clause of a query since
composite keys are detrimental to optimization (as I can see).

Is that the case?

Thanks once again.

 

[Mike Lewis]

Benson,

could I have misunderstood the meaning of
composite keys? I thought it's a key defined with many columns in it's expression as above.

Your understanding is correct. It's just that VFP doesn't support the syntax. In VFP, every key is an expression. The equivalent of a composite key would be an expression that concatenates several values, for example:

TRANS(year) + TRANS(month) + CODE

Although you'll probably get some good advice from the folk in this forum (including Mike Yearwood -- see above), you might try posting your question in a SQL Server-related forum, as the optimisation issues are not really VFP-related.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[benasumwa]

Thanks Mike/Mike

I'll do exactly that. Great guys!

Benson

 

[Olaf Doschke]

Hi Benson,

I'd say, as your composite key is on (YEAR,MONTH,CODE,PFNUMBER) a query on a certain year, month and code may be optimized (6 seconds as you said),
while a query without a value for the month will not be optimized (10 mins).

Have seperate indexes on year, month, code and pfnumber additional to that primary key and also this queries should run faster. On the other side simply add a field to hold some primary key, as myearwood said: The problem with composite keys is that it results in a huge primary key.

Bye, Olaf.

 

[benasumwa]

Olaf,

Thanks. I made up my mind to do that. You know, I am just learning SQL so I got some utility to act as DTS and ported
all my data to SQL server. I purposely made that table
huge enough for testing so that I can work with real
statitics on what works best/worst. I think I am getting there.


Thanks you guys for continual help. You are great guys!


Benson