SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

Morpheus: Apr 18, 2008; My favourite little monsters

Okay so I've got this question in Oracle that I can't figure out an answer to. It's probably not too complicated, but I just have a lack of general knowledge about the stuff.

So I've got some tables, for example:

code:

   TABLE A
Province | App_Status 
-----------------------------
ON       |    Retry
PEI      |    Retry
ON       |    New
ON       |    Retry
MAN      |    Retry

  TABLE B
Province | Type
-----------------------------
ON       |  Big
MAN      |  Small
SASK     |  Medium
YKN      |  Small

Kind of weird to describe, but to actually describe it would be worse. So anyway, I just want to run a simple query where I can get the following info:

code:


PROV |   # of Retry APPs  | Number of Small
--------------------------------------------
ON   |   2                | 1
MAN  |   1                | 1
..

And so forth. So I'm counting the occurence of different values in different tables, then joining them into one table. So I can make a select statement for each of those columns (I think) but I need a way to put the statements together, like a union statement but with columns. Is there a way to do this? Am I being too convoluted with my explainin'?

# ? May 27, 2009 21:21

Adbot: ADBOT LOVES YOU

# ? May 29, 2024 02:49

Mongolian Queef: May 6, 2004

Triple Tech posted:

tunah, if the data is already normalized, what more can you do to store it? Not sure how else you'd go about it.

I just can't say that I have thought it through correctly.
Would this be completely unusable:
table articles
primary key id | release_id | ... subject, date, and so on for a single article

Wouldn't it completely kill the database to select every row with a specified release_id out of the 45 million rows? Can I make the release_id a key even though there will be several articles that share that same release_id?

Edit: What I mean to ask is, since a release is really a group of several articles, is it better to have all of the articles in a column for the release, or can it be laid out like above?

# ? May 27, 2009 21:31

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

morpheous:

select from table a province, count(*) group by province
select from table b province, count(*) group by province

And then just join the second statement onto the first, by province. It's like joining on to a table, but just one declared on the spot. The complexity comes in the following scenario

A: distinct provinces: A, B, C
B: distinct provinces: C, D, E, F

You have to full join the two tables together and then coalesce a display column for A.province and B.province. Or, make a CTE that's master list of all provinces (distinct A union distinct B) and then left join both tables against that master list (not against each other).

# ? May 27, 2009 21:36

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Hmm, not totally clear on what you're saying... A release is one to many with articles, in that a set of articles composes a release, right? So you're saying maybe... The metadata of an article doesn't matter? That there's some way of stitching the body of an article (a data point which is some sort of varchar or blob?) into one huge data point of a row in the release table?

I would say... If the metadata of articles or their atomicity is still important to you, then keep them in a seperate table and do the stitching elsewhere. Also, if you know that they will always be fetched by release id, then cluster index on that, article_id.

I don't know enough about databases and performance to comment on how large streams are stored in a DB. But if they're a property of an article, then technically I guess they'd be in the article table. Your release table should be extremely thin, since it's generated summary/meta-data mined from the articles, if I'm understanding your scenario correctly.

# ? May 27, 2009 21:41

Mongolian Queef: May 6, 2004

Triple Tech posted:

Hmm, not totally clear on what you're saying... A release is one to many with articles, in that a set of articles composes a release, right? So you're saying maybe... The metadata of an article doesn't matter? That there's some way of stitching the body of an article (a data point which is some sort of varchar or blob?) into one huge data point of a row in the release table?

I would say... If the metadata of articles or their atomicity is still important to you, then keep them in a seperate table and do the stitching elsewhere. Also, if you know that they will always be fetched by release id, then cluster index on that, article_id.

I don't know enough about databases and performance to comment on how large streams are stored in a DB. But if they're a property of an article, then technically I guess they'd be in the article table. Your release table should be extremely thin, since it's generated summary/meta-data mined from the articles, if I'm understanding your scenario correctly.

Yes, that's spot on. I think I may as well just try it out and see what happens.
The shortened subject can perhaps be hashed and stored as that instead, rather than using the actual shortened subject when selecting from releases.

