slow and overly complicated views

slow and overly complicated views

Post by Peter Schwartzbaue » Sun, 10 Dec 2000 02:03:52



I'm trying to get to View 3 (InventoryTotalsSummed).  Right now I rely on
Views 1 and 2 in order to do so.  I get the correct results, but its too
slow.  Does anyone have any ideas of how I can clean this up and make it
more efficient?  I would greatly appreciate it.  Let me know if you need
more info.

View 1 - InventoryCounts:

SELECT ProductID, FromID AS Type, - SUM(Quantity) AS Qty
FROM Inventory
GROUP BY ProductID, FromID
UNION
SELECT ProductID, ToID AS Type, SUM(Quantity) AS Qty
FROM Inventory
GROUP BY ProductID, ToID

View 2 - InventoryTotals:

SELECT PR.ProductID, ISNULL(SUM(TM.Received), 0) AS Received,
    ISNULL(SUM(TM.Inventory), 0) AS Inventory,
    ISNULL(SUM(TM.Quarantine), 0) AS Quarantine,
    ISNULL(SUM(TM.Dead), 0) AS Dead, ISNULL(SUM(TM.Shipped),
    0) AS Shipped, ISNULL(SUM(TM.Adjustment), 0)
    AS Adjustment
FROM Products PR LEFT OUTER JOIN
    (SELECT ProductID, SUM(Qty) AS Received, 0 AS Inventory,
         0 AS Quarantine, 0 AS Dead, 0 AS Shipped,
         0 AS Adjustment
     FROM InventoryCounts
     WHERE Type = 1
     GROUP BY ProductID
     UNION
     SELECT ProductID, 0 AS Received, SUM(Qty) AS Inventory,
         0 AS Quarantine, 0 AS Dead, 0 AS Shipped,
         0 AS Adjustment
     FROM InventoryCounts
     WHERE Type = 2
     GROUP BY ProductID
     UNION
     SELECT ProductID, 0 AS Received, 0 AS Inventory, SUM(Qty)
         AS Quarantine, 0 AS Dead, 0 AS Shipped,
         0 AS Adjustment
     FROM InventoryCounts
     WHERE Type = 3
     GROUP BY ProductID
     UNION
     SELECT ProductID, 0 AS Received, 0 AS Inventory,
         0 AS Quarantine, SUM(Qty) AS Dead, 0 AS Shipped,
         0 AS Adjustment
     FROM InventoryCounts
     WHERE Type = 4
     GROUP BY ProductID
     UNION
     SELECT ProductID, 0 AS Received, 0 AS Inventory,
         0 AS Quarantine, 0 AS Dead, SUM(Qty) AS Shipped,
         0 AS Adjustment
     FROM InventoryCounts
     WHERE Type = 5
     GROUP BY ProductID
     UNION
     SELECT ProductID, 0 AS Received, 0 AS Inventory,
         0 AS Quarantine, 0 AS Dead, 0 AS Shipped, SUM(Qty)
         AS Adjustment
     FROM InventoryCounts
     WHERE Type = 6
     GROUP BY ProductID) TM ON PR.ProductID = TM.ProductID
GROUP BY PR.ProductID

View 3 - InventoryTotalsSummed:

SELECT ProductID, SUM(Inventory) AS Inventory, SUM(Quarantine)
    AS Quarantine
FROM (SELECT ProductID, Inventory, Quarantine
      FROM InventoryTotals
      UNION
      SELECT ProductID, Inventory, Quarantine
      FROM InventoryBaseCount) IB
GROUP BY ProductID

 
 
 

slow and overly complicated views

Post by Joe Celk » Sun, 10 Dec 2000 03:58:23


Quote:>> I'm trying to get to View 3 (InventoryTotalsSummed).  Right now I

rely on Views 1 and 2 in order to do so.  I get the correct results,
but its too slow.  Does anyone have any ideas of how I can clean this
up and make it more efficient?  I would greatly appreciate it.  Let me
know if you need more info. <<

DDL always helps.  We have no idea of constraints, keys, datatypes or
anything else about these tables.  For example, you have a lot of
UNIONs that look like they should be UNION ALL, but without the DDL, we
don't know a thing about the uniqueness required for the two operators
to be equivalent.

You have a column and a table both named inventory; this is legal, but
it is a very confusing design practice that needs to be fixed.

 Here is my best guesses at improvements:

CREATE VIEW InventoryCounts (product_id, type, qty)
AS
SELECT product_id, from_id, - SUM(quantity)
  FROM Inventory
 GROUP BY product_id, from_id
UNION ALL                              <== union removes dups
SELECT product_id, to_id, SUM(quantity)
  FROM Inventory
 GROUP BY product_id, to_id

However, if I understand the idea of the from_id and to_id both being
drawn from a common type code domain, we can change this to avoid the
UNION [ALL].  I also assume that from_id is never equal to_id.  This
VIEW should give you the one total for each product and type, not
separate positive and negative totals as you have now

