I created my tables in Teradata mirgrating from Access and the more I dig into this the more I really do not like Teradata. I have researched my ascending because in Access I have PK's and they are ascending and for example my Data Entry form when the end-user see's the list of items they see row 1 has QID 1 and the following columns for that specific record. Next is row 2 and so on all the way to row 500373. My issue is, if I don't have ascend in the Access query (ODBC to the Teradata tables) then the Data Entry form displays however it wants to. The first time it had QID 45867 as the first row. Because I have so many rows attempting to sort once the information is displayed causes an ODBC call failed and if I sort in the query Ascending, sometimes the query runs and sometimes I get the ODBC call failed. There is nothing wrong with Access or the queries. The issue is something with Teradata. Whether the tempspace allotted is so incredibly low the query to view 500373 rows will never run, ever. Or what, who knows.
I have another query that searches through all the 500373 and displays based on multi list values, etc and that query takes 25 minutes to run. The queries are in Access with the ODBC to the Teradata. I have yet to find a way to build the saved queries in they Teradata data labs and then have the ability to ODBC to them. If I could do that, then perhaps I would not get all these time outs and call fails. Is there a way to store my queries in Teradata as they are in Access and they point to the forms that are front-end or am I just out of luck and have to tell my management team let's migrate phase II to a SQL Server?
There's no guarantee that rows are retuned in a specific order in any DBMS without an ORDER BY.
Access happens to store the data based on the PK, but in SQL Server this might change, too.
500373 rows is a small table, I can select them on my TD Express VM in a few seconds.
Performance mainly depends on how Access retrieves the data, in worst case it's every row with a single SELECT, resulting in 500373 messages sent to TD. When you used Access locally (MDB?) there was no network involved.
You should push all the conditions into TD and retrieve only the small results (you're not going to display 500000 rows in a grid to an end user).
How do I push all conditions to TD? Are you talking about the queries? The current Access db does display the listbox with the values as a datasheet and the end-user can sort to view all the 500K plus rows. Example they can put in the textbox their name and sort to get records that are only their name. This is how mgmnt wanted it. Display it all then filter from the all. If there is a way to move these queries to the server and ODBC to them that might be the ticket. Just dont know how to store the queries on the server and ODBC to them since I am brand new to Teradata. I know Access, VBA and SAS.