| SGreen@unimin.com 2006-04-04, 9:27 am |
| --=_alternative 004C87C185257146_=
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
Ariel S=E1nchez Mora <Arsanchez@bp.fi.cr> wrote on 04/03/2006 09:07:34 PM:
> This table holds latest data from an app:
>=20
> mysql> select * from ultimas=5Frespuestas
=5Fsnmp limit 10;
> +----+----------------+-----------------------
> +-------------------------+-----------+------------+----------+
> | id | info=5Foficina | columna=5Fdonde=5Fgu
ardar | info=5Finterfaz=20
> | valorSNMP | nombre=5Fdns | hora |
> +----+----------------+-----------------------
> +-------------------------+-----------+------------+----------+
> | 0 | Sucursal Canas | USO=5FCPU=5F1min | "=20
> | error | canas | 18:49:53 |
> | 1 | Sucursal Canas | RAM=5FLIBRE | "=20
> | error | canas | 18:49:54 |
> | 2 | Sucursal Canas | ESTADO=5FADMIN=5F1 | TDM 195-2883 ICE=20
> | 1 | canas | 18:49:55 |
> | 3 | Sucursal Canas | ESTADO=5FADMIN=5F2 | RDSI 669-9010 ICE=20
> | error | canas | 18:49:56 |
> | 4 | Sucursal Canas | ESTADO=5FADMIN=5F3 | RDSI=5Fdoble 669-9010=
=20
> ICE | error | canas | 18:49:57 |
> | 5 | Sucursal Canas | ESTADO=5FPROTOCOLO=5
F1 | TDM 195-2883 ICE=20
> | error | canas | 18:49:58 |
> | 6 | Sucursal Canas | ESTADO=5FPROTOCOLO=5
F2 | RDSI 669-9010 ICE=20
> | error | canas | 18:49:59 |
> | 7 | Sucursal Canas | ESTADO=5FPROTOCOLO=5
F3 | RDSI=5Fdoble 669-9010=
=20
> ICE | 5 | canas | 18:50:00 |
> | 8 | Sucursal Canas | BW=5FENTRADA=5F1 | TDM 195-2883 ICE=20
> | error | canas | 18:50:01 |
> | 9 | Sucursal Canas | BW=5FENTRADA=5F2 | RDSI 669-9010 ICE=20
> | error | canas | 18:50:02 |
> +----+----------------+-----------------------
> +-------------------------+-----------+------------+----------+
> 10 rows in set (0.00 sec)
>=20
> without the "limit 10"
>=20
> 1780 rows in set (0.03 sec)
>=20
> the create table:
>=20
> ultimas=5Frespuestas
=5Fsnmp CREATE TABLE `ultimas=5Frespuesta
s=5Fsnmp` (=
=20
> `id` int(4) NOT NULL default '0', =20
> `info=5Foficina` varchar(35) default NULL, =20
> `columna=5Fdonde=5Fg
uardar` varchar(30) defaul=
t=20
NULL,=20
> `info=5Finterfaz` varchar(30) default NULL, =20
> `valorSNMP` varchar(12) default NULL, =20
> `nombre=5Fdns` varchar(20) default NULL, =20
> `hora` varchar(10) default NULL =20
> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1=20
>=20
> I cannot use indexes because my dbexpress driver doesn't support it=20
> (long story short, I'll change it in the next version).
>=20
> Now, I have to make a lot of sequential updates, like
>=20
> UPDATE ultimas=5Frespuestas
=5FSNMP SET valorSNMP =3D"1", hora =3D"18:47:2=
1"=20
> WHERE id =3D"0";
> UPDATE ultimas=5Frespuestas
=5FSNMP SET valorSNMP =3D"10", hora =3D"18:47:=
22"
> WHERE id =3D"1";
> UPDATE ultimas=5Frespuestas
=5FSNMP SET valorSNMP =3D"1", hora =3D"18:47:2=
2"=20
> WHERE id =3D"2";
> UPDATE ultimas=5Frespuestas
=5FSNMP SET valorSNMP =3D"1", hora =3D"18:47:2=
2"=20
> WHERE id =3D"3";
> UPDATE ultimas=5Frespuestas
=5FSNMP SET valorSNMP =3D"1", hora =3D"18:47:2=
3"=20
> WHERE id =3D"4";
> UPDATE ultimas=5Frespuestas
=5FSNMP SET valorSNMP =3D"1", hora =3D"18:47:2=
3"=20
> WHERE id =3D"5";
> UPDATE ultimas=5Frespuestas
=5FSNMP SET valorSNMP =3D"5", hora =3D"18:47:2=
4"=20
> WHERE id =3D"6";
> UPDATE ultimas=5Frespuestas
=5FSNMP SET valorSNMP =3D"5", hora =3D"18:47:2=
4"=20
> WHERE id =3D"7";
> UPDATE ultimas=5Frespuestas
=5FSNMP SET valorSNMP =3D"7000", hora =3D"18:4=
7:
> 24" WHERE id =3D"8";
> .
> .
> .
> UPDATE ultimas=5Frespuestas
=5FSNMP SET valorSNMP =3D"0", hora =3D"18:48:3=
8"=20
> WHERE id =3D"1778";
> UPDATE ultimas=5Frespuestas
=5FSNMP SET valorSNMP =3D"", hora =3D"18:48:38=
"=20
> WHERE id =3D"1779";
>=20
> This makes my server CPU load top 100% for about 1:20 s.=20
>=20
> First question: is update the best command for this? I've seen=20
> replace that might work too; has anyone played around with something
> like this before?
>=20
> Second: is there a better way of formulating the update command, for
> this sequence?? Perhaps one that takes advantage of the sequential=20
inserts?
>=20
> TIA, all comments welcome. I am a newbie by the way, trying to=20
> optimize my first MySQL related program.
>=20
> Ariel
Yes, there is a way to make this go MUCH faster. Assuming you followed=20
the advice of the previous responses and added an INDEX to your ID column=20
on ultimas=5Frespuestas
=5FSNMP. Indexes are used internally to MySQL, the f=
act=20
that you are using dbexpress has no bearing on good database design. If=20
you want, or in this case *NEED* an index, add it. MySQL deals with those, =
not your connection library.
[color=darkred]
CREATE TEMPORARY TABLE bulkUpdate (
id int not null,
newHora varchar(10),
newSNMP varchar(12),
PRIMARY KEY (id)
)
INSERT bulkUpdate (id, newHora, newSNMP) VALUES=20
(0,'18:47:21','1'),(
1,'18:47:22','10'), ...
the rest of the 1800 rows of changes you want to make ...;
UPDATE ultimas=5Frespuestas
=5FSNMP ur
INNER JOIN bulkUpdate bu
ON bu.id =3D ur.id
SET ur.hora =3D bu.newHora, ur.valorSNMP =3D bu.newSNMP;
DROP TEMPORARY TABLE bulkUpdate;[color=da
rkred]
This works faster for several reasons:
a) There is an index on the column you are using most often for your=20
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.=20
More things that will make all of your SQL processing go faster (general=20
speed tips):
a) Define appropriate indexes. Indexes are internal to MySQL and are not=20
influenced by your connection library.
b) If a value is supposed to be a number, store it in a numeric column=20
type. Numeric comparisons occur from 5 to 50 times faster than string=20
comparisons.
c) Do not "quote" numbers. Quoting a value tells the SQL engine that you=20
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=20
possible number. That auto-conversion takes time that you could have=20
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=20
values as strings. Store them as the appropriate date-like storage type.=20
It converts those values internally to numbers. This gives you a 5-50x=20
performance kicker over storing them as strings.
HTH!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 004C87C185257146_=--
|