January 16, 2009 at 1:59 pm
Our software/database vendor permits us to add custom tables and views with the restriction that they them must start with our three letter "code" they assigned to us. Wartburg College was assigned 'wrt'; other clients have different codes. They will never add a new table or view that starts with any of these three letter codes. That way, any future updates won't inadvertantly mess up existing ones.
Julie
January 16, 2009 at 9:20 pm
Heh... it'll be interesting to see what sort of panic they go into if they ever go over 17576 customers. Thanks for the great explanation.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2009 at 8:42 am
Generate Data into a table
without using a loop
Hello all
I have been asked by a co-worker for an answer to the following question.
"How can I efficiently generate a set of around 40 million records with using loops or recursion?"
Tally table was my immediate thought. But I'm stuck.
So I'm turning to the much better minds here for an answer so I can show off on Monday 😉
More on the problem. Stephen is a member of a brass band. Apparently, brass bands compete against each other and are awarded points according to the result. These are compiled into a league table. The way leagues work in the brass band world is a little odd, taking into account last year’s final league standing into account when working out this year’s position. So what he wants is a set of all possible combinations for a league consisting of 12 teams. Using the permut function in Excel ( =permut(12,12)) returns ~480 million possible combinations. Which is a big number!
An example might be useful here
Imagine a league of 3 teams; Atherton, Belper and Chesterfield.
All possible outcomes would be:
Atherton
Belper
Chesterfield
Chesterfield
Belper
Atherton
Belper
Chesterfield
Atherton
Atherton
Chesterfield
Belper
Chesterfield
Atherton
Belper
Belper
Atherton
Chesterfield
So a league of 3 has 6 possible outcomes. 12 has ~480 million! Or possibly ~480 million/12, which is ~40 million. I'm not sure to be honest!
Anyway, imagine we have a team’s table
Create Table Team (TeamId int not null Identity(1,1) Primary Key,
TeamName Varchar(50) not Null)
Insert Team (TeamName)
Select 'Atherton' union
Select 'Belper' union
Select 'Chesterfield'
--union --commented out until I get the select to work...
--Select 'Derby' union
--Select 'Edale' union
--Select 'Froggatt' union
--Select 'Glossop' union
--Select 'Heanor' union
--Select 'Ilkeston' union
---- no town in Derbyshire begins with a J
--Select 'Kilburn' union
--Select 'Long Eaton' union
--Select 'Matlock'
What I want is a query that will generate the result sets as posted above, possibly using a tally or self join?
Create a table to hold the results
create table possibleOutcomes (outcomeId int not null identity(1,1) Primary Key,
setId int not null, -- this will be unique to each 'set' of results.
pos01TeamId int not null,
pos02TeamId int not null,
pos03TeamId int not null
)--,
--pos04TeamId int not null,
--pos05TeamId int not null,
--pos06TeamId int not null,
--pos07TeamId int not null,
--pos08TeamId int not null,
--pos09TeamId int not null,
--pos10TeamId int not null,
--pos11TeamId int not null,
--pos12TeamId int not null,
--pos2TeamId)
This is where I get stuck. In fact, I haven't a clue how to approach this!
something along the lines of using column n from tally to populate setId and a self referencing join using not exists? My head hurts.
Any thoughts/suggestions for an approach appreciated.
Dave J
February 14, 2009 at 10:16 am
Wait a minute... is the goal to have every team play every other team twice (once at home and once away)? If so, just go get a bowling schedule for 12 teams. Use the even numbered lanes as "home" and the odd numbered lanes as "away". Play through two sets... the second set on the schedule is the reverse of the first.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2009 at 12:06 pm
David Jackson (2/14/2009)
What I want is a query that will generate the result sets as posted above, possibly using a tally or self join?
Hi David,
Interesting question. And no, the answer is not a tally table. It is, as you already suspected, a self join. Or rather twelve self joins. And because they are basically cross joins (don't be fooled by the inner join syntax I used below - the join predicate is in inequality one so it is in fact a slightly restricted cross join), you will get a huge number of results. For the full twelve teams, you'll get the factorial of twelve (12! which is indeed almost 480 million) rows. The sample I give below uses just five teams to return 120 (5!) rows).
CREATE TABLE Team
(TeamID int NOT NULL IDENTITY PRIMARY KEY,
TeamName varchar(50) NOT NULL UNIQUE
);
INSERT INTO Team (TeamName)
SELECT 'Atherton' UNION ALL
SELECT 'Belper' UNION ALL
SELECT 'Chesterfield' UNION ALL
SELECT 'Derby' UNION ALL
SELECT 'Edale';
CREATE TABLE PossibleOutcomes
(SetNo int NOT NULL IDENTITY PRIMARY KEY,
Pos01TeamId int NOT NULL,
Pos02TeamId int NOT NULL,
Pos03TeamId int NOT NULL,
Pos04TeamId int NOT NULL,
Pos05TeamId int NOT NULL
-- Ommitting the foreign key constraints for brevity
);
INSERT INTO PossibleOutcomes
(Pos01TeamId, Pos02TeamId, Pos03TeamId, Pos04TeamId, Pos05TeamId)
SELECT p1.TeamID, p2.TeamID, p3.TeamID, p4.TeamID, p5.TeamID
FROM Team AS p1
INNER JOIN Team AS p2
ON p2.TeamID <> p1.TeamID
INNER JOIN Team AS p3
ON p3.TeamID NOT IN (p1.TeamID, p2.TeamID)
INNER JOIN Team AS p4
ON p4.TeamID NOT IN (p1.TeamID, p2.TeamID, p3.TeamID)
INNER JOIN Team AS p5
ON p5.TeamID NOT IN (p1.TeamID, p2.TeamID, p3.TeamID, p4.TeamID);
SELECT * FROM PossibleOutcomes;
Note that I do really wonder WHY anyone would want to populate a table with all 480 million permutations. Note also that the code will probably run for quite a while and use excessive amounts of space in both tempdb and in the log file. If you don't have that available, then using simple procedural loop logic might be a better idea in this case. (Even though that would probably run for even longer). There is no way that you can ever expect to generate a result set of this size in a very short time. 😉
February 16, 2009 at 4:07 am
Hugo Kornelis (2/14/2009)
... no, the answer is not a tally table. It is, as you already suspected, a self join. Or rather twelve self joins.
Aha! Many thanks Hugo. I'll show him this code tomorrow. (I'm off to a job interview today).
Note that I do really wonder WHY anyone would want to populate a table with all 480 million permutations.
Ours is not to reason why, but to do and die... :Whistling: 🙂
Once more, many thanks. That's yet another beer I owe you.
Dave J
February 16, 2009 at 10:42 am
David Jackson (2/16/2009)
Ours is not to reason why, but to do and die... :Whistling: 🙂
Heh... I really hate it when THAT happens. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
April 29, 2009 at 2:10 pm
Why does it seem that everything is three to five times harder to do in Oracle than it is in SQL Server? I'm trying to figure out how to apply this tally table in what should be a very simple problem. I'm working on a database solution for a steel furnace that manages inventory, and the customer has an Oracle backend. I want to move a group of Coils (Coil ID is the primary key) from inventory to the furnace's charge lineup (schedule).
I collect a CSV string of all the coil IDs in my Drag and Drop event, and pass them to a stored procedure. Ok, splitting the CSV into separate IDs was relatively easy (meaning that part was only three times harder in Oracle -- turns out you can't view variable output in a select statement, so I actually had to create a dummy table to populate just to see my results). Now I want to use that list to update the inventory's available flag from 'Y' to 'N'.
I could create what's called a nested table (a one-dimensional collection) and use that in an inner-join where the inventory Coil Id equals the nested table's coil id, but Oracle won't allow me to use an inner join on an Update statement. I haven't completely given up on this method, though, since I might be able to trick Oracle into doing the same inside of a where clause.
But it seems like I should be able to include the results of the parsing directly within an update statement. Of course, my head keeps telling me that to make that work, the parsing routine (which contains a select statement), would have to be the object of the "IN" predicate, and I keep thinking that an IN predicate is rather a lot like RBAR, not so?
April 29, 2009 at 2:37 pm
rboggess (4/29/2009)
Why does it seem that everything is three to five times harder to do in Oracle than it is in SQL Server?
Simple math, looks like you've just solved the 'theorem of RDBMS':
SELECT convert(decimal(4,2),len('Oracle')) / convert(decimal(4,2),len('SQL Server')),
convert(decimal(4,2),3)/convert(decimal(4,2),5)
😀
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
April 29, 2009 at 2:40 pm
Why does it seem that everything is three to five times harder to do in Oracle than it is in SQL Server?
Heh... it's designed that way. Seriously... it's meant to more strictly enforce ACID and a whole bunch of other things.
Personally, I like SQL Server much better than Oracle and will happily give up things like ConnectBY and even MERGE just so I don't have to work with it. You should try "overlaying" a variable in Oracle sometime...
--Jeff Moden
Change is inevitable... Change for the better is not.
April 29, 2009 at 2:45 pm
rboggess (4/29/2009)
Why does it seem that everything is three to five times harder to do in Oracle than it is in SQL Server? I'm trying to figure out how to apply this tally table in what should be a very simple problem. I'm working on a database solution for a steel furnace that manages inventory, and the customer has an Oracle backend. I want to move a group of Coils (Coil ID is the primary key) from inventory to the furnace's charge lineup (schedule).I collect a CSV string of all the coil IDs in my Drag and Drop event, and pass them to a stored procedure. Ok, splitting the CSV into separate IDs was relatively easy (meaning that part was only three times harder in Oracle -- turns out you can't view variable output in a select statement, so I actually had to create a dummy table to populate just to see my results). Now I want to use that list to update the inventory's available flag from 'Y' to 'N'.
I could create what's called a nested table (a one-dimensional collection) and use that in an inner-join where the inventory Coil Id equals the nested table's coil id, but Oracle won't allow me to use an inner join on an Update statement. I haven't completely given up on this method, though, since I might be able to trick Oracle into doing the same inside of a where clause.
But it seems like I should be able to include the results of the parsing directly within an update statement. Of course, my head keeps telling me that to make that work, the parsing routine (which contains a select statement), would have to be the object of the "IN" predicate, and I keep thinking that an IN predicate is rather a lot like RBAR, not so?
Actually, as a long-time Oracle developer (20 yrs - Oracle5 through 10g) I've found that I have to do more work in SQL Server to accomplish the same thing that I can do in Oracle.
The need for the tally table is because SQL Server, through SQL Server 2005, cannot handle an array of values being passed to a stored procedure. Thus the need for the delimited list and all of the ugly overhead of parsing it into a table (array) so that it can be used.
Should you wish to parse a string into an Oracle array, there is no real need for the tally table. It will work quite well with a PL/SQL function using the array datatype and loop logic.
So first look at the array datatype being passed to your stored procedure. And Oracle can use that array and perform "bulk" operations against the array. E.g., perform a delete of the rows whose values are in the array. No need for complex joins, IN lists, etc.
If you want some parsing logic contact me out of thread.
April 29, 2009 at 4:11 pm
JohnG (4/29/2009)
Should you wish to parse a string into an Oracle array, there is no real need for the tally table. It will work quite well with a PL/SQL function using the array datatype and loop logic.
I'd love to see that.
If you want some parsing logic contact me out of thread.
Why not post it here? I'm sure folks would appreciate it. Something that would parse a "standard" 8k string would be really cool.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 30, 2009 at 11:33 am
It's funny, really, because until this job, I've always liked Oracle. But our current shop is too small to warrant a full-time Oracle DBA, and none of us developers know how to tune this monster, so I'm stuck writing threaded applications to return 200 records because it takes 12 to 15 seconds and is causing my main thread's TCP/IP communications to appear to have failed.
Of course, most of this isn't Oracle, per se, but how it interacts with Windows Forms 2.0 controls and VB.Net. And it's not that I should expect two competing vendors to play well together, but Windows is what most of our customers want to run the HMI on, and I can create a new HMI from scratch in VB.Net in a lot less time than most other development environments on pretty much any other OS. (Admittedly, I haven't tried to create a GUI for Mac.)
As for the powerful options in Oracle, it beats SQL Server (and pretty much every other competitor out there) hands down. But I had hoped (in hindsight, a very niave hope) that I could create code that was nearly interchangeable except for writing stored procedures in the respective RDBMS. But (and I would call this a failure in ADO.Net), even the code to setup the recordset is quite different between the two systems. That said, I was initially avoiding use of an array for input parameters because I knew it wouldn't translate well. Now, I'm simply running out of time and I know it will require significant developer time (and I may be fast and easy, but I ain't cheap) to implement.
I used the tally table to parse it, and I used the results in an IN predicate. It works quickly from SQL Plus and SQL Developer. I can guess now that it will take at least two seconds to establish a connection to Oracle, but I'm hoping no longer than that, simply because this procedure doesn't return anything. Of course, I could have done the parsing in a loop. The code (as I'm sure you know) is almost trivial. Just not as fast.
April 30, 2009 at 12:26 pm
I have written "portable" stored procedures for both SQL Server and Oracle used by a .Net application.
To return a record set from Oracle to ADO.Net you use "REF" cursors. The method that SQL Server uses (firehose output) is not ANSI compliant.
Example of PL/SQL Procedure (within a Package Body):
PROCEDURE foo
(
p_language_code IN psp_common.iso_2char_lang_code,
p_user_id IN psp_common.GUID,
p_content_id IN psp_common.GUID,
p_refcursor0 OUT SYS_REFCURSOR,
p_refcursor1 OUT SYS_REFCURSOR
)
IS
-- local variables here
BEGIN
OPEN p_refcursor0 FOR
SELECT
-- column list
FROM
-- tables...
WHERE -- filtering...
ORDER BY -- sorting...
OPEN p_refcursor1 FOR
SELECT
-- column list
FROM
-- tables...
WHERE -- filtering...
ORDER BY -- sorting...
END foo;
Note that the data types of some of the parameters are mapped to some data types that have been established in a "common" package. Nice way of managing data types so that they only have to be changed in one place.
April 30, 2009 at 1:20 pm
Heh... I wonder if the ANSI committee like pork chops. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 211 through 225 (of 511 total)
You must be logged in to reply to this topic. Login to reply