Comparing Result Set to Result Table

Comparing Result Set to Result Table

Post by stuvwxy.. » Wed, 03 Oct 2001 00:40:34



I have three tables:

CREATE TABLE History (
a int NOT NULL,
b char(1) NOT NULL,
c char(10) NOT NULL)

CREATE TABLE Worklist (
a int NOT NULL,
b char(1) NOT NULL)

CREATE TABLE Result(
a int NOT NULL,
b char(1) NOT NULL,
c char(10) NOT NULL)

The Primary Key on all three is (a,b)

Worklist contains a copy of all keys that are affected. Result
contains the results of some calculations. Not all members of Worklist
are represented in Results.

I need to

1) Delete from History where a record is in Worklist but not in Result
2) Amend History to reflect the contents of Result (UPDATE where
EXISTS, Else INSERT)

So three operations in all: DELETE, UPDATE, INSERT. There seems to be
many ways to achieve each of these operations. For example, For Step
2) I could use a further column in Result which indicates whether an
Update or Insert to History is needed. This could be updated
beforehand by joining Result to History (to find the UPDATEs). However
I get the feeling this extra step can be avoided.

Can anyone comment on some of the better ways to acheive what I
require(where "better" equals quicker, I guess).

 
 
 

Comparing Result Set to Result Table

Post by lindawi » Wed, 03 Oct 2001 12:33:51


G,

How about something like this:

CREATE TABLE Result(
    a int       NOT NULL,
    b char(1)   NOT NULL,
    c char(10)  NOT NULL
    constraint PK_Result primary key(a, b)
)
CREATE TABLE History (
    a int       NOT NULL,
    b char(1)   NOT NULL,
    c char(10)  NOT NULL
    constraint PK_History primary key(a, b)
)
CREATE TABLE Worklist (
    a int       NOT NULL,
    b char(1)   NOT NULL
    constraint PK_WorkList primary key(a, b)
)
insert Result   values (1, 'a', 'aaa')
insert Result   values (2, 'b', 'bbb')

insert History  values (1, 'a', 'zzz')
insert History  values (3, 'c', 'ccc')
insert History  values (4, 'd', 'ddd')

insert WorkList values (1, 'a')
insert WorkList values (2, 'b')
insert WorkList values (3, 'c')
insert WorkList values (4, 'd')

select * from Result
select * from History
select * from WorkList

delete  History
from    History
join    WorkList
on      History.a = WorkList.a
and     History.b = WorkList.b
where not exists (select * from Result
                  where  WorkList.a = Result.a
                  and    WorkList.b = Result.b)

update  History set c = Result.c
from    History join Result
on      History.a = Result.a
and     History.b = Result.b

insert  History (a, b, c)
select  a, b, c
from    Result
where not exists (select * from History
                  where  History.a = Result.a
                  and    History.b = Result.b)

drop table Result
drop table History
drop table WorkList

Linda


Quote:> I have three tables:

> CREATE TABLE History (
> a int NOT NULL,
> b char(1) NOT NULL,
> c char(10) NOT NULL)

> CREATE TABLE Worklist (
> a int NOT NULL,
> b char(1) NOT NULL)

> CREATE TABLE Result(
> a int NOT NULL,
> b char(1) NOT NULL,
> c char(10) NOT NULL)

> The Primary Key on all three is (a,b)

> Worklist contains a copy of all keys that are affected. Result
> contains the results of some calculations. Not all members of Worklist
> are represented in Results.

> I need to

> 1) Delete from History where a record is in Worklist but not in Result
> 2) Amend History to reflect the contents of Result (UPDATE where
> EXISTS, Else INSERT)

> So three operations in all: DELETE, UPDATE, INSERT. There seems to be
> many ways to achieve each of these operations. For example, For Step
> 2) I could use a further column in Result which indicates whether an
> Update or Insert to History is needed. This could be updated
> beforehand by joining Result to History (to find the UPDATEs). However
> I get the feeling this extra step can be avoided.

> Can anyone comment on some of the better ways to acheive what I
> require(where "better" equals quicker, I guess).


 
 
 

Comparing Result Set to Result Table

Post by stuvwxy.. » Wed, 03 Oct 2001 17:52:18


For Step 1), I am using the following Statement:

DELETE History
FROM History AS H
  INNER JOIN Worklist AS WL
    ON H.a = WL.a
    AND H.b = WL.b
  LEFT OUTER JOIN Result AS R
    ON (R.a = WL.a
    AND R.b = WL.b)
  WHERE R.a IS NULL

Can anyone comment on whether there is a better method?


> I have three tables:

> CREATE TABLE History (
> a int NOT NULL,
> b char(1) NOT NULL,
> c char(10) NOT NULL)

> CREATE TABLE Worklist (
> a int NOT NULL,
> b char(1) NOT NULL)

> CREATE TABLE Result(
> a int NOT NULL,
> b char(1) NOT NULL,
> c char(10) NOT NULL)

> The Primary Key on all three is (a,b)

> Worklist contains a copy of all keys that are affected. Result
> contains the results of some calculations. Not all members of Worklist
> are represented in Results.

> I need to

> 1) Delete from History where a record is in Worklist but not in Result
> 2) Amend History to reflect the contents of Result (UPDATE where
> EXISTS, Else INSERT)

> So three operations in all: DELETE, UPDATE, INSERT. There seems to be
> many ways to achieve each of these operations. For example, For Step
> 2) I could use a further column in Result which indicates whether an
> Update or Insert to History is needed. This could be updated
> beforehand by joining Result to History (to find the UPDATEs). However
> I get the feeling this extra step can be avoided.

> Can anyone comment on some of the better ways to acheive what I
> require(where "better" equals quicker, I guess).

 
 
 

1. Result Sets vs copying result sets into memory.

Does anyone see a problem with copying a result set into a collection
for display vs looping through the result set for display, so I can
return the connection back to the database little faster? I am using
connection pooling, so there is no connection overhead in creating a
new query.

The only performance issue I can see that each result set will take up
memory, but after it's displayed, that memory will be available for
something else, right?  I'm in a situation where up to 500 people can
hit a webpage for data at the same time.

I'm starting to run out of database connections on the web application
and a bigger database isn't an option for me. I can (and do) cache
persistant non-changing data, but have been told that calls for
changing and updated data must always come straight from the database.

Some of the result sets are over 10000 records long and there's no way
to split them. Through sql, I am able to return only rows X through
X+20, so I only return what I am going to show.

Any POSITIVE feedback would be appreciated.

Thanks in advance,

-- ABS

2. Trapping database errors?

3. how to compare result sets?

4. How to cache some records locally ?

5. comparing two result sets

6. Data Environment

7. OBDB Result size <> ISQL Result Set

8. ADO PROBLEM:NOT USEFUL FOR GRIDS!!!

9. Receiving results of a SP result set

10. Oracle Gateway - result set issues - results not matching data in DB2

11. [Querying A Subset of Results in a Result Set]

12. Compare 2 tables and return result.

13. Comparing Two Tables & Results