Forum

I need to generate an XML file for tax service agency and they are using special codes for the field names so I have a table with this codes (id, code, description) and it's used in other tables like one of the parameters.

When I generate the report I need to add this code to result table so I have two options:
1. Use a .dot syntax in a query and doing this include one table more to the query (tablename.parameter.code) or
2. Leave UUID in the query (tablename.parameter) and use a cashed function to get a code using the UUID

How to make the query and the future processing run more quickly? The resulting table could have up to millions of rows. The table containing the codes have only 30 records and this records repeatedly used.

I beliveve it depends on number of joins and number of fields you select. If you have tiny fields selected from a single main table, then your cached function might work faster and use less memory when used right in XML generation, but if your main query is complicated and have many columns, using this technique will produce more problems with support than a profit you will have.

I would recommend you to use an accumulation register for this report and fill it with enough data including codes and values of tax agency.

Right now I am using accumulation register and an additional filed like you recommend. But i still wondering is there a reason to add more additional fields to accumulation register. It's clearly lower the time to generate a report (which is necessary every month) but it will add some time to post the document, which is necessary several o several hundreds times a day. The additional space to save the data should be considered too.

Well, it is always up to you to decide, but the common way is to do as it is done currently. You need a very good proof to do in the other way due to the one who will support your code after you will not be happy spending time to understand your ideas.