Speed it up!

[DanWallace]

Hey guys,

I do data conversion for a software company. We receive a batch of data from our clients and have to write a set of queries and modules that will take that data and convert it into our tables. Usually, we store the client data in SQL Server 2005 and have all the tables linked into our "conversion program" access database. In our conversion database, we have our table structure and I create any queries or modules necessary to convert the data. I then create a macro to run all of this in sequence.

The problem is, the whole thing takes a good 7-8 hours on average to run.

I was wondering if anyone out there has a suggestion of a better way to do this sort of thing. I'm open to any thoughts on the matter.

Thanks!

 

[KenHigg]

There could be any number of issues. I would suggest building some kind gizmo that records the number of records and time for each conversion routine. Then you can start working on the problematic routines.

 

[BeerSnob]

Have you thought about doing your conversions in Sql Server instead?

I import a lot of data every morning from our legacy system, and it only takes about 20 minutes using vbscript and sprocs. With the added benefit that it can be scheduled and run on the server, rather than waiting for me to click on a button in Access to fire a macro - and tying up my machine while it runs.

 

[KenHigg]

Network latency was going to be my first guess. In which case you could pull it all over to the client then process it...

 

[DanWallace]

Perhaps "client" was a poor choice of words for me as I was referring to our customers. Network latency shouldn't be an issue as the whole process works on the server at run-time.

@BeerSnob: That idea has been tossed around for a while now. What I'm primarily concerned with is the learning curve. Time is always a factor, and I don't know a lot about SQL Server. How is the query builder? Access is very visual and easy to troubleshoot (and troubleshooting is a huge part of this as it constantly needs to be reworked). Does SQL Server include any sort of macro functionality where I can call all of these queries and run code in a sequence non-stop?

Thanks for the suggestions, guys.

 

[KenHigg]

Another option is to use passthrough querys

 

[DanWallace]

Another option is to use passthrough querys

I'm not sure what that is. I'm a little new. lol

 

[KenHigg]

It's were you send the sql to the databse server and it executes there instead of on the client. If you google it you should get some good results.

 

[BeerSnob]

Sql Server has something called DTS that allows you to "build" (or map) something similar to a macro. You're able to build much more dynamic (powerful) queries in Stored Procedures then you can in Access.

There is a learning curve, but if I was able to pick it up, then anybody should be able to After a while, you get so used to typing SQL that you don't use the graphical query builders anymore.