# Partial FSDat indexes

Partial FSDat Indexes are used to speed up some FastStats queries for systems that have large transaction tables.

Every Multi-table FastStats system has a single master .FSDat file. This provides a map to the query engine of which table the records exist in. A record in a table contributes a single numeric record to the .FSDat file. As this .FSDat file increases in size then multi-queries will decrease in performance.

Not all multi-table queries require all the records in the .FSDat file. If you have the case of a large transaction table (for example Communications History) that is queried relatively infrequently then by generating a partial index you can speed up queries that do not require the transactional data.

> [!NOTE]
>
> **Example**
>
> In this example a partial .FSDat file is created that contains all the tables apart from the Comms History table.
>
> Consider a FastStats Demo System with additional Comms History Data. So the four main tables Households, People, Bookings, Insurances (\~4m records) plus an additional large Comms History table (248m records).
>
> The full FSDat file size for this system is 120 MB.
>
> Create a Partial FSDat that excludes the Comms History by adding a Post-Load action like this:
>
> Adding this index increases the base system size by 2MB but it means that any multi-table query that does not involve the Comms History table will run faster since these queries will scan the 2MB partial index instead of the 120MB full index.

## Recommendations

The partial index is generated with a filename that is the Post-load action description.

### What partial .fsdats should i create?

Create a partial .fsDat every time you have a big table in a separate relationship branch. So if the system above had another very large transaction table called Responses that join to the People table then create the following 3 partials FSDats for optimal query speeds:

- Index1: households, people, bookings, insurances
- Index2: households, people, bookings, insurances, comms history
- Index3: households, people, bookings, insurances, responses

### Can Designer work out what i need automatically?

Not yet.

### Should i create lots of partial .fsdats?

There is a diminishing benefit as you create more small .fsDats No point giving the query engine a choice between a small index and a very small index.

### Does it work with distributed processing?

Yes, and distributed processing will gain in the same way as sequential processing.