0004777: Performance of links can be optimized

Viewing Issue Simple Details [ Jump to Notes ]

[ View Advanced ] [ Issue History ] [ Print ]

Category

Severity

Reproducibility

Date Submitted

Last Update

[VCOMPONENT-VKERNEL] 0004777

Performance

tweak

have not tried

2010-03-04 21:31

2010-04-12 10:38

Reporter

Kem Tekinay

View Status

public

Assigned To

Priority

normal

Resolution

open

Status

new

Product Version

4.5.1

Summary

0004777: Performance of links can be optimized

Description

Consider this query: select Zip_Codes.Zip_Code, Zip_Lookup.State from Zip_Codes, Zip_Lookup where Zip_Codes.Zip_Code = Zip_Lookup.Zip_Code Where Zip_Codes has 5 million+ records, and Zip_Lookup has about 42,000 records, this query takes 0.45 seconds. (This takes 5.5 seconds in MySQL, btw.) If I use a BinaryLink or ObjectPtr and write the query like this: select Zip_Code, L_Zip->State From Zip_Codes This query takes 0.117 seconds. So far, so good. If I change from NoLock to ReadOnly, the first query takes 1.732 seconds, but the second query takes 6.73 seconds if L_Zip is an ObjectPtr link, and 4 seconds if it's a Binary link. If L_Zip is a Foreign Key link, it takes 33 seconds, even if the Foreign Key is Zip_Code. It seems that the queries using the links should be faster, not slower, than using the traditional WHERE fld=fld syntax, and the ForeignKey link should be at least as fast.

Additional Information

Issue History
Date Modified	Username	Field	Change
2010-03-04 21:31	Kem Tekinay	New Issue
2010-04-12 10:38	Claudius Sailer	Issue Monitored: Claudius Sailer

Relationships

Notes
There are no notes attached to this issue.