MS SQL Server or VFP Database???  
Author Message
hoabinh





PostPosted: Visual FoxPro General, MS SQL Server or VFP Database??? Top

Hi every one, hi MVFPs,

I am working with SQL Server database, but I wonder that I've got some big problem with SQL table that has more than 500,000 records. If there are about 50 connections access to that table with select, update,,, task, the SQL will become very slowly and return error message as: ....query out of time,,, or dead lock....

If I work with VFP database, there are about more 100 connections with command "use" the table, and so often tasks to seek, select, update.... but It still work well even if not too fast. And I have a VFP table with more than 1,5 billion records working hard like that.

So now I don't know if I will working with VFP database or getting SQL instead because of the more security of using SQL server

And why SQL server only work well with the record less than 1 billion Some one tell me to try with Oracle, but I like VFP most because I've worked with it for a long time.

Would U like to give me something clearer about it

Thanks all of you very much.



Visual FoxPro1  
 
 
Alex Feldstein





PostPosted: Visual FoxPro General, MS SQL Server or VFP Database??? Top

It is hard to answer the question with few details.

You mean you have 1.5 Million (not Billion) records in a DBF Cannot be 1.5 Billion as that is over the VFP 9.0 limit of 1Billion records or 2GB per file.

See: VFP System Capacities:

http://msdn.microsoft.com/library/default.asp url=/library/en-us/dv_foxhelp9/html/2cb5be90-2563-455d-bd3c-8a243aff5211.asp frame=true

 

Both VFP and SQL Server can be very fast, if properly optimized and properly maintained. In SQL Server I would say that for that size database you must have a dedicated DBA. It's the DBA's job to maintain indices, optimization, stored procedures, etc. Talk to the DBA about this.

It also depends on what kind of hardware, server, routers, LAN, you have. A slow system that has trouble to keep up with net and query traffic will have an impact. A poorly optimized database will also slow things down.

What kind of SQL Server licensing do you have What version (SQL Server 6.5, 7.0, 2000, 2005) SQL Server optimization questions, would probably be better asked in a SQL Server forum. As for VFP, similar questions arise, LAN , WAN , hardware , OS , VFP version Optimized Using Rushmore Direct or remote access LAN (tcp/ip) or web-services-HTTP, etc.

etc.

You need to give more details.


 
 
CetinBasoz





PostPosted: Visual FoxPro General, MS SQL Server or VFP Database??? Top

I agree with Alex.

Though I wish VFP could be comparable to SQL server it's not (may be it was in history). The case you describe sounds pure inefficient design (I don't mean to criticize you). Query time outs and dead locks might occur due to different scenarios (one common is 2 tables cross referencing each other over 2 connections) but SQL server at least have an implicit mechanism to recover and gives clues to DBA.

500K records and 50 connections is not even a concern for SQL server (ie: SQL2000 connection limit is 32767 and database size is expressed in TerraBytes - 32 TB for data file * 32767 data files * 32767 databases per SQL server instance .... limit summing up to 1M TerraBytes - I might be wrong about exact numbers but that's what I remember). SQL2005 added many new cool features over 2000 and now it is faster,smarter.


 
 
hoabinh





PostPosted: Visual FoxPro General, MS SQL Server or VFP Database??? Top

Thanks for your reply, I'm sorry for my mistaken, What I mean is 1.5 million records of a dbf, not billion.

I have work with SQL server 2000 full licences, the LAN model and the servers are consider as the same between SQLservers and file servers contain dbf file. Ok, you let me know about the capacity of SQL, it's really "teribble" than I can think. (I must backup so often my table in SQL to prevent the record not come over 500,000 to make it work faster). But this is the first time I know about DBA, would you please to give me some link to work out with DBA

And If I use VFP to work well with SQL server database as with VB6 (sql pass through, view, oledb) The convenience of programing with VFP and the great capacity of SQL server ( ) could be the best combination for working with data model

Thanks a lot

Best regards


 
 
Alex Feldstein





PostPosted: Visual FoxPro General, MS SQL Server or VFP Database??? Top

A DBA is a Data Base Administrator. A whole different job than a Software Developer. It has a different set of skills and a different Certification.

Sometimes you can do it all; sometimes the jobs should not be mixed. It is as if you wanted to build a garden shed. You could probably be the Architect, the Civil Engineer, the Consultant that gets the City permits, and the bricklayer, carpenter  and electrician, and do all these jobs yourself. On the other hand, if you wanted to build a 10-story building, you would probably not be qualified to fill all of these jobs.

In software, you can surely be the architect, software developer and DBA all in one. But when the job gets over certain size, as you stated, it is probably more indicated to have a dedicated, professional DBA maintaining the back-end. If you still want to do it, then you should be trained as such and hopefully certified.

Start by understanding what a Data Base Administrator's job is, and hpw to optimize, maintain and backup a working, big size database. SQL Server is very fast, very secure and very reliable, when handled properly. It can be slow and unsecure when handled poorly. The difference is a good DBA. Even when the DBA and Software Developer are the same person. there is no excuse for not knowing a DBA's job and doing it professionally.

Why "must you backup SQL Server frequently to prevcent it from going over 500,000 records". That does not make sense. I work with a SQL Server table of over 40 million records, through a VFP program, adding about 1.2 million records a month. Not one problem.

 

>And If I use VFP to work well with SQL server database as with VB6 (sql pass through, view, oledb) The convenience of programing with VFP and the great capacity of SQL server ( ) could be the best combination for working with data model

Yes. VFP is an excellent platform to do a front-end and a middle-tier for accessing data from SQL Server. In fact it is as fast as VB6 and more powerful as you can easily manage the data set locally once queried from the back-end.


 
 
hoabinh





PostPosted: Visual FoxPro General, MS SQL Server or VFP Database??? Top

Yeah, thanks for a very useful and immediately support. You also let me know that I have got something about DBA yet. But I will study carefully and deeply about DBA, I think my problem may be crossing references when coding with the table. May be the way I use to query data with VFP can not work the same with SQL data.

I will come to SQL forum and hope find out something to tell U that I will solve my 500K problem. And I do agree with your conclusion: "Yes. VFP is an excellent platform to do a front-end and a middle-tier for accessing data from SQL Server. In fact it is as fast as VB6 and more powerful as you can easily manage the data set locally once queried from the back-end"

Hope to see U soon

Many thanks.


 
 
Garrett Fitzgerald





PostPosted: Visual FoxPro General, MS SQL Server or VFP Database??? Top

Something that Alex didn't seem to touch on directly is that you do _not_ want to bring 500000 records across the wire from SQL Server, unless you actually have a reason to. Bring only the data you need at any given time, and upload as much processing as you can to the server.

 
 
hoabinh





PostPosted: Visual FoxPro General, MS SQL Server or VFP Database??? Top

Would you like to tell me: between using sqloledb to connect to SQL from VFP platform and using ODBC connection string, what i faster and if VFP could make SQL slow down

With ODBC, I must re-config ODBC on each workstation that run my application, I don't have to do that with oledb. I have a program on VFP to update the SQL table with sql passthrough command: update. It's update about 200 records of total 500K at each time. It's only simple update the sql table from a prepaired dbf, I set the inkey=0.3 for the each record updated. When I run that program, the other tasks using that SQL table with select command (with VB6) became slow down until VFP task finished. If I do something wrong with VFP and SQL server

With sql passthrough in VFP, I've just used a simple command select from SQL server, and the result return in the memory as cursor, and just 1 more command copy to a certain dbf, I have what I need. But if I use recordset object, after run sql command, I must take a loop do while,,,,,enddo to get each record from my record set to a dbf I need. So I will take time using my PC memory longer than using sql pass through

Could U tellme what is the best corrected method to process SQL server from VFP that Microsoft advised the users to do And if the select processing from VFP could effect the operating of SQL server (I'm carefully disconnect the process after each sql command used, and add the new connection for the next)

Thanks a lot


 
 
CetinBasoz





PostPosted: Visual FoxPro General, MS SQL Server or VFP Database??? Top

If you use a cursoradapter you can use ADODb.recordset as a source. However with SQL server ODBC driver works just fine and you don't need to use OleDb driver instead.

Also if you use SQLconnectstring() then you don't need to configure each workstation.


 
 
hoabinh





PostPosted: Visual FoxPro General, MS SQL Server or VFP Database??? Top

When I use ODBC: "Driver={SQL Server};Server=Aron1;Database=pubs;Uid=myUsername;Pwd=myPassword;"

If I choose on SQL server: authenticate by windows, I don't have to configure my workstation DNS ODBC. But If I choose: authenticate by SQL server: username & password, I have to configure ODBC on each PC, my application only run when I run Test datasource successfull.

Could you help me to get this problem


 
 
CetinBasoz





PostPosted: Visual FoxPro General, MS SQL Server or VFP Database??? Top

IMHO authent,ication by windows should be preferred one.

But still I don't understand why you have to configure on each PC. You can ask to user their server/uid/pwd, if need be, and write to somewhere encrypted.


 
 
hoabinh





PostPosted: Visual FoxPro General, MS SQL Server or VFP Database??? Top

Hi,

When I use the linked server command in the SQL2000: Query analyzer:

OPENROWSET('VFPOLEDB', 'F:\DATA\CM1.dbf'; ''; '', 'SELECT * FROM CM1 WHERE acount="123456" ')

the querry is running successfull

But when I creat a SQL job with step detail is that command too, the job return fail: Executed as user: NT AUTHORITY\SYSTEM. Could not initialize data source object of OLE DB provider 'VFPOLEDB'. [SQLSTATE 42000] (Error 7303)   [SQLSTATE 01000] (Error 7312)  OLE DB error trace [OLE/DB Provider 'VFPOLEDB' IDBInitialize::Initialize returned 0x80040e21]. [SQLSTATE 01000] (Error 7300).  The step failed.

"F:" is the network map drive from other server with read only attr

How could I use a job to do the same thing as running by Query analyzer with "F:" 

Nothing trouble with my local drive with that job

Thanks a lot

 


 
 
CetinBasoz





PostPosted: Visual FoxPro General, MS SQL Server or VFP Database??? Top

Sorry, I don't use SQL2000 DTS. It looks like it's having a trust problem when called from DTS package.