Michel,

From what you've told me VFP should be able to do the job. VFP does not bring across the entire file every time you make a request using an index and optimized query. This is simply WRONG and people who do not know better state this. If that were true then 1.5 gig of data (in your case) would be brought across the network with EACH query. This is not happening since the response time would be much slower than it already is.

Suggestions?

Determine if indexes are begin brought across the network. This can happen if queries and indexes are not formed properly. IOW, the query could still be optimized but the index comes across as part of the query. Some indexes can be quite large.

How much data is actually returned as part of the query? If it's a large amount, then even moving to SQL Server won't address your issues. This then becomes a network bandwidth issue.

How long does it take to process a single query? Try to find a quiet time in the network and take a benchmark time. Then monitor the time during the day to see how this time changes.

Do all of this before going to SQL Server since if these issues still exist then SQL Server won't help.

Hope this helps, Val Matison


Here's what you wrote to me:

We have people who work outside our offices. They send us electronic (e-mail) data requests about a client. Every day we receive between 100 and 300 requests. Our network is only 10 mbs and is already quite occupied by the employees which work in our building. All the requests received are treated (processed) on a “post” dedicated solely for this task. The mail processor is occupied between 10 and 15% at the time of dealing with these tasks, whereas the transfer network is constantly at 100%. In other words, work mail waits for the network. All the programmes are optimized, the indexes are good, the sql requests are optimized. For example, at the time of scanning we only receive the records which we need (seek and after scan while). We have also used the tool “coverage” to find places where programmes could take more time and optimized these parts. For each request received files (table dbf) are produced and compressed with a tool(?) then sent by mail to the one req uesting it.

Our tables are between 1 and 1.5 gig. Currently we are at the time of most requests, yesterday we had more than 500 requests and this morning the requests were not all taken care of. Someone in charge in our network told us that the network was overloaded because foxpro works with flat files and that we ought to turn to a real data server.

Is it possible that in turning to SQL server we would be capable of improving the “treatment” time.

I would like to add that we are part of a large company and there is a department which manages the network and the work mail. The one responsible for this department says that if the network is too overloaded it is because of the utilization of Foxpro and the dbf files which are flat files and generate a lot of traffic between the mail which does the “treatments” and the server. He claims that if we had a real data server this would not be the case.





>Je veux ajouter que nous faisons partie d'une grosse compagnie et il y a un département qui gère le réseau et les postes de travail. Le responsable de ce département dit que si le réseau est trop chargé c'est du au fais de l'utisation de Foxpro et des fichiers dbf qui sont des flat file et génère beaucoup de traffic entre le poste qui fais les traitements et le serveur. Il prétend que si on avait un vrai serveur de données ce ne serais pas le cas.
>
>Michel
>
>>Michel
>>
>>Please write in French if it's easier for you. I will reply in English however.
>>
>>From what you describe, it lok slike you are returning all the rows to the client side.
>>
>>Can you create a stored proc that returns only the data you wish? This would likely solve your issues.
>>
>>- Val
>>
>>
>>>I will try to explain but because I am frech I have some difficulty to find the right word.
>>>
>>>People who work for our company (outside of the office and doing auditing) ask for some data about client (they send us a email with a request). When we receive the request a task is launch on a computer doing only this task to extract data from the main tables (about 12 tables and some of them are 1 to 1.5 gig ).
>>>From those table we are extracting data using scan, select sql and other code to result table that are compress into a zip file and send to the person who have make the request by email. We can receive from 100 to 300 request on one day. During the process we are not locking table
>>>
>>>
>>>Michel
>>>
>>>>Michel,
>>>>
>>>>We have a lot experience in this regard so we'll try to help out. That said, you did not provide any information on which to base an intelligent reply.
>>>>
>>>>What are you doing with the data?
>>>>Can filter conditions apply?
>>>>Are you locking any data?
>>>>How large is the data?
>>>>How are you returning to the client station?
>>>>
>>>>- Val Matison
>>>>
>>>>>Hi
>>>>>
>>>>>We have some heavy task using table that are on a server on the basement. The network speed is 10 mb. These task is taking more than 12 hours. In these task we are extracting some data, scanning table.......
>>>>>
>>>>>Foxpro table are flat file and someone told me that in this case this is doing lot of traffic on the network. In fact the processor of the computer doing these task is used about 2 to 14 % and the graphic about the network traffic is about 80 to 100%. The same person told me that using sql server for data will really make a big difference for the traffic on the network and the time for these task.
>>>>>
>>>>>I wish the have the advice from people who move to sql server and if this is true.
>>>>>
>>>>>Michel