VB & SQL Server vs. VBA & Access

[amberH]

Hi all,
I created a database in Access, and populated the tables with data imported from text files. Then I found out I should use SQL Server, and VB...so I rewrote everything, and now have my data being pulled with VB 6 and populating a SQL server database.
My problem is this...the VB code is VERY slow. It takes ~10 min to process one text file, whereas the Access code took less than one minute.
Is there an obvious explanation for this?? Or should I post my code and maybe someone could offer some suggestions? I didn't really know what I was doing...I just figured out how to get it working, but there is definitely a chance I didn't do it the optimal way.
Thanks,
amber

 

[DangerPowers]

I'm assuming that you are reading in the text file in VB, then saving it row by row into SQL Server. Also that in Access you used an import wizard to get the file into a table.

If this is a one-time import, then you may want to try the bulk copy utility to import a text file into SQL Server--it's much faster.

If you import the file on a regular basis, try using a DTS package.

 

[amberH]

Hi,
Thanks for your reply.
I do almost identical actions in both the VBA to Access code, and the VB to SQL Server code. The text files are pulled in line by line, and populate 3 tables.
The files will be pulled probably 1 each hour or so....a maximum of 30 a day.
What is a DTS package??
Thanks,
amber

 

[DangerPowers]

DTS=Data Transformation Services.

As the name implies, it allows you to transform data from one source to another. Search the SQL Server Books on Line for DTS for more information.

 

[stoolpigeon]

You will want to look at your database. Things like indexes can make a huge difference in performance.

Then you need to look at your code. Are you using ADO or DAO?

Much of how you write your code will have a huge impact on performance.

Stored Procedures can really speed up your app. This is another thing to look at. You can run stored procedures that insert rows as methods directly off your connection if you are using ado- pretty clean and quick.

Then there is the whole hardware issue. What kind of machine is running sql server? And is this over a network? Network issues can really slow you down.

SQL Server is to Access as a real car is to a matchbox car. It can do a lot more and do it better but it takes much more knowledge and work on the part of the operator.

 

[jlschulte]

I would guess that you are having network issues (like I was having trying to submit this post). If you are updating the SQL server alot, you are sending a lot of trafiic and waiting for replys, while the Access work is done mostly on your computer.

Try building your recordset on the computer and then doing a large update at the end. While you will be sending a larger chunk of data, you will be spending less time waiting for network connections.

Jeff