Board index » delphi » URGENT: Date field with index and NULL values.

URGENT: Date field with index and NULL values.

I have this query. All the fields used in join and where are indexed using
FK or index.

SELECT  ARTICULOS.ID_ARTICULO,
               ARTICULOS.REF_INTERNA,
               ARTICULOS.DESC_INTERNA,
               ARTICULOS.GARANTIA_CLIENTE,
               ARTICULOS.TARIFA_MT,
               articulos.DESCATALOGADO_FECHA,
               ALMACENES_STOCK.DISPONIBLES
FROM ARTICULOS
JOIN ALMACENES_STOCK ON (ALMACENES_STOCK.ID_ARTICULO =
ARTICULOS.ID_ARTICULO)
 and  (ALMACENES_STOCK.ID_ALMACEN = 1)
WHERE
(ARTICULOS.DESC_INTERNA STARTING 'TRIVIAL') and
(ARTICULOS.DESCATALOGADO_FECHA is NULL)

With the 3 constraints, the query need 43 seconds. If I put only two, the
query return the rows in 1 second.
What is the problem.

Later I try droping index for DESCATALOGADO_FECHA (the field use date format
with many null values), the query with return values directly.

Can some body explain me what is the diferrence. How IB can solve the query
quickly whitout index for the date field ?

P.S.:
I use Quickdesk.
the plan for the query is PLAN JOIN (ALMACENES_STOCK INDEX
(RDB$PRIMARY299),ARTICULOS INDEX (RDB$PRIMARY8,ARTICULOS_DESC_INTERNA))

and the adapted plan is PLAN JOIN (ALMACENES_STOCK INDEX
(PK_ALMACENES_STOCK),ARTICULOS INDPK_ARTICULOSARY8,ARTICULOS_DESC_INTERNA))

Alex D.M.

 

Re:URGENT: Date field with index and NULL values.


If you use only the FROM and WHERE clause to join your tables, do you have
the same results.

I'm not sure interbase react same as PostGRES, but when you use the JOIN
clause it is to prevent the Database server to plane the request, you have
to use JOIN clause in the good order, if you don't sure of the order,
rewrite your request adding "ALMACENES_STOCK" on the FROM clause and
"ALMACENES_STOCK.ID_ARTICULO = ARTICULOS.ID_ARTICULO)
  and  (ALMACENES_STOCK.ID_ALMACEN = 1)" on the WHERE clause.

The change is that the server can now make himself the planification of the
request, it test the better manner to join table and to increase the
execution speed...

"Alexandre Durn Mertens - Departamento Desarrollo - Computer Soft, S.A."
<cs_desarro...@nostracom.com> a crit dans le message news:
3c849ec1_1@dnews...

Quote
> I have this query. All the fields used in join and where are indexed using
> FK or index.

> SELECT  ARTICULOS.ID_ARTICULO,
>                ARTICULOS.REF_INTERNA,
>                ARTICULOS.DESC_INTERNA,
>                ARTICULOS.GARANTIA_CLIENTE,
>                ARTICULOS.TARIFA_MT,
>                articulos.DESCATALOGADO_FECHA,
>                ALMACENES_STOCK.DISPONIBLES
> FROM ARTICULOS
> JOIN ALMACENES_STOCK ON (ALMACENES_STOCK.ID_ARTICULO =
> ARTICULOS.ID_ARTICULO)
>  and  (ALMACENES_STOCK.ID_ALMACEN = 1)
> WHERE
> (ARTICULOS.DESC_INTERNA STARTING 'TRIVIAL') and
> (ARTICULOS.DESCATALOGADO_FECHA is NULL)

> With the 3 constraints, the query need 43 seconds. If I put only two, the
> query return the rows in 1 second.
> What is the problem.

> Later I try droping index for DESCATALOGADO_FECHA (the field use date
format
> with many null values), the query with return values directly.

> Can some body explain me what is the diferrence. How IB can solve the
query
> quickly whitout index for the date field ?

> P.S.:
> I use Quickdesk.
> the plan for the query is PLAN JOIN (ALMACENES_STOCK INDEX
> (RDB$PRIMARY299),ARTICULOS INDEX (RDB$PRIMARY8,ARTICULOS_DESC_INTERNA))

> and the adapted plan is PLAN JOIN (ALMACENES_STOCK INDEX
> (PK_ALMACENES_STOCK),ARTICULOS

INDPK_ARTICULOSARY8,ARTICULOS_DESC_INTERNA))

- Show quoted text -

Quote

> Alex D.M.

Re:URGENT: Date field with index and NULL values.


In article <3c849ec1_1@dnews>, cs_desarro...@nostracom.com says...

Quote

> With the 3 constraints, the query need 43 seconds. If I put only two, the
> query return the rows in 1 second.
> What is the problem.

        Care to tell us *which* two?  :)

        Also, the PLAN with and without the third item would be useful.

Quote

> Later I try droping index for DESCATALOGADO_FECHA (the field use date format
> with many null values), the query with return values directly.

        It's hard to make a comment on an index without knowing the DDL.

        -Craig

--
 Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
     Delphi/InterBase WebLog: http://delphi.weblogs.com
     InterBase PLANalyzer (Free IB optimization tool):
          http://delphi.weblogs.com/IBPLANalyzer

Re:URGENT: Date field with index and NULL values.


InterBase does not work like PostGRES in this case. Using the JOIN syntax
does not bypass the optimizer.

--
Bill
(TeamB cannot answer questions received via email)

Re:URGENT: Date field with index and NULL values.


Ok. But why, with this query, it work fine. I only change the last condition
using two "not"

SELECT  ARTICULOS.ID_ARTICULO,
               ARTICULOS.REF_INTERNA,
               ARTICULOS.DESC_INTERNA,
               ALMACENES_STOCK.DISPONIBLES,
               ARTICULOS.GARANTIA_CLIENTE,
               ARTICULOS.TARIFA_MT,
               articulos.DESCATALOGADO_FECHA
FROM ARTICULOS, ALMACENES_STOCK
WHERE
(ARTICULOS.ID_ARTICULO = ALMACENES_STOCK.ID_ARTICULO)
and  (ALMACENES_STOCK.ID_ALMACEN = 1)
and (ARTICULOS.DESC_INTERNA STARTING 'TRIVIAL')
and (not (not (ARTICULOS.DESCATALOGADO_FECHA is NULL)))

Alex D.M.

Re:URGENT: Date field with index and NULL values.


I understand that if the field is indexed, normally, a where clause is more
eficient that when the field is not indexed.

Alex D.M.

Re:URGENT: Date field with index and NULL values.


That is not correct. It depends on the selectivity of the index and the
fraction of the rows that will be returned. Consider a column that has two
values, for example M and F. An index scan that will retrieve 50% of the
index records plus the individual record reads from the table will require
much more I/O and be much slower than a seqential scan of the table.

--
Bill
(TeamB cannot answer questions received via email)

Re:URGENT: Date field with index and NULL values.


In article <3c84fca3_2@dnews>, cs_desarro...@nostracom.com says...

Quote
> Ok. But why, with this query, it work fine. I only change the last condition
> using two "not"

        Since we don't have your database, it's hard to comment on SQL
snippets like these.  At a minimum, we need to see the *exact* query,
with and without the change, and the PLAN for each version.

        -Craig

--
 Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
     Delphi/InterBase WebLog: http://delphi.weblogs.com
     InterBase PLANalyzer (Free IB optimization tool):
          http://delphi.weblogs.com/IBPLANalyzer

Other Threads