How about splitting the master table into multiple tables?
If your scan is taking that long, you don't have it optimized.
You can't do a scan on SQL Server, so you may have to rearchitect your application. Everything in SQL Server is done with SQL statements.
Last time I check SQL Server costs, it was $5,000 per processor. So, with 5 users, you're looking at $1000/user and that doesn't include the server.
>Hi all,
> 1. Should I move to SQL at all (Y/N)? Advantages, Disadvantages.
> 2. Basic procedures?
>
>I have an database written in FoxPro. I'm thinking about moving only the Master table to SQL. The Master table:
> 5 million records, grows by 40K+ records a week.
> File size is 1 GB to, will grow rapidly due to additional project to be added.
> Maximum Foxpro file size is 2GB.
> Record size is 187. Will grow to over 230 because of new project.
> Currently takes ~8 minutes to do a scan. Across Network, takes 20 minutes.
> No more than 5 users, no simultaneous users.
> Only manipulation of table is appending new data and reading to create subsets.
>
> I'm thinking about the SQL solution as opposed to Normalizing the table and making it multiple related table. I would then have to rewrite a lot of code.
>
> Since I have never worked with SQL, I'm not sure what my results would be. Can anyone help?
>
>TIA
>Pete
Craig Berntson
MCSD, Microsoft .Net MVP, Grape City Community Influencer