On Mon, Mar 2, 2009 at 6:36 PM, Ashley Sheridan <ash@xxxxxxxxxxxxxxxxxxxx> wrote: > On Mon, 2009-03-02 at 09:30 -0600, Boyd, Todd M. wrote: >> > -----Original Message----- >> > From: Andrew Ballard [mailto:aballard@xxxxxxxxx] >> > Sent: Saturday, February 28, 2009 12:05 AM >> > To: ash@xxxxxxxxxxxxxxxxxxxx >> > Cc: Boyd, Todd M.; PHP General list >> > Subject: Re: Re: How important is your Express or Web Edition >> > database? Please weigh in-- >> > >> > On Fri, Feb 27, 2009 at 7:32 PM, Ashley Sheridan >> > <ash@xxxxxxxxxxxxxxxxxxxx> wrote: >> > > On Fri, 2009-02-27 at 16:41 -0600, Boyd, Todd M. wrote: >> > >> > -----Original Message----- >> > >> > From: Andrew Ballard [mailto:aballard@xxxxxxxxx] >> > >> > Sent: Friday, February 27, 2009 3:26 PM >> > >> > To: Bastien Koert >> > >> > Cc: Shawn McKenzie; php-general@xxxxxxxxxxxxx >> > >> > Subject: Re: Re: How important is your Express or Web >> > Edition >> > >> > database? Please weigh in-- >> > >> I use SQLExpress (SQL Server Express) all the time at work for >> > prototyping and such... although, I have to say--if my company hadn't >> > installed it on my machine to begin with, and they weren't running SQL >> > Server 2005 on the production servers, I would rather just use a >> > private MySQL installation for prototyping and then push to a MySQL >> > production server. Alas... >> > >> >> > >> >> > >> // Todd >> > > For me it's MySQL all the way. My company is too cheap to pay for >> > later >> > > versions of MS SQL Server, so the versions we have there are *very* >> > > limited in features (for example, no limit function!) MySQL also >> > seems a >> > > lot faster for me too. I regularly deal with large databases (think >> > > millions of records) and MSSQL is a real bottleneck here, whereas >> > MySQL >> > > seems fine (althogh, it is running on Linux, which frees up more >> > > resources for actually getting stuff done!) >> > > >> > > Oh, funny thing. I filled in the questionnaire above, and when it got >> > to >> > > the final 'thanks' page, I clicked the button, and it bombed out to a >> > > completely blank page. Doesn't bode too well for a company attempting >> > to >> > > sell a product for use in enterprise situations! >> > > >> > > >> > > Ash >> > > www.ashleysheridan.co.uk >> > > >> > >> > It all depends on what you need. I know from your previous posts that >> > you're not very well disposed to SQL Server, but I've used it quite a >> > bit now for the last 8 years and haven't really had any problems with >> > performance. I'll grant that it doesn't have the LIMIT clause (Is it >> > part of the actual ANSI SQL spec, or is it something handy that MySQL >> > added to their product?) The newer versions offer a row number >> > function that can be used to provide the the same functionality, but >> > I'll admit it is not nearly as simple as being able to say LIMIT 25, >> > 50. >> > >> > While I like MySQL, it has its oddities as well. I've run into >> > situations where I had to add ORDER BY clauses to UPDATE statements >> > (I'm not sure that's really valid SQL either) because it updated the >> > rows sequentially and validated a unique index after each row rather >> > than after all the rows were processed. I wish it would support CHECK >> > constraints. And as convenient as I've found the SET and ENUM >> > datatypes in simple databases, I'm coming to the notion that they are >> > not a good idea in most situations. And while the availability of >> > different engines has benefits, it can also cause issues. >> >> Wait, wait, wait... I know SQL Server doesn't have "LIMIT", but haven't you guys ever used "TOP"? As in... >> >> select top 10 * from some_table where some_column = 'some_value'; >> >> ?? I'm not sure about getting lower bounds (maybe there is a BOTTOM, but I'm too lazy right now)... but if you're just trying to limit the number of rows in your result with a cap, then TOP does the trick just fine. >> >> I've had to do a lot of searching to find ways to do stuff in SQL Server that were already natural for me in MySQL (as I learned on MySQL and develop independently with it), but I have yet to be completely taken aback by something that's missing in SQL Server. (I am a little miffed that you have to do a sub-query on information_schema in order to test for object existence, though.) >> >> Anyway, I don't see what all the anti-MSSQL sentiment is all about. I use it all the time (SQL Express, SQL Server 2000 and 2005 Professional) and I don't find myself wanting for something I could have done in MySQL but cannot do in MSSQL. >> >> SSIS packages are pretty sweet to work with, BTW, if you've ever needed to build DTS solutions. :D >> >> My 2c, >> >> >> // Todd > There isn't a BOTTOM, the solution looks something like this: > > SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 20 * FROM table WHERE > clause ORDER BY col) AS temp ORDER BY col DESC) as temp2 ORDER BY col > > It's an awful mess, but was the only way I found to select results x to > y in a reliable manner. > > > Ash > www.ashleysheridan.co.uk > Yep. That's one of the hacks I've seen that *almost* works. The flaw I've found is that if the complete result set is not evenly divisible by the page size, the last page repeats enough records from the previous page to display a full page. I've seen a couple approaches that "work" with SQL 2000 and higher. 1) Load the entire matching result set into a temporary table or table variable that includes its own identity (auto-increment) column and then SELECT * FROM @temp WHERE id BETWEEN @rowfirst AND @rowlast. This works, but it is not at all efficient if the overall result set is very large since the server has to copy the entire set into temporary storage. 2) Something very close to what you posted, except that rather than simply selecting the TOP 10 from the BOTTOM 20, it adds a filter to find the TOP 10 of the BOTTOM 20 that are not also in the BOTTOM 20. If you're writing a single query against a single table with a known primary key, this is not too much more difficult. I had a much more difficult time revising it to work inside a Zend_Db_Adapter/Zend_Db_Statement since it has to work regardless of the primary key, even on joined recordsets. The rest I've seen involve the ROWNUMBER() with or without CTEs, so they are limited to 2005 and above. The variations are designed to limit the size of the rowset inside the CTE and/or to leverage indexes to increase the efficiency of the execution plan used by the database. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
