472,952 Members | 1,789 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,952 software developers and data experts.

help optimizing transpose query

Hello All!
Hoping some folks could help me optimize and or choose the best route to do this process.
First off, here is what I am trying to achieve. I have a (fairly large) table of ~34million rows that looks something like this.


I am trying to transpose the table by ID into a new table that looks like this (although there will be 96 time fields)


This was the original query I used (which worked fine when the table was 1.4 million rows, but now doesn't... it runs out of tempspace (even at 100mb tempspace size)

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE trans_data parallel 8 AS
  2. SELECT /*+ parallel (l,8) */
  3.   l.ID,
  4.   tbl_TIME1.TIME1,tbl_TIME2.TIME2,tbl_TIME3.TIME3,tbl_TIME4.TIME4
  5. FROM org_data l 
  6.   LEFT JOIN
  7.     (
  8.     SELECT
  9.       ID,
  10.       SPD AS TIME1
  11.     FROM
  12.       org_data l
  13.     WHERE
  14.       TIME=1
  15.     )
  16.      tbl_TIME1
  17.   ON
  18.     l.ID=tbl_TIME1.ID
  19.   LEFT JOIN
  20.     (
  21.     SELECT
  22.       ID,
  23.       SPD AS TIME2
  24.     FROM
  25.       org_data l
  26.     WHERE
  27.       TIME=2
  28.     )
  29.      tbl_TIME2
  30.   ON
  31.     l.ID=tbl_TIME2.ID
  32.   LEFT JOIN
  33.     (
  34.     SELECT
  35.       ID,
  36.       SPD AS TIME3
  37.     FROM
  38.       org_data l
  39.     WHERE
  40.       TIME=3
  41.     )
  42.      tbl_TIME3
  43.   ON
  44.     l.ID=tbl_TIME3.ID
  45.   LEFT JOIN
  46.     (
  47.     SELECT
  48.       ID,
  49.       SPD AS TIME4
  50.     FROM
  51.       org_data l
  52.     WHERE
  53.       TIME=4
  54.     )
  55.      tbl_TIME4
  56.   ON
  57.     l.ID=tbl_TIME4.ID;
this code worked about the same as far as speed little bit faster.. but again on the new larger table it runs out of tempspace.

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE CREATE TABLE trans_data parallel 8 AS
  2. SELECT /*+ parallel (l,8) */
  3. a.tmc,
  8. FROM
  9. org_data a
  10. GROUP BY
  11. a.ID;
and here is my latest attempt
I create the table first, populate it with unique ID's then run this

Expand|Select|Wrap|Line Numbers
  1. UPDATE trans_data a SET TIME1 = (SELECT /*+ index (trans_data, ID_INDEX) */ AVG FROM orig_data b WHERE TIME=1 and a.ID=b.ID);
  2. COMMIT;
this works but takes about 8-10 minutes per column
which at 96 columns runs to over 8 hours, trying to shorten up the amount of time taken (if possible!)
Sorry for posting such a long post here, but if anyone can help, I will forever be indebted!
Mar 12 '08 #1
2 3888
153 100+
I like the second approach best personally, creating the pivot table seems is good. Is there no way to restrict on the rows? I mean why do you need to keep redoing the rows you did before? (there is no where clause in the query so your redoing the entire table!)

if you have built a new table from the old can you not add some kind of timestamp to it so you know what you have done already?
Mar 13 '08 #2
Thanks for the reply Dave44
was a bit frustrated yesterday and not thinking as clearly as I should have been, your post gave me the much needed inspiration ie adding a where statement.
so just broke up the case statements into 4 temp tables by adding a where time between # and #
and then did a left join of the four temp tables at the end. It ran in less then 5 minutes...
Mar 13 '08 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Uros | last post by:
Hello! I have some trouble getting good results from my query. here is structure stat_views id | integer id_zone | integer created | timestamp
by: ti33m | last post by:
Hi All, I'd like to include a datasheet on my user interface but since I'm starting to run tight on space, I'd like to have a vertically-oriented datasheet (column 1 has labels, column 2 has...
by: Alan Lane | last post by:
Hello world: I'm including both code and examples of query output. I appologize if that makes this message longer than it should be. Anyway, I need to change the query below into a pivot table...
by: jenny.rhodes | last post by:
Hello, Please can anyone guide me on how to transpose an access table where I have many records per id eg UserID Question Answer 1 1 a 1 2 d 1 3 ...
by: Jenny | last post by:
Hello, Please can anyone guide me on how to transpose an access table where I have many records per id eg UserID Question Answer 1 1 a 1 2 d
by: Gerard Brunick | last post by:
My way is ugly. These has to be a better way. Thanks, Gerard
by: Nitin | last post by:
I have two tables in sql database as follows. I need to generate the output given at the end. ======= Table1 ======= PlanName PlanType PlanDescription Sequence
by: eskelies | last post by:
Below you will find the code that I have written. I am asking it to search for an Excel file. Once it finds the correct dated Excel file it opens it. From there it is in search of information. I...
by: jenniferhelen | last post by:
I am working with a query that has 6 columns and 101 rows; I would like to transpose the rows and columns. I have tried using a crosstab query but Access limits the row "fields" to 3 and this was...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.