Accessing ODBC linked data in large mainframe table

Question

I'm setting up a database with a series of linked tables to our CA-Datacom mainframe database. The tables are huge. I'll also be setting up a set of pre-built queries to perform various commonly required data requests.

I'm wondering what is the effect of making a column indexed? Will it speed up queries? For instance, we have  a member table with over a million records. The primary key is the member id. If I want to run a query against the DOB (not indexed), can I just make the DOB column indexed or will that have no effect?

Answer 1

Yes, the DOB column with a index will MOST certainly help.

However, keep in mind we talking about a index on the mainframe side. No need (or ability) to set up indexing Access side on linked tables - this is strictly a server only side issue and option.

So such indexing is setup on the server or “mainframe” side.

Such an index will not reduce network traffic from server to the Workstation running Access, but any column in a large table that will have “where” criteria in the SQL will MOST certainly benefit from such indexing.

So, a big yes to your question. The indexing of course is NOT a function of the Access client, but of the server side.

However, for any table that is the result of SQL join to OTHER tables, then I strongly suggest you create a view server side (if your system supports as such, and you have permissions to create such views). When (if you can) create such views, then you will “link” to that view from Access (it will look like a linked table). You can then query on this view, but the “join” and heavy lifting of that join will occur server side.

If you can’t use views, then using a pass-through query is your best bet. And once again, for quires that involve more than one table, then PT query will ensure that the heavy lifting of joining data between those tables occurs server side.

If you dont' use a pass-though (or view) when multiple tables are involved, then the Access client often make sa real mess of the query - and will pull down far too many rows, or often even all rows!

So it not really Access that cares about the indexing server side, but the server will most certainly feel the difference and thus retrieve the data in far less time. The “transfer” time to the client will be the same in both cases regardless of the index, but delay time server side of course can be dramatic reduced if indexing can be used by the server.

As noted, even with indexing on table server side, if the query in query has multiple tables (sql joins), then for large datasets, it is strong recommend that you use a view, or Pass-through query.

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 2

Thank you for your most informative reply. I have until now been using Visual Studio Server Explorer with ODBC connections to view and retrieve data from these tables. It is fast but the interface is much clunkier than MS Access. MS Access data retrieval, however, seems much slower in comparison. I get the hourglass for a very long time and sometimes have to use Task Manager to cancel MSACCESS.EXE just to get out. 

Answer 3

For a single table query  you should not see much difference, even when building such a query in Access.

However, for a group-by, or any query with aggregates (sum(), or count()), then you have to use a pass-though, and again such quires should perform the SAME in terms of speed as when using SQL server management studio (SSMS) or the server explorer from visual studio.

Of course, if  you build a pass-through query, then you can't use grid designer view in Access, but only the SQL view.

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 4

This is CA-Datacom not SQL Server. And these are single table SELECT queries, there are no joins or aggregates. I used the SQL view to build the queries. I'm not sure what makes a query a 'pass-thru' query. But as I said these tables are millions of rows. I guess its possible the Visual Studio connections are using a more efficient driver that MS Access is using. I will have to investigate that.

Answer 5

Hi AllTheGood,

As we know, Access database is lightweight database, and it might not be suitable for large data.

For creating and using an index to improve performance, I suggest you refer the link below:

# Create and use an index to improve performance

https://support.office.com/en-us/article/Create-and-use-an-index-to-improve-performance-0a8e2aa6-735c-4c3a-9dda-38c6c4f1a0ce?ui=en-US&rs=en-US&ad=US

For databases performance, I suggest you refer the link below:

# Performance Tips To Speed Up Your Access 2007 Database

https://msdn.microsoft.com/en-us/library/dd942824(v=office.12).aspx#odc_ac2007_ta_PerformanceTipsToSpeedUpYourAccessDB_SQLServerAndODBCPerformanceTips

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.