Splitting table

[Roy Goldhammer]

Hello there

I have some table with two many fields on the same table: 40

This table works very slow and i thing that maybe i will split the table to
many tables.

The question is: will splitting table will bring better preformance?

here is the splitting i think:

1. rare data: there is data that always being entered and there is data that
entered rarly. Is it right to move the rare data to new table?

2. this table provide me not only one type of data (all the record and not
some field) but 3 types of data that is very similar. The diffrance between
these data comes with 2 or 3 fields that use to all the diffrence. Will it
be smart to create new 3 tables of almost the same type exapt the data
diffrence and store all the data of the 3 types of data on each table?

 

[Roy Goldhammer]

Thankes Steve

But what you said is that i should leave the table stacture as it is.

So maybe the problem is in the second table who relate to the current table
with one to many.

This table have the current structure:
ID - autonumber of the the table
DataID - foreign key from the first table
Break- the break of the rate(using only on one of the cases)
RateType - there are two types of rates(1,2) - a key
Price type - data indexe foreign key
Rate- number
Cur - text
Min - Text

the present of the data is
Air Rate | Truck Rate
-------------------|-----------------
1.10 USD Per Kg | 500 USD Fixed

the type of payment is being declared by another table.

For the query that run this presentation takes 6 seconds to run.

Do you have some idea if the reason of the slow preformance relate to the
table structure?

 

[Roy Goldhammer]

Thankes steve

Here is the query:

TRANSFORM Min(IIf(IsNull([Break]),Format([Rate],"Fixed")+' '+[Cur]+'
'+[Unit]+nz(" Min: "+[min],""),"More...")) AS Expr1
SELECT tblSpotRates.DataID, tblSpotRates.RefChange
FROM tblSpotRates INNER JOIN tblUnits ON tblSpotRates.PriceType =
tblUnits.UintID
GROUP BY tblSpotRates.DataID, tblSpotRates.RefChange
PIVOT tblSpotRates.RateType In (1,2);
the current amout of data that present is 594 records.

Each record gives the rate of price offer.

Can you see the problem on this query?

any help would be useful