Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
This is a surprise
Message
From
01/03/2000 18:14:06
 
 
To
01/03/2000 17:24:40
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Miscellaneous
Thread ID:
00338270
Message ID:
00340241
Views:
28
Good! At least now we know that the deleted thing still applies with a UNION.

Now your original slow command was:

SELECT A.* FROM Address A, Cons C ;
>> WHERE C.CCust = "GREATLA2" ;
>> AND (C.Addrnum = A.Addrnum OR C.PAddrnum = A.Addrnum)
>>

You say that C.Addrnum and C.PAddrnum are not indexed in CONS. That being the case, can you also try it reversing them in both sides of the OR... trying
... AND (A.Addrnum = C.Addrnum OR A.Addrnum = C.PAddrnum) instead?


Cheers,

Jim N

>Here are the figures Jim...
>SET DELETED ON
>Union 0.356 secs
>Single 22.3 secs
>
>SET DELETED OFF
>Union 0.01 secs
>Single 20.9 secs
>
>This is in line with what you are saying, but the join even more spectacularly outperforms the "OR" query with SET DELETED OFF!!
>
>>Hi David,
>>
>>That was the point of the article (or at least one of them) - when you have no deleted records you have the worst case (performance-wise) when DELETED is ON *and* you have a key on Deleted().
>>
>>You can check this out quickly by simply running the SQL with DELETED set OFF to see if there is a difference.
>>
>>Prior discussions on this led the believers to conclude that one should *test* using a key on Deleted() (assuming DELETED is ON) to verify that the query is otherwise fully optimized but them remove the key for production purposes.
>>
>>Cheers,
>>
>>Jim N
>>
>>
>>>G'day Jim,
>>>I haven't looked at the article again, you could be right but I'd be surprised. My tables had no deleted records.
>>>
>>SNIP
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform