Access vs SQL Server 7

[VBXL]

I have created a program which imports text files. When i run a similar program in access it is a lot faster than running in sql server 7.

Why

 

[jjgraf]

Let make an assumtion. the access data is local to the computer where access is running which would make it faster.

Reason why is because you don't have the Network overhead.


Now what you should do is put 50,000 records on the SQL server and then in Access and stored the Access table some where on the network and see how fast it runs.


Access is fast with little bits of data but as you add lots of records it gets very slow plus it is not mean to be in multi-user enviroment

 

[tlbroadbent]

There are a lot of factors including the network overhead mentioned by jjgraf. You didn't mention how you are importing the text file - SQL Server offers several options depending on the version.

Bulk inserts using SQL Server are generally very fast. However, if the proper options are not set the import process may not use bulk inserts. You may also be dealing with overhead of growing transaction logs and maintaining indexes on the tables. Perhaps, you can provide some more details about the import process. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[VBXL]

The problem is when i import a text file, i want to attach numbers to the file i have just imported in some kind of join. i dont know how to do this quickly. What i am doing up to now is importing a text file then running a cursor though the imported file and updating the table which corresponds to the imported text file.

i.e

col 1 col 2 col3 col4(other table) col 5

is the away of doing the complete processes in a update in SQL 7 instead of using a cursor. i.e while importing the text file look in an other table and find the correct number to update the table with.

Cheers

 

[tlbroadbent]

You could import to a another table using a bulk insert process. Then insert the results of a JOIN query into the permanent table. That will be a much faster process.

Depending on the process, you may be able to do a lookup in DTS. See the following link.

http://www.swynk.com/friends/green/dtslookups.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[SreeSudha]

you may try the BCP utilty in the SQl Server which will inport the text file in very less time