On Fri, Feb 7, 2014 at 2:10 PM, Simon Slavin <slav...@bigfraud.org> wrote:
> You're right. Let's see if I can make it up to you. I do think you came > up with the right strategy, and that doing INSERT OR IGNORE is going to > give you the best result overall. > Thanks. I myself couldn't come up with anything better. Two scans on a field which is indexed won't take long. Probably less time > in total than those MERGE commands or some complicated triggers. > I would expect the implementation of the merg e to do a single scan and then choose which branch to execute having kept the found record in memory, so I would expect it to be as fast as an UPDATE. Also, while not an issue in a trigger a merge is atomic; so is the insert or ignore strategy of course. > > A few notes: > > An alternative way to do it would simply be not to store current balances. > Whenever you want to know the balance as of a particular date, calculate > it using total(). Not only does it mean smaller files and faster inputs, > but you never have to sanity-check your totals and for a system which has > more inputs than reports it is faster overall. This technique is > increasingly used in systems used by banks and in double-entry bookkeeping. > > I assume you have a good reason for not keeping the CustomerAccum.amount > field in the customer table. You did mention that your real scenario was > more complicated than you were giving details of in your post, so that > would explain it. > > Also, SQLite does not have VARCHAR fields (they're stored as text and the > '50' will be ignored) or DATE fields. Dates must be stored as numbers or > text. FLOAT will be interpreted as REAL, and will work the same way, so > that's okay, but if I was doing something primarily for backing or > accounting purposes I wouldn't use a REAL field, I'd use INTEGER and > multiply by 100. > My example was made up to describe the case. The accumulated amounts table actually has much more dimensions, some of them relative to time. For instance, a fiscal year is divided into fiscal periods and the fiscal year and period of the transaction is a dimension as well. Also the table stores a running total as well as period totals. This allows for very fast balance reports, even when the requested time interval extends half-way between periods in which case I do a UNION ALL with transaction records for "half-way" transactions.and a GROUP BY/SUM() afterwards.Transaction processing will not be that intensive in my app's case, but balance reports need to be as fast as possible, that's why I use this concept of a "live cube" although its maintenance introduces an overhead in OLTP.. Column data types are named thus because the Delphi implementation of SQLite API I am using uses names of SQLite declared datatypes to map to dataset field types (TStringField, TIntegerField, TDateTimeField, etc.) a DATE field is considered to be a native datetime double and I have written an SQLite extension library that implements a bunch of manipulation functions to deal with datetimes. I consider float datetimes smaller in size and faster in calculations and comparisons that formatted strings, e.g. YYYY-MM-DDThh:nn:ss:zzz. The database needs to be able to be created in database engines of various vendors with only minor changes to table definitions and that may be handled by consultants, that's why you are seeing the terms VARCHAR(), FLOAT, DATE etc; they are familiar from other database vendors. As for scaled integers for amounts, if it was up to me I would prefer BCDs but this choice is out of my hands. --Constantine _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
