Mike Yearwood
Toronto, Ontario, Canada
General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
>Hi Todd
>
>I just tried the following...
>
>index on ttoc(ctr_contractdate,1) tag test
>set order to 0 && although this should not matter to SQL!!!
>
>select * from Contracts where ttoc(ctr_ContractDate,1) between ttoc(datetime(1999,1,1),1) and ttoc(datetime(),1) into cursor test
>
>select * from Contracts where between(ttoc(ctr_ContractDate,1),ttoc(datetime(1999,1,1),1),ttoc(datetime(),1)) into cursor test
>
>Now, since I don't have a deleted() tag on my table, I also SET DELETED OFF. I also SYS(3054,1) and both commands show full optimization. However, SQL's way of doing between is the first command. If you're not using these indexes for something else, I'd add an index tag on the field without the TTOC() and modify your SQL accordingly. I believe you'll get better performance overall if you can do something without extra function calls.
>
>Hope that helps!
Mike,
Thanks for the confirmation. Through much work and rebuilding and testing, I found the problem:
In the index expression, I had "ttoc(histcall.tCreated,1)" which created two problems.
1. The index would not be used for optimization; and
2. When validate database was run, it would pop up an error saying "Alias not found" for that particular table but would then say that the container was valid.
Thanks again,
Todd
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only