Re: Indexing Question

From: Bob Butler (tiredofit_at_nospam.com)
Date: 10/04/04


Date: Mon, 4 Oct 2004 07:14:19 -0700


"Darwin Weyh" <shtdev.delete.this@weyh.net> wrote in message
news:%23E$1ybhqEHA.1712@tk2msftngp13.phx.gbl
> I have a VB6 application in which I access a FP6 DB with ADO.
>
> As one of the tables gets bigger ( 20,000 Records) My response is
> degrading.
>
> A typical Recordset open is as follows
>
> SQLstr = "select * from orders where jobdate < {" & pDate & "} and
> complete = .T."
> RS.open sqlstr,db, adOpenStatic, adLockReadOnly
>
> What kind of index should I set up to speed this open up?
>
> Can someone point me to a good explanation of Indexes and how they
> relate to the SQL 'select' commands.

Since you are specifying criteria on jobdate and complete it *might* help to
index one or both of those fields although that depends on how the FP engine
processes the queries. You'd need to find an in-depth book on FP or do some
timing tests yourself to determine if it helps.

What are you doing with this recordset after opening it? Loading it into a
control or keeping it in memory and working with it dynamically? There may
be more efficient ways to open it (e.g. client-side cursor vs server-side,
forward-only vs dynamic cursor, etc) but it depends on what you are doing
with it.

Also, if you don't need every single field it'd be faster to select specific
fields rather than doing a select *.

-- 
Reply to the group so all can participate
VB.Net... just say "No"


Relevant Pages

  • Re: Indexing Question
    ... "Darwin Weyh" wrote in message ... > degrading. ... be more efficient ways to open it (e.g. client-side cursor vs server-side, ... forward-only vs dynamic cursor, etc) but it depends on what you are doing ...
    (microsoft.public.vb.database)
  • Re: Indexing Question
    ... "Darwin Weyh" wrote in message ... > degrading. ... be more efficient ways to open it (e.g. client-side cursor vs server-side, ... forward-only vs dynamic cursor, etc) but it depends on what you are doing ...
    (microsoft.public.data.ado)