Hello how to connect MySQL with Visual Foxpro
Hi all, I have got a database in mysql and i need to use the gis and that was in VFOXPRO.I need to display my data which is mysql on the map which database is in VFoXPRO.Is there any way to have communication betweeen these two DBs. Thanks and regards, venu.
Re: Hello how to connect MySQL with Visual Foxpro
Hi Venu, I would like to know the OS your using (for mysql server), BTW if your using window. Try installing the myodbc and configure it so that vfoxpro can communicate with it. check this site: http://fox.wikis.com/wc.dll?Wiki~VFPCommandSQLStringConnect~VFP At 06:28 PM 5/22/2006, VenuGopal Papasani wrote: Hi all, I have got a database in mysql and i need to use the gis and that was in VFOXPRO.I need to display my data which is mysql on the map which database is in VFoXPRO.Is there any way to have communication betweeen these two DBs. Thanks and regards, venu. Ehrwin C. Mina Chikka Phils. Inc. A CMMI LEVEL 5, AND A ISO 9001:2000; ISO 15000:2002; ISO 17799:2002 CERTIFIED COMPANY This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, you must not copy this message or attachment or disclose the contents to any other person. If you have received this transmission in error, please notify the sender immediately and delete the message and any attachment from your system. Chikka does not accept liability for any omissions or errors in this message which may arise as a result of E-Mail-transmission or for damages resulting from any unauthorized changes of the content of this message and any attachment thereto. Chikka does not guarantee that this message is free of viruses and does not accept liability for any damages caused by any virus transmitted therewith. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Beating Rushmore! -- was: Really slow query (compared with Visual FoxPro)
Alexander Barkov wrote: Héctor, Changing various buffer sizes can improve performance significantly, with key_buffer_size as the first thing to tune. Also, make sure you are using not debugging version of the server. Please also take a look in this section of the documentation for details: http://www.mysql.com/doc/en/Optimising_the_Server.html Hi guys! Ok, just to report my findings (and MySQL achievements). I added this lines to my.ini (in the Windows directory): set-variable=key_buffer_size=128M set-variable=read_rnd_buffer_size=16M set-variable=join_buffer_size=16M set-variable=sort_buffer_size=16M and, after this changes, the winner is MySQL! It is almost 10% faster than VFP! (and it is easier for me to use it within Python!) Thank you all for your help, since this was my first time at this list! Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Almost beating Rushmore -- was: Really slow query (compared with Visual FoxPro)
Hi guys! Ok, I'm closer to beat Rushmore (VFP optimizer) now! After some reading about MySQL optimization techniques, here is the summary of what I've done: 1. Add a compound index to the table 2. Use EXPLAIN to check out the query (with GROUP BY on multiple fields) 3. Create the summary table And here's the detailed instructions: mysql alter table traf_oper add index (tel, telefb, rutaentran, rutasalien, minutos); Query OK, 5067215 rows affected (5 min 22.36 sec) Records: 5067215 Duplicates: 0 Warnings: 0 mysql explain select tel, telefb, rutaentran, rutasalien, sum(minutos) from traf_oper group by 1, 2, 3, 4; ++-+---+---+---+--+-+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+---+---+--+-+--+-+-+ | 1 | SIMPLE | traf_oper | index | NULL | tel | 45 | NULL | 5067215 | Using index | ++-+---+---+---+--+-+--+-+-+ 1 row in set (0.03 sec) mysql create table grp_oper select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4; Query OK, 3326541 rows affected (33.81 sec) Records: 3326541 Duplicates: 0 Warnings: 0 Adding the times together it would take MySQL like 6 minutes (VFP does it in about 4 minutes). I still haven't tweaked some server variables (read_rnd_buffer_size, sort_buffer_size, max_join_size), but, as always, I'll keep trying :) Thanks in advance for your comments and suggestions, Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Almost beating Rushmore -- was: Really slow query (compared with Visual FoxPro)
Héctor, Changing various buffer sizes can improve performance significantly, with key_buffer_size as the first thing to tune. Also, make sure you are using not debugging version of the server. Please also take a look in this section of the documentation for details: http://www.mysql.com/doc/en/Optimising_the_Server.html Héctor Villafuerte D. wrote: Hi guys! Ok, I'm closer to beat Rushmore (VFP optimizer) now! After some reading about MySQL optimization techniques, here is the summary of what I've done: 1. Add a compound index to the table 2. Use EXPLAIN to check out the query (with GROUP BY on multiple fields) 3. Create the summary table And here's the detailed instructions: mysql alter table traf_oper add index (tel, telefb, rutaentran, rutasalien, minutos); Query OK, 5067215 rows affected (5 min 22.36 sec) Records: 5067215 Duplicates: 0 Warnings: 0 mysql explain select tel, telefb, rutaentran, rutasalien, sum(minutos) from traf_oper group by 1, 2, 3, 4; ++-+---+---+---+--+-+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+---+---+--+-+--+-+-+ | 1 | SIMPLE | traf_oper | index | NULL | tel | 45 | NULL | 5067215 | Using index | ++-+---+---+---+--+-+--+-+-+ 1 row in set (0.03 sec) mysql create table grp_oper select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4; Query OK, 3326541 rows affected (33.81 sec) Records: 3326541 Duplicates: 0 Warnings: 0 Adding the times together it would take MySQL like 6 minutes (VFP does it in about 4 minutes). I still haven't tweaked some server variables (read_rnd_buffer_size, sort_buffer_size, max_join_size), but, as always, I'll keep trying :) Thanks in advance for your comments and suggestions, Hector -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Barkov [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Izhevsk, Russia ___/ www.mysql.com +7-912-856-80-21 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really slow query (compared with Visual FoxPro)
Chris wrote: Hmm It's just occured to me that you're basically copying and entire table from one place to another. If I recall correctly, FoxPro cheats somewhat in this situation - it just copies the concerned files! Which table type are you using (something I should have asked in the beginning)? Can we see the output of SHOW CREATE TABLE table_name; ? Regards, Chris Hi, I'm using MyISAM tables. BTW, I'm almost done with the optimization chapter from the manual... and I haven't had one of those Eureka! moments... anyway I'll keep trying... any more suggestions? Thanks again. Hector Here's an example of what you requested: mysql show create table tmp; +---++ | Table | Create Table | +---++ | tmp | CREATE TABLE `tmp` (`tel` int(11) default NULL) TYPE=MyISAM CHARSET=latin1 | +---++ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really slow query (compared with Visual FoxPro)
Mojtaba Faridzad wrote: BUT my experience: try to change the logic of your report not to retrieve large number of records. user LIMIT to create the reports page by page. this is the best and even better for the user. Ok thanks, but how exactly do I change-the-logic of this query: mysql select tel, telefb, rutaentran, rutasalien, sum(minutos) from traf_oper group by 1, 2, 3, 4; I also found this in the manual: Note that in some cases MySQL will not use an index, even if one would be available. Some of the cases where this happens are: * If the use of the index would require MySQL to access more than 30% of the rows in the table. (In this case a table scan is probably much faster, as this will require us to do much fewer seeks.) Note that if such a query uses |LIMIT| to only retrieve part of the rows, MySQL will use an index anyway, as it can much more quickly find the few rows to return in the result. So, it seems that I REALLY need to change the logic of this query, since it obviusly would do a table scan. Any help would be greatly appreciated! Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Really slow query (compared with Visual FoxPro)
Hi all, I'm migrating from Visual FoxPro (I'm a newbie). On a daily basis I need to run this query on a table with a little more than 5 million records. + MS Visual FoxPro 6.0 select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4 into table oper_grp This took about 173 seconds (let's say 3 minutes) + MySQL 4.1.0-alpha-max-nt mysql create table oper_grp select tel, telefb, rutaentran, rutasalien, - sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4; Query OK, 3301017 rows affected (8 hours 36 min 48.83 sec) --- 516 minutes! Records: 3301017 Duplicates: 0 Warnings: 0 Why is there such a big difference?! Any optimization tips I could follow? Thanks in advance, Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really slow query (compared with Visual FoxPro)
Hi! Given VFP's internals (I have to support FoxPro 2.6 apps, oh the pain of it all!), MySQL should be capable of much better performance, considering the only thing FoxPro has in terms of a possible advantage is Rushmore. Which indexes do you have on the tables in the query? Regards, Chris Héctor Villafuerte D. wrote: Hi all, I'm migrating from Visual FoxPro (I'm a newbie). On a daily basis I need to run this query on a table with a little more than 5 million records. + MS Visual FoxPro 6.0 select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4 into table oper_grp This took about 173 seconds (let's say 3 minutes) + MySQL 4.1.0-alpha-max-nt mysql create table oper_grp select tel, telefb, rutaentran, rutasalien, - sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4; Query OK, 3301017 rows affected (8 hours 36 min 48.83 sec) --- 516 minutes! Records: 3301017 Duplicates: 0 Warnings: 0 Why is there such a big difference?! Any optimization tips I could follow? Thanks in advance, Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really slow query (compared with Visual FoxPro)
Chris Nolan wrote: Hi! Given VFP's internals (I have to support FoxPro 2.6 apps, oh the pain of it all!), MySQL should be capable of much better performance, considering the only thing FoxPro has in terms of a possible advantage is Rushmore. Which indexes do you have on the tables in the query? Regards, Chris Indexes? hmmm... I knew those were useful for something :) In Visual FoxPro I don't use indexes for this table... so I didn't considered them necessary in MySQL (now I think I need to get to the basics of RDBMS). This table I'm talking about is a CDR (call detail record), so a record represents a call from a given telephone. I'm surely wrong but, ain't the index suppossed to be on a field with distinct entries? Does VFP automagically creates indexes depending on the query? As you can see, I would greatly appreciate any pointers to some database theory. Thanks again! Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really slow query (compared with Visual FoxPro)
Hi! One thing that you have to remember is that FoxPro will always index some fields, while MySQL will only ever index the primary key field automatically. Indexes can be on fields that are distinct or not distinct. The only difference that exists is the nature of the index, which is one of those details the RDBMS handles by itself. VFP has Rushmore, which does a bunch of funky things. At the moment (and the MySQL docs currently agree with me on this), MySQL only has one real weakness - it's optimiser. If you consider this fact (Oracle's optimiser is really it's greatest strength for example), the speed of MySQL is even greater an achievement. The great Monty himself names the optimiser as amongst the hardest things to get right for an RDBMS. It's not to say that the optimiser is bad, it's just to say that MySQL AB have quite a few things planned for improvement in that particular area. Regards, Chris Héctor Villafuerte D. wrote: Chris Nolan wrote: Hi! Given VFP's internals (I have to support FoxPro 2.6 apps, oh the pain of it all!), MySQL should be capable of much better performance, considering the only thing FoxPro has in terms of a possible advantage is Rushmore. Which indexes do you have on the tables in the query? Regards, Chris Indexes? hmmm... I knew those were useful for something :) In Visual FoxPro I don't use indexes for this table... so I didn't considered them necessary in MySQL (now I think I need to get to the basics of RDBMS). This table I'm talking about is a CDR (call detail record), so a record represents a call from a given telephone. I'm surely wrong but, ain't the index suppossed to be on a field with distinct entries? Does VFP automagically creates indexes depending on the query? As you can see, I would greatly appreciate any pointers to some database theory. Thanks again! Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Really slow query (compared with Visual FoxPro)
Indexes are your friend. Perhaps your best friend (as far as databases go) --- To give light on your question... you can index any field... if the entries are distinct, it's called a 'unique index' which are the best kind to use. Otherwise you have a 'non-unique index', which can also be handy Think of indexes, like... well... book indexes... to prevent you from looking all over the book for an answer, you can find the exact page the topic is on (unique index), or if it's covered in multiple pages, you reduce your searching to just that list of pages (non-unique index). Then there's compound indexes, which is an index on the value of two seperate fields (which can be unique, or non-unique). As a rule of thumb, you want indexes on your unique key (primary key) (call record id) Most db's make indexes automatically on keys you say are the primary key. Also on your most commonly searched fields (usually non-unique) such as foreign keys (call record id in your record notes table for example) -- If you're that new to db design, I would recommend going out and looking for some books/sites on the subject... There have been a few mentioned on this mailing list recently (Michael Kofler and Paul Dubois), http://www.databaseanswers.com/, and Database Design for Mere Mortals and finally Mike ([EMAIL PROTECTED]) mentioned the following online tutorials: Try one of these MySQL tutorials: http://www.mysql.com/doc/en/Tutorial.html http://www.analysisandsolutions.com/code/mybasic.htm http://www.devshed.com/Server_Side/MySQL http://www.sqlcourse.com/ http://www.w3schools.com/sql/default.asp http://www.juicystudio.com/tutorial/mysql/ http://www.justphukit.com/mysql/mysql-tutorials-1.php http://sqlzoo.net/ http://www.troobloo.com/tech/mysql.shtml http://perl.about.com/cs/mysql/index.htm http://www.anu.edu.au/web/authors/mysql/manual_Tutorial.html Reference: http://www.mysql.com/doc/en/ (the MySQL manual is quite good too) http://tiger.la.asu.edu/Quick_Ref/MySQL_QuickRef.pdf (MySQL Quick Reference Card) Related links http://dmoz.org/Computers/Software/Databases/MySQL/Tutorials/ -Original Message- From: Héctor Villafuerte D. [mailto:[EMAIL PROTECTED] Sent: Friday, October 24, 2003 1:40 PM To: [EMAIL PROTECTED] Subject: Re: Really slow query (compared with Visual FoxPro) Chris Nolan wrote: Hi! Given VFP's internals (I have to support FoxPro 2.6 apps, oh the pain of it all!), MySQL should be capable of much better performance, considering the only thing FoxPro has in terms of a possible advantage is Rushmore. Which indexes do you have on the tables in the query? Regards, Chris Indexes? hmmm... I knew those were useful for something :) In Visual FoxPro I don't use indexes for this table... so I didn't considered them necessary in MySQL (now I think I need to get to the basics of RDBMS). This table I'm talking about is a CDR (call detail record), so a record represents a call from a given telephone. I'm surely wrong but, ain't the index suppossed to be on a field with distinct entries? Does VFP automagically creates indexes depending on the query? As you can see, I would greatly appreciate any pointers to some database theory. Thanks again! Hector -- 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]
Re: Really slow query (compared with Visual FoxPro)
Dan Greene wrote: Indexes are your friend. Perhaps your best friend (as far as databases go) --- To give light on your question... you can index any field... if the entries are distinct, it's called a 'unique index' which are the best kind to use. Otherwise you have a 'non-unique index', which can also be handy Think of indexes, like... well... book indexes... to prevent you from looking all over the book for an answer, you can find the exact page the topic is on (unique index), or if it's covered in multiple pages, you reduce your searching to just that list of pages (non-unique index). Then there's compound indexes, which is an index on the value of two seperate fields (which can be unique, or non-unique). As a rule of thumb, you want indexes on your unique key (primary key) (call record id) Most db's make indexes automatically on keys you say are the primary key. Also on your most commonly searched fields (usually non-unique) such as foreign keys (call record id in your record notes table for example) -- Ok, I've found the optimization chapter in the manual and I'm still reading it. Here's what I've done so far: mysql alter table traf_oper add index (tel), add index (telefb); mysql explain select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4; ++-+---+--+---+--+-+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+--+---+--+-+--+-+-+ | 1 | SIMPLE | traf_oper | ALL | NULL | NULL |NULL | NULL | 5014313 | Using temporary; Using filesort | ++-+---+--+---+--+-+--+-+-+ 1 row in set (0.50 sec) mysql describe traf_oper; ++--+---+--+-+-+---+ | Field | Type | Collation | Null | Key | Default | Extra | ++--+---+--+-+-+---+ | tel| char(8) | latin1_swedish_ci | YES | MUL | NULL| | | fecha | char(8) | latin1_swedish_ci | YES | | NULL| | | hora | char(6) | latin1_swedish_ci | YES | | NULL| | | telefb | char(14) | latin1_swedish_ci | YES | MUL | NULL| | | tiempotasa | char(6) | latin1_swedish_ci | YES | | NULL| | | rutasalien | char(7) | latin1_swedish_ci | YES | | NULL| | | rutaentran | char(7) | latin1_swedish_ci | YES | | NULL| | | serie | char(3) | latin1_swedish_ci | YES | | NULL| | | tipotraf | int(1) | binary| YES | | NULL| | | minutos| int(4) | binary| YES | | NULL| | ++--+---+--+-+-+---+ 10 rows in set (0.00 sec) So, why isn't SELECT using indexes (key = NULL, key_len = NULL)? Where else do I need to add indexes? Thanks for your help! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Really slow query (compared with Visual FoxPro)
As you are selecting all records (no where clause), it will scan the whole table every time, I believe... does anyone know if he added the other columns to his index, or had 4 seperate indexes (one per column) would they be used in this operation? Ok, I've found the optimization chapter in the manual and I'm still reading it. Here's what I've done so far: mysql alter table traf_oper add index (tel), add index (telefb); mysql explain select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4; ++-+---+--+---+--+ -+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+--+---+--+ -+--+-+-+ | 1 | SIMPLE | traf_oper | ALL | NULL | NULL |NULL | NULL | 5014313 | Using temporary; Using filesort | ++-+---+--+---+--+ -+--+-+-+ 1 row in set (0.50 sec) mysql describe traf_oper; ++--+---+--+-+ -+---+ | Field | Type | Collation | Null | Key | Default | Extra | ++--+---+--+-+ -+---+ | tel| char(8) | latin1_swedish_ci | YES | MUL | NULL| | | fecha | char(8) | latin1_swedish_ci | YES | | NULL| | | hora | char(6) | latin1_swedish_ci | YES | | NULL| | | telefb | char(14) | latin1_swedish_ci | YES | MUL | NULL| | | tiempotasa | char(6) | latin1_swedish_ci | YES | | NULL| | | rutasalien | char(7) | latin1_swedish_ci | YES | | NULL| | | rutaentran | char(7) | latin1_swedish_ci | YES | | NULL| | | serie | char(3) | latin1_swedish_ci | YES | | NULL| | | tipotraf | int(1) | binary| YES | | NULL| | | minutos| int(4) | binary| YES | | NULL| | ++--+---+--+-+ -+---+ 10 rows in set (0.00 sec) So, why isn't SELECT using indexes (key = NULL, key_len = NULL)? Where else do I need to add indexes? Thanks for your help! -- 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]
Re: Really slow query (compared with Visual FoxPro)
Hi Hector, Umm, it looks like you're simply doing a SELECT in the first query and *populating a new table* in the second. Of course inserting 3.3 million rows is going to take extra time! How can you even compare the 2 when they're doing different things? Matt - Original Message - From: Héctor Villafuerte D. Sent: Friday, October 24, 2003 12:10 PM Subject: Really slow query (compared with Visual FoxPro) Hi all, I'm migrating from Visual FoxPro (I'm a newbie). On a daily basis I need to run this query on a table with a little more than 5 million records. + MS Visual FoxPro 6.0 select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4 into table oper_grp This took about 173 seconds (let's say 3 minutes) + MySQL 4.1.0-alpha-max-nt mysql create table oper_grp select tel, telefb, rutaentran, rutasalien, - sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4; Query OK, 3301017 rows affected (8 hours 36 min 48.83 sec) --- 516 minutes! Records: 3301017 Duplicates: 0 Warnings: 0 Why is there such a big difference?! Any optimization tips I could follow? Thanks in advance, Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really slow query (compared with Visual FoxPro)
Hector, I migrated from Visual FoxPro too. VFP has different style to work with a table. MySQL gives you some advantage which you don't have it in VFP. for example, try to connect to the work server from home and run this query. it will take 5 days to finish it, but still MySQL takes 3 min.s :) believe me because I did this test and our company decided to stop using VFP and rewrite everything in VB and MySQL. BUT my experience: try to change the logic of your report not to retrieve large number of records. user LIMIT to create the reports page by page. this is the best and even better for the user. good luck - Original Message - From: Matt W [EMAIL PROTECTED] To: Héctor Villafuerte D. [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, October 24, 2003 3:00 PM Subject: Re: Really slow query (compared with Visual FoxPro) Hi Hector, Umm, it looks like you're simply doing a SELECT in the first query and *populating a new table* in the second. Of course inserting 3.3 million rows is going to take extra time! How can you even compare the 2 when they're doing different things? Matt - Original Message - From: Héctor Villafuerte D. Sent: Friday, October 24, 2003 12:10 PM Subject: Really slow query (compared with Visual FoxPro) Hi all, I'm migrating from Visual FoxPro (I'm a newbie). On a daily basis I need to run this query on a table with a little more than 5 million records. + MS Visual FoxPro 6.0 select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4 into table oper_grp This took about 173 seconds (let's say 3 minutes) + MySQL 4.1.0-alpha-max-nt mysql create table oper_grp select tel, telefb, rutaentran, rutasalien, - sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4; Query OK, 3301017 rows affected (8 hours 36 min 48.83 sec) --- 516 minutes! Records: 3301017 Duplicates: 0 Warnings: 0 Why is there such a big difference?! Any optimization tips I could follow? Thanks in advance, Hector -- 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]
Re: Visual Foxpro
Sorry, but what did dou mean with 'Are you still using VisualFoxPro...'? :) Mensaje citado por: Ruiz González, Jose de Jesus [EMAIL PROTECTED]: Are you still using VisualFoxPro as the programmig language in your project? If so, you can use SQL functions from VisualFoxPro to connect to mySQL using ODBC Install mySQL (if you have not allready do it ) Then install MyODBC-3.51.06.exe (downloadable from mySQL page) Create the ODBC bridge to mySQL with the ODBC Administrator If your project is OO then you can understand the next code I wrote (Whith this classes I connecto to Oracle, SQL Server and, of course mySQL ) COdbc ^ ^ | | COdbcSynchBatchMan COdbcSynchBatchNotrans ^ ^ ^ | | | CORACLECSQLSERVER CMYSQL Instantiate a CMYSQL object and you are ready * // define class COdbc as custom protected m_sClsNam protected m_sSourceName CO protected m_sUserID ops$jjr protected m_sPasswordjjr protected m_iConnectionIDnumero devuelto por sqlconnect protected m_sCursorResults cursor en que se devuelven los datos protected m_sCursorColumns cursor en que estan los nombres de las columnas SQLCOLUMNS protected m_sCursorTablescursor en que estan los nombres de la tablas SQLTABLES protected m_bInTransaction bandera para indicar si se esta en transaccion o no protected m_sErrorString cadena con mensaje de error protected m_sLogFile ruta y nombre de archivo log protected m_bLoggingOut ¿Logging? protected m_oLogFile CTextFile protected m_sSqlCmd protected m_sScriptsPath protected m_iIdiom idioma a usar para los nombres de meses ** * ts1: SourceName * ts2: UserId * ts3: Password procedure Init parameter ts1, ts2, ts3 this.m_sClsNam = 'Codbc' this.m_sSourceName = ts1 this.m_sUserID = ts2 this.m_sPassword = ts3 this.m_iConnectionID = -1 this.m_sCursorResults = '' this.m_sCursorColumns = '' this.m_sCursorTables = '' this.m_sErrorString = '' this.m_sLogFile = TEMPDIR + 'codbc.log' this.m_bLoggingOut = .F. this.m_sSqlCmd = '' this.m_sScriptsPath = 'd:\fpw25\importa\oracle\scripts\' this.m_iIdiom = THIs.GENGLISH() endproc * procedure destroy if this.m_bLoggingOut this.SetLogOff endif if this.m_iConnectionID # -1 this.Disconnect endif endproc function GStillExecuting return 0 ** function GFinished return 1 function GNoMoreData return 2 ** function GSystemTables return 'SYSTEM TABLE' function GTables return 'TABLE' *** function GViews return 'VIEW' function GDBComplete return 1 ** function GDBPrompt return 2 function GDBNoPrompt return 3 *** function GTransAuto return 1 * function GTransManual return 2 ** function GAsynchronous return .T. * function GSynchronous return .F. *** function GBatchMode return .T. * function GNoBatchMode return .F. function Connect local m.bRet ? 'COdbc::Connect()' m.bRet = .T. this.m_iConnectionID = SQLCONNECT( this.m_sSourceName, this.m_sUserID, this.m_sPassword ) ? this.m_iConnectionID if this.m_iConnectionID 0 this.GetError( this.m_iConnectionID ) this.m_iConnectionID = -1 m.bRet = .F. endif
Visual Foxpro
Are you still using VisualFoxPro as the programmig language in your project? If so, you can use SQL functions from VisualFoxPro to connect to mySQL using ODBC Install mySQL (if you have not allready do it ) Then install MyODBC-3.51.06.exe (downloadable from mySQL page) Create the ODBC bridge to mySQL with the ODBC Administrator If your project is OO then you can understand the next code I wrote (Whith this classes I connecto to Oracle, SQL Server and, of course mySQL ) COdbc ^ ^ | | COdbcSynchBatchMan COdbcSynchBatchNotrans ^ ^ ^ | | | CORACLECSQLSERVER CMYSQL Instantiate a CMYSQL object and you are ready * // define class COdbc as custom protected m_sClsNam protected m_sSourceName CO protected m_sUserID ops$jjr protected m_sPasswordjjr protected m_iConnectionIDnumero devuelto por sqlconnect protected m_sCursorResults cursor en que se devuelven los datos protected m_sCursorColumns cursor en que estan los nombres de las columnas SQLCOLUMNS protected m_sCursorTablescursor en que estan los nombres de la tablas SQLTABLES protected m_bInTransaction bandera para indicar si se esta en transaccion o no protected m_sErrorString cadena con mensaje de error protected m_sLogFile ruta y nombre de archivo log protected m_bLoggingOut ¿Logging? protected m_oLogFile CTextFile protected m_sSqlCmd protected m_sScriptsPath protected m_iIdiom idioma a usar para los nombres de meses ** * ts1: SourceName * ts2: UserId * ts3: Password procedure Init parameter ts1, ts2, ts3 this.m_sClsNam = 'Codbc' this.m_sSourceName = ts1 this.m_sUserID = ts2 this.m_sPassword = ts3 this.m_iConnectionID = -1 this.m_sCursorResults = '' this.m_sCursorColumns = '' this.m_sCursorTables = '' this.m_sErrorString = '' this.m_sLogFile = TEMPDIR + 'codbc.log' this.m_bLoggingOut = .F. this.m_sSqlCmd = '' this.m_sScriptsPath = 'd:\fpw25\importa\oracle\scripts\' this.m_iIdiom = THIs.GENGLISH() endproc * procedure destroy if this.m_bLoggingOut this.SetLogOff endif if this.m_iConnectionID # -1 this.Disconnect endif endproc function GStillExecuting return 0 ** function GFinished return 1 function GNoMoreData return 2 ** function GSystemTables return 'SYSTEM TABLE' function GTables return 'TABLE' *** function GViews return 'VIEW' function GDBComplete return 1 ** function GDBPrompt return 2 function GDBNoPrompt return 3 *** function GTransAuto return 1 * function GTransManual return 2 ** function GAsynchronous return .T. * function GSynchronous return .F. *** function GBatchMode return .T. * function GNoBatchMode return .F. function Connect local m.bRet ? 'COdbc::Connect()' m.bRet = .T. this.m_iConnectionID = SQLCONNECT( this.m_sSourceName, this.m_sUserID, this.m_sPassword ) ? this.m_iConnectionID if this.m_iConnectionID 0 this.GetError( this.m_iConnectionID ) this.m_iConnectionID = -1 m.bRet = .F. endif return m.bRet ***
Visual Foxpro MySQL
Hi, I have to convert a FoxPro project and use MySQL as the database engine. I've read some documents about FoxPro and MySQL but still I think I am not ready to start. Specially I don't know how to use SQLSETPROP properly and the connection in FoxPro should be synchronous or asynchronous. Please guide me to a online document or something to give me more information (MSDN help is not enough). Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySql and Visual FoxPro
Is there any api that i can use mySql whith Visual FoxPro ? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Visual Foxpro
Tengo una base de datos en Mysql pero programando en visual foxpro no me regresa la
informacion
no se, si hay que hacer algo en especial ??
el codigo en que hace la conexion es el sig.
STORE SQLCONNECT('Sipaae') TO db_conec
IF db_conec = 0
= MESSAGEBOX('Falla al Conectarse al Servidor', 16, 'SQL Connect Error')
Do \sipaaepc\CleanUp.prg
Restore Macros
Close DataBase All
Clear Events
Quit
Else
= SQLSETPROP(db_conec, 'Transactions', 2)
ENDIF
= SQLEXEC(db_conec, SELECT cve_acceso, nombre, niv_acc FROM +;
catalogos:ca_operadores Where rpe='+wrpe_usuario+', CurSubesta)
Select CurSubesta
Go Top
If !Eof()
If(cve_acceso==wcontraseña_usuario)
Store niv_acc To wnivel_usuario
Store nombre To wnombre
ThisForm.Lb1.Visible=.f.
ThisForm.Lb2.Visible=.f.
ThisForm.Tx1.Visible=.f.
ThisForm.Tx2.Visible=.f.
alguna sugerencia???.
Visual Foxpro 6
I wish to use MySQL as a backend to a Visual Foxpro 6 client application, has anyone got any experience of this ? Does it work ? Regards Chris.br clear=allhrGet Your Private, Free E-mail from MSN Hotmail at a href="http://www.hotmail.com"http://www.hotmail.com/a.br/p
Re: Visual Foxpro 6
Hi Christopher, I wish to use MySQL as a backend to a Visual Foxpro 6 client application, has anyone got any experience of this ? Does it work ? We're using MySQL as backend for our VFP6-Application. It's working with the MyODBC-Driver but you have to take care about memo-fields and boolean fields. We're no longer use boolean cause it isn't supported by MySQL. So we changed to the good old fashioned binary value (tinyint in MySQL) with 0 and 1. Using Memofields is a bit more tricky cause you have to use text or blob in MySQL to store their content. MySQL has no problems storing it but when you're trying to get the data back to VFP you can't use a passthrough select statement. If you do so VFP handles the content of the blob field as an object, not as text. For this reason you have to use a view. In the view definition you can change the type for the blob field from object to memo so it will be displayed right in VFP. Oh, I almost forgot the nastiest point up to now, the view definition: When you create a view with VFP the MyODBC-Driver loads down the complete table content before showing the dialog where you can choose the fields from the table structure. If you got a tabelle with a million rows (like we do) you can spend much time in drinking coffee and walking around before you get this dialog. So create the views on an empty table and fill them after creating the view. Greetings from cloudy Germany Lutz Maibach EasyCom GmbH - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Visual Foxpro 6 (again)
Thanks for all the advice about using Visual Foxpro 6 with MySQL. I have started to create my application and have come across a strange problem. In MySQL i have a field in a table called Address1, it is VARCHAR(40) ... when i create my view i can run the sql and see the results no problem. As soon as I save the view and then try to browse the view I get a datatype is invalid error. Looking at the properties of Address1 it seems to have been bought into the fox view a CHAR(1) ... any ideas Regards Chris. I have the latest win32 version of mysql (binary) the latest mysqlodbc driver and visual fox 6 (sp5)br clear=allhrGet Your Private, Free E-mail from MSN Hotmail at a href="http://www.hotmail.com"http://www.hotmail.com/a.br/p
Re: Visual Foxpro 6
Hi, it works very well via MyODBC with either SQLCONNECT(), SQLEXEC() into local cursors or Remote Views stored in a local VP database. From: "Christopher Davis" [EMAIL PROTECTED] To: "mysqllist" [EMAIL PROTECTED] Subject: Visual Foxpro 6 Date: Thu, 5 Apr 2001 10:01:57 +0100 I wish to use MySQL as a backend to a Visual Foxpro 6 client application, has anyone got any experience of this ? Does it work ?
