(01-05-2018, 02:58 PM)Unknown98 Wrote: because the income statement uses almost exclusively SUM aggregate functions to get the data, it slows it down significantly
Sum the aggregate, warehouse the old data? Eg. At the end of the month, sum and store data, warehouse/archive the old stuff, and add total to the new month of transactions? Maybe indexing by months, then sum a month at a time.
(01-05-2018, 02:58 PM)Unknown98 Wrote: I'm having the same problem with the flight history database.
Only thing I can think of is leveraging indexes. Make sure they cover the values you search by. That way, when looking up values, you are not searching the whole database, just what is in that particular index. Or when selecting data for the flight history page, do it by primary key, rather than one of your columns, and limit the range of data. Eg. page 1 selects (last key ) - (last key -50) kind of thing. That way it doesn't have to load all the data, search through it, then do the select. You may already doing this.
Lastly, set up some caching, and maybe use some database in between your SQL and the engine, that stores it in memory or only stores aggregated data for speed. Lastly, if you are scaling to the point where you just need more power, putting the SQL on its own server helps to scale easier.