?Equivalent of SEEK

[benasumwa]

Hi Friends,

I am converting my legacy systems to client/server architecture. I was used to foxpro's SEEK to find
a particular record in a table, now I am stuck at
that. Which method is used to find a record?

I am using SPT in VFP6.

Thanks in advance

Benson

---------------------------
Benson O. A.
Infinity Link Limited

 

[zedlands]

If I have understood correctly, you are looking for alternative ways to find a record in a table using VFP.

In this case, instead of seek, try LOCATE (for serial search), or use an SQL statement with a WHERE clause for the desired record's record number ( RECNO() )

eg.

SELECT FROM myTable * WHERE RECNO() = recNo_to_find

Zed Lands

 

[benasumwa]

Hi zed,

Actually, I am finding out whether a certain stock code
exists in the inventory table (SQL Server 2000).

On a VFP table I could just:

if SEEK(m.stock_code) then
blah...
endif

I now can't use SEEK on SQL Server 2000.

What's the alternative?

Benson

---------------------------
Benson O. A.
Infinity Link Limited

 

[zedlands]

try this

select myTable
SELECT * FROM myTable WHERE m.stock_code = myStockCode

or

select myTable
LOCATE FOR m.stock_code = myStockCode
myForm.Refresh

Zed Lands

 

[zedlands]

...correction

LOCAL myStockCode
myStockCode = set_stock_code
select myTable
LOCATE FOR myTable.stock_code = myStockCode

Zed

 

[benasumwa]

Zed

Thanks Zed, I was just wondering whether
LOCATE will not cause perfomance issues
on large tables.

Is SELECT * on large tables ok?
(Or is this the cost of getting to client/server?)

Just thoughts.

Benson

---------------------------
Benson O. A.
Infinity Link Limited

 

[zedlands]

Dear Benson,

In general, its good practice to use SQL statements instead of LOCATE or SEEK, especially in client/server applications.

LOCATE and SEEK search through the table row by row (serial search) which can be slow if the computer is not powerful enough or the network is overloaded. So far, I am working with SQL statements in tables containing more than 16.000 records distributed via lan and it works fine.

You can make use of Views (from the VFP Project Manager), to build your SQL statement and parametrise it.

Regards

Zed Lands

 

[jesylv]

While using SQL statements make better sense if you think migrating your application to a Client/server architecture, it does not provide speed gains to a classic Foxpro application using the native Database container and DBF Files.

In the Client-Server architecture, VFP sends an SQL command to an application residing on a server (MSSql, Mysql, msql, Oracle, Postgres, etc). This SQL command is executed by the server and only the matching records are sent back to the client machine reducing the stress on the network.

In a Classic VFP Application, processing takes place on the client machine. All records have to be looked at by the application running on a client station. Therefore your table is sent over the network to the client station and processing is done locally. This is true for both LOCATE and SQL SELECT Statements.

I did benchmarks last september comparing access speed on a large DBF file (200 000 records) over a 100 meg Network and found that LOCATE was , on average, 5 times faster than SQL SELECT Statements. You should however keep in mind that you will want to use Rushmore Optimization. This means that you'll need to define an index for fields you are querying.

Hope this is usefull.

Jean

http://www.sigp.ca

 

[jesylv]

Right, I just saw the SPT in the original message. Need new glasses...

Jean

http://www.sigp.ca

 

[benasumwa]

Thanks All

He cannot use SEEK and he cannot use LOCATE except on the returned result. In that case he cannot use SEEK until he adds indexes. He can use LOCATE on the returned result, even without indexes, which may be faster than taking the time to index first.

I have learned the lesson.

Thanks YearWood!!

This means that you'll need to define an index for fields you are querying.

Does this mean every field used in the WHERE clause
should have an index defined?

Thanks




---------------------------
Benson O. A.
Infinity Link Limited

 

[jesylv]

Hi Benson,

You are using an MSSQL server in the backend. My suggestions do not apply for they are, as Mike pointed out, relevant only if you use the native VFP backend (DBC-DBF).

Jean

http://www.sigp.ca

 

[jesylv]

Hi Mike,

I believe my comments where refering to SEEK, LOCATE AND SQL SELECT commands and not to the browse Command.

In a traditionnal VFP Application, all the data processing is done on the local machine.
Therefore data has to be sent via the network to the workstation. Rushmore optimization greatly reduces the amount of data that an application needs to read provided that queried fields have appropriate indexes. Optimized queries therefore reduces the amount of data sent on the wire. That was the main topic of my original post. Point final as we say in french.

Jean

http://www.sigp.ca

 

[jesylv]

Hi Mike,

Put a packet sniffer on your machine and do an unoptimzed SQL Select.




Jean

http://www.sigp.ca

 

[jesylv]

Hi Mike,

Ouf. I am glad we agree at last. And I will be more carefull in the way I put things forward in the future. Keep up the good work in this forum....

Jean

http://www.sigp.ca