Re: IDS Next Version - features wanted - now widely off topic

rkusnet wrote: > "Neil Truby" <neil.truby@ardenta.com> wrote in message news:3h67oaFfho6cU1@individual.net... > > As for "responding to the competition": as per previous posts, from where I > > sit that competiton is SQL Server and not, as the recent US-based speakers > > here perceive, Oracle. > > > watch out when SS 2005 comes out later this year. It will wipe out Informix from UK. > > Key features of SS 2005 > > * table level partioning > > * database mirroring with automatic client redirection. > > * online index and table rebuild. > > * oracle like READ CONSISTENCY isolation mode > > * vastly improved TSQL and error handling. > > The last point is important for me bcos I was pissed off with TSQL's error handling > capability. > > I predict that with SS2005, SS will start posing serious threat to Oracle and DB2 in > enterprise > installations. Their tools was always excellent (Enterprise Manager, Query Analyzer, > Profiler). > Now with the engine also ramped up, I have reasons to believe that many large organizations > will start migrating to SS big time, specially those running SS with O or Db2. Maybe... but there are other things to consider too... http://news.com.com/Open-source+LAMP+a+beacon+to+developers/2100-7344_3-5744767.html?tag=st.pop Speaking of features wanted, MySQL has the LIMIT clause, which allows you to select a bunch of rows starting at one row and going forward n-rows. Example: SELECT site_code , country_code , region , active , url , description FROM web_sites WHERE 1=1 ORDER BY 1 LIMIT 20, 10 This would select rows starting at row 20 for the next 10 rows. It gives you the capability to create "NEXT" "PREVIOUS" links on a web page without having to set up a cache, as in the infamous 4GL INPUT ARRAY methodology many 4GL programmers are familiar with. The difference is that there is no cursor to manage, or stale data, other than what is presented on the page. As I understand it, the LIMIT clause was put into MySQL specifically for PHP programmers. It's pretty nice to work with data this way, and allows the programmer to program a web application where a user could enter absolutely nothing on a search form, press submit, and the most that will be retrieved are 10 rows--or however many rows you want to limit. After you use LIMIT you don't want to do without it.

Data Goob wrote: > rkusnet wrote: > >>"Neil Truby" <neil.truby@ardenta.com> wrote in message news:3h67oaFfho6cU1@individual.net... >> >>>As for "responding to the competition": as per previous posts, from where I >>>sit that competiton is SQL Server and not, as the recent US-based speakers >>>here perceive, Oracle. >> >> >>watch out when SS 2005 comes out later this year. It will wipe out Informix from UK. >> >>Key features of SS 2005 >> >>* table level partioning >> >>* database mirroring with automatic client redirection. >> >>* online index and table rebuild. >> >>* oracle like READ CONSISTENCY isolation mode >> >>* vastly improved TSQL and error handling. >> >>The last point is important for me bcos I was pissed off with TSQL's error handling >>capability. >> >>I predict that with SS2005, SS will start posing serious threat to Oracle and DB2 in >>enterprise >>installations. Their tools was always excellent (Enterprise Manager, Query Analyzer, >>Profiler). >>Now with the engine also ramped up, I have reasons to believe that many large organizations >>will start migrating to SS big time, specially those running SS with O or Db2. > > > Maybe... but there are other things to consider too... > > http://news.com.com/Open-source+LAMP+a+beacon+to+developers/2100-7344_3-5744767.html?tag=st.pop > > Speaking of features wanted, MySQL has the LIMIT clause, which > allows you to select a bunch of rows starting at one row and > going forward n-rows. Example: > > SELECT site_code , country_code , region , active , url , description > FROM web_sites WHERE 1=1 ORDER BY 1 LIMIT 20, 10> > This would select rows starting at row 20 for the next 10 rows. It > gives you the capability to create "NEXT" "PREVIOUS" links on a web > page without having to set up a cache, as in the infamous 4GL INPUT > ARRAY methodology many 4GL programmers are familiar with. The > difference is that there is no cursor to manage, or stale data, other > than what is presented on the page. > > As I understand it, the LIMIT clause was put into MySQL specifically > for PHP programmers. It's pretty nice to work with data this way, > and allows the programmer to program a web application where a user > could enter absolutely nothing on a search form, press submit, and > the most that will be retrieved are 10 rows--or however many rows > you want to limit. After you use LIMIT you don't want to do without > it. > ROWNUMBER() OVER() does the same thing and is SQL Standard. The intended feature for this kind of work are scrollable cursors btw. The downside of limit is that the engine has to re-scan all the previous rows again and again everytime you hit next. There is also no gurantee you hold position if the result set changes under your feet. Not sure which, if any of tehse features IDS support. Cheers Serge -- Serge Rielau DB2 SQL Compiler Development IBM Toronto Lab

Serge Rielau wrote: > Data Goob wrote: > >> > ROWNUMBER() OVER() does the same thing and is SQL Standard. > The intended feature for this kind of work are scrollable cursors btw. I'm very familiar with cursors in SQL-Server, Sybase, and Informix, but they don't help much with web applications, at least not that I'm aware of. For an online application I'd have to argue with cursors-- but it depends on the engine. SQL-Server can be notoriously slow with cursors, as well as Sybase, Informix has always been pretty fast with them. > The downside of limit is that the engine has to re-scan all the previous > rows again and again everytime you hit next. There is also no gurantee > you hold position if the result set changes under your feet. Web applications suffer this fate constantly. > Not sure which, if any of tehse features IDS support. > > Cheers > Serge To be sure the LIMIT clause is not recommended for large table usage, but it does have a lot of uses on mid-size tables. I've used it with tables having a few 100K rows without any noticable drag, and yes, rownumber is preferred. MySQL is so darn fast though it's hard to really hit the wall till you get to some really large tables. Too bad Informix SE isn't everywhere like MySQL.