I believe the migration from Access backend and frontend to Teradata Data Labs was not the right path to take with this project. I believe the DBA's misunderstood our intended use.
From what I have researched, Teradatas usage is meant to store extremely large data and people can go in and query. I don't think it was ever meant to house the backend and utilize something else that is massively robust for the front-end. My old org, United Health, we used Teradata and DB2. We connected via SAS. The DBA's for both had to increase our tempspace because our team used the most tempspace throughout the entire org which was about 10%. This is because of our giantic projects. I had queries in SAS that would run for 52 hours because of the massive inner, outer, right, subquery joins.
I have been having massive issues with ODBC call fail using my Access front-end application. I have two forms that have massive joins and are very complex and work perfect in Access querying the 500K plus rows and it does move pretty fast. Max response time has never been over 5 minutes. The forms in question are a Data Entry and an Upate Form. So, because of this error, I have been conducting tests in Teradata Studio. The large query does not ever run. It comes back with a spool error that I don't have enough space for userID. So, I made the query small and did 1 join from main table to my measures table and that ran pretty fast but then gave me a message before the query would finish about 2,000 maximum rows of 500K plus do you want to continue? I say yes and then it returns all those 500k plus rows. If I add the next join, I get the spool error.
I suspect these 2 forms will never work with Teradata and my front-end Access based on:
1) The DBA's gave us a database, data labs that is housed on our claims server. We are a large health insurer. Not sure why they suggested this other than it would be easier to query our database against the claims database?????? There are tons of data labs out there on this server that I can see. They also put us on the production server. Every one and their mother is on this server.
2) Because everyone and their mother is on this server, everyone probably has limited tempspace thus the reason I get the spool error that I am out of space for my userID. Querying space, not DB space. My DB is only 156MB of 6GB.
3) Even if I were able to figure out how to store my queries in Teradata and ODBC to the tables and queries, the queries would still never run because I have 12 joins and it bombs on the server itself for running out of space.
My suggestion now to management is we don't use the data entry and data update forms and I will just have to run update queries or append queries when new items require adding. We figure out a backend like maybe SQL Server? However not sure if we would run into same issues. We find a host for the Access DB and keep backend and frontend there. This whole migration is just a major headache. Even the DBA that set me up with the database does not know any workarounds. I am posting this basically because I want to see if anyone else has experienced this and get some feedback. Maybe because I am so new to Teradata I don't know what I am talking about and someone out there can correct me.
12 joins is not that much, the same for 500k rows.
It might be your query which leads to a bad plan. Could you show your query and the plan produced by Teradata (simply add EXPLAIN before the SELECT and submit it)?