Board index » delphi » Query hang server

2005-06-14 06:28:17 AM
delphi184
Hi,
I've a problem using IB7 SP2 in Windows 2000 server.
DB has 1GB. When execute a query (with 7 tables join) this not response.
Then I do a Backup/Restore and execute the same query again and this run
very fast.
If after of backup/restore the users make read/write transaction (common
operation in the application) and try to execute the query, this not
response and hang the server.
The query is:
SELECT DISTINCT
0 AS NRO_REG,
CPRDET.COD_ART,
CPRDET.DESCRIPCION,
SUM(CPRDET.CANT) AS CANT_PEDIDA,
0.0000 AS EXISTENCIA,
PROVEEDORES.NOM_FANTASIA,
ENTIDADES.RZ,
MARCAS.MARCA,
ARTRUBROS.RUBRO,
CPRDET.ART_ID,
CPRDET.MED,
CPRDET."MOD"
FROM
CPRDET
JOIN CPR ON (CPR.CPR_ID = CPRDET.CPR_ID)
JOIN ARTICULOS ON (CPRDET.ART_ID = ARTICULOS.ART_ID)
LEFT JOIN ARTRUBROS ON (ARTICULOS.RUBRO_ID = ARTRUBROS.RUBRO_ID)
LEFT JOIN MARCAS ON (ARTICULOS.MARCA_ID = MARCAS.MARCA_ID)
LEFT JOIN ART_PROV ON (ARTICULOS.ART_ID = ART_PROV.ART_ID)
LEFT JOIN PROVEEDORES ON (ART_PROV.PROV_ID = PROVEEDORES.PROV_ID)
LEFT JOIN ENTIDADES ON (ENTIDADES.ENT_ID = PROVEEDORES.ENT_ID)
WHERE (CPR.CPR_TIPO_ID = 'FA')
AND (CPR.CPR_LOTE_ID = :CPR_LOTE_ID)
GROUP BY PROVEEDORES.NOM_FANTASIA, ENTIDADES.RZ, MARCAS.MARCA,
ARTRUBROS.RUBRO,
CPRDET.ART_ID, CPRDET.COD_ART, CPRDET.DESCRIPCION, CPRDET.MED,
CPRDET."MOD"
ORDER BY MARCAS.MARCA
I'am using for access to IB, IBX components for Delphi 6.02 and Datasnap.
Any can tellme what's wrong ?
Thank in advance.
---
Maxi.
 

Maximiliano Robaina writes:
no_record_version and wait ?
hth
--
Rob Schieck
TeamB
 

read_committed
rec_version
nowait
 

Maximiliano Robaina writes:
hangs ?
You might want to get the query plan after you restore and when you
think it is going to hang to see if there is a difference.
hth
--
Rob Schieck
TeamB
 

Hi Robert, thank for you response:
You think what this problem is by the select statatement in query?
I don't understand why after backup/restore this work.
<Robert Schieck (TeamB)>escribi?en el mensaje