Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

SQL and index

5 posts in FoxPro Last posting was on 2010-01-01 17:20:33.0Z
Ron Gu Posted on 2009-12-30 23:54:18.0Z
From: "Ron Gu" <ron@edn.ca>
Newsgroups: Advantage.FoxPro
Subject: SQL and index
Date: Wed, 30 Dec 2009 18:54:18 -0500
Lines: 17
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: 69.70.72.14
Message-ID: <4b3be69e@solutions.advantagedatabase.com>
X-Trace: 30 Dec 2009 16:47:42 -0700, 69.70.72.14
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!69.70.72.14
Xref: solutions.advantagedatabase.com Advantage.FoxPro:332
Article PK: 1109686

Hi, I have a CDX table (log.dbf) with associated index file (log.cdx). My
only index (called DateTime) is on 2 fields (dtos(Date)+Time). When I run
SQL queries (with ADS in remote mode) such as

select * from log where Date >= '2009-12-01'

it takes up to 28 seconds.

Then, if I create an index on Date (called Date) and run the same query, it
takes less than a second. Is there a way to force SQL to use the DateTime
index in the query above?

TIA,

Ron


Joachim Duerr (ADS) Posted on 2009-12-31 13:13:27.0Z
From: "Joachim Duerr (ADS)" <jojo.duerr@gmx.de>
Subject: Re: SQL and index
Newsgroups: Advantage.FoxPro
References: <4b3be69e@solutions.advantagedatabase.com>
Date: Thu, 31 Dec 2009 14:13:27 +0100
User-Agent: XanaNews/1.19.1.194
X-Face: u2p+</,mb|Ah!x!/qxX5q0t:O~.<1&JzwNHYhSqcviY{~&|iDc"U.Je1A.ZeHR`d;;y#R
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 92.73.192.37
Message-ID: <4b3ca305@solutions.advantagedatabase.com>
X-Trace: 31 Dec 2009 06:11:33 -0700, 92.73.192.37
Lines: 24
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!92.73.192.37
Xref: solutions.advantagedatabase.com Advantage.FoxPro:333
Article PK: 1109687


Ron Gu wrote:

>Hi, I have a CDX table (log.dbf) with associated index file
>(log.cdx). My only index (called DateTime) is on 2 fields
>(dtos(Date)+Time). When I run SQL queries (with ADS in remote mode)
>such as
>
>select * from log where Date >= '2009-12-01'
>
>it takes up to 28 seconds.
>
>Then, if I create an index on Date (called Date) and run the same
>query, it takes less than a second. Is there a way to force SQL to
>use the DateTime index in the query above?

the index cannot be used since you use a function inside..maybe it
helps to query for a string conversion:

... where cast(date as sql_char)>='2009-12-01'

--
Joachim Duerr
Advantage Presales
check out my new ADS book on http://www.jd-engineering.de/adsbuch


Maico Friedrich Posted on 2009-12-31 13:39:56.0Z
Date: Thu, 31 Dec 2009 14:39:56 +0100
From: Maico Friedrich <m.friedrich@accurata.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; de; rv:1.9.1.5) Gecko/20091204 Thunderbird/3.0
MIME-Version: 1.0
Newsgroups: Advantage.FoxPro
Subject: Re: SQL and index
References: <4b3be69e@solutions.advantagedatabase.com>
In-Reply-To: <4b3be69e@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: 93.208.174.10
Message-ID: <4b3ca94f@solutions.advantagedatabase.com>
X-Trace: 31 Dec 2009 06:38:23 -0700, 93.208.174.10
Lines: 34
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!93.208.174.10
Xref: solutions.advantagedatabase.com Advantage.FoxPro:334
Article PK: 1109688

Hi Ron.

> Hi, I have a CDX table (log.dbf) with associated index file (log.cdx). My
> only index (called DateTime) is on 2 fields (dtos(Date)+Time). When I run
> SQL queries (with ADS in remote mode) such as
>
> select * from log where Date>= '2009-12-01'
>
> it takes up to 28 seconds.

I guess it´s more slow cause the index is a concated
expression and cannot be optimized!

Try using

date;time

instead. No need to use DTOS!

If you are using ";" the ADS is able to select parts of the
index, even there is more than one field or expression under
one index!

Do not use "+" operator!

Happy new year!

--
Maico M. Friedrich - IT System Analyst
Visit me here : http://www.datanaut.de
DataNAUT - The only complete Database Workbench for the ADS
Server!


Joachim Duerr (ADS) Posted on 2010-01-01 13:32:47.0Z
From: "Joachim Duerr (ADS)" <jojo.duerr@gmx.de>
Subject: Re: SQL and index
Newsgroups: Advantage.FoxPro
References: <4b3be69e@solutions.advantagedatabase.com> <4b3ca94f@solutions.advantagedatabase.com>
Date: Fri, 1 Jan 2010 14:32:47 +0100
User-Agent: XanaNews/1.19.1.194
X-Face: u2p+</,mb|Ah!x!/qxX5q0t:O~.<1&JzwNHYhSqcviY{~&|iDc"U.Je1A.ZeHR`d;;y#R
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
NNTP-Posting-Host: 88.78.136.40
Message-ID: <4b3df90b@solutions.advantagedatabase.com>
X-Trace: 1 Jan 2010 06:30:51 -0700, 88.78.136.40
Lines: 18
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!88.78.136.40
Xref: solutions.advantagedatabase.com Advantage.FoxPro:335
Article PK: 1109689


Maico Friedrich wrote:

>
>date;time
>
>instead. No need to use DTOS!
>
>If you are using ";" the ADS is able to select parts of the index,
>even there is more than one field or expression under one index!
>
>Do not use "+" operator!

; is not possible with DBFs:(

--
Joachim Duerr
Advantage Presales
check out my new ADS book on http://www.jd-engineering.de/adsbuch


Maico Friedrich Posted on 2010-01-01 17:20:33.0Z
Date: Fri, 01 Jan 2010 18:20:33 +0100
From: Maico Friedrich <m.friedrich@accurata.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; de; rv:1.9.1.5) Gecko/20091204 Thunderbird/3.0
MIME-Version: 1.0
Newsgroups: Advantage.FoxPro
Subject: Re: SQL and index
References: <4b3be69e@solutions.advantagedatabase.com> <4b3ca94f@solutions.advantagedatabase.com> <4b3df90b@solutions.advantagedatabase.com>
In-Reply-To: <4b3df90b@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 93.208.175.176
Message-ID: <4b3e2e83@solutions.advantagedatabase.com>
X-Trace: 1 Jan 2010 10:18:59 -0700, 93.208.175.176
Lines: 13
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!93.208.175.176
Xref: solutions.advantagedatabase.com Advantage.FoxPro:336
Article PK: 1109690


>> If you are using ";" the ADS is able to select parts of the index,
>> even there is more than one field or expression under one index!
>>
>> Do not use "+" operator!
>
> ; is not possible with DBFs:(
>

ups - I forgot...

However, he can follow the other suggestions...

Juats make two indexes!