Re: Slowly query
In article <6eka2g$5u$1@news.xmission.com>, correo
<rodevalg@ns.guate.net> writes
>
>
>------ =_NextPart_000_01BD50F5.49C68B00
>Content-Type: text/plain; charset="us-ascii"
>Content-Transfer-Encoding: quoted-printable
>
>Hi=20
>
>I been having some problems with a query, it use to get lost in the data =
>and never come out with anything.
>
>After many revision, now it just takes eternity to answer, thow I think =
>the index are ok, i am not too shure that the query is using them or is =
>is maybe the construccion which is kind of wrong. So I want to ask you =
>specifi topics
>
>1.- matches versus greather=20
> which is faster in a table size mediun to large. To use=20
> -- a matches "aa*"
> or to use -- a > a and a < b (where b is the next option)
>
I'd probably go for matches "aa*" since it uniquely specifies 2
characters rather than one i.e. allows informix to use more of the key.
>2. to use between rather than greater and lesser in a table size medium =
>to large
>
>like number between x and y
>or to use number >=3D x and number <=3D y
>
No difference. between is just easier to read.
>
>The query that is giving me white hairs is the next one
>
>select count(*) from con_catalogo_cont a, con_partidas_det c where
> a.dependencia = '0014201'
> and a.numero_cuenta matches '100*'
>and a.permiso = 120
>and c.dependencia = a.dependencia
>and c.serial_cuenta = a.serial_cuenta
>and c.fecha_partida between '01/01/1997' and '28/02/1997'>
Run this is dbaccess doing a
set explain on;
first. What is in the sqexplain.out file which is produced?
>Thanks in advance
>
>Milagro Barneond
>Guatemala
>------ =_NextPart_000_01BD50F5.49C68B00
>Content-Type: application/ms-tnef
>Content-Transfer-Encoding: base64
>
>eJ8+IgcWAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5NaWNy
>b3NvZnQgTWFpbC5Ob3RlADEIAQ2ABAACAAAAAgACAAEEkAYAtAEAAAEAAAAQAAAAAwAAMAIAAAAL
>AA8OAAAAAAIB/w8BAAAAcwAAAAAAAAC1O8LALHcQGqG8CAArKlbCFQAAAOiXdE3zd9ERgYAAALRl
>O0pEgQAAAAAAAIErH6S+oxAZnW4A3QEPVAIAAAAASW5mb3JtaXggbGlzdABTTVRQAGluZm9ybWl4
>LWxpc3RAcm15LmVtb3J5LmVkdQAAHgACMAEAAAAFAAAAU01UUAAAAAAeAAMwAQAAABwAAABpbmZv
>cm1peC1saXN0QHJteS5lbW9yeS5lZHUAAwAVDAEAAAADAP4PBgAAAB4AATABAAAAEAAAACdJbmZv
>cm1peCBsaXN0JwACAQswAQAAACEAAABTTVRQOklORk9STUlYLUxJU1RAUk1ZLkVNT1JZLkVEVQAA
>AAADAAA5AAAAAAsAQDoBAAAAHgD2XwEAAAAOAAAASW5mb3JtaXggbGlzdAAAAAIB918BAAAALAAA
>AL8AAAC1O8LALHcQGqG8CAArKlbCFQAAAOiXdE3zd9ERgYAAALRlO0pEgQAAAwD9XwEAAAADAP9f
>AAAAAAIB9g8BAAAABAAAAAAAAAKDXwEEgAEADQAAAFNsb3dseSBxdWVyeQDgBAEFgAMADgAAAM4H
>AwAQABAABAAUAAEAEQEBIIADAA4AAADOBwMAEAAPACsAJgABAEkBAQmAAQAhAAAAQTczNDM1NzFF
>NUJDRDExMTgxODAwMDAwQjQ2NTNCNEEA4QYBA5AGANgGAAAgAAAACwACAAEAAAALACMAAAAAAAMA
>JgAAAAAACwApAAAAAAADADYAAAAAAEAAOQBgys94J1G9AR4AcAABAAAADQAAAFNsb3dseSBxdWVy
>eQAAAAACAXEAAQAAABYAAAABvVEneIyG2CVjvOUR0YGAAAC0ZTtKAAAeAB4MAQAAAAUAAABTTVRQ
>AAAAAB4AHwwBAAAAFgAAAHJvZGV2YWxnQG5zLmd1YXRlLm5ldAAAAAMABhB49KymAwAHECQDAAAe
>AAgQAQAAAGUAAABISUlCRUVOSEFWSU5HU09NRVBST0JMRU1TV0lUSEFRVUVSWSxJVFVTRVRPR0VU
>TE9TVElOVEhFREFUQUFORE5FVkVSQ09NRU9VVFdJVEhBTllUSElOR0FGVEVSTUFOWVJFVklTAAAA
>AAIBCRABAAAA0AMAAMwDAADIBQAATFpGdecVR5QDAAoAcmNwZzEyNQYyAPgLYG5nNDEw0jYBVjA4
>AfcgAqQD4wkCAGNoCsBzZXQwPiAHEwKDAFADVAIAcHJecQ5QEYgHbQKAfQqBdT5jAFALAwzQAcEL
>piBIjmkK4wqECoBJIGIJ4YogEaB2C4BnIHMDcJRlIBNgbwJgZW0EIIED8HRoIGEgcQpQkHJ5LCAZ
>0CB1EdCgIHRvIGcR4CAJAC5zBUALgBsAaBkQZGEDAZAaAG5kIG5ldn0EkCAFoBkBCGAFQBnEbtZ5
>GeAYoS4XSkEBgB0B+QOBeSAJcBiQAJACIBqA7G5vB+AaoWoa0AVAAZD+aweREeAEkRnQH/AbEQBx
>fncEkBqAGeAgwRgAHkJrdxvzC4ABAHgaAAlwHXBrWxqBGgBtIKEhUW8bIHP+aAhwGvERoCFRHBEa
>MxqQ/wQgGtAYohwBJTAFsScSJyE9AMB5GCAb8wWgAIB0cn0VoGMgYRmwHlARkCcSa/skIR1wZhmw
>A2AecQYAGyBdGAB3AHAlchoAcyPAeXsIYBjQcAWQBpAXIBsQcEcN4BFgF1kxLi0fsXT3EZAHkRzx
>cxrQGzAJcBxQHxwQCtQKhAswILFpZGM0dGwKsVwPAg9QMzT3EqEoICoXZixgH4Ib0RoQZwGRGXAY
>0Gl6GRAHgGTcaXUb4RsgC2ByG0ArcH5UGyAa0hdEKCA2jzbBLQ8uoDbBGhAuxiJhYSp+IjYVBbEb
>ERrSN5YaED53GgEckhoQPBgQDIQBoCD+KCoQBJAZEDwQJyEcAhzQVngFQC1wdCBhKRdKMrcrcDnF
>GCB0IsAYQXIv5P8mMQOgL6QdARySGXAEEDNvzzR1JTA1BhdKbGkhkDbC3m5DEBggBcA/ViAkYRyh
>znkXRDmaRQU+PUX1RQXuPEgQRmUXSlQmlyYzJyF8Z2kYkxkBKhEfgBhhaf8RwDy8HNAXShHQGXAx
>UCggwwWgNNB0KCopMxADYZkpMl9jHFEJAGdvUCB/AiEaABqAT/IKsT2gHEBzdl8BAAVAYyoBPGE2
>F2HuLgEALPAkMW4pwBoQNsGBSBAgJzAwMTQB0OwxJzYVOxMuRQEEkFChFwpQAjA4OCcPUDAqJ+cX
>NVWULPBybQQAGyBUM/syYFfIY1NvU01ZekGRBzHvVlZbE1zrWXpmBZARoFF2Ez9HVJAxL2ERMTk5
>RjdXoBySJzI4YTAyf2FlMEoWNA8HATFKFQBwa1MoUUHhZHYAcGNDu03tAxBhCcAbIEIKwBzQAiDz
>CzEXYkd1QNEAwAtgF0QFFWEAaXADABAQAAAAAAMAERAAAAAAAwCAEP////9AAAcwICEtlCRRvQFA
>AAgwICEtlCRRvQEDACSACCAGAAAAAADAAAAAAAAARgAAAAAQhQAAAAAAAAMAJYAIIAYAAAAAAMAA
>AAAAAABGAAAAABGFAAAAAAAACwAmgAggBgAAAAAAwAAAAAAAAEYAAAAAA4UAAAAAAAADACeACCAG
>AAAAAADAAAAAAAAARgAAAABShQAAtw0AAB4AKIAIIAYAAAAAAMAAAAAAAABGAAAAAFSFAAABAAAA
>BAAAADguMAADACmACCAGAAAAAADAAAAAAAAARgAAAAABhQAAAAAAAAsAKoAIIAYAAAAAAMAAAAAA
>AABGAAAAAA6FAAAAAAAAAwArgAggBgAAAAAAwAAAAAAAAEYAAAAAGIUAAAAAAAAeACyACCAGAAAA
>AADAAAAAAAAARgAAAAA2hQAAAQAAAAEAAAAAAAAAHgAtgAggBgAAAAAAwAAAAAAAAEYAAAAAN4UA
>AAEAAAABAAAAAAAAAB4ALoAIIAYAAAAAAMAAAAAAAABGAAAAADiFAAABAAAAAQAAAAAAAAAeAD0A
>AQAAAAEAAAAAAAAAAwANNP03AADzjA==
>
>------ =_NextPart_000_01BD50F5.49C68B00--
>
--
David Williams
Maintainer of the Informix FAQ
Primary site (Beta Version) http://www.smooth1.demon.co.uk
Official site http://www.iiug.org/techinfo/faq/faq_top.html
I see you standin', Standin' on your own, It's such a lonely place for you, For
you to be If you need a shoulder, Or if you need a friend, I'll be here
standing, Until the bitter end...
So don't chastise me Or think I, I mean you harm...
All I ever wanted Was for you To know that I care