SELECT count(*) FROM commercial_entity, country, user_account, address_list LEFT JOIN state_province ON address_list.state_province_id = state_province.state_province_id LEFT JOIN contact_info ON address_list.contact_info_id = contact_info.contact_info_id WHERE address_list.address_type_id = 101 AND commercial_entity.commercial_entity_id = address_list.commercial_entity_id AND address_list.country_id = country.country_id AND commercial_entity.user_account_id = user_account.user_account_id AND user_account.user_role_id IN (101, 101);
SELECT count(*) FROM commercial_entity, address_list, country, user_account LEFT JOIN state_province ON address_list.state_province_id = state_province.state_province_id LEFT JOIN contact_info ON address_list.contact_info_id = contact_info.contact_info_id WHERE address_list.address_type_id = 101 AND commercial_entity.commercial_entity_id = address_list.commercial_entity_id AND address_list.country_id = country.country_id AND commercial_entity.user_account_id = user_account.user_account_id AND user_account.user_role_id IN (101, 101); The first one takes 1.73 seconds, the other takes 0.34 seconds. The only difference is the order of the tables (address_list comes last in the FROM clause in the first example, and is in the middle of the FROM clause in the second example)... I stumbed on this when benchmarking Postgres vs MySQL, and accidentally pulled the Postgres version of the query and put it into the MySQL window (Postgres won't run the MySQL version, but MySQL runs the Postgres version). Oracle has the concept of a "driving table". Is this a similar example? David. ----- Original Message ----- From: "Mike R. Duncan" <[EMAIL PROTECTED]> To: "Director General: NEFACOMP" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, September 25, 2003 2:55 PM Subject: RE: Ideas on creating connections try adding a '\' before the comma like a \n in c++ -----Original Message----- From: Director General: NEFACOMP [mailto:[EMAIL PROTECTED] Sent: Thursday, September 25, 2003 10:41 AM To: Roger Davis; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Ideas on creating connections Thank you for your time. I don't want to be too much demanding. I currently have a problem with MyODBC 3.51. When my client machine is set to use French numbers (with a COMMA instead of a PERIOD or DOT for floats), MyODBC does not let the decimal part to pass. Venu told me he will try to look into this before the next release. It seems I am impatient!!! You said you are trying to move from DAO to using the MySQL DLL (libmysql.dll). Can we share some code using VB and the DLL? Just send me a piece of code doing the following: ===== - Creating a connection to the MySQL server, - Running a DELETE query on the server (or any other query that doesn't need to return values) - Running a SELECT query against the MySQL server and - Populating data of the RecordSet creating into a Flex grid - Closing and Distroying the RecordSet - Closing and Distroying the Connection ===== One time I saw such a code on http://www.vbmysql.com but it seems to have dissappeared. Thanks Emery ----- Original Message ----- From: "Roger Davis" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, September 25, 2003 19:08 Subject: RE: Ideas on creating connections > DAO is, I guess you would say, the predessor to ADO. I am currently in the > process of getting rid of all the DAO code in the application and moving to > a custom recordset using the libmysql.dll. Some of this is done in VB and > it is still extremely fast for a VB based recordset. Although there are > numerous ways to do this, it is really difficult to tell what would be the > best in your situation. As has been mentioned before, if you are doing > queries one after another in your application, then by all means connect > once and pass all the queries through that connection. If you a making very > few queries and you have limited resources on the server, then it would be > best to connect only when you need to and drop the connection when you are > done. These are of course only my opinions based on what I have experienced > in the past. Your experience may vary. Void where prohibited. > > Roger > > > -----Original Message----- > > From: Director General: NEFACOMP [mailto:[EMAIL PROTECTED] > > Sent: Thursday, September 25, 2003 12:48 PM > > To: Roger Davis; [EMAIL PROTECTED]; [EMAIL PROTECTED] > > Subject: Re: Ideas on creating connections > > > > > > I never use DAO. > > > > Is there any benefit over ADO? > > > > > > Thanks > > Emery > > ----- Original Message ----- > > From: "Roger Davis" <[EMAIL PROTECTED]> > > To: "Director General: NEFACOMP" <[EMAIL PROTECTED]>; > > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > Sent: Thursday, September 25, 2003 18:14 > > Subject: RE: Ideas on creating connections > > > > > > > My experience is this. I use DAO through MyODBC, and I also use the > > > libmysql.dll with a custom recordset. Connection times through both > > options > > > are extremely fast. Fast enough in my situation that it can > > basically can > > be > > > ignored. There are times when I open several connections to > > the server at > > > once and have yet to run into any problems. If you strictly want to use > > ADO > > > though, I would suggest that you open one connection to the server and > > > process your queries through that connection. Leave it open > > for the life > > of > > > the program. While the program is open, the connection to the > > server will > > > stay open and the thread for MySQL will be a "sleep". > > > > > > Just my $0.02. > > > > > > Roger > > > > -----Original Message----- > > > > From: Director General: NEFACOMP [mailto:[EMAIL PROTECTED] > > > > Sent: Thursday, September 25, 2003 11:57 AM > > > > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > > > > Subject: Ideas on creating connections > > > > > > > > > > > > Hi groups, > > > > > > > > I need some experienced users to guide me on this issue. > > > > I am developping an Application that will be accessing a MySQL > > > > database through MyODBC. > > > > The application is developped using Visual Basic. > > > > > > > > Since my application will be used simultaneously by more than 200 > > > > users, I want to know the implication of using one of the > > > > following methods for connecting to the server: > > > > > > > > 1. At logon, I create a connection to the server and maintain it > > > > throught the application life. By the application life I mean > > > > that the connection stays open as long as the application is > > > > loaded in the memory. So, for a user who is online for 3 hours, > > > > the connection is on for those three hours, even if the user goes > > > > out for a coffee. > > > > > > > > 2. I create a connection only when a query is about to be sent to > > > > the server. That means, I create a connection when I want to > > > > create an ADO recordset and run a query against the server. After > > > > running my query, I distroy the connection. > > > > > > > > > > > > Currently I am using the first option since I don't exactly what > > > > it is required (time and resources) to make a connection to the > > > > server. Will you please tell me how fast is to connect to the > > > > server. If you advise me that this method is the best, I will add > > > > functions to reconnect a dead connection. > > > > > > > > > > > > Any ideas and advices are highly welcomed > > > > > > > > > > > > Thanks, > > > > __________________________________ > > > > NZEYIMANA Emery Fabrice > > > > NEFA Computing Services, Inc. > > > > P.O. Box 5078 Kigali > > > > Office Phone: +250-51 11 06 > > > > Office Fax: +250-50 15 19 > > > > Mobile: +250-08517768 > > > > Email: [EMAIL PROTECTED] > > > > http://www.nefacomp.net/ > > > > > > > > --- > > > > Incoming mail is certified Virus Free. > > > > Checked by AVG anti-virus system (http://www.grisoft.com). > > > > Version: 6.0.520 / Virus Database: 318 - Release Date: 9/18/2003 > > > > > > > --- > > > Outgoing mail is certified Virus Free. > > > Checked by AVG anti-virus system (http://www.grisoft.com). > > > Version: 6.0.520 / Virus Database: 318 - Release Date: 9/18/2003 > > > > > > > > > -- > > > MySQL ODBC Mailing List > > > For list archives: http://lists.mysql.com/myodbc > > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > > > > > > > -- > > MySQL ODBC Mailing List > > For list archives: http://lists.mysql.com/myodbc > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > --- > Incoming mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.520 / Virus Database: 318 - Release Date: 9/18/2003 > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.520 / Virus Database: 318 - Release Date: 9/18/2003 > > > -- > MySQL ODBC Mailing List > For list archives: http://lists.mysql.com/myodbc > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > -- MySQL ODBC Mailing List For list archives: http://lists.mysql.com/myodbc To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
