Forum Stats

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

Discussions

Categories

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:
· Share on TwitterShare on Facebook

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)

    RUNSUM.rar

    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
    · Share on TwitterShare on Facebook

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.

    · Share on TwitterShare on Facebook
  • 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".

    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 10,081 Blue Diamond

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

    · Share on TwitterShare on Facebook
  • User_Y17QL
    User_Y17QL Member Posts: 5 Green Ribbon
    edited Sep 4, 2021 2:15PM
    xp.png

    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.

    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • 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;
    image.png

    Best regards

    Jan

    User_Y17QL
    · Share on TwitterShare on Facebook
  • User_Y17QL
    User_Y17QL Member Posts: 5 Green Ribbon
    edited Sep 5, 2021 2:53PM
    RUNSUM.rar

    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 

    · Share on TwitterShare on Facebook
  • 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)

    RUNSUM.rar

    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
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook