Forum Stats

  • 3,759,247 Users
  • 2,251,515 Discussions
  • 7,870,551 Comments

Discussions

Speed Problem

User_Y17QL
User_Y17QL Member Posts: 5 Green Ribbon
edited Sep 4, 2021 9:32AM in SQL & PL/SQL

how to convert query to table without calculation

Tagged:

Best Answer

  • Jan Gorkow
    Jan Gorkow Member Posts: 133 Gold Badge
    Accepted Answer

    Hi @User_Y17QL ,

    based on your access db you sent me which I also upploaded here (Thanks for your permission)

    I've done the following.

    First I created the data table in oracle. For this I used your original table design out of access except the SUM columns which are not needed for my solution. It is not common to use case sensitive column names or column names starting with numbers in Oracle, but it is possible, so I kept your original column and table names:

    CREATE TABLE "xp"
    (
      "ID"            NUMBER,
      "Date"           DATE,
      "ID2"           VARCHAR2 (255 CHAR),
      "typeID"          VARCHAR2 (255 CHAR),
      "Goods"          VARCHAR2 (255 CHAR),
      "quantity"         NUMBER,
      "Cost"           NUMBER,
      "unitcost"         NUMBER,
      "Company"         VARCHAR2 (255 CHAR),
      "unitPrice"        NUMBER,
      "1PaymentDate"       DATE,
      "1PaymentAmount"      NUMBER,
      "2PaymentDate"       DATE,
      "2PaymentAmount"      NUMBER,
      "3PaymentDate"       DATE,
      "3PaymentAmount"      NUMBER
    );
    

    Then I imported your data from access to oracle. This is done by the following query after linking the oracle table via odbc in access:

    INSERT INTO [WORKSHOP_xp] ([ID], [Date], [ID2], [typeID], [Goods], [quantity], [Cost], [unitcost], [Company], [unitPrice], [1PaymentDate], [1PaymentAmount], [2PaymentDate], [2PaymentAmount], [3PaymentDate], [3PaymentAmount])
    SELECT [ID], [Date], [ID2], [typeID], [Goods], [quantity], [Cost], [unitcost], [Company], [unitPrice], [1PaymentDate], [1PaymentAmount], [2PaymentDate], [2PaymentAmount], [3PaymentDate], [3PaymentAmount]
    FROM [xp];
    

    After that I translated your access queries to oracle views:

    CREATE OR REPLACE VIEW "xp1"
    AS
      SELECT "xp"."ID",
         "xp"."Date",
         TO_CHAR ("xp"."Date", 'yyyymmdd') || TO_CHAR ("xp"."ID")
           AS "ID2",
         "xp"."typeID",
         "xp"."Goods",
         "xp"."quantity",
         "xp"."Cost",
         "xp"."Cost" / "xp"."quantity"
           AS "unitcost",
         CASE
           WHEN "xp"."typeID" = 'IN' THEN "xp"."quantity"
           ELSE -1 * "xp"."quantity"
         END
           AS "rtquantity",
         "xp"."Company",
         "xp"."Cost" / "xp"."quantity"
           AS "unitPrice",
         "xp"."1PaymentDate",
         "xp"."1PaymentAmount",
         "xp"."2PaymentDate",
         "xp"."2PaymentAmount",
         "xp"."3PaymentDate",
         "xp"."3PaymentAmount",
         CASE "xp"."typeID"
           WHEN 'IN'
           THEN
             -1 * "xp"."Cost"
            + "xp"."1PaymentAmount"
            + "xp"."2PaymentAmount"
            + "xp"."3PaymentAmount"
           WHEN 'OUT'
           THEN
             "xp"."Cost"
            - "xp"."1PaymentAmount"
            - "xp"."2PaymentAmount"
            - "xp"."3PaymentAmount"
           ELSE
            0
         END
           AS "rtblanace",
         CASE "xp"."typeID"
           WHEN 'IN' THEN -1 * "xp"."Cost"
           WHEN 'OUT' THEN "xp"."Cost"
           ELSE 0
         END
           AS "rtpl"
       FROM "xp";
    


    CREATE OR REPLACE VIEW "xp2"
    AS
      SELECT "xp1"."ID",
         "xp1"."Date",
         "xp1"."ID2",
         "xp1"."typeID",
         "xp1"."Goods",
         "xp1"."quantity",
         "xp1"."Cost",
         "xp1"."unitcost",
         SUM ("xp1"."rtquantity")
           OVER (PARTITION BY "xp1"."Goods"
              ORDER BY TO_NUMBER ("xp1"."ID2") ASC
              RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
           AS "∑quantity",
         "xp1"."Company",
         "xp1"."unitPrice",
         "xp1"."1PaymentDate",
         "xp1"."1PaymentAmount",
         "xp1"."2PaymentDate",
         "xp1"."2PaymentAmount",
         "xp1"."3PaymentDate",
         "xp1"."3PaymentAmount",
         SUM ("xp1"."rtblanace")
           OVER (PARTITION BY "xp1"."Company"
              ORDER BY TO_NUMBER ("xp1"."ID2") ASC
              RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
           AS "∑blanace",
         SUM ("xp1"."rtpl")
           OVER (PARTITION BY "xp1"."Goods"
              ORDER BY TO_NUMBER ("xp1"."ID") ASC
              RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
           AS "∑ProfitAndLossByGoods",
         SUM ("xp1"."rtpl")
           OVER (ORDER BY TO_NUMBER ("xp1"."ID2") ASC
              RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
           AS "∑ProfitAndLoss"
       FROM "xp1";
    

    Here we're able to test it for the first time:

    SELECT * FROM "xp2";
    

    The result returned in far under 0.1s in my environment.

    After that I wanted to test this agains much more data. So I multiplied the data set a couple of times. After executing the following block we have 98.304 rows in the table:

    BEGIN
      FOR i IN 0 .. 5
      LOOP
       INSERT INTO "xp" ("ID",
                "Date",
                "ID2",
                "typeID",
                "Goods",
                "quantity",
                "Cost",
                "unitcost",
                "Company",
                "unitPrice",
                "1PaymentDate",
                "1PaymentAmount",
                "2PaymentDate",
                "2PaymentAmount",
                "3PaymentDate",
                "3PaymentAmount")
         SELECT "ID" + (1569 * POWER (2, i)),
            "Date",
            "ID2",
            "typeID",
            "Goods",
            "quantity",
            "Cost",
            "unitcost",
            "Company",
            "unitPrice",
            "1PaymentDate",
            "1PaymentAmount",
            "2PaymentDate",
            "2PaymentAmount",
            "3PaymentDate",
            "3PaymentAmount"
          FROM "xp";
      END LOOP;
    
      COMMIT;
    END;
    /
    

    Based on that we're able to test again against an amount of data you mentioned to want to handle:

    --Test against data set of 98.304 rows
    
    SELECT * FROM "xp2";
    
    --Test select of single row
    
    SELECT *
     FROM "xp2"
     WHERE "ID" = 50000;
    
    --Test fetch of entire data
    
    DECLARE
      v_count  NUMBER := 0;
    BEGIN
      FOR v_xp2 IN (SELECT * FROM "xp2")
      LOOP
       v_count := v_count + 1;
      END LOOP;
    
      dbms_output.put_line (v_count);
    END;
    /
    

    Alle three tests return in about 1s in my environment (normal laptop).

    I hope this helps.

    Best regards

    Jan

    User_Y17QL

Answers

  • Paulzip
    Paulzip Member Posts: 8,452 Blue Diamond

    This is an Oracle forum, not an MS Access one. Try posting to an Access one.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,063 Red Diamond

    Hi, @User_Y17QL

    how to convert query to table without calculation

    That depends on what you mean. Post an example of a query and a description of the table that you want to create from it. (A CREATE TABLE statement would be good.) Explain what you mean by "without calculation".

  • mathguy
    mathguy Member Posts: 10,081 Blue Diamond

    Also explain what "speed" has to do with your question - I wasn't able to understand.

  • User_Y17QL
    User_Y17QL Member Posts: 5 Green Ribbon
    edited Sep 4, 2021 2:15PM

    database calculates when showing table

    if i want convert query to table, it calculates again .

    is it posible convert quaery to table winthout second calculation, if database shows it create table from values without calculation 

    at the system level, a huge calculation speed, I want to use this speed, it's like copying values without formulas in Excel

    display speed is higher than conversion speed

    it takes seconds to show and minutes to convert

    I have hard running total logic and I try not to get more than 50,000 transactions, but only the database display speed is best. if user copy past query to new table it will be more faster than creating table from query with command.

  • mathguy
    mathguy Member Posts: 10,081 Blue Diamond

    I am still not entirely sure what you are asking, but if I got most of it right, you should be asking about creating a materialized view based on your query, rather than a plain table. If you are not familiar with mv's, you would do well to start learning about them.

    When you talk about the speed of getting the results - are you sure you are seeing ALL the results? Many interfaces will start showing you the first results as soon as they are ready, long before the WHOLE query execution ends.

    User_Y17QL
  • Jan Gorkow
    Jan Gorkow Member Posts: 133 Gold Badge

    Hi @User_Y17QL ,

    please post the query actually used for the "Running total" caluculation. In my opinion the query itself is not optimal which causes your "Speed problem" while getting the data.

    In these simple case the "Running total" should be caclulated like this:

    WITH
      dat
      AS
       (SELECT 1 AS "Number" FROM DUAL
        UNION ALL
        SELECT 2 AS "Number" FROM DUAL
        UNION ALL
        SELECT 7 AS "Number" FROM DUAL)
     SELECT dat."Number",
         SUM (dat."Number")
          OVER (ORDER BY dat."Number" ASC
             RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  AS "Running total"
      FROM dat
    ORDER BY dat."Number" ASC;
    

    Best regards

    Jan

    User_Y17QL
  • User_Y17QL
    User_Y17QL Member Posts: 5 Green Ribbon
    edited Sep 5, 2021 2:53PM

    I am trying to do this on an oracle database because it is the fastest database in the world but 50,000 transactions will be hard for oracle to with this method 

  • Jan Gorkow
    Jan Gorkow Member Posts: 133 Gold Badge
    Accepted Answer

    Hi @User_Y17QL ,

    based on your access db you sent me which I also upploaded here (Thanks for your permission)

    I've done the following.

    First I created the data table in oracle. For this I used your original table design out of access except the SUM columns which are not needed for my solution. It is not common to use case sensitive column names or column names starting with numbers in Oracle, but it is possible, so I kept your original column and table names:

    CREATE TABLE "xp"
    (
      "ID"            NUMBER,
      "Date"           DATE,
      "ID2"           VARCHAR2 (255 CHAR),
      "typeID"          VARCHAR2 (255 CHAR),
      "Goods"          VARCHAR2 (255 CHAR),
      "quantity"         NUMBER,
      "Cost"           NUMBER,
      "unitcost"         NUMBER,
      "Company"         VARCHAR2 (255 CHAR),
      "unitPrice"        NUMBER,
      "1PaymentDate"       DATE,
      "1PaymentAmount"      NUMBER,
      "2PaymentDate"       DATE,
      "2PaymentAmount"      NUMBER,
      "3PaymentDate"       DATE,
      "3PaymentAmount"      NUMBER
    );
    

    Then I imported your data from access to oracle. This is done by the following query after linking the oracle table via odbc in access:

    INSERT INTO [WORKSHOP_xp] ([ID], [Date], [ID2], [typeID], [Goods], [quantity], [Cost], [unitcost], [Company], [unitPrice], [1PaymentDate], [1PaymentAmount], [2PaymentDate], [2PaymentAmount], [3PaymentDate], [3PaymentAmount])
    SELECT [ID], [Date], [ID2], [typeID], [Goods], [quantity], [Cost], [unitcost], [Company], [unitPrice], [1PaymentDate], [1PaymentAmount], [2PaymentDate], [2PaymentAmount], [3PaymentDate], [3PaymentAmount]
    FROM [xp];
    

    After that I translated your access queries to oracle views:

    CREATE OR REPLACE VIEW "xp1"
    AS
      SELECT "xp"."ID",
         "xp"."Date",
         TO_CHAR ("xp"."Date", 'yyyymmdd') || TO_CHAR ("xp"."ID")
           AS "ID2",
         "xp"."typeID",
         "xp"."Goods",
         "xp"."quantity",
         "xp"."Cost",
         "xp"."Cost" / "xp"."quantity"
           AS "unitcost",
         CASE
           WHEN "xp"."typeID" = 'IN' THEN "xp"."quantity"
           ELSE -1 * "xp"."quantity"
         END
           AS "rtquantity",
         "xp"."Company",
         "xp"."Cost" / "xp"."quantity"
           AS "unitPrice",
         "xp"."1PaymentDate",
         "xp"."1PaymentAmount",
         "xp"."2PaymentDate",
         "xp"."2PaymentAmount",
         "xp"."3PaymentDate",
         "xp"."3PaymentAmount",
         CASE "xp"."typeID"
           WHEN 'IN'
           THEN
             -1 * "xp"."Cost"
            + "xp"."1PaymentAmount"
            + "xp"."2PaymentAmount"
            + "xp"."3PaymentAmount"
           WHEN 'OUT'
           THEN
             "xp"."Cost"
            - "xp"."1PaymentAmount"
            - "xp"."2PaymentAmount"
            - "xp"."3PaymentAmount"
           ELSE
            0
         END
           AS "rtblanace",
         CASE "xp"."typeID"
           WHEN 'IN' THEN -1 * "xp"."Cost"
           WHEN 'OUT' THEN "xp"."Cost"
           ELSE 0
         END
           AS "rtpl"
       FROM "xp";
    


    CREATE OR REPLACE VIEW "xp2"
    AS
      SELECT "xp1"."ID",
         "xp1"."Date",
         "xp1"."ID2",
         "xp1"."typeID",
         "xp1"."Goods",
         "xp1"."quantity",
         "xp1"."Cost",
         "xp1"."unitcost",
         SUM ("xp1"."rtquantity")
           OVER (PARTITION BY "xp1"."Goods"
              ORDER BY TO_NUMBER ("xp1"."ID2") ASC
              RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
           AS "∑quantity",
         "xp1"."Company",
         "xp1"."unitPrice",
         "xp1"."1PaymentDate",
         "xp1"."1PaymentAmount",
         "xp1"."2PaymentDate",
         "xp1"."2PaymentAmount",
         "xp1"."3PaymentDate",
         "xp1"."3PaymentAmount",
         SUM ("xp1"."rtblanace")
           OVER (PARTITION BY "xp1"."Company"
              ORDER BY TO_NUMBER ("xp1"."ID2") ASC
              RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
           AS "∑blanace",
         SUM ("xp1"."rtpl")
           OVER (PARTITION BY "xp1"."Goods"
              ORDER BY TO_NUMBER ("xp1"."ID") ASC
              RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
           AS "∑ProfitAndLossByGoods",
         SUM ("xp1"."rtpl")
           OVER (ORDER BY TO_NUMBER ("xp1"."ID2") ASC
              RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
           AS "∑ProfitAndLoss"
       FROM "xp1";
    

    Here we're able to test it for the first time:

    SELECT * FROM "xp2";
    

    The result returned in far under 0.1s in my environment.

    After that I wanted to test this agains much more data. So I multiplied the data set a couple of times. After executing the following block we have 98.304 rows in the table:

    BEGIN
      FOR i IN 0 .. 5
      LOOP
       INSERT INTO "xp" ("ID",
                "Date",
                "ID2",
                "typeID",
                "Goods",
                "quantity",
                "Cost",
                "unitcost",
                "Company",
                "unitPrice",
                "1PaymentDate",
                "1PaymentAmount",
                "2PaymentDate",
                "2PaymentAmount",
                "3PaymentDate",
                "3PaymentAmount")
         SELECT "ID" + (1569 * POWER (2, i)),
            "Date",
            "ID2",
            "typeID",
            "Goods",
            "quantity",
            "Cost",
            "unitcost",
            "Company",
            "unitPrice",
            "1PaymentDate",
            "1PaymentAmount",
            "2PaymentDate",
            "2PaymentAmount",
            "3PaymentDate",
            "3PaymentAmount"
          FROM "xp";
      END LOOP;
    
      COMMIT;
    END;
    /
    

    Based on that we're able to test again against an amount of data you mentioned to want to handle:

    --Test against data set of 98.304 rows
    
    SELECT * FROM "xp2";
    
    --Test select of single row
    
    SELECT *
     FROM "xp2"
     WHERE "ID" = 50000;
    
    --Test fetch of entire data
    
    DECLARE
      v_count  NUMBER := 0;
    BEGIN
      FOR v_xp2 IN (SELECT * FROM "xp2")
      LOOP
       v_count := v_count + 1;
      END LOOP;
    
      dbms_output.put_line (v_count);
    END;
    /
    

    Alle three tests return in about 1s in my environment (normal laptop).

    I hope this helps.

    Best regards

    Jan

    User_Y17QL
  • User_Y17QL
    User_Y17QL Member Posts: 5 Green Ribbon

    Thank you so much, this was the hardest problem and you solved it. Oracle is the best database with excellent experts

    Jan Gorkow