Hi,

the database in my project is growing and I'm looking for places to
improve the performance. For this I have the following question:

Is it worth to combine fields to get faster queries? For example I
have this query:

select * from SWIMRESULT SR join MEET M on SR.MEETID = M.MEETID
where
SR.STYLEID = 8 and SR.COURSE = 2 and SR.GENDER = 1
and M.ENDDATE >= '01.01.2006'
order by
SR.COURSE, SR.GENDER, SR.STYLEID, SR.SWIMTIME

the plan is:
PLAN JOIN (SR ORDER IX_SWIMRESULT_STYLE,M INDEX (PK_MEET))

the index IX_SWIMRESULT_STYLE is:
CREATE ASC INDEX ON SWIMRESULT (COURSE, GENDER, STYLEID, SWIMTIME);

The idea is to combine COURSE, GENDER and STYLEID in one database
field. For COURSE and GENDER one byte is enough and STYLEID two bytes
are ok. So I could use a field COURSEGENDERSTYLE of type INTEGER (32bit).


Then the query would be:
select * from SWIMRESULT SR join MEET M on SR.MEETID = M.MEETID
where
SR.COURSEGENDERSTYLE = 131336
and M.ENDDATE >= '01.01.2006'
order by
SR.COURSEGENDERSTYLE, SR.SWIMTIME


the index would be IX_SWIMRESULT_STYLE is:
CREATE ASC INDEX ON SWIMRESULT (COURSEGENDERSTYLE, SWIMTIME);



cu Christian