Select Inner.* from nested(outer)-nested(inner) query
in [Programming SQL]
|
From: MRe on 8 Jul 2010 14:30 On Jul 7, 5:16 pm, --CELKO-- <jcelko...(a)earthlink.net> wrote: > >> Why do you say it should be done at the front-end? << > > 1) In a tiered architecture, formatting is done in the front end and > not the DB. Would this still be the case with a web-site. From my understanding, DBs are great at storing and filtering massive amounts of data, but not so great at transferring it - so the query should be designed to return the smallest possible set. As I'm doing this for a web-site, with the user dynamically hopping around pages, I think the DB should filter the page > 2) I am used to having a report server. You write the basic query > once, throw it over the wall to the report server where it is sorted > and aggregated many different ways (totals by product, totals by > region, etc), sent out as emails (hre is your regional report), turned > into graphics (It must have a dancing bear in the top left corner of > the spreadsheet), and so forth. I would imagine in this scenario, the smallest set is not the page, as everything is immediately needed (if I understand correct, it's being put into a static output) > This is SOOOO much faster and safer than having a ton of DB side > routines for each report. > > >> these methods look good. I will certainly given them a try. << > > We have no timings on them --they were a programming exercise. Let me > know how theyw ork. I gave them a twirl. Note: I don't know enough about DB performance testing to give this meaningful timings, I usually just try a few different queries on my given example data-set, and take the one that worked best at the time (if it becomes a bottleneck later, it will be analyzed against the new data). But for what it's worth, here's my results: Notes: Celko is your larger query, CelkoLite is your smaller; Sommarskog is Erland Sommarskog's "performance implications" example of inserting into a temp table, and dropping the offending column; and Eliott.. actually, I have no idea what that one is Celko: 6.043 + 6.189 + 7.369 + 8.225 = 27.826 CelkoLite: 4.93 + 5.107 + 5.356 + 7.301 = 22.694 Sommarskog: 6.294 + 6.148 + 6.759 + 11.147 = 30.348 Eliott: 6.027 + 4.67 + 5.212 + 6.794 = 22.703 As I don't know enough about how to test for DB performance, what I did was write a program that ran 25 tests, each test ran 10 times. Each test was generated automatically by taking one table from our test database and, from that table, taking any random number of columns, in any order, then taking a random sub-set of these columns to sort by (with a random sort-order). Some of these tables are tiny, and others are huge, with all kinds of data-types and indexing. The 4 tests were run interleaved (it does Celko, CelkoLite, etc. once each, and repeats this 10 times) and the whole test was re-run 4 times with the order of the tests rotated, so one run would be (Celko, CelkoLite, Sommarskog, Eliott) and the next would be (Eliott, Celko, CelkoLite, Sommarskog) etc. Each of these re-runs make up the 4 times given. All re-run tests were again on randomly generated queries (so their times should not be compared to their previous or next, just to their neighbour query). They were timed using .NET's System.Diagnostics.Stopwatch (which I believe uses the Windows performance counters) Is this useful.. I'm not sure about it - the numbers show they're all quite similar (one isn't always faster than another) Thank you for your helps, Kind regards, Eliott
From: MRe on 8 Jul 2010 14:36 Hi Erland Sommarskog, > > Is it possible to "SELECT *" just the fields of a sub-nested query > > (i.e., I don't want * of a nested query, but of a nested-nested > > query)? > > No. OK. > You could do SELECT INTO a temp table, and then drop the row_number > column from the temp table, and then do SELECT * from the temp table. > > But don't this, as this can have some performance implications on > a busy system. I tried this in a performance test against the other examples (posted in response to Celko) and its performance is not bad. > SELECT * is after all considered bad practice. Understood. I never use * in practice, but as it was for an auto- generated wrapper, it seemed like the easiest way. I have been thinking about writing an SQL parser (I loove writing parsers soo much :) so I can populate the names (plus it would be good for some other auto-generated features I'd like to implement) so this might be an option for me too > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thank you kindly, Eliott
From: Erland Sommarskog on 8 Jul 2010 17:11 MRe (pgdown(a)gmail.com) writes: >> But don't this, as this can have some performance implications on >> a busy system. > > I tried this in a performance test against the other examples (posted > in response to Celko) and its performance is not bad. I said on a busy system. :-) This is the scoop: SQL Server caches temp table definitions, so that when you repeated call a stored procedure that creates a temp table, the definition from the temp table will be taken from the cache, which saves some time. But temp-table caching does not happen under all circumstances, and one case where it does no happen is when you change the table definition after the creation of the temp table. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Erland Sommarskog on 8 Jul 2010 17:19
MRe (pgdown(a)gmail.com) writes: > Would this still be the case with a web-site. From my understanding, > DBs are great at storing and filtering massive amounts of data, but > not so great at transferring it - so the query should be designed to > return the smallest possible set. As I'm doing this for a web-site, > with the user dynamically hopping around pages, I think the DB should > filter the page That's not the reason you want to get data page-by-page to a web server. Was it only raw performance, it would certainly be better to get the whole result set, and then page on the server. The way you do it now, you do a server roundtrip for each page, and if the query is not fast, that can be slow for the user. And if data changes, it can be interesting. But the problem if you do this indiscrimantly on a busy web site, the web server will explode, because it get lots of data from SQL Server which no user ever looks at. Personally, I hate web pages which only gives me a spoonful of data everytime. Give me at least 100 hits everytime! -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |