Re: Which query would be better?
On Tuesday 23 Oct 2007, [EMAIL PROTECTED] wrote: Okay. Except Oracle != MySQL 5. MySQL 5 was what was specified in the original, forgive the pun, query. Heh. Free beats multi-thousend pound again :-) -- Tom Chiverton. Are you a great ColdFusion programmer, who knows Reactor and ColdSpring, and has done some Flex work ? Would you like to work for a top 30 law firm in Manchester, UK ? Are not an agency ? If yes, send email ! This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by The Solicitors Regulation Authority. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. ~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291944 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Which query would be better?
On Monday 22 Oct 2007, [EMAIL PROTECTED] wrote: Which, according to the docs, is only limited by max_allowed_packet. You must be on the wrong page. #oldCompany# had an Oracle install that crapped out at 100 items. -- Tom Chiverton. Are you a great ColdFusion programmer, who knows Reactor and ColdSpring, and has done some Flex work ? Would you like to work for a top 30 law firm in Manchester, UK ? Are not an agency ? If yes, send email ! This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by The Solicitors Regulation Authority. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. ~| Check out the new features and enhancements in the latest product release - download the What's New PDF now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291809 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Which query would be better?
Okay. Except Oracle != MySQL 5. MySQL 5 was what was specified in the original, forgive the pun, query. --Ben Doom Tom Chiverton wrote: On Monday 22 Oct 2007, [EMAIL PROTECTED] wrote: Which, according to the docs, is only limited by max_allowed_packet. You must be on the wrong page. #oldCompany# had an Oracle install that crapped out at 100 items. ~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291842 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Which query would be better?
Offhand, I'd guess using the in syntax, if only because it makes your million-line query smaller. However, if you really want to know, write it each way, populate a db with a million rows of fake data, and time it. --BenD Will Tomlinson wrote: MySQL 5. Which would be the better choice for a report table. I'm concerned and want the query to run as fast as possible because there could end up being millions of records in it. I could add the number of fields I need (not worried about the flattening of this table and lack of normalization). Instructor1, instructor2, 3, 4, etc. Then query like this: AND (e2.instructor1id = cfqueryparam value=#ARGUMENTS.instructor# OR e2.instructor2id = cfqueryparam value=#ARGUMENTS.instructor# OR e2.instructor3id = cfqueryparam value=#ARGUMENTS.instructor# ) OR ... I could easily create a list of values, then use the IN keyword: AND someotherfield IN (cfqueryparam value=#ARGUMENTS.somevaluelist# list=yes) Thanks, Will ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291745 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Which query would be better?
Also, regardless of the performance factors, I prefer the IN method as it is easier to upgrade. Dominic On 22/10/2007, Ben Doom [EMAIL PROTECTED] wrote: Offhand, I'd guess using the in syntax, if only because it makes your million-line query smaller. However, if you really want to know, write it each way, populate a db with a million rows of fake data, and time it. --BenD Will Tomlinson wrote: MySQL 5. Which would be the better choice for a report table. I'm concerned and want the query to run as fast as possible because there could end up being millions of records in it. I could add the number of fields I need (not worried about the flattening of this table and lack of normalization). Instructor1, instructor2, 3, 4, etc. Then query like this: AND (e2.instructor1id = cfqueryparam value=#ARGUMENTS.instructor# OR e2.instructor2id = cfqueryparam value=#ARGUMENTS.instructor# OR e2.instructor3id = cfqueryparam value=#ARGUMENTS.instructor# ) OR ... I could easily create a list of values, then use the IN keyword: AND someotherfield IN (cfqueryparam value=#ARGUMENTS.somevaluelist# list=yes) Thanks, Will ~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291748 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Which query would be better?
On Monday 22 Oct 2007, [EMAIL PROTECTED] wrote: Also, regardless of the performance factors, I prefer the IN method as it is easier to upgrade. Until one day you randomly hit the limit of things that can be in the IN clause. -- Tom Chiverton. Are you a great ColdFusion programmer, who knows Reactor and ColdSpring, and has done some Flex work ? Would you like to work for a top 30 law firm in Manchester, UK ? Are not an agency ? If yes, send email ! This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by The Solicitors Regulation Authority. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291750 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Which query would be better?
Just as a note on performance, When thinking about using OR, you should consider splitting it into separate queries and using a UNION or UNION ALL to join them together. Rob -Original Message- From: Dominic Watson [mailto:[EMAIL PROTECTED] Sent: 22 October 2007 16:35 To: CF-Talk Subject: Re: Which query would be better? Also, regardless of the performance factors, I prefer the IN method as it is easier to upgrade. Dominic On 22/10/2007, Ben Doom [EMAIL PROTECTED] wrote: Offhand, I'd guess using the in syntax, if only because it makes your million-line query smaller. However, if you really want to know, write it each way, populate a db with a million rows of fake data, and time it. --BenD Will Tomlinson wrote: MySQL 5. Which would be the better choice for a report table. I'm concerned and want the query to run as fast as possible because there could end up being millions of records in it. I could add the number of fields I need (not worried about the flattening of this table and lack of normalization). Instructor1, instructor2, 3, 4, etc. Then query like this: AND (e2.instructor1id = cfqueryparam value=#ARGUMENTS.instructor# OR e2.instructor2id = cfqueryparam value=#ARGUMENTS.instructor# OR e2.instructor3id = cfqueryparam value=#ARGUMENTS.instructor# ) OR ... I could easily create a list of values, then use the IN keyword: AND someotherfield IN (cfqueryparam value=#ARGUMENTS.somevaluelist# list=yes) Thanks, Will ~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291753 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Which query would be better?
Which, according to the docs, is only limited by max_allowed_packet. The default is 1Mb, but it can be set arbitrarily up to the size of available RAM if that becomes a problem. --BenD Tom Chiverton wrote: On Monday 22 Oct 2007, [EMAIL PROTECTED] wrote: Also, regardless of the performance factors, I prefer the IN method as it is easier to upgrade. Until one day you randomly hit the limit of things that can be in the IN clause. ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291755 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Which query would be better?
Which, according to the docs, is only limited by max_allowed_packet. The default is 1Mb, but it can be set arbitrarily up to the size of available RAM if that becomes a problem. Databases generally have some limit on the number of items within an IN clause, which is completely separate from the maximum size of text used for an SQL query. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291760 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Which query would be better?
Until one day you randomly hit the limit of things that can be in the IN clause. Lol indeed, though I'd hate to see that SQL if it were multiple ORs ;) Dom ~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finderproductID=1522loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291759 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Which query would be better?
Ah, I see you remember Spectra :) -Original Message- From: Tom Chiverton [mailto:[EMAIL PROTECTED] Sent: Monday, October 22, 2007 11:48 AM To: CF-Talk Subject: Re: Which query would be better? On Monday 22 Oct 2007, [EMAIL PROTECTED] wrote: Also, regardless of the performance factors, I prefer the IN method as it is easier to upgrade. Until one day you randomly hit the limit of things that can be in the IN clause. -- Tom Chiverton. Are you a great ColdFusion programmer, who knows Reactor and ColdSpring, and has done some Flex work ? Would you like to work for a top 30 law firm in Manchester, UK ? Are not an agency ? If yes, send email ! This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by The Solicitors Regulation Authority. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. ~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291764 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Which query would be better?
I assumed there would be as well, but when I looked it up, that's what the docs for MySQL 5 said. Specifically, I dug around until I found the limit on the IN clause. It said it was limited by max_allowed_packet, which I then looked up. Hence, according to the docs. --Ben Doom Dave Watts wrote: Which, according to the docs, is only limited by max_allowed_packet. The default is 1Mb, but it can be set arbitrarily up to the size of available RAM if that becomes a problem. Databases generally have some limit on the number of items within an IN clause, which is completely separate from the maximum size of text used for an SQL query. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291766 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Which query would be better?
Thanks all! I went with the IN. :) Will ~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291781 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Which query would be better?
I have no scientific evidence to back me up, but I would use the second list method in the query. I would think that the optimizers would handle a list better than multiple ORs. Also, it's easier to read. If you have control over the structure of the database, you may be better off changing the structure from having multiple columns (instructor1id, instructor2id, etc), to a separate table for instructor enrollments. Typically, a course management system would have the following tables: Courses --- course id course name Users - user id user name Enrollment -- course id user id role M!ke -Original Message- From: Will Tomlinson [mailto:[EMAIL PROTECTED] Sent: Saturday, October 20, 2007 9:31 PM To: CF-Talk Subject: Which query would be better? MySQL 5. Which would be the better choice for a report table. I'm concerned and want the query to run as fast as possible because there could end up being millions of records in it. I could add the number of fields I need (not worried about the flattening of this table and lack of normalization). Instructor1, instructor2, 3, 4, etc. Then query like this: AND (e2.instructor1id = cfqueryparam value=#ARGUMENTS.instructor# OR e2.instructor2id = cfqueryparam value=#ARGUMENTS.instructor# OR e2.instructor3id = cfqueryparam value=#ARGUMENTS.instructor# ) OR ... I could easily create a list of values, then use the IN keyword: AND someotherfield IN (cfqueryparam value=#ARGUMENTS.somevaluelist# list=yes) Thanks, Will ~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291686 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
