Adam wrote:

>
>
> Lee,
>
> > FB 2.1
> >
> > I have a query that will be ordering a TimeStamp field descending.
> Is it
> > generally a good idea to place a descending index on a field like that?
>
> The underlying data type doesn't really matter too much when
> considering the suitability of an index.
>
> > I would
> > assume that a Time Stamp would have fairly low selectivity, but I
> guess that
> > would depend on how many records are being inserted consistently...?
>
> OK, I think I now understand you. You are referencing a timestamp as
> in the time the current record was inserted rather than the SQL
> concept of a timestamp; a field capable of storing any date and time.
>

Correct.

> If I do understand you correctly, I suppose it depends on the
> frequency of inserts. If your program bulk inserts once a day, then
> selectivity wont be the best. If your program trickle inserts the
> records across the day, it will most likely be excellent.
>

The records are not bulk inserted, but inserted as new sales are created.

> >
> >
> > Query.SQLText :=
> > 'SELECT ' +
> > ' SALE.OID, ' +
> > ' SALE.USER_OID, ' +
> > ' SALE.CUSTOMER_OID, ' +
> > ' SALE.SALE_ENTRY_TIME, ' +
> > ' SALE.SALE_DEPART_TIME, ' +
> > ' SALE.SALE_ENTRY_TIME, ' +
> > ' POS_USER.USER_FIRST_NAME, ' +
> > ' POS_USER.USER_LAST_NAME, ' +
> > ' CUSTOMER.CUST_FIRST, ' +
> > ' CUSTOMER.CUST_LAST ' +
> > 'FROM ' +
> > ' CUSTOMER ' +
> > ' INNER JOIN ' +
> > ' (POS_USER INNER JOIN SALE ON POS_USER.OID = ' +
> > ' SALE.USER_OID) ON CUSTOMER.OID = SALE.CUSTOMER_OID ' +
> > 'WHERE ' +
> > ' SALE.DELIVERY_STATUS = :pDeliveryStatus ' +
> > 'ORDER BY ' +
> > ' SALE.SALE_DEPART_TIME DESC;';
> >
>
> I personally wouldn't bother with an index for ordering unless
> * You are using First X or rows syntax to limit records.
> * You really need the first page of results quicker. (A fetch all
> results will most likely be faster without the index at all).
>
> This query looks like it will be reasonably slow because
> DeliveryStatus most likely has poorly selectivity, so you are doing a
> tablescan. Unless you really really need every record, I would see if
> you can't get some sort of date range into the where clause as well to
> limit the returned records.

I'll try that, thank you.

> I also wouldn't bother using brackets around your inner join. You only
> really need to do that if the join inside the brackets is some form of
> outer join, and as it is you may be limiting the choices of the
> optimiser (it has to join pos_user to sale prior to joining with
> customer, which may not be the fastest in every case).
>

Excellent. Thank you.

--
Warm Regards,

Lee