« April 2006 | Main | June 2006 »
May 31, 2006
The Chair is Vacant
I'm gone on vacation from Friday, June 2nd until Sunday, June 11th. For assistance you can call the UCS Help Desk at 88925. See you on the 12th!
Posted by rossm at 8:33 AM | Comments (0)
May 24, 2006
Reporting on Expenditures
Well, I just finished coding up a tricky little SWAMP report for Tom Neal. It's a report that shows what's been spent on our equipment (Cushmans, mowers, trencher, tractors, etc.), vehicles, and grounds maintenance (plantings, fertilizer, etc.) this fiscal year. The main challenge for this report was gathering all the required pieces then breaking down the huge list of details into some sort of meaningful groups.
After some extensive poking around and discussions with Dave and John K., it was decided to collect information based upon work orders and then grouped by commodity code. Some examples of commodity codes are 58120 - Tools, garden & farm and 84301 - Nursery stock. Ultimately, this report was divided into three main groups: Work order 7509 - Campus Grounds Maintenance (grouped by Commodity code); All work orders like 'A%' (A002-A256) which includes Cushmans, mowers, chipper, etc. (grouped by Work Order number - one work order for each piece of equipment); and All work orders like 'E%' - vehicles (also grouped by Work Order number).
SWAMP maintains meticulous records on all purchases and all stock issues from Stores. Collecting together these records on the above groups resulted in an overwhelming amount of detail. What Tom was wanting was a quick way to look over a summary of expenses so that he could then go to the details on groups of items where expenses might be too high.
Although I tried to amass all the data required into one huge cursor, I finally gave up and just used some queries to load portions of the data into a temporary holding table, then used the holding table to display the results. Here is an example query:
INSERT INTO proj_test
(user, wo_num, desc,
detail_dte, qty, acct,
material, part_no, comm)
SELECT USER, B.wo_num, B.desc,
issue_date, B.qty, C.ind,
nvl(B.qty * pri,0),
B.part_no, E.comm
FROM iss_heer A,
iss_det B,
wo_nur C,
det_lst D,
part_data E
WHERE A.issue_no = B.issue_no
AND B.wo_no = C.wo_no
AND B.wo_no = D.wo_no
and B.part_no = E.part_no
AND D.enter_user = USER
and B.wo_no <= decode(end_wo,'',b.wo_no,end_wo)
and B.wo_no >= decode(beg_wo,'',b.wo_no,beg_wo)
AND A.issue_dte >= decode(beg_dte,'',A.issue_dte,beg_dte)
AND A.issue_dte <= decode(end_dte,'',A.issue_dte,end_dte)
AND A.issue_dte >= wo_eff_date
AND A.issue_dte <= wo_nchg_date;
As you can see, a pretty hairy query that returns the data on issues over a given date range and work order range. So, about six of these things loads up the temp table with all the pieces necessary for a nice report.
Here's the first page of the report:
Nice and sweet - three items and the money spent so far on each one. You will notice that each item is a link. Let's look at the Vehicles link page, which is a summary for each vehicle:
You can see how much has been spent so far on each vehicle, plus another link to the detailed expense information. In order to reduce the number of items, total expenses of less than $100 are excluded. Let's take a look at the Ford Ranger Pickup details:
Now, we've gotten the expenses down to a manageable number, and you can see what's been needed for this pickup so far this year pretty easily.
Nice report. Mostly just brute force gathering the data, then displaying it in bite sized chunks. There is one small coding trick that's interesting. The summary display for the 7509 work order is different than the display for the Equipment and Vehicles and required HTML tables of differing widths. So, I split out the HTML code that displays the header information into a separate procedure. Here's the procedure information:
procedure dsp_hdr(pwide varchar2 default '500')
Yes, that's right - you call this procedure like this:
dsp_hdr('750'); - Put in whatever width you need.
The procedure has a table definition like this:
<table align=center width='||pwide||'>
So, you can get report headers of varying widths for different parts of the report just by calling this procedure with whatever width you need. It's not rocket science, but it did work well for this report.
Posted by rossm at 9:03 AM | Comments (0)
May 11, 2006
Oracle Tuning - Table Partitioning
This week Dale and I continued our pursuit of the elusive Oracle tuning knowledge. We wanted to test the effect of partitioning on a large table (about 1 million records). The table we selected is very interesting not only because of it's size, but we discovered the data tends to be clustered around certain points, such as crn's (course numbers). Therefore, figuring out the best way to partition this table was quite a challenge. In many tables you will have a primary key, or a column such as "Order Number" that keeps growing over time. These kinds of data make for pretty easy partitioning. Our table, however, has nearly 172,000 records that relate to crn's in the range of 20801 to 30000 - many records around not too many differing values.
Read on to see how we partitioned this table, and what we learned.
After considering the nature of the data contained in this table, we decided against partitioning by year and/or term. This data was mostly gathered from one term per year, so the partition with that term held about 90% of the year's data, while the other 10% was spread around the rest of the year. Not very useful as this would leave us with one huge partition and one to four tiny partitions. We finally settled upon using the course number (crn) column, and created eight partitions. Here's the code we used to create our table.
First step is to create the table:
create table raw_data_p
(QUEST_NUM NUMBER,
QUEST_VALUE NUMBER,
YEAR NUMBER,
CRN NUMBER,
SURVEY_NUMBER NUMBER,
TERM NUMBER)
partition by range(crn)
( partition part_1 values less than (20100),
partition part_2 values less than (20400),
partition part_3 values less than (20800),
partition part_4 values less than (30000),
partition part_5 values less than (30300),
partition part_6 values less than (30700),
partition part_7 values less than (31000),
partition part_8 values less than (200000));
Second step is to load up the data into the partitions:
insert into raw_data_p
(select * from raw_data);
We then created one index on the crn column, and set timing on. By the way, the Command window in PL/SQL Developer did allow us to turn on timing (set timing [on|off]).
So, now we have a partitioned table - raw_data_p to compare with the production table raw_data, and we also have the ability to time various queries against these tables. Let's try it!
First of all we tried something simple:
select count(*) from [table];
Normal: .016 seconds Partitioned: .656 seconds
(It takes time to traverse the partitions for this count.)
select * from [table] where crn = 39708;
Normal: 2.000 seconds Partitioned: 1.938 seconds
select quest_num, quest_value from [table] where crn in (36748,99815,22882,4,61792) and quest_num=5 and quest_value=3;
Normal: 4.796 seconds Partitioned: 4.172 seconds
select quest_num, quest_value from [table] where crn in (310108,1075815,20745882,1075824,2095861) and quest_num=5 and quest_value=3;
Normal: .078 seconds Partitioned: .063 seconds
What does it all mean? Well, although we did notice some gain, we weren't particularly impressed by the times. It actually takes longer to do a simple count of the records. We speculate that part of the reason that the gains were minimal is that the data in each record is small (6 number columns). Oracle is pretty fast just using the index, whether searching through 130,000 records or 1,000,000.
Next week Dale and I plan on pursuing the mechanics of tuning tables and SQL statements.
Posted by rossm at 11:54 AM | Comments (0)
May 3, 2006
Preparing for Physical Inventory
Well, June is shaping up to be an interesting month. Physical inventory at the Bookstore is scheduled for the 22nd and 23rd, and physical inventory for Stores is scheduled for the 30th. Oh, boy. The interesting news this week, though, is that I've been working on prepping some laptops from Nathan to use instead of buying/renting Telxons or PDAs. If we can make the laptops work, this will save quite a few $$$ - always a good thing. It just so happens that the Bookstore inventory is scheduled between terms, a time when the laptops aren't in high demand.
The issues we still need to resolve are whether the University's wireless network can be used to connect the laptops to the AS/400 (otherwise I'll need a couple miles of cat5 cabling), how to connect good scanners to the laptops, and what terminal emulation software to use. If we can get satisfactory results on these issues, then each laptop will be used to create a live inventory count "batch", which will report any inventory errors immediately. This means, that as each "batch" is completed, it will be error free, and all inventory errors will be caught right as an item is counted. This is much easier than trying to resolve errors after the fact.
I'd like to thank Nathan for his help. Is he the easiest person on campus to work with or what?
Posted by rossm at 9:38 AM | Comments (0)