Effective programming of data layer management/access

Question

Short Version: What is the term/concept which describes the efficient design of programming the data layer for efficient data access and what are some sources/books/articles/website which discuss this concept?

Long Version: So I had a recent conversation with the senior developer at my small company about how and why we access data the way we do. He told me that it was the single most important aspect of the program and explained how it is the largest bottleneck in efficiency if done incorrectly. He also said he had never learned about it from the courses he took and he never read about it in any books, despite the fact that he thinks it's the first thing he chooses to teach others.

I should explain that this is my first year at a job programming, that I have a degree in Electrical Engineering, but not in CS, so I wouldn't know if it was covered in school. Also, the language I work in is Java, but this question was meant to ask about any language's access to data in a general sense.

So what am I talking about? I'll explain through an example. We work with a lot of data, so the data layer is a bit complicated. It uses a combination of singleton pattern and factory design pattern to achieve different access.

For example, an Object, let's say Customer would be most often instantiated as follows:

Customer customer = Customer.getDefaultInstance(CustomerAccessType.LIST) 

OR

Customer customer = Customer.getDefaultInstance(CustomerAccessType.ONE_RECORD)

OR

Customer customer = Customer.getDefaultInstance(CustomerAccessType.LIST_BY_ACCOUNT)

As you may have already guessed, each access type accesses the database in a different manner. The LIST access type most of the time has very few columns, so even though it's grabbing many rows of data, in some cases up to tens of thousands of records (maybe not with customers, but maybe something like orders from a website you can imagine that becoming very large), it might only contain the primary keys on the table and some other very specific information. But when you edit specific data you access using type "ONE_RECORD" and with only a single row you have all the columns of the table (and most probably joins to a lot of other tables).

This is more apparent than ever when you're looking at a screen that is painting information in front of tellers and the information is changing constantly. The information shown may be from a table that has as previously mentioned, tens of thousands of records currently being worked on. A LIST type object will grab only the primary keys from the table, then only the records that require painting are accessed, one row at a time, and the information is painted to the screen.

So I have two questions as a result of all this:

1. What is the term/concept which describes the efficient design of programming the data layer for efficient data access? Most large programs that have to deal with a lot of data access must have to deal with these same issues. Surely others have coined a term for it?

2. What are some good resources to learn more about what else is out there on this subject? Again, surely others have dealt with these same issues/bottlenecks we are dealing with. Surely other brilliant minds have tackled the problem and written extensively on it, no?

3. What are other ways that perhaps your personal experience has shown you in dealing with this problem, even if you've never read it anywhere else?

Your help and knowledge is much appreciated,

-Asaf

Answer 1

I have been asked to write the same type of interfaces and this is what I have been told/learned.
I believe what you are referring to is the "database layer of an application".
I have seen this called the "Data access layer (of an application) also:

http://en.wikipedia.org/wiki/Data_access_layer
http://www.paulgrenyer.com/Integration_Testing_A_Java_Enterprise_Application_Data_Access_Layer.pdf

1. In order to have efficient data access

the underlying database objects themselves have to not have performance related issues. E.G. If you are using tables, the tables should have appropriate indexes, the statistics on the tables have to be up-to-date, and if you don't need the most recent data in the table, it is a good idea to consider using materialized views.

This seems obvious, but make sure you have access to the database itself and the database doesn't have any problems of its own, E.G. too many outstanding sessions, I/O problems, etc.

2. In the code for the database access,

it is a good idea to avoid hard parsing and using bind variables will help avoid this.

3. If the work that is done in your data access layer

consists mostly of short lived transactions it may be a good idea to use connection pooling. I have

http://www.javaranch.com/journal/200601/JDBCConnectionPooling.html

I am not sure whether the class you are using will work/is designed for connection pools but if you are using a Session object (usage of getDefaultInstance leads me to believe this might be a Session object) and you are using this for Hibernate then this URL does state it will work with a connection pool

http://docs.jboss.org/hibernate/orm/3.5/api/org/hibernate/Session.html

I have a feeling you could be using a different object - the object documentation should state whether it uses/is compatible with connection pooling

4. This URL states getDefaultInstance will always create

a new object and there are alternatives. I am not sure if that is what you want - it is something to consider

https://stackoverflow.com/questions/4184204/what-is-the-difference-between-getdefaultinstance-and-getinstance-in-session

Again I apologize I am not sure if this document is actually for the class you are using - this is just a best guess.