RV: HELP - SQL access problem

------ =_NextPart_000_01BD5A31.4D7BD3A0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable HI I'm in trouble, I have a query in a program/REPORT that is doing some = really strange thing is an select join of two tables, and it just gets lost in the data and = never comes out of the foreach record on the select. I have an index for the two field '"dependencia" and "serial_cuenta" and anothen index for "dependencia", "serial", and "fecha_partida" but I do not think the query is going throu them. I did put at the = beginning of the program the set explain on, and I am attaching the = sqexp*.out file for you to see the query. If I get the query in the isql and run it with out the dates = "fecha_partida" and group it does come out fast, but as soon I include = the dates it get lost too. NOTE: The most strange thing is that I been following the execution of the = program in the tbmonitor/status/user option and as soon as it initiates = to execute the program gets a lock, the report does not have any insert = or delete or update statement, so why do I get a lock. Another thing is that when it reaches the query in discution in get in a = loop were it tryes to acces some (+- 40) record and then realeses them = because a time out which I think is because the lock.=20 So I keep seeing the status line of the user going from --B-R- to --B--- = (this status line is the last column in the tbmonitor/status/user option Please give me a clue, I do not get it, don't know what is happening Mili Barneond ------ =_NextPart_000_01BD5A31.4D7BD3A0 Content-Type: application/octet-stream; name="SQEXP.OUT" Content-Transfer-Encoding: base64 QUERY: ------ select codigo,descripcion from con_dependencias where 1=1 order by codigo Estimated Cost: 3 Estimated # of Rows Returned: 1 1) informix.con_dependencias: INDEX PATH Filters: 1 = 1 (1) Index Keys: codigo QUERY: ------ select sistema from ctrl_controles Estimated Cost: 2 Estimated # of Rows Returned: 10 1) informix.ctrl_controles: SEQUENTIAL SCAN QUERY: ------ select dependencia from ctrl_controles Estimated Cost: 2 Estimated # of Rows Returned: 10 1) informix.ctrl_controles: SEQUENTIAL SCAN QUERY: ------ select username from sysusers where username = user Estimated Cost: 1 Estimated # of Rows Returned: 1 1) informix.sysusers: INDEX PATH (1) Index Keys: username (Key-Only) Lower Index Filter: informix.sysusers.username = USER QUERY: ------ select abreviatura from ctrl_dependencia dep where dep.dependencia =? Estimated Cost: 2 Estimated # of Rows Returned: 1 1) informix.dep: INDEX PATH (1) Index Keys: dependencia Lower Index Filter: informix.dep.dependencia = '0014200' QUERY: ------ select abreviatura from ctrl_dependencia dep where dep.dependencia =? Estimated Cost: 2 Estimated # of Rows Returned: 1 1) informix.dep: INDEX PATH (1) Index Keys: dependencia Lower Index Filter: informix.dep.dependencia = '0014200' QUERY: ------ select dependencia from ctrl_controles Estimated Cost: 2 Estimated # of Rows Returned: 10 1) informix.ctrl_controles: SEQUENTIAL SCAN QUERY: ------ select * from con_mascaras_ctas where dependencia =? Estimated Cost: 6 Estimated # of Rows Returned: 5 1) informix.con_mascaras_ctas: INDEX PATH (1) Index Keys: dependencia posicion_inicial Lower Index Filter: informix.con_mascaras_ctas.dependencia = '0014201' QUERY: ------ select * from con_catalogo_cont a where a.dependencia =? and length ( a.numero_cuenta) =? and a.numero_cuenta matches "100*" Estimated Cost: 3 Estimated # of Rows Returned: 1 1) informix.a: INDEX PATH Filters: (LENGTH (informix.a.numero_cuenta ) = '3' AND informix.a.numero_cuenta MATCHES '100*' ) (1) Index Keys: dependencia tipo grupo numero_cuenta Lower Index Filter: informix.a.dependencia = '0014201' QUERY: ------ select count(*),'' from con_catalogo_cont a where dependencia ='0014201' and length(a.numero_cuenta) = 3and a.numero_cuenta matches '100*' Estimated Cost: 1 Estimated # of Rows Returned: 1 1) informix.a: INDEX PATH Filters: (LENGTH (informix.a.numero_cuenta ) = 3 AND informix.a.numero_cuenta MATCHES '100*' ) (1) Index Keys: dependencia tipo grupo numero_cuenta (Key-Only) Lower Index Filter: informix.a.dependencia = '0014201' QUERY: ------ select sum ( cargos_mes), sum ( abonos_mes) from con_maestro_saldos d where d.dependencia =? and d.numero_cuenta =? and d.mes between month (?) and month (?) and d.anio between year (?) and year (?) Estimated Cost: 1 Estimated # of Rows Returned: 1 1) informix.d: INDEX PATH Filters: (informix.d.anio >= YEAR ('01/01/1997' ) AND informix.d.anio <= YEAR ('28/02/1997' ) ) (1) Index Keys: dependencia numero_cuenta mes Lower Index Filter: (informix.d.dependencia = '0014201' AND (informix.d.numero_cuenta = 84 AND informix.d.mes >= MONTH ('01/01/1997' ) ) ) Upper Index Filter: informix.d.mes <= MONTH ('28/02/1997' ) QUERY: ------ select * from con_maestro_saldos d where d.dependencia =? and d.numero_cuenta =? and d.mes = month (?) and d.anio = year (?) Estimated Cost: 1 Estimated # of Rows Returned: 1 1) informix.d: INDEX PATH Filters: informix.d.anio = YEAR ('01/01/1997' ) (1) Index Keys: dependencia numero_cuenta mes Lower Index Filter: (informix.d.dependencia = '0014201' AND (informix.d.numero_cuenta = 84 AND informix.d.mes = MONTH ('01/01/1997' ) ) ) QUERY: ------ select a.dependencia,c.fecha_partida,c.numero_partida, 0, c.serial_cuenta,sum(c.debe),sum(c.haber) from con_catalogo_co