In my case I had monster joins many times to make only 5 #tables. The refactor gets the bare minimum of rows from the primary table with only a few joins to make my primary #temp_bal table. I then verify if I need to get amounts for the first numeric column, opening balance, or not depending on period chosen. I then sum the amount column to cover between the periods. Last bit is to bring non expense opening bals from other table.
For period 13 it use to take 7-15 min to run. Now under 1 min, returns 16,000 rows on average. On Tue, Jan 20, 2015 at 10:42 AM, Ken Kixmoeller (ProFox) < [email protected]> wrote: > I always pre-select, too, but I often do it in two steps. Pre-select query > #1 is the simplest, fastest SQL which eliminates the obvious 99% of data > not needed for this answer. The second refines the raw results into the > precise ones. I find that two queries like this can run in 2-3 seconds, > where a single SQL statement to get the precise results can take 5x the > time. Plus, the two statements are easier to write! > > FWIW, > > Ken > > On Mon, Jan 19, 2015 at 7:13 PM, Gene Wirchenko <[email protected]> wrote: > > > At 13:39 2015-01-16, Stephen Russell <[email protected]> wrote: > > > > [snip] > > > > I can select the raw rows in a Select * from Where t_yer = 2014 and get > >> them in just over 2 min. > >> > >> Or I Pre-Sum the bastards with a group by and only bring back 48 > thousand > >> rows in 20 seconds. > >> > > > > Yes, I preprocess. Why grovel through more data than needed? > > > > Sincerely, > > > > Gene Wirchenko > > > > [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/CAJidMYLRh+j6bOzA9XRYP=MuTz5KfbbsU80CBzA+0GV01Z=n...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
