Slow open remote view

[dickylam]

Anybody can help me please!

I am using VFP 6.0 service 5 installed and going to open a remote review from SQL 7.0. But the speed is extremely slow, it almost use 1 min to finish one remote view, if I include a word of "NODATA" when apply USE command, very very litte (may be 5 seconds) time save only, still require 50 secs to 1 min to open only 1 file.

Do you have any ideas ?

Thanks a lot.

 

[EdLeafe]

Sounds like a network issue to me, like not being able to connect to the SQL Server box. Also, have the SQL DBA check the processes running on the SQL Server machine. I've seen rogue processes eat up the CPU cycles, causing slow access. Killing those processes made a huge difference in speed.

-- Ed Leafe

 

[foxdev]

How many records in the table(s) to which the remote view pulls data? Is there a WHERE condition? Is there a join?

The fact that USE MyView NODATA takes 5 seconds is a bit odd. Does using other views with NODATA take a similar amount of time? -----------------
Robert Bradley
use coupon code "TEKTIPS" for 15% off at:

http://www.lowcostprints.com

http://www.foxdev.com

 

[dickylam]

Edleafe,

My network is building on 10Mbps, Server is on 1 machine running at 800MHz, workstation is also at 800MHz.

SQL 7.0 service pack 3 was installed.
Visual Foxpro Service Pack 5 installed.

I don't understand how to check SQL DBA is running on SQL server maching, and how to kill this processer

Thanks.

 

[EdLeafe]

Sorry, "DBA" means "database administrator". This is the person who knows the guts of SQL Server. If this is your own system, you're the DBA. ;-)

If that's the case, open up the Windows Task Manager, and switch to the Processes tab. Click on the "CPU" header to list them in order of CPU usage. If you see something that is taking a large percentage, and you feel safe killing that process, kill it and see if the response improves. Of course, if this is a production system, you shouldn't kill a process unless you're sure you know what you're doing.

-- Ed Leafe

 

[dickylam]

Edleafe

Again,

according to your info, I saw only 1 process called "System Idle Process" which use CPU 99, sometimes 95, All other are taking 1 or even 0. I don't think this process related to SQL. Anyway I try to end such process, but as I thought, the system could you allow me to terminate.

Do you any other ideas,

Thanks again.

 

[EdLeafe]

Did you check the processes on the machine running SQL Server? That's the one that matters.

The only other thing I can suggest is to create a new ODBC DSN to the SQL Server, and try using that DSN in your remote view's connection. If that helps, then there was something wrong with your DSN. If that fails, then the only other thing I could suggest is to try running the query in Query Analyzer instead of going through Fox. If that's slow, then there's a database problem with SQL Server.

-- Ed Leafe

 

[dickylam]

Edleafe,

Again, I may think the problem is caused by SQL server.
I am using SQL 7.0 SP3, VFP SP5.

I am only create a simple database in SQL 7.0 , which contains around 20 tables, even no indexes have been built.

One of the tables I added about 2,500 testing records via remote view in VFP. The problem is - slow. Although I tried to open table with NODATA keyboard, by SQLEXEC, it is still slow. I checked the Microsoft support web, they realize this was a bug (Article# Q216318), and fixed on VFP SP3. I am using VFP SP5, same problem, no speed.

Do you know, if using Visual Basic, through ADO to open and handle records in SQL Tables will make any improvement ?

Thanks.

 

[EdLeafe]

I looked at that article, and it doesn't have anything to do with your problem. The article deals with inserting large numbers of records containing Text fields into a remote view.

Did you try running the query directly in Query Analyzer? You need to determine if it is the query itself, rather than somthing specific to VFP, that is responsible for the slowdown. I don't see why VB would be any faster here; my experience is that it is much slower.

-- Ed Leafe

 

[DEDMOD]

When you say you 'added 2500 test records' do you mean you only have 2500 records in the table or that you add 2500 more records to an existing table with possibly many more records? If the latter, then the problem can be simply either that you need an index set on whatever you're selecting on or that it takes a very long time for the entire table to be downloaded to your work station. This last problem can be alieviated by going into the Misc tab of the view designer and picking something other than 'all' for what's to be downloaded. Of course you'll generally want all the records eventually, so you'd need to do new queries as needed. And that probably brings you back to having an appropriate index.

Dave Dardinger

 

[dickylam]

Dedmod,

I am working on first case, not second cases.In the first case, I am trying to add records in 2 ways. The first way is to open a remote view with the command "USE REMOTE_VIEW" and the second way is to open with "USE REMOTE_VIEW NODATA", as I said before, the first way took me almost 57 secs and the second took me almost 52 seconds on opening.

Adding these testing records is just by the following simple command:

For I = 1 to 2500
select 1
use remote_view_file
append blank
replace remote_view_file_field1 with 'test1'
replace remot...
...
next I

It tooks around 1 sec on 1 records.

I am worrying, in fact, it is the truth now, after adding records inside. When I open, it takes around 2 mins on first way and 1-1/2 on second way.

Anyhow, I am really not satisifying the speed on open any table through remote_view to SQL 7.0 tables. I am only working 1 file, it takes around 1 min to open, if continue to open more complicated modules which requires 5-10 tables to be open, then the user should first go to take lunch and back to continue because he need to wait....

Thanks everybody.

 

[DEDMOD]

Why do you have your 'select' and 'use' inside your loop? I think each time you start back through the loop you're having to download the entire file again. In essence you're downloading (2500)^2/2 records. Plus all the overhead of going in and out of the view 2500 times. Just go in once, add all the records and then get out.

I certainly know that I've added tens of thousands of records to SQL 7 files from VFP via ODBC in just a few seconds. And those were much more complicated than your sample.

Dave Dardinger

 

[dickylam]

Dedmod,

Yes, I made some changes, the code shoud change as :-

select 1
use remote_view_file nodata
For I = 1 to 2500
select 1
append blank
replace remote_view_file_field1 with 'test1'
replace remot...
...
next I

Also, Dedmod, can you give me some tips how you are coding as you said in few seconds about adding tens of thousand records.

Thanks again.

 

[DEDMOD]

Did you get better speed by changing things around?

I'm not sure that my code is anything to write home about. I was just trying to indicate that under normal circumstances adding a bunch of records is a pretty fast process. Unfortunately if you really want to see my code I'll have to log into another computer and copy the code. I just looked and it doesn't look like I copied it to this one.

But I do have some partial programs. The heart of the process was :

INSERT INTO vschedule(Schedule_ref, comp_ref, Subject, name1_ref,;
    Schedtype_ref, completed, Descript, Notes, Printlabel, User_ref, Private,;
    Resource, Spons_ref, Pledge_ref);
VALUES (SYS(2015), "FH", .NULL., vname,; 
   vtype, .t., "Consultant", vnote, .f., ".NULL.", .f.,;
   vResource, ".NULL.", ".NULL.")

which is pretty simple. The point was that I had to switch back and forth from the vschedule remote view into SQL 7.0 and a local VFP table I had the data like vname, vtype, etc. stored in. And despite all that it didn't take all that long to run.

Dave Dardinger

 

[dickylam]

Dedmod,

Thanks for your help. As you know, my problem is the speed about the open the remote_view, as well as adding records. I think by using your method like INSERT may speed up the records addition.

Accidently, today I found the key factor how to speed up the opening remote view. It is really simple, just stop the ODBC tracing in ODBC sources under control panel.

I hope my discover can also give all other professional an small idea.

Thanks Dedmod

 

[DEDMOD]

Can't say I've ever looked at the logging possibilities. Does look like it could be of some use when you're debugging. But since I've never used it, I've obviously never turned it on. Since you had to turn it off, it appears that you have been using it, or at least someone else has been.

As for INSERT vs APPEND BLANK, I'm not sure what the true experts think. It may be just one of the many cases where there's more than one way to do things in VFP or there may be some advantages to one over the other...

Reading in the Hacker's Guide just now gave me some insight. It says that INSERT is better in general because it only updates the table once whereas Appending followed by putting in values updates it twice. But if you want the user to imput the data, you need to APPEND because INSERT will trigger field-level rules.

Dave Dardinger