|
|
Posted by Hugo Kornelis on 07/05/06 22:26
On 5 Jul 2006 09:41:41 -0700, wgblackmon@yahoo.com wrote:
>I'm currently running the following statement that is used in a Crystal
>Report. Basically, a record is returned when the T_PAYMENT.amount
>has a record in the database based on the value of the T_MULTILIST.code
>field. Currently, if there is no record returned, there is no listing
>in the report for the given T_MULTILIST.code.
(snip)
Hi wgblackmon,
The statement you gave is horrible, and I'd be surprised if it shows
anything even resembling performance.
If I may assume that the other 40 UNION'ed SELECT statements all
resemble the first two, you have 42 almost equal SELECT statements with
only one code in the WHERE clause and some constants in the SELECT list
different. Why don't you include the 42 relevant codes and the
accompanying values for Rec_Type, Super_Type, Other_Type, and Proc_Type
in a table? Then, yoou can rewrite the complete monster with one single
query.
There are many other things at fault with your query too:
- Think about readability and maintainability: Why do you supply aliases
that are exactly equal to the table's name? Why do you mix "new style"
infixed joins with "old style" joins (using a comma-seperated list of
tables)? Why do you use nested INNER JOINs? Why don't you stick to one
style for using newlines and indentation to make your queries more
readable?
- Think about performance: Why do you use >> '2006' + '0601' << to
denote a fixed date? If you use >> '20060601' <<, it can be converted at
compile-time. Now, you're forcing string concatenation and conversion at
execution time. It gets even worse in the complicated datetime formula
that can be replaced with '20070601'. Also, why do you use "UNION"
instead of "UNION ALL", forcing SQL Server to search for duplicates if
the constants in the 42 SELECT lists are distinct anyway? And do you
really need a DISTINCT on the individual queries? It's often a token of
bad design.
>The user now wants a record to be displayed on the report when there is
>no record in the database - she wants it to display a value of '$0'
>for the given T_MULTILIST.code record. I tried to explain the fact that
>is not possible the way things stand at the moment.
If you add the extra table suggested above, fulfilling this requirement
is as easy as changing an INNER JOIN to an OUTER JOIN and adding some
COALESCE functions in the SELECT list!
Here's a quick stab (retaining the DISTINCT for now, but do check if you
can leave it out). I have already added the user's wish.
First, set up and fill a table of codes and type descriptions:
CREATE TABLE dbo.CodeList
(Code char(4) NOT NULL PRIMARY KEY,
Rec_Type varchar(40) NOT NULL,
Super_Type varchar(40) NOT NULL,
Other_Type varchar(40) NOT NULL,
Proc_Type varchar(40) NOT NULL)
go
INSERT INTO CodeList (Code, Rec_Type, Super_Type, Other_Type, Proc_Type)
SELECT '1040', 'English Language Arts, Grade 1',
'English Language Arts (Consumable)', '', 'Continuing Contracts'
UNION ALL
SELECT '0040', 'English Language Arts, Kindergarten',
'English Language Arts (Consumable)', '', 'Continuing Contracts'
-- etc for the other codes
go
Now, attempt to rewrite your query.
SELECT DISTINCT c.Rec_Type, c.Super_Type, c.Other_Type, c.Proc_Type,
mg.grade, m.description, m.code, p.amount
FROM T_PAYMENT AS p
INNER JOIN T_SHIPPING_DETAIL AS sd
ON p.transaction_id = sd.transaction_id
INNER JOIN T_MULTILIST AS m
ON m.code = sd.multilist_code
AND m.expiration_year >= '2006'
INNER JOIN T_MULTILIST_GRADE mg
ON m.code = mg.multilist_code
INNER JOIN T_ORDER AS o
ON o.id = sd.order_id
AND o.latest_record_flag = 1
INNER JOIN T_REQUISITION AS r
ON r.id = o.requisition_id
AND r.latest_record_flag = 1
AND r.requested_shipment_date >= '20060601'
AND r.requested_shipment_date < '20070601'
INNER JOIN T_REQUISITION_DETAIL AS rd
ON rd.requisition_id = r.id
AND rd.latest_record_flag = 1
RIGHT OUTER JOIN dbo.CodeList AS c
ON c.Code = m.code
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
--
Hugo Kornelis, SQL Server MVP
[Back to original message]
|