Open Views with lots of data

[hjohnson]

I've got a View setup in a DBC to a SQL-Server Table that currently has about 500k records with a potential of having 100x that amount.
The real problem is that when the view is opened, it has to retrieve all the records before being able to do anything. 500k records takes about 2 minutes- when it gets to a 10 million records, it will take longer to open the data than to it will to make any of the changes/additions I need to do.
I'm alreading issueing a "Go Bottom" command which speeds up the process, but is there any additinal suggestions that anyone might have for me. Each block of approx 500k records represents 1 month of data and so I've considered creating Views on that represent each month or creating an "Empty" view temporarely when all I need to do is append new records.

As Always,Thanks for any suggestions.

HJ

 

[ramani]

HI

May be it sounds trivial..

Are you talking about the View opening in Data environment. You can reduce the time by using NODATAONLOAD property in DE.

Are you using filtered views? When there is no filter, the views generaly dont take that much time.

Or are you having any complex referntial link between tables to make that view.



ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[Nifrabar]

Hi,
Probably a suggestion might be to:

1- create parametrized view, so only one view might be required to serve you for every separate month

2- have the view in a dataenvironment and set property NoDataOnload to .F. That way you have fast control over a form.
Once you need to use the actual data the =requery('myView') command does the job.

-Bart

 

[jimoo]

HJ

This sounds like a remove view that is setup in a VFP database container.

It also appears that this view has a lot of data. Therefore, there is a performance hit when you open the view.

Assuming this is correct.

1. You may wish to have a view created on the SQL Server and then access that view just like you would a VFP Table. Let SQL Server do most of the work.

2. Execute a SQL Server Stored Procedure to bring the data down.

These methods should bring faster performance, but the bottom line is that if you are access that much data at one time there will be a performance hit. If possible, re-think the code and see if you can just bring down the few records you need -- "Assuming you only need a few and not all"


Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[hjohnson]

Thanks all,

Not having worked that much with remote data, so I may have approached this in the wrong direction in the first place. A little more explanation of what I'm trying to accomplish may be in order. Essentially, I'm creating a SQL Datawarehourse for a production VFP application. It's a stand-alone app that constantly querying production VFP data and updateing/appending as needed. I'm not really concerned about using the data in view, but rather using the view as the import mechanism. The problem is that now the view contains 500k+ records and when I issue an append command- it still has to load the entire record set before the program can continue- granted this is only once per view, but it can still be time consuming.

Can a DE be setup and maintained programatically? I'm not currently using one and therefore can't set the NODATAONLOAD properties. Views are constantly being added and updated which would make it a nightmare to have to update it manually each time it changes.


Hope that helps understand a little more of what I am doing
Thanks
HJ

 

[jimoo]

A couple ways to approach this, but first I want to make sure I understand completely.

1. The 500k+ records are on the SQL Server
2. New Updates will be in the VFP Tables.
3. You need to apply new updates to the SQL tables.

If this is correct, one way to do it that is not all that much work is to create a SQL Stored Procedure to handle the update. Spin thru the VFP records and call the update stored procedure each time using SQLEXEC() function.


Let SQL Sever handle the CRUD (Create, Read, Update, Delete) and use VFP to power the local data.



Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[finsys]

Sounds like you are going about this the wrong way. I've just finished a SQL/VFP rpoject which has huge amounts of data in and had similar problems until I adjusted my old Foxpro thinking to take into account remote data.

If you are adding a record to a SQL table, you don't need to pull back all the records on that table. Create a view that contains all the required fields from the table and put a filter on the primary key, such as 'TRANS.ID = ?gcCaseID'.

When you open the view initially, set gcCaseID to blank and you will get an empty recordset. You can then append a blank record, enter the data and then save this back to SQL.

When you pull data off the server, build your queries to reduce the number of records in the recordset to only those required. Also only pull back the required fields. Might sound obvious, but is easily overlooked! A collegue wrote a search screen that pulled data in from several tables. The search grid only showed about 10 fields, but he was pulling back about 30 fields in the query. Once I removed the non-essential fields, the query sped up 10 fold.

You really have to adjust your thinking to work with remote data over the old Fox tables. It's not easy at first, but once you do, you reap the benefits.

 

[hjohnson]

Thanks all for the suggestions. This is a great resource from which I often can't live without.

I have created a view which returns an empty set and then start appending the new records. I'm sure there are way to furth tweak the process for better performance but it's now updating SQL at an average of about 250-300 records per second which is 100x better than before.

Again thanks for all the help and if anyone has any suggestions to further increase the performance, they are welcome as well.

HJ