My first try was to have a single table for releases. All articles would be stored in a column for their release. Selecting by "name" from that took quite some time, probably since the column with articles grew extremely large, so a lot of data was in between where the "name" was stored for rows.

# ? May 27, 2009 21:56

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Well, I mean, technically you could get around that by indexing, but having very wide tables precludes you from serendipitous page reads... Essentially the smaller the better.

I was sort of going to advocate storing the streams on the filesystems and having no article body data in the database, but again, I cannot comment on the performance and use of such data having never done it myself.

But since articles are so frequently talked about as first class objects, I think that alone merits them being in a seperate table. There's always growth and optimization available there. While, if you use a mega-release table, not only is it huge and bulky, it's denormalized, in a way, robbing you of that flexibility.

# ? May 27, 2009 22:31

MoNsTeR: Jun 29, 2002

Morpheus: A couple of options...

code:

select p.province
     , a.cnt as "# of Retry APPs"
     , b.cnt as "Number of Small"
  from (select province 
          from a
        union
        select province
          from b
       ) p
     , (select province
             , count(1) as cnt
          from a
         where app_status = 'Retry'
         group by province
       ) r
     , (select province
             , count(1) as cnt
          from b
         where type = 'Small'
         group by province
       ) s
 where p.province = r.province(+)
   and p.province = s.province(+)

or...

code:

select province
     , sum(case when tablename = 'a' then cnt else 0 end) as "# of Retry APPs"
     , sum(case when tablename = 'b' then cnt else 0 end) as "Number of Small"
  from (
        select province
             , 'a' as tablename
             , count(1) as cnt
          from a
         where app_status = 'Retry'
         group by province
        union all
        select province
             , 'b' as tablename
             , count(1) as cnt
          from b
         where type = 'Small'
         group by province
       )
 group by province

I marginally prefer the second approach. I've used this a few times to collapse dissimilar data into a single result set, for applications like real-time text reports formatted for Blackberry screens.

# ? May 27, 2009 22:36

Argon: Mar 16, 2001; The REAL Invisible Man

I don't have a specific SQL question, but I'm looking for a book recommendations. Specifically I'm looking to do development in C#. Anyone have a favorite or are they all pretty much the same?

# ? May 28, 2009 01:16

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Well SQL and C# are two different realms of development. And I think with the advent of LINQ based in the C# world, you won't really be using SQL much. Consider looking that up before you start learning SQL that you might not need (even though SQL is p tits)

# ? May 28, 2009 03:36

4001 Dicks In Mouth: Aug 1, 2003

Does anyone have experience with postgresql? I'm transferring a database from SQL Server to Postgres and I'm just trying to get the schema down now.

In SQL Server I would do something like

code:

CREATE TABLE tb_rltnshp_type
(
	rltnshp_type_cd  CHAR(3)  NOT NULL ,
	rltnshp_type_desc  VARCHAR(20)  NOT NULL  ,
	PRIMARY KEY (rltnshp_type_cd)
);


CREATE  UNIQUE INDEX XPKtb_rltnshp_type ON tb_rltnshp_type
(
	rltnshp_type_cd  ASC
);

and I think I should do this in postgres like

code:

CREATE TABLE tb_rltnshp_type
(
	rltnshp_type_cd  CHAR(3)  NOT NULL ,
	rltnshp_type_desc  VARCHAR(20)  NOT NULL  ,
	PRIMARY KEY (rltnshp_type_cd),
        UNIQUE (rltnshp_type_cd)
) WITH (OIDS=FALSE)
;

Someone please tell me I'm on the right track.

# ? May 28, 2009 04:29

Morpheus: Apr 18, 2008; My favourite little monsters

Oh man thanks. I finally put together the query that I needed, and it is a monster. Basically, I used MoNsTeR's first example. I needed to select from five different tables (and a table for the province list), each created with subqueries, and each making their own subquery to join tables and values and good lord. But it produces beautiful results, and I learned about the (+) thing today, which is delightful.

This is what one of my five subqueries looks like:

(select PROVINCE, count(*) as COUNT from (select INFO_TABLE.PROV as PROVINCE, COLLECTION_TABLE.TYPE AS APP_TYPE from INFO_TABLE, COLLECTION_TABLE where INFO_TABLE.ID = COLLECTION_TABLE.ID) where APP_TYPE like '%New%' group by PROVINCE) NEW_APPS

Ah, the sweet programming high of getting a problem solved.

# ? May 28, 2009 16:59

ilikechapstick: Jun 17, 2007; very, very notorious.

nevermind.

ilikechapstick fucked around with this message at 15:59 on May 29, 2009

# ? May 29, 2009 15:52

4001 Dicks In Mouth: Aug 1, 2003

ilikechapstick posted:

Having trouble with this question here...maybe I'm just not understanding it.

Write a SQL statement to return the emails that show up more than once in a table with the following schema?
code:
Table:  Users
Email    Varchar(100)
City    Varchar(100)
State    Char(2)

Maybe something like

select email, count(*)
from users
group by 1
having count(*) > 1;

# ? May 29, 2009 15:59

Land Mime: Jul 13, 2007; Love never blows up and gets killed

Numbnuts posted:

code:

CREATE TABLE tb_rltnshp_type
(
	rltnshp_type_cd  CHAR(3)  NOT NULL ,
	rltnshp_type_desc  VARCHAR(20)  NOT NULL  ,
	PRIMARY KEY (rltnshp_type_cd),
        UNIQUE (rltnshp_type_cd)
) WITH (OIDS=FALSE)
;

Someone please tell me I'm on the right track.

You're on the right track, but saying PRIMARY KEY is the same as saying UNIQUE and NOT NULL. What version of postgres are you using? I think WITH (OIDS=FALSE) is the default for more recent versions.

code:

CREATE TABLE tb_rltnshp_type
(
	rltnshp_type_cd  CHAR(3)  PRIMARY KEY ,
	rltnshp_type_desc  VARCHAR(20)  NOT NULL
)
;

# ? May 31, 2009 17:20

4001 Dicks In Mouth: Aug 1, 2003

Land Mime posted:

You're on the right track, but saying PRIMARY KEY is the same as saying UNIQUE and NOT NULL. What version of postgres are you using? I think WITH (OIDS=FALSE) is the default for more recent versions.
code:
CREATE TABLE tb_rltnshp_type
(
	rltnshp_type_cd  CHAR(3)  PRIMARY KEY ,
	rltnshp_type_desc  VARCHAR(20)  NOT NULL
)
;

Thanks, I'm using 8.3.7. After I created a table pgAdmin gave me a SQL pane that shows how they would have created the table and it's pretty much exactly what you posted there. Now I just need to finish converting all the stored procedures

# ? May 31, 2009 22:40

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

I asked a question about this problem a few pages ago and got a response from Triple Tech, but didn't ask any of the followup questions that have occurred to me since (mostly since I wrote it off as something I would deal with later on). I will restate the problem for convenience.

My database (MySQL) stores information about play-by-email board games (one particular board game) that people are playing against each other. Each game allows for up to 4 players, and there are 5 colours that the users can play as (allowing for future functionality that is anticipated to be necessary). When a user starts or joins a game, he chooses what colour he will be. I have a table Game and a table User. The table Game, in a poor piece of design, carries all of the information about who is playing in the game, in 5 columns: UserR, UserY, UserG, UserP, UserH (for Red, Yellow, Green, Purple and Grey). (These columns are NULL if there is nobody playing as the corresponding colour.) When running database queries, it is presently necessary to perform 5 LEFT JOINs, as in

code:

SELECT Game.(some columns),
       UserR.(some columns),
       UserY.(some columns),
       UserG.(some columns),
       UserP.(some columns),
       UserH.(some columns)
FROM Game LEFT JOIN User AS UserR ON Game.UserR = User.UserID
          LEFT JOIN User AS UserY ON Game.UserY = User.UserID
          LEFT JOIN User AS UserG ON Game.UserG = User.UserID
          LEFT JOIN User AS UserP ON Game.UserP = User.UserID
          LEFT JOIN User AS UserH ON Game.UserH = User.UserID
WHERE Game.GameID = x

NB. Here I write "UserR.(some columns)" as shorthand for "UserR.column1, userR.column2, ..."; not because I intend to write "UserR.(column1, column2, ...)" or suchlike.

Sometimes even 6 LEFT JOINs are needed, as there is a separate column for "game creator" and it is sometimes desirable to have this user's information joined as UserC. (The game's creator is usually one of the players, but need not always be.)

This is evidently not good design and should be restructured by DROPping the columns UserR, UserY, UserG, UserP and UserH and implementing a new table, GamePlayer, that has three columns: Game, User and Colour. Then, the above query becomes

code:

SELECT GamePlayer.Colour,
       Game.(some columns),
       User.(some columns)
FROM GamePlayer LEFT JOIN Game ON GamePlayer.Game = Game.GameID
                LEFT JOIN User ON GamePlayer.User = User.UserID
WHERE GamePlayer.Game = x

When finding information on (say) the games being played by a particular user, the following query may be used:

code:

SELECT GamePlayer.Colour, Game.(some columns)
FROM GamePlayer LEFT JOIN Game ON GamePlayer.Game = Game.GameID
WHERE GamePlayer.User = x

One question I have is this: Sometimes I want to get information about all the games a particular user is playing, and also information about the people with whom he is playing those games. For example, I might want to generate an HTML table showing details of all his games, to include the name of the player whose turn it is right now as well as a link to that player's list of games. For this I need the user ID and user name of that user, which necessitates fetching his name (either that or adding a redundant "user name" column to the GamePlayer table).

Suppose I want to fetch information about the games that user x is playing, the creators of those games, and the players who are playing in the games. Is the following query the best way of doing this?

code:

SELECT GamePlayer.Colour,
       Game.(some columns),
       GamePlayerX.Colour,
       User.(some columns),
       UserC.(some columns)
FROM GamePlayer LEFT JOIN Game ON GamePlayer.Game = Game.GameID
                INNER JOIN GamePlayer AS GamePlayerX ON Game.GameID = GamePlayerX.Game
                LEFT JOIN User ON GamePlayerX.User = User.UserID
                LEFT JOIN User AS UserC ON Game.Creator = UserC.UserID
WHERE GamePlayer.User = x
ORDER BY Game.GameID

This query would, if I understand correctly, return more than one row per game; it would, for each game, return n rows where n is the number of players in the game. These rows would be identical everywhere except in the column from GamePlayerX and the columns from User. The PHP program that receives the data will then have to go through the rows checking at each new row whether the game being reported on is a new one, or the same as the previous row and detailing a new player. Is there a better way to do this?

# ? Jun 1, 2009 13:48

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Common problem, at least for me. You could also seperate out the two queries (game per user, users per game) and loop through the list of games individually. The overhead for the entire process goes up, but it's more functional and logical from a coder's point of view.

# ? Jun 1, 2009 14:21

Atoramos: Aug 31, 2003; Jim's now a Blind Cave Salamander!

Is it possible to gain information from 2 different tables in 1 column?

Example:

You want to pull reviews for games into a table, where you have the game's keys already stored, such as this:

Act101031 48
Act030122 78
Pla012394 92
Pla302144 67

However, the ratings for Pla* games are in Table Pla_Score and the ratings for Act* games are in Act_Score. All you have is the game number to act as a key.

# ? Jun 1, 2009 16:24

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Look up UNION as a way to stack SELECT statements together, vertically. Keep in mind that it may automatically DISTINCT the results.

# ? Jun 1, 2009 16:33

Atoramos: Aug 31, 2003; Jim's now a Blind Cave Salamander!

Triple Tech posted:

Look up UNION as a way to stack SELECT statements together, vertically. Keep in mind that it may automatically DISTINCT the results.

This looks good, but I'm not sure how to actually tie this together in a statement. Here's my problem, a bit more fleshed out:

code:

Select A.GAME_NUMBER as 'GAME_ID',
CASE
WHEN LEFT(A.GAME_NUMBER, 3) IN ('ACT', 'ADV') THEN (Select GAME_RATE From ADV_LIST as B where A.GAME_NUMBER = B.GAME_NUMBER) as 'Game Rank'
ELSE (Select GAME_RATE From PLA_LIST as C where A.GAME_NUMBER = C.GAME_NUMBER) as 'Game Rank'
END
From GAMELIST as A
WHERE LEFT(A.GAME_NUMBER, 3) IN ('PLA', 'ACT', 'ADV')

Is it possible to union PLA_LIST and ADV_LIST as a temp table D, and then match up to that? I've never done something like that before but if it's possible that would be great.

# ? Jun 1, 2009 16:43

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Oh I see what your thing is now... You're doing inheritance and the part of the key that dictates the subtable is mashed into that one key... Ideally that key in the GAMELIST table would be a two-column composite key. So consider that.(pseudo) select g.game_number, a.rank from gamelist g left join adv_list a on a.game_number = g.game_number where g.game_number looks like act/adv UNION ALL <-- dis ting right hurr select g.game_number, p.rank from gamelist g left join adv_list p on p.game_number = g.game_number where g.game_number looks like pla

Inheritance... Never pretty...

# ? Jun 1, 2009 17:05

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Triple Tech posted:

Common problem, at least for me. You could also seperate out the two queries (game per user, users per game) and loop through the list of games individually. The overhead for the entire process goes up, but it's more functional and logical from a coder's point of view.

So as an alternative to doing that big complicated query I posted, which of the following do you think is better...?

(Ignore please my failure to use a database framework, this is an entirely separate issue that I may address at another time)

1) Query to get all the games user x is playing in ...

code:

SELECT GamePlayer.Game,
       GamePlayer.Colour,
       Game.(some columns),
       User.(some columns)
FROM GamePlayer LEFT JOIN Game ON GamePlayer.Game = Game.GameID
                LEFT JOIN User ON Game.Creator = User.UserID
WHERE GamePlayer.User = x
ORDER BY GamePlayer.Game

... followed by PHP code similar to this ...

php:

<?
$GamesArray = array();
while ( $row = mysqli_fetch_assoc($QueryResult) ) {
    $GamesArray[] = $row;
}
$NumGames = count($GamesArray);
$GameIDList = array();
for ($i=0;$i<$NumGames;$i++) {
    $GameIDList[] = $GamesArray[$i][Game];
}
$GameIDListString = implode(', ',$GameIDList);
?>

... followed by the following query to get information about the players of these games ...

code:

SELECT GamePlayer.Game, GamePlayer.Colour, User.(come columns)
FROM GamePlayer LEFT JOIN User ON GamePlayer.User = User.UserID
WHERE GamePlayer.Game IN ($GameIDListString)
ORDER BY GamePlayer.Game

... and then the following PHP code ...

php:

<?
$Counter = 0;
$ReadPlayerYet = 0;
while ( $row = mysqli_fetch_assoc($QueryResult) ) {
    if ( $row[Game] != $GamesArray[$Counter][Game] ) {
        if ( $ReadPlayerYet ) {
            $Counter++;
            if ( $row[Game] != $GamesArray[$Counter][Game] ) {
                $error = 1;
            } else {
                $error = 0;
            }
        } else {
            $error = 1;
        }
        if ( $error ) {
            // handle broken games that have no players
        }
    } else {
        $ReadPlayerYet = 1;
    }
    foreach ( $row as $key => $value ) {
        $GamesArray[$Counter][$key][$row[Colour]] = $value;
    }
}
?>

... after which I pass to whatever function outputs the HTML table.

2) Query to get all the games user x is playing in, as in 1) ...

code:

SELECT GamePlayer.Game,
       GamePlayer.Colour,
       Game.(some columns),
       User.(some columns)
FROM GamePlayer LEFT JOIN Game ON GamePlayer.Game = Game.GameID
                LEFT JOIN User ON Game.Creator = User.UserID
WHERE GamePlayer.User = x
ORDER BY GamePlayer.Game

... then use PHP code similar to this to loop through all of the games and query to find out who is playing in them?

php:

<?
$GamesArray = array();
while ( $row = mysqli_fetch_assoc($QueryResult) ) {
    $QueryResultX = mysqli_query($link,
                    'SELECT GamePlayer.Colour,
                            User.(some columns)
                     FROM GamePlayer LEFT JOIN User
                                     ON GamePlayer.User = User.UserID
                     WHERE GamePlayer.Game = '.$row[Game]);
    while ( $rowX = mysqli_fetch_assoc($QueryResultX) ) {
        foreach ( $rowX as $key => $value ) {
            $row[$key][$rowX[Colour]] = $value;
        }
    }
    $GamesArray[] = $row;
}
?>

And then pass to the function that generates my HTML table.

What's the difference in overhead likely to be between doing these things? 1) looks the tidiest, in that it involves doing two queries of limited complexity instead of n+1 - what's the difference in overhead likely to be between doing that and just using a single query to get all the data, as at the end of my previous post?

I guess I'm asking opinions, which way of doing it is "least bad"?

What happens if I'm looking to get information about a single game (to include information about its creator and all of its players)? I imagine I'd use the following:

code:

SELECT GamePlayer.Colour,
       Game.(some columns),
       User.(some columns),
       UserC.(some columns)
FROM GamePlayer LEFT JOIN Game ON GamePlayer.Game = Game.GameID
                LEFT JOIN User ON GamePlayer.User = User.UserID
                LEFT JOIN User AS UserC ON Game.Creator = UserC.UserID
WHERE GamePlayer.Game = x

I guess there's no reason that should be overly taxing for the database. But perhaps I should instead use two queries:

code:

SELECT Game.(some columns), User.(some columns)
FROM Game LEFT JOIN User ON Game.Creator = User.UserID
WHERE Game.GameID = x

(expect to receive 0 or 1 rows back from this)

code:

SELECT GamePlayer.Colour, User.(some columns)
FROM GamePlayer LEFT JOIN User ON GamePlayer.User = User.UserID
WHERE GamePlayer.Game = x

(expect multiple rows back from this)

# ? Jun 1, 2009 18:04

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Well, this is an implementation detail/engineering concern. I think you should do the n + 1 version just to get it out of the way since it's simpler and more obvious. The other one, while it minimizes the amount of queries, makes your code more complex. Just solve your workflow first and worry about the queries later, I say.

Just to be clear, we're choosing one of

code:

Player 1
|
+-- Game A
+-- Game B
+-- Game C

vs

Player 1  |  Game A
Player 1  |  Game B
Player 1  |  Game C

# ? Jun 1, 2009 19:48

Bad Titty Puker: Nov 3, 2007; Soiled Meat

This code is for entertainment purposes only. This demonstrates what happens when prolonged sleep deprivation meets stimulants of various forms. Although I like the idea of using a query that returns one row per seat, instead of returning everything in one denormalized row, thus avoiding unions, left outer joins, self joins etc. I also like the idea of a Seats table (game, player, seat)

Something like this?

code:

CREATE TABLE Games(
  Game_ID,
  Red_Seat_ID, Blue_Seat_ID, Yellow_Seat_ID, /* etc */
 /* This is normalized */
);


CREATE TABLE Players(
  Player_ID,
...
);

CREATE TABLE SeatColors(
  Seat_Color
  PRIMARY KEY(Seat_Color)
  CHECK( Seat_Color IN ( Red, Blue, Green, /* ... */ ) )
);
/* SeatColors table has one row per color */


CREATE TABLE GameSeats(
  GameID,
  SeatID,
  Seat_Color
)

CREATE TABLE PlayerSeats(
  Game_ID,
  Seat_ID,
  Player_ID

  PRIMARY KEY(Game_ID, Player_ID),
  /* This ensures player may occupy one 
     or fewer seats per game. */

  FOREIGN KEY(Game_ID) REFERENCES Games(Game_ID),
  FOREIGN KEY(Seat_ID) REFERENCES Seats(Seat_ID),
  FOREIGN KEY(Player_ID) REFERENCES Players(Player_ID)
)
 
   
/* 1). Get player info for all the players in a given game
  SELECT
    Game.GameName,
    Players.FirstName,
    Players.LastName,
    SeatColors.Color
  FROM
    Players
  JOIN   
    PlayerSeats
  JOIN
    GameSeats
  JOIN 
    SeatColors
  WHERE
    Games.Game_ID = /* game id */

/* Returns one row per seat in a game. */
/* Doesn't need to use LEFT OUTER JOINS. */

    

/* 2). Query to get all the games user x is playing in ...*/

SELECT
  Games.GameName, /* would be nice to design a key instead of using 
                     autoincrementing numbers */
  /* etc. */
FROM
  Games
JOIN
  PlayerSeats ON PlayerSeats.GameID = Games.GameID
WHERE  
  PlayerSeats.Player = /* user x */

Bad Titty Puker fucked around with this message at 20:03 on Jun 1, 2009

# ? Jun 1, 2009 19:54

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

OK, well, thank you both for your suggestions! I will give it some more thought which approach to take.

# ? Jun 1, 2009 22:28

8ender: Sep 24, 2003; clown is watching you sleep

This is long so please bear with. I need some advice to try to ease a problem we have with our Oracle database. Right now we have a database schema that is organized roughly like this:

Organization
Site
Service
ND_Service
Service_Hours
ND_Service_Hours
service_status
ND_service_status
service_requirements
ND_service_requirements
etc.

Every row in the site table has an organization_id, every row in the two service tables has a site_id. The problem is that our schema evolved oddly and we've ended up with those two sets of service tables to represent data for two different service types. Both sets of tables are almost identical except for a few differences here and there.

This worked alright until we needed to get data out of both sets of service tables at once. To pull this off we needed to do a lot of union SQL queries. The performance and complexity penalties from doing this sucks. I've tried to pitch merging the two tables but I'm not getting buy in because of how much software would need to be refactored.

Last week I pitched the idea of trying out some materialized views to our DBA. We set up some materialized views that merge the two sets of tables leaving us with something like this:

mv_service
mv_service_hours
mv_service_status
mv_service_requirements
etc.

These views are great but I'm finding that we're taking a nasty hit in performance when I join say the mv_service with mv_service_hours because its doing full table scans on each materialized view.

I'm at a loss at how to ease this problem. I think at this point we may just use some regular old views to at least help us with the complexity issue but that doesn't help us on the performance front. Any ideas?

# ? Jun 3, 2009 14:53

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Is your data indexed properly?

# ? Jun 3, 2009 15:08

8ender: Sep 24, 2003; clown is watching you sleep

Triple Tech posted:

Is your data indexed properly?

Yeah the tables themselves are indexed properly. The problem is that once the two sets of tables are a materialized view I'm unable to join between the different materialized views without lots of full table scans.

# ? Jun 3, 2009 15:11

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Is your view indexed? I don't know what else to say other than that you shouldn't ever be getting full table scans. That and I don't have any experience with materialized views, so I'm not sure what the implementation details/limits are.

# ? Jun 3, 2009 15:41

var1ety: Jul 26, 2004

8ender posted:

Yeah the tables themselves are indexed properly. The problem is that once the two sets of tables are a materialized view I'm unable to join between the different materialized views without lots of full table scans.

You will need to create indexes and constraints (such as a primary key) on the materialized views consistent with the queries you are running against them.

# ? Jun 3, 2009 15:42

8ender: Sep 24, 2003; clown is watching you sleep

var1ety posted:

You will need to create indexes and constraints (such as a primary key) on the materialized views consistent with the queries you are running against them.

I wasn't aware this was possible with materialized views.

# ? Jun 3, 2009 15:51

var1ety: Jul 26, 2004

8ender posted:

I wasn't aware this was possible with materialized views.

You can find more information on their feature set in the Data Warehousing Guide and in the SQL Reference, but for the most part you can think of them as tables.

Data Warehousing Guide - 8 Basic Materialized Views
http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/basicmv.htm#i1007299

SQL Reference - CREATE MATERIALIZED VIEW
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6002.htm#i2063793

SQL Reference - CREATE MATERIALIZED VIEW LOG
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6003.htm#i2064649

If the underlying data changes with any regularity and response time is sensitive you will want to read about fast refreshing, which uses materialized view logs on the base tables to intelligently update only rows which have changed since the last rebuild.

Data Warehousing Guide - 8 Basic Materialized Views - General Restrictions on Fast Refresh
http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/basicmv.htm#sthref534

# ? Jun 3, 2009 16:19

8ender: Sep 24, 2003; clown is watching you sleep

var1ety posted:

If the underlying data changes with any regularity and response time is sensitive you will want to read about fast refreshing, which uses materialized view logs on the base tables to intelligently update only rows which have changed since the last rebuild.

We've got fast refreshing setup and running well right now. If I can get our DBA to put some indexes and primary keys on these views then we might be in business. Now the only thing left to sort out is making Toplink work well with the views.

# ? Jun 3, 2009 16:34

MoNsTeR: Jun 29, 2002

Fight to merge the tables now before it's too late. The difficulty, perceived and actual, of refactoring is only going to get worse as the application and schema continue to evolve.

# ? Jun 3, 2009 21:04

8ender: Sep 24, 2003; clown is watching you sleep

MoNsTeR posted:

Fight to merge the tables now before it's too late. The difficulty, perceived and actual, of refactoring is only going to get worse as the application and schema continue to evolve.

Believe me, I know. I've fought the good fight and lost on that one unfortunately.

I've gotten the indexes setup on the materialized views and thats solved the performance problem but now I have another. There doesn't seem to be a primary key defined in any of these views. It should be the column SERVICE_ID, which both the table used to create the view have, but its not being carried into the view.

Unfortunately this is making Toplink gently caress up when I try to create objects from tables and include the new views. Any ideas?

# ? Jun 4, 2009 15:47

var1ety: Jul 26, 2004

8ender posted:

Believe me, I know. I've fought the good fight and lost on that one unfortunately.

I've gotten the indexes setup on the materialized views and thats solved the performance problem but now I have another. There doesn't seem to be a primary key defined in any of these views. It should be the column SERVICE_ID, which both the table used to create the view have, but its not being carried into the view.

Unfortunately this is making Toplink gently caress up when I try to create objects from tables and include the new views. Any ideas?

Manually add a primary key constraint on the materialized views.

# ? Jun 4, 2009 16:47

Squashy Nipples: Aug 18, 2007

I've got a Table that gets updated monthly, usually on the 5th or 6th business day after month-end. I need to be able to do a quick check to make sure that the required data is there before the rest of code executes.

(bear in mind that this is a pretty big table, with several thousand rows per month, for the last 100-150 months.)

What is the fastest way to do this?

code:

SELECT COUNT(*) FROM MSPERFRM
 WHERE MSP_MTH_STR_DT = '05/01/2009'

Logic: if the returned value is greater then zero ( > 0 ), then data present.

code:

SELECT * FROM MSPERFRM
 WHERE MSP_MTH_STR_DT = '05/01/2009'

Logic: if the EOF Property is False, then data present.

Maybe it will depend on wether the cursor/recordset is Client-side or Server-Side?

# ? Jun 4, 2009 20:20

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

I don't know what language you're using but a count would be faster than selecting an entire set of records. Also, make sure you index on the field you're partitioning on (which seems to be the date). Even faster would be a clustered index, but that depends entirely on how else the table is used.

# ? Jun 4, 2009 20:25

Squashy Nipples: Aug 18, 2007

Triple Tech posted:

I don't know what language you're using

Ooops, this is DB2. I only work with DB2 and JET.

Triple Tech posted:

a count would be faster than selecting an entire set of records.

Ok, thats what I thought, but I was getting confused by trying to think through client/server (I do almost everything with a static, client-side cursor).

Triple Tech posted:

Even faster would be a clustered index, but that depends entirely on how else the table is used.

Eh, I'm just an analyst, I have no powers over that stuff. I can't even create/update Views. If I want to make a change like that, the process is loving byzantine.

Anyway, thanks for the help.

Squashy Nipples fucked around with this message at 00:46 on Jun 5, 2009

# ? Jun 4, 2009 20:39

Adbot: ADBOT LOVES YOU

# ? May 29, 2024 02:49

var1ety: Jul 26, 2004

If you do not need a count it's better to just check for a single row meeting your criteria. Two mostly equivalent Oracle versions follow.

code:

select count(*)
  from dual
 where exists (select null
          from foo_test
         where dte = to_date('2018-10-28', 'YYYY-MM-DD'))

code:

select count(*)
  from foo_test
 where dte = to_date('2018-10-28', 'YYYY-MM-DD')
 and rownum <= 1

The queries will return exactly one row containing a 0 or 1.

# ? Jun 4, 2009 20:48

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »