Ariel Sánchez Mora <[EMAIL PROTECTED]> wrote on 04/03/2006 09:07:34 PM:
> This table holds latest data from an app: > > mysql> select * from ultimas_respuestas_snmp limit 10; > +----+----------------+----------------------- > +-------------------------+-----------+------------+----------+ > | id | info_oficina | columna_donde_guardar | info_interfaz > | valorSNMP | nombre_dns | hora | > +----+----------------+----------------------- > +-------------------------+-----------+------------+----------+ > | 0 | Sucursal Canas | USO_CPU_1min | " > | error | canas | 18:49:53 | > | 1 | Sucursal Canas | RAM_LIBRE | " > | error | canas | 18:49:54 | > | 2 | Sucursal Canas | ESTADO_ADMIN_1 | TDM 195-2883 ICE > | 1 | canas | 18:49:55 | > | 3 | Sucursal Canas | ESTADO_ADMIN_2 | RDSI 669-9010 ICE > | error | canas | 18:49:56 | > | 4 | Sucursal Canas | ESTADO_ADMIN_3 | RDSI_doble 669-9010 > ICE | error | canas | 18:49:57 | > | 5 | Sucursal Canas | ESTADO_PROTOCOLO_1 | TDM 195-2883 ICE > | error | canas | 18:49:58 | > | 6 | Sucursal Canas | ESTADO_PROTOCOLO_2 | RDSI 669-9010 ICE > | error | canas | 18:49:59 | > | 7 | Sucursal Canas | ESTADO_PROTOCOLO_3 | RDSI_doble 669-9010 > ICE | 5 | canas | 18:50:00 | > | 8 | Sucursal Canas | BW_ENTRADA_1 | TDM 195-2883 ICE > | error | canas | 18:50:01 | > | 9 | Sucursal Canas | BW_ENTRADA_2 | RDSI 669-9010 ICE > | error | canas | 18:50:02 | > +----+----------------+----------------------- > +-------------------------+-----------+------------+----------+ > 10 rows in set (0.00 sec) > > without the "limit 10" > > 1780 rows in set (0.03 sec) > > the create table: > > ultimas_respuestas_snmp CREATE TABLE `ultimas_respuestas_snmp` ( > `id` int(4) NOT NULL default '0', > `info_oficina` varchar(35) default NULL, > `columna_donde_guardar` varchar(30) default NULL, > `info_interfaz` varchar(30) default NULL, > `valorSNMP` varchar(12) default NULL, > `nombre_dns` varchar(20) default NULL, > `hora` varchar(10) default NULL > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 > > I cannot use indexes because my dbexpress driver doesn't support it > (long story short, I'll change it in the next version). > > Now, I have to make a lot of sequential updates, like > > UPDATE ultimas_respuestas_SNMP SET valorSNMP ="1", hora ="18:47:21" > WHERE id ="0"; > UPDATE ultimas_respuestas_SNMP SET valorSNMP ="10", hora ="18:47:22" > WHERE id ="1"; > UPDATE ultimas_respuestas_SNMP SET valorSNMP ="1", hora ="18:47:22" > WHERE id ="2"; > UPDATE ultimas_respuestas_SNMP SET valorSNMP ="1", hora ="18:47:22" > WHERE id ="3"; > UPDATE ultimas_respuestas_SNMP SET valorSNMP ="1", hora ="18:47:23" > WHERE id ="4"; > UPDATE ultimas_respuestas_SNMP SET valorSNMP ="1", hora ="18:47:23" > WHERE id ="5"; > UPDATE ultimas_respuestas_SNMP SET valorSNMP ="5", hora ="18:47:24" > WHERE id ="6"; > UPDATE ultimas_respuestas_SNMP SET valorSNMP ="5", hora ="18:47:24" > WHERE id ="7"; > UPDATE ultimas_respuestas_SNMP SET valorSNMP ="7000", hora ="18:47: > 24" WHERE id ="8"; > . > . > . > UPDATE ultimas_respuestas_SNMP SET valorSNMP ="0", hora ="18:48:38" > WHERE id ="1778"; > UPDATE ultimas_respuestas_SNMP SET valorSNMP ="", hora ="18:48:38" > WHERE id ="1779"; > > This makes my server CPU load top 100% for about 1:20 s. > > First question: is update the best command for this? I've seen > replace that might work too; has anyone played around with something > like this before? > > Second: is there a better way of formulating the update command, for > this sequence?? Perhaps one that takes advantage of the sequential inserts? > > TIA, all comments welcome. I am a newbie by the way, trying to > optimize my first MySQL related program. > > Ariel Yes, there is a way to make this go MUCH faster. Assuming you followed the advice of the previous responses and added an INDEX to your ID column on ultimas_respuestas_SNMP. Indexes are used internally to MySQL, the fact that you are using dbexpress has no bearing on good database design. If you want, or in this case *NEED* an index, add it. MySQL deals with those, not your connection library. >>>start script<<< CREATE TEMPORARY TABLE bulkUpdate ( id int not null, newHora varchar(10), newSNMP varchar(12), PRIMARY KEY (id) ) INSERT bulkUpdate (id, newHora, newSNMP) VALUES (0,'18:47:21','1'),(1,'18:47:22','10'), ... the rest of the 1800 rows of changes you want to make ...; UPDATE ultimas_respuestas_SNMP ur INNER JOIN bulkUpdate bu ON bu.id = ur.id SET ur.hora = bu.newHora, ur.valorSNMP = bu.newSNMP; DROP TEMPORARY TABLE bulkUpdate; >>>end script<<< This works faster for several reasons: a) There is an index on the column you are using most often for your lookups (see previous posts) b) You are asking the parser to evaluate only 3 statements, not 1800+. c) You are performing all of the UPDATES at once. More things that will make all of your SQL processing go faster (general speed tips): a) Define appropriate indexes. Indexes are internal to MySQL and are not influenced by your connection library. b) If a value is supposed to be a number, store it in a numeric column type. Numeric comparisons occur from 5 to 50 times faster than string comparisons. c) Do not "quote" numbers. Quoting a value tells the SQL engine that you are giving it a string. However, if that value is supposed to be processed as a number the SQL engine will need to auto-convert it to the nearest possible number. That auto-conversion takes time that you could have avoided using if you had just not quoted the values in the first place. d) Unless absolutely necessary, do not store time or date or date+time values as strings. Store them as the appropriate date-like storage type. It converts those values internally to numbers. This gives you a 5-50x performance kicker over storing them as strings. HTH! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