CREATE VIEW InventoryCounts (product_id, type, qty)
AS
SELECT I1.product_id, T1.type,
       SUM(CASE WHEN T1.type = I1.from_id
                THEN - quantity
                WHEN T1.type = I1.to_id
                THEN quantity
                ELSE 0 END)
  FROM Inventory AS I1,
       TypeCodes AS T1   <== assuming this table exists
 GROUP BY product_id;

We can definitely improve the second view:

CREATE VIEW InventoryTotals
            (product_id, received, inventory,
             quarantine, dead, shipped, adjustment)
AS
SELECT PR.product_id,
       TM.received,
       TM.inventory,
       TM.quarantine,
       TM.dead,
       TM.shipped,
       TM.adjustment,
FROM Products AS PR
     LEFT OUTER JOIN
     (SELECT product_id,
             SUM(CASE WHEN type = 1 THEN qty ELSE 0) AS received,
             SUM(CASE WHEN type = 2 THEN qty ELSE 0) AS inventory,
             SUM(CASE WHEN type = 3 THEN qty ELSE 0) AS quarantine,
             SUM(CASE WHEN type = 4 THEN qty ELSE 0) AS dead,
             SUM(CASE WHEN type = 5 THEN qty ELSE 0) AS shipped,
             SUM(CASE WHEN type = 6 THEN qty ELSE 0) AS adjustment
        FROM InventoryCounts
       GROUP BY product_id) AS TM
      ON PR.product_id = TM.product_id
GROUP BY PR.product_id;

That trick with SUM() and CASE is very useful for replacing UNIONs.
The finally VIEW looks okay to me, without any further information on
the tables.

CREATE VIEW InventoryTotalsSummed
AS
SELECT product_id,
       SUM(inventory) AS inventory,
       SUM(quarantine) AS quarantine
  FROM (SELECT product_id, inventory, quarantine
          FROM InventoryTotals
        UNION ALL
        SELECT product_id, inventory, quarantine
          FROM InventoryBaseCount) AS IB
 GROUP BY product_id;

--CELKO--
Joe Celko, SQL Guru & DBA at Trilogy
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
which can be cut and pasted into Query Analyzer is appreciated.

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

slow and overly complicated views

Post by Peter Schwartzbaue » Sun, 10 Dec 2000 05:17:33


Do you ever have those moments when you feel like a door has been opened?
The best part of all of this is that I no longer needed the first two views,
but prior to your help I couldn't figure out how to join them into one.
With your insight, I was able to combine the three views into the one below.
Thank you very much for your help, and in the future I will try to provide
the info you recommended.

CREATE VIEW InventoryTotalsSummed
AS
SELECT ProductID, SUM(Inventory) AS Inventory, SUM(Quarantine)
    AS Quarantine
FROM (SELECT IC.ProductID, IC.Inventory, IC.Quarantine
      FROM (SELECT ProductID,
                SUM(CASE WHEN type = 2 THEN qty ELSE 0 END)
                 AS Inventory,
                SUM(CASE WHEN type = 3 THEN qty ELSE 0 END)
                 AS Quarantine
            FROM (SELECT IV.ProductID, IT.InvTypeID AS Type,
                      SUM(CASE WHEN IT.InvTypeID = IV.FromID
                       THEN - Quantity WHEN IT.InvTypeID = IV.ToID
                       THEN Quantity ELSE 0 END)
                      AS Qty
                  FROM Inventory AS IV,
                      InventoryTypes AS IT
                  GROUP BY IV.ProductID, IT.InvTypeID)
                AS QR
            GROUP BY ProductID) AS IC
      UNION
      SELECT ProductID, Inventory, Quarantine
      FROM InventoryBaseCount) AS IB
GROUP BY ProductID


Quote:

> >> I'm trying to get to View 3 (InventoryTotalsSummed).  Right now I
> rely on Views 1 and 2 in order to do so.  I get the correct results,
> but its too slow.  Does anyone have any ideas of how I can clean this
> up and make it more efficient?  I would greatly appreciate it.  Let me
> know if you need more info. <<

> DDL always helps.  We have no idea of constraints, keys, datatypes or
> anything else about these tables.  For example, you have a lot of
> UNIONs that look like they should be UNION ALL, but without the DDL, we
> don't know a thing about the uniqueness required for the two operators
> to be equivalent.

> You have a column and a table both named inventory; this is legal, but
> it is a very confusing design practice that needs to be fixed.

>  Here is my best guesses at improvements:

> CREATE VIEW InventoryCounts (product_id, type, qty)
> AS
> SELECT product_id, from_id, - SUM(quantity)
>   FROM Inventory
>  GROUP BY product_id, from_id
> UNION ALL                              <== union removes dups
> SELECT product_id, to_id, SUM(quantity)
>   FROM Inventory
>  GROUP BY product_id, to_id

> However, if I understand the idea of the from_id and to_id both being
> drawn from a common type code domain, we can change this to avoid the
> UNION [ALL].  I also assume that from_id is never equal to_id.  This
> VIEW should give you the one total for each product and type, not
> separate positive and negative totals as you have now

> CREATE VIEW InventoryCounts (product_id, type, qty)
> AS
> SELECT I1.product_id, T1.type,
>        SUM(CASE WHEN T1.type = I1.from_id
>                 THEN - quantity
>                 WHEN T1.type = I1.to_id
>                 THEN quantity
>                 ELSE 0 END)
>   FROM Inventory AS I1,
>        TypeCodes AS T1   <== assuming this table exists
>  GROUP BY product_id;

> We can definitely improve the second view:

> CREATE VIEW InventoryTotals
>             (product_id, received, inventory,
>              quarantine, dead, shipped, adjustment)
> AS
> SELECT PR.product_id,
>        TM.received,
>        TM.inventory,
>        TM.quarantine,
>        TM.dead,
>        TM.shipped,
>        TM.adjustment,
> FROM Products AS PR
>      LEFT OUTER JOIN
>      (SELECT product_id,
>              SUM(CASE WHEN type = 1 THEN qty ELSE 0) AS received,
>              SUM(CASE WHEN type = 2 THEN qty ELSE 0) AS inventory,
>              SUM(CASE WHEN type = 3 THEN qty ELSE 0) AS quarantine,
>              SUM(CASE WHEN type = 4 THEN qty ELSE 0) AS dead,
>              SUM(CASE WHEN type = 5 THEN qty ELSE 0) AS shipped,
>              SUM(CASE WHEN type = 6 THEN qty ELSE 0) AS adjustment
>         FROM InventoryCounts
>        GROUP BY product_id) AS TM
>       ON PR.product_id = TM.product_id
> GROUP BY PR.product_id;

> That trick with SUM() and CASE is very useful for replacing UNIONs.
> The finally VIEW looks okay to me, without any further information on
> the tables.

> CREATE VIEW InventoryTotalsSummed
> AS
> SELECT product_id,
>        SUM(inventory) AS inventory,
>        SUM(quarantine) AS quarantine
>   FROM (SELECT product_id, inventory, quarantine
>           FROM InventoryTotals
>         UNION ALL
>         SELECT product_id, inventory, quarantine
>           FROM InventoryBaseCount) AS IB
>  GROUP BY product_id;

> --CELKO--
> Joe Celko, SQL Guru & DBA at Trilogy
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
> which can be cut and pasted into Query Analyzer is appreciated.

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

1. more complicated view

I'm creating a rather complicated view... or at least trying to do
so...

We have a table like this:
Key     123
Key1    123
Key2    456
Key3    789
data1   A
data2   B
data3   C

that we wold like to view as such: (rotated view from above)
Key     data
123     A
465     B
789     C

further if the data looks like
Key     123_Z
Key1    123
Key2    456
Key3    789
data1   A
data2   B
data3   C

we want to see:
Key     data
123_Z   A
465_Z   B
789_Z   C

our data is actually keys1-6 and spans several tables...

I've created a view of the data of a union of selects.. but am getting
an error:
E_PS0F02 There is no more available memory. Try again later.
    (Wed Jun 18 13:42:32 2003)

E_SC022A Insufficient system/server resources to complete the current
    query. Consult the system administrator and/or error log.
    (Wed Jun 18 13:42:32 2003)

and in errlog.log:
TMC3_AST::[54713           , 20ac8f80]: Wed Jun 18 13:42:32 2003 E_PS0F02_MEMORY_FULL   There is no more available memory. Try again later.

(tmc3 is our server)
****************
The resulting view would have around 3000 rows... so it's not that big really.
anything i can tweak to get this view to work?
I've tried "set nojournaling" in the terminal monitor.

i might try rebooting the machine off hours and going from a fresh
startup with the minimum number of other processes running on the
machine...

perhaps i'm expecting too much from the view?

any ideas/advice?

--
be safe.
flip
^___^  Count to three. Make a wish. Close your eyes.
\^.^/  Wait. Scratch that, reverse it.
==u==  - apologies to Roald Dahl

2. Maryland - Oracle Internet Programmer

3. VFP 5.0 -- Creating a complicated view

4. Help: is Software AG's Esperant providing you with good service?

5. Overly Complex Simple Query

6. changing database using c api

7. Overly zealous security of schemas...

8. SUN Sparc 2000 with SUN Storage Array (30GB)`

9. Unique Index gets slower and slower and slower for inserts

10. Slow slow slow from an ASP

11. DB Getting Slower and Slower and Slower....

12. Slow, slow, slow search on VB6, ADO and SS7

13. Insert into SQL Server slow, slow, slow...