Large database Linked Table ODBC Source needs to be crosstab (or pviot if that work)

[TROD]

crosstabquery.pdfBoth the joined tables shown contain data from 6 different sites for the company that I work for. Each site contains 20 projects. So these Linked tables contain millions of data. I only need 2 projects from my 1 site. (That's why my project name criteria is Online-Master & Online-Scope).
I created the crosstab query shown since table: KR_DD_KR_TASK_CODE ,column CODE_TYPE needs to be pivoted.

From this Crosstab query I also created an append query which puts all this data into another table called TASK_CODE_APPEND. I also created a delete query for the table. I have this TASK_CODE_APPEND joined with other tables to run work week reports.

Every time I use this access data base I have to refresh (which runs the delete query and then append query in order to have the most up to date data into the TASK_CODE_APPEND table from my Linked ODBC Source table).

The crosstab query takes about a full minute to run. Its slow.

Is there any other option for me to avoid a refresh and speed up the process in pivoting CODE_TYPE for use?
Kind of defeats the purpose of Linked ODBC Source if I have to continuously refresh to update my table.

[ItsMe]

Perhaps the solution is to use a temp table. Bring the data that the user needs to analyze over from the backend to the client. As the user analyzes the data, they can make real time edits to the backend via linked tables.

[TROD]

I apologize since I am not too familiar with back-end tables. I just viewed a video on how to create one.

If not mistaken the back-end table would still have to be updated/refreshed to have to most up to date data correct?



Here's my dilemma. I have a an external ODBC Database linked to my access ( KR_DD_KR_TASK_CODE ) which is continuously changing through out the day.
There is one specific column (CODE_TYPE ) in that Database that needs to be pivoted.

I was wondering if there was a way to pivot that specific column and automatically stay updated from the ODBC database? Without some type of manual refresh. Only because ODBC Database is always changing.

I created a crosstab query for (CODE_TYPE) column but since ODBC Database ( KR_DD_KR_TASK_CODE ) is so large with additional sites that I don't need my Crosstab query takes about 2minutes to run.


I was trying to avoid any type of refresh unless there was something quick like within a couple of seconds since ODBC Database is always changing.

[ItsMe]

A temp table is a table that you create for and is part of the Client. A client will be the front end and the production DB will be the backend. Multiple clients can connect to the backend. A local table that acts as a temp table will not be accessed by anyone other than the client.

In the situation you are describing, a temp table will look very similar to a query that retrieves data from the backend. The tables structure will have the same columns as the query. The query will retrieve data from the backend and append records to the temp table. This append process will likely occur when the user opens a form, eg the form that has this pivot thing. As the user interacts with the pivot thing, the data in the temp table will be the source for the pivot thing.

When the user is finished using the form, all records within the temp table are deleted. The temp table remains as a structure without any data. It is ready for the next time the form and pivot thing is used.

[TROD]

HHHMMM. Sorry but I might be a little confused here.

Are you say that I should create a Back End Database to KR_DD_KR_TASK_CODE using the Database Splitter in Access?????
If so I tried that but a message popped up stating "No Tables found" Creating a blank table as be. This was with just "KR_DD_KR_TASK_CODE" ODCB Database linked to my access.
Not sure if it matters or not but there is a blue arrow pointing to my linked "KR_DD_KR_TASK_CODE" in my Access database.



I created a crosstab query "TASK_CODE_1" to pivot one of the columns from "KR_DD_KR_TASK_CODE".
I then created an Append Query from my Crosstab Query to create a table "Task_Code_Pivot _Table" containing the data pivoted. I also created a Delete Query to erase "Task_Code_Pivot_Table" and update with the append.
I then performed the Split Database function in access. But all that does is create a back end for "Task_Code_Pivot _Table".
Not sure what this is buying since I still have to run Append query & delete query from my crosstab query.



Maybe I completely misunderstood what you meant by creating a temp table that is like a query.

[ItsMe]

Somewhere, there is data. Let's call this your database. You mentioned you have an Access file with an ODBC connection. Let's call this the front end or the client. When you use an ODBC connection you are connecting your client/FE to the backend. In your case, the backend is the database. One can have more than one database and this is why I phrase it as 'In your case'.

[TROD]

I believe that I understand what your describing with backend and front end tables.

Is there a way for me to speed up my crosstab query when is executed?
Reference my initial post which contains my crosstabquery pdf.
Both joined tables contain shown contain over 3million rows of data.

I created a crosstabquery to pivot and filter the dat that i needed. But like I said executing this query is extremely slow. Are there any other options to retrieving data like my crosstabquery which is way much faster???

Thank You

[ItsMe]

...Is there a way for me to speed up my crosstab query when is executed?...

There may be a way to improve the performance by refactoring your SQL statement and or restructuring your tables. Another approach would be to use stored procedures within your backend server. All of these options would be in an effort to improve the initial query that retrieves the data from the backend.

Another issue may be that you are taking subsequent trips to the data. How you User Interface is built and how the User interacts with the application may be causing large queries to be run repeatedly. If a re-query of the data is necessary, if a second and third trip to the data is necessary, you might be able to speed up re-queries by storing a snapshot of the data in a temp table.

[TROD]

Thanks for the info.
I feel as I'm getting close or at least I think I am.
I create a pass-through query that now runs really fast to bring in my columns already pivoted.
the only thing now is appending or somehow having that query to save as a table.
I tried appending the pass-through query but its really slow. the query contains about 12 columns with about 21,767 rows of data.
I need to be able to extract this pass-though query as a table.
I tried linking my other tables to this pass-through query but the end result is slow.

Any options of storing the data from this pass-through query really fast for use to join other tables?


Thank You very much.

[ItsMe]

You can use another pass-through query to execute a stored procedure on the SQL database. However, I am failing to see how a pivot thing, alone, will require appending so many records. Isn't all of that data already stored in tables on the server? I suggest updating or appending new data, only.

[TROD]

All data is already stored on a server. the 12 pivoted columns that I acquired is all I need from that specific server table. That server table alone contains about 2million rows of data along with 4 additional columns besides my 12.
I wrote an Oracle SQl query to GET my data via a pass-through query. Which is about 21,767 rows.
Now that I have 21,767 rows, I have to join a pass-through query with other data tables in my access for multiple reports.
I created a new query which append my pass-through query which is slow. Are you suggestion I create an update?


I have Oracle SQl Developer. That's how I figure out how to SQL query the server table for my pivot items.



I just created an append query via the pass-through query. It took 4minutes to append the data to a table.

I tried creating an update query from the pass-through query (in access 2010 design view) but I got the following error, "Operation must use an updateable query". Maybe it has to do with using the a QUERY(pass-through) in updating a table. I've never updated a table from an existing query. Typically I go table to table. It would be nice to go from query to table. Is that possible.


The table that I need updated routinely in Access is called TASKCODE.
Below is my pass through query called WORK

select
t.task_id TID,
to_char(t.early_start,'MM/DD/YYYY') EARLY_START,
t.ACTIVITY_ID,
GET_CODE_VALUE('ACT STATUS',t.task_id) ACT_STATUS,
GET_CODE_VALUE('DISP CODE', t.task_id) DISP_CODE,
GET_CODE_VALUE('SCHED CODE',t.task_id) SCHED_CODE,
GET_CODE_VALUE('SCHED FLAG',t.task_id) SCHED_FLAG,
GET_CODE_VALUE('UNIT',t.task_id) UNIT,
GET_CODE_VALUE('WINDOW',t.task_id) WINDOW,
GET_CODE_VALUE('RCM',t.task_id) RCM,
GET_CODE_VALUE('PRE-POST OUTAGE FLAG',t.task_id) PRE_POST_OUTAGE_FLAG,
GET_CODE_VALUE('PARAGON ACTIVITY',t.task_id) PARAGON_ACTIVITY,
GET_CODE_VALUE('PARAGON SELECT',t.task_id) PARAGON_SELECT,
GET_CODE_VALUE('SYSTEM',t.task_id) SYSTEM,
GET_CODE_VALUE('CREW SUPV',t.task_id) CREW_SUPV
from KR_DD.KR_TASK t
where
( t.project_name = 'PEA-ONLINE-MASTER' or
t.project_name = 'PEA-ONLINE-SCOPE')
and t.early_start IS NOT NULL

[ItsMe]

The way understand what your user will be doing is using a dataset from the backend inside of a pivot table or a pivot control or something.

From my perspective, one way to enhance performance is to chunk over the dataset to the client. Now that the client has the dataset stored locally, they do not need to visit the data over the network each time they adjust the pivot.

The dataset that us retrieved should be based on criteria, if possible.

If the user decides the data on the server needs to be updated, they can use action queries to do so.

Perhaps I am not understanding correctly. I get the sense you are trying to write data to tables on the server that represents a particular image created by the pivot thing. Any updates to the server should be small.

[TROD]

I really appreciate you working with me.
What I am trying to do is have my pass-through query go straight to a table in my Access Database.

Below is my pass-through query which I want to some how update the associated (make table) in my Access Database.
I am trying to write the data from the Pass-through query into a table in my Access Database table. (Trying find a faster way to do this other than append)
This will be my backend table for my front end users.


select
t.task_id TID,
to_char(t.early_start,'MM/DD/YYYY') EARLY_START,
t.ACTIVITY_ID,
GET_CODE_VALUE('ACT STATUS',t.task_id) ACT_STATUS,
GET_CODE_VALUE('DISP CODE', t.task_id) DISP_CODE,
GET_CODE_VALUE('SCHED CODE',t.task_id) SCHED_CODE,
GET_CODE_VALUE('SCHED FLAG',t.task_id) SCHED_FLAG,
GET_CODE_VALUE('UNIT',t.task_id) UNIT,
GET_CODE_VALUE('WINDOW',t.task_id) WINDOW,
GET_CODE_VALUE('RCM',t.task_id) RCM,
GET_CODE_VALUE('PRE-POST OUTAGE FLAG',t.task_id) PRE_POST_OUTAGE_FLAG,
GET_CODE_VALUE('PARAGON ACTIVITY',t.task_id) PARAGON_ACTIVITY,
GET_CODE_VALUE('PARAGON SELECT',t.task_id) PARAGON_SELECT,
GET_CODE_VALUE('SYSTEM',t.task_id) SYSTEM,
GET_CODE_VALUE('CREW SUPV',t.task_id) CREW_SUPV
from KR_DD.KR_TASK t
where
( t.project_name = 'PEA-ONLINE-MASTER' or
t.project_name = 'PEA-ONLINE-SCOPE')
and t.early_start IS NOT NULL



I apologize if I still don't seem clear about my explanation.

[ItsMe]

I do not understand Oracle. I know a little bit of Microsoft SQL server, but I am not familiar with Oracle products. So the Code you are providing is foreign to me. Where my headspace is right now is best described in your bolded question in post #1 and my response in post #2. Obviously, you are having more than one problem. However, I see this thread as trying to address a performance issue.

To address some statements in your last post:

What I am trying to do is have my pass-through query go straight to a table in my Access Database.

If you create a query, reference it. If you want to use the dataset retrieved by your passthrough query to append records in a local table, create an action query within Access to append records. This other action query will reference your passthrough query.

Below is my pass-through query which I want to some how update the associated (make table) in my Access Database.

A make table is something an Access query does to create a table. There is a tool in Access to Make Table. Since the table does not yet exist, there is nothing to update.

I am trying to write the data from the Pass-through query into a table in my Access Database table.

If you create a query, reference it. If you want to use the dataset retrieved by your passthrough query to append records in a local table, create an action query within Access to append records. This other action query will reference your passthrough query. I suggest you use a temp table. The idea behind a temp table is to append records to a table that is never deleted. After you are finished with the temp table, delete all existing records within the temp table, leaving behind only the table's structure.

(Trying find a faster way to do this other than append)

An append action will always be an append action. If you are looking for another approach, the only one I know of is bidirectional synchronization. This is where all of the data is stored in two or more databases. One database is the main database, sometimes referred to as the master database. Access does not have a way to perform bidirectional synchronization. Access used to have a feature that is now deprecated. There are better ways to get it done than using the Access feature that did not really work, anyway. If you use Access, you are stuck with temp tables.

This will be my backend table for my front end users.

It will not be the backend because there will not be bidirectional synchronization. Your client's backend will always be the Oracle database. Using a local table that has a snapshot will not be the backend.

A passthrough query that retrieves a dataset from the 4 columns within the BE will act as something referred to as the persistence layer. The persistence layer will retrieve data from the backend (data source). You need to decide if a call will be large (Chunky) or small (Chatty). The architecture of the application needs to be designed in a way that a user will not be able to make many, repetitive, chunky calls. This control is implemented via the Domain and or Application layer(s).