Seeking with long strings

[Bryan - Gendev]

I have 2 tables containing fields with paths within them including the file-name.

I need to find if a file in a folder ( fullpath) is in the smaller list of fullpaths.

I was previously given a 'seek' routine with some testing of success and if not found try another method. The 240chr limit is not exceeded - typically a path would be

P:\_Test_Projects\Workshops\exhibits\002799 Bareholl -Leonard (NZ) =Marcelda Selwyn 1901.png

which I think is 92chrs

It only works sometimes - some matches are missed.

My users aren't happy.

I'm wondering if a lookup in an array would be more effective.

Any comments?

Thanks

GenDev

 

[GriffMG]

Actually, that's not true... the data (file names etc) actually is local - it is in temporary files on the local disk by the time the comparisions are done.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

It would be nice if you all were giving code and not just talk. Griff, I'm sure SQL can be faster than a scan loop with seeks. You just need to do the right indexing of the start of the path and mimic you seek near/do while in having a where clause using the index on LEFT(path,240) or SYS(2007) checksum and a seconary clause nonoptimized to compare the full path. It's a partial optimization like now, but in one single SQL instead of a loop.

* needed once 
Use yourtable1 exclusive
INDEX on Left(pathfield,240) tag leftpath

* SQL query
Select t2.* from yourtable2 t2 inner join yourtable1 t1
On t2.pathfield = Left(t1.pathfield,240)
Where t2.pathfield == t1.pathfield

This would find paths in yourtable2 already in yourtable1 due to the == in the where, but would at least partially optimize the matching on the first 240 chars via the leftpath index of yourtable1, which will be quite near to a full optimization, as few paths will be longer and so the matches are already near the final result, only few records will further be filtered by the where.

Prerequisites: pathfield must be equal length char fields or both fields are memo. Paths are in the same case. Otherwise you need to use uppper() or lower(), which will take longer again.

DON'T use a collation sequence though, that will not make this index rushmore friendly.

Bye, Olaf.

 

[GriffMG]

Hi Olaf

I think that is exactly what I am doing in code... I didn't try your two stage approach in SQL when I was testing last night, if I get time I might.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.