March 12, 2008 at 9:21 am
We have a setup of 3 mdb’s with several hundreds of queries and tables that are linked in this big spider web, don’t ask it’s a build up web starting back oh about 10 years ago. :w00t: I do not have access to the SQL server only the Query Analyzer and then of course Access, used as the data source right now. Because of limited space there is only one database setup on the SQL server, there is not QA database or Dev. … again access is very very locked down, we are not IT thus they won’t give us any access and IT resource at this time is null.
Task at hand is getting that database from Access to SQL – access is used as the “UI” , tables are suppose to be uploaded but the queries are so complex that from what I can tell they have to be all rewritten. Now I am considering myself a humble learner at this point, primary exposure to Access and very little SQL. :hehe:
IT tried to upload some of the tables and the queries remained in Access, but we had to bring them back to Access since performance was unbelievably slow and at times would hang so bad that we had to stop the process, mostly update or append queries. All the tables have indexes. We do not have the option to replicate and just focus on Dev to get it moved one due to space, two because of the constant changes that are being made in all the queries. Our change log contains already tons of changes made since the first attempt to move tables then queries over.
Any resources that can help me in getting acquainted with TSQL or does anyone know of any good online course work? Any feedback suggestions input from the experts. I am not able to download tools and I had a hard time even getting access to the Analyzer. 😀
March 12, 2008 at 12:36 pm
T tried to upload some of the tables and the queries remained in Access, but we had to bring them back to Access since performance was unbelievably slow and at times would hang so bad that we had to stop the process, mostly update or append queries.
I think this is problem #1, you have the data on the SQL server but the Queries to the table is outside of the SQL server.
You indicated that the complexity of the queries does not allow for it to be on SQL or transferred???
SQL server is going to handle complex queries 1000 times more than Access. I would first start transferring both query and table onto the server to see if the performance improves.
If you are truly locked down and IT will not give you much wiggle, I think your options are limited.
March 12, 2008 at 12:51 pm
I agree with Jsheldon. It's going to be painful, but you need to at least start transferring the tables, and progressively re-write the queries to leverage SQL's power.
There's a huge difference between having the queries in Access and having them in SQL Server. For example - if you have a customers table (with 100,000 customers), and you need customer #4123: if the query is done from Access, then Access will pull down all 100,000 customers, sort through them to find the one it wants, and will essentially "discard" the 99,999 rows it doesn't need. If SQL Server does the processing, then Access sends the QUESTION (or QUERY) to SQL, which find the 1 row, and send the 1 row back. A lot more efficient.
Your best strategy is to see which queries run the longest, and start re-writing them. You may find that you don't have to re-write them all, but that the performance gets to be a lot better after the top 50 queries are rewritten.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 12, 2008 at 1:02 pm
Well it was my understanding from some of the reading I have done there is a certain amount of syntax conflict that won’t allow us to simply move the queries as is?
I am thinking of things like and more of course:
Concatenation:
Access: use & “ “&
SQL: use + ‘ ‘ +
I agree the no access to the server is a big issue. The last attempt to move to SQL prior to my arrival consisted of an Access Projects database and link to tables and queries/views from the SQL database as some of the queries where attempted to be moved then shortly after only tables where on the SQL side.
There are approx 4500 + queries and 250 + tables all together. I can’t create any tables etc on the server, we can only work within Access.
My hope would be a duplicate of the existing database on the server to start testing some of the processes and then deal with the query changes later just to get a feel for what needs to be re-written.
But thus far no luck on getting a replicate on the server.
Any resources you can recommend to do some additional research on the syntax diffs and the migration? I can only put tools on my own laptop but in return can't work with data from the job.
March 12, 2008 at 1:08 pm
Any resources you can recommend to do some additional research on the syntax diffs and the migration? I can only put tools on my own laptop but in return can't work with data from the job.
You said you have access to query analyzer. The very least you can open the query in Query Analyzer (export it first from Access) and then Parse it in Query analyzer it will tell you what syntax is incorrect.
Again by the looks of this though this is tedious I do not know of any comparison software (I do but it compares SQL to SQL) so I will let others chime in.
March 12, 2008 at 1:17 pm
If you're not going to get any cooperation from your IT shop, then I'm not quite sure what to tell you. You may be better off leaving them alone. You won't get any performance enhancement out of T-SQL if you can't get the data on the server, normalized, indexed, etc...
Perhaps talk your IT department into setting up a development machine with SQL Developer edition. It could just be a fancy workstation, and it would not mess up anything on their side. On the other hand, it WOULD give you a fully functional SQL Server installation to start importing and re-writing some of the queries. Like you already know - some of the syntax is a little different, but it's pretty predictable, and you'll find that you will know rather quickly what needs to be replaced, and a series of find and replace ops will handle 90% of the issues from that. That will leave you with a few things still needing to be tweaked.
Just getting the queries to run natively is however only phase 0.01. Some directly transcribed queries won't run particularly well (or downright badly), so they will need some more help. you'll need to learn about performance tuning, etc... It's involved.
That being said - with numbers like you're talking about - that's going to be someone's full time job, and it will take a while to do..... If you don't want this to become your full-time job for years to come, you may need to get someone to come in to do this for you.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply