Re: [Zope-DB] Z SQL equivelant of MySQL's SQL_CALC_FOUND_ROWS?
Charlie Clark wrote: I wonder how the two methods compare efficiency-wise? Yes, well, we're talking about MySQL specifically ;-) Well, with reference to Zope we shouldn't be... Huh? the original poster was talking about MySQL, why should he care about any other rdb? Not so. Think of batches: Now showing Results 5 - 10 of 25 But why not just return all the rows and only show 5 of them? Now showing Results 5 - 10 of 25 Yes, let's talk about batches - ZSQL doesn't implement batching so it collects the whole set of results available so len() is available. How would you implement batching in a way that's agnostic of the rdb used? Result sets aren't supported directly by what? so unless the DA provides a method to query on the connection or cursor you will also have to run a separate query with count(). *sigh* cheers, Chris -- Simplistix - Content Management, Zope Python Consulting - http://www.simplistix.co.uk ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] Z SQL equivelant of MySQL's SQL_CALC_FOUND_ROWS?
On 2005-12-06 at 10:48:29 [+0100], Chris Withers [EMAIL PROTECTED] wrote: Charlie Clark wrote: I wonder how the two methods compare efficiency-wise? Yes, well, we're talking about MySQL specifically ;-) Well, with reference to Zope we shouldn't be... Huh? the original poster was talking about MySQL, why should he care about any other rdb? Well, he was talking about using ZSQL and cited by MySQL and MS SQL statements. ie., he was interested in a generic approach. At least that's how I read it. Not so. Think of batches: Now showing Results 5 - 10 of 25 But why not just return all the rows and only show 5 of them? Now showing Results 5 - 10 of 25 Yes, let's talk about batches - ZSQL doesn't implement batching so it collects the whole set of results available so len() is available. How would you implement batching in a way that's agnostic of the rdb used? Currently I do this with a script but the LCD solution would be to use a LIMITs based on parameters passed in. Essentially, however, the RDBMS has to support the feature and the Python DB API should be cover it. Having had to work with both MySQL and SQLite recently the compromises required to support lesser systems can be really annoying. Result sets aren't supported directly by what? By ZopeDA's and ZSQL so unless the DA provides a method to query on the connection or cursor you will also have to run a separate query with count(). *sigh* I've not discovered this to be a real problem yet although my result sets are rarely more than several thousand items long when ZPTs rendering speed becomes the problem. One could imagine RDBMs support in Zope3 being so much better if only anyone could be really bothered about it ;-) Charlie ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] Z SQL equivelant of MySQL's SQL_CALC_FOUND_ROWS?
Charlie Clark wrote: SELECT count(attribute) FROM relatiin WHERE condition Ah, okay, now I gotcha... only returns 1 result so it's independent of LIMIT and result sets. I wonder how the two methods compare efficiency-wise? Yes, well, we're talking about MySQL specifically ;-) Maybe although I sometimes why MySQL does anything: the documentation seems to imply that many decisions were sort of let's do it like this: cf. particularly the recommendations for writing queries with JOINs. Personally I do not see it as valid SQL to predicate a query on what was just asked. Sorry, not a lot of this paragraph made sense :-S Regarding the original question: if I know I am working with LIMITs or result sets then this implies I don't want to know the total size of the results beyound len(results). Not so. Think of batches: Now showing Results 5 - 10 of 25 But why not just return all the rows and only show 5 of them? Now showing Results 5 - 10 of 25 ;-) Yes, it'll likely be a slower query anyway, but I can assure you, Zope turning each of those rows into a Result object will be a LOT slower... cheers, Chris -- Simplistix - Content Management, Zope Python Consulting - http://www.simplistix.co.uk ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] Z SQL equivelant of MySQL's SQL_CALC_FOUND_ROWS?
On 2005-12-05 at 08:42:09 [+0100], Chris Withers [EMAIL PROTECTED] wrote: Charlie Clark wrote: SELECT count(attribute) FROM relatiin WHERE condition Ah, okay, now I gotcha... only returns 1 result so it's independent of LIMIT and result sets. I wonder how the two methods compare efficiency-wise? Yes, well, we're talking about MySQL specifically ;-) Well, with reference to Zope we shouldn't be... Maybe although I sometimes why MySQL does anything: the documentation seems to imply that many decisions were sort of let's do it like this: cf. particularly the recommendations for writing queries with JOINs. Personally I do not see it as valid SQL to predicate a query on what was just asked. Sorry, not a lot of this paragraph made sense :-S It still does to me. Have you had your morning tea? It's not that important. Regarding the original question: if I know I am working with LIMITs or result sets then this implies I don't want to know the total size of the results beyound len(results). Not so. Think of batches: Now showing Results 5 - 10 of 25 But why not just return all the rows and only show 5 of them? Now showing Results 5 - 10 of 25 Yes, let's talk about batches - ZSQL doesn't implement batching so it collects the whole set of results available so len() is available. I agree that this is likely to be inefficient for large results but it doesn't require any additional calls. Or you do as Dieter suggested an run two calls - first one simply counts and the second one effectively does batching. Result sets aren't supported directly so unless the DA provides a method to query on the connection or cursor you will also have to run a separate query with count(). Which means: it would be nice if ZSQL supported batching, preferably by result sets and this means the DAs have to expose more of the underlying functionality and getting more information from the Python driver. Charlie ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] Z SQL equivelant of MySQL's SQL_CALC_FOUND_ROWS?
Charlie Clark wrote: That doesn't work if you use offsets and limits to only return part of a result set... SELECT count(attribute) FROM relatiin WHERE condition Ah, okay, now I gotcha... only returns 1 result so it's independent of LIMIT and result sets. I wonder how the two methods compare efficiency-wise? Of course, ZopeDAs such as our mxODBCZopeDA can be extended to support additional commands on the connection but that is not part of the ZSQL specification and will vary from RDBMS to RDBMS as the initial mail noted. Well, that's why MySQL does it as an SQL statement, no? ;-) Chris -- Simplistix - Content Management, Zope Python Consulting - http://www.simplistix.co.uk ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] Z SQL equivelant of MySQL's SQL_CALC_FOUND_ROWS?
On 2005-12-02 at 10:33:34 [+0100], Chris Withers [EMAIL PROTECTED] wrote: Charlie Clark wrote: That doesn't work if you use offsets and limits to only return part of a result set... SELECT count(attribute) FROM relatiin WHERE condition Ah, okay, now I gotcha... only returns 1 result so it's independent of LIMIT and result sets. I wonder how the two methods compare efficiency-wise? ah, that might well be in the realms of db-tuning and magic but one might expect a count() operation to be slightly faster as it requires less data to be packaged. The implementation of LIMITs and result sets might vary significantly from system to system. Of course, ZopeDAs such as our mxODBCZopeDA can be extended to support additional commands on the connection but that is not part of the ZSQL specification and will vary from RDBMS to RDBMS as the initial mail noted. Well, that's why MySQL does it as an SQL statement, no? ;-) Maybe although I sometimes why MySQL does anything: the documentation seems to imply that many decisions were sort of let's do it like this: cf. particularly the recommendations for writing queries with JOINs. Personally I do not see it as valid SQL to predicate a query on what was just asked. Regarding the original question: if I know I am working with LIMITs or result sets then this implies I don't want to know the total size of the results beyound len(results). Of course, it can be a right Tony Blair rewriting a complex SQL query just to add a count in there as opposed to calling a method on a cursor object. And this is where ZopeDA connections could do with improved support beyond the current it's good enough as it is situation so that database functions can be called directly more easily where required. But as we all know, so far no one has come forward to champion new generation ZSQLs and ZopeDAs to do this. Charlie ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] Z SQL equivelant of MySQL's SQL_CALC_FOUND_ROWS?
On 2005-11-29 at 20:20:10 [+0100], Kevin Monceaux [EMAIL PROTECTED] wrote: Zope Enthusiasts, I'm new to Zope so forgive me if the answer to this question is obvious. If one performs a select with a limit clause is it possible to determine how many results would have been returned if there was no limit? For example, if I wanted to select the first picture in a photo album but wanted to know the total number of pictures in the album, how would I do that via Z SQL? With MySQL it can be done this way: SELECT SQL_CALC_FOUND_ROWS, * from Pictures where AlbumID = 25 LIMIT 1; SELECT FOUND_ROWS() as Count; According to the Z SQL Methods User's Manual multiple SQL statements can be included in a Z SQL method but that no more than one select statement can be included. The above MySQL technique requires two select statements. You can use dtml-var sqldelimiter - search the archives for that. But that is wrong. If you want to count the number of records, use count() Charlie -- Charlie Clark eGenix.com Professional Python Services directly from the Source Python/Zope Consulting and Support ...http://www.egenix.com/ mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/ mxODBC, mxDateTime, mxTextTools ...http://python.egenix.com/ 2005-10-17: Released mxODBC.Zope.DA 1.0.9http://zope.egenix.com/ ::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
