[thelist] sql spaghetti or script?
Tab Alleman
talleman at autobex.com
Fri Dec 8 10:24:03 CST 2000
Seriously, what does your server say about it? If it runs, and it runs fast
enough for ya, I'd keep it the way you have it. I've written scripts that
have dynamically composed WHERE clauses that were so long, that Hal stopped
and said "I'm sorry Dave, I'm afraid I can't let you do that." But I think
that generally, performance-wise, it's better to let your SQL do the work
rather than your script.
PS: I'd really make this a stored procedure if that's an available option.
-----Original Message-----
From: thelist-admin at lists.evolt.org
[mailto:thelist-admin at lists.evolt.org]On Behalf Of Liz Lawson
Sent: Friday, December 08, 2000 10:23 AM
To: thelist at lists.evolt.org
Subject: [thelist] sql spaghetti or script?
Hi
I've come up against one of those questions of good practice where being
self-taught starts to show...I've seen a lot of neat tutorials but not so
much real-world code.
I need to records from my db depending on the date. These records can be
active once only, annually, monthly, weekly, daily or always (default), and
they have a start and end date. If the active period is monthly, only the
day part of these dates is considered, and so on for
weekly, daily etc. The shortest rotation period has priority. When the dates
are inserted, unused parts of the dates will be given default values so I
can assume that they will always be the same.
In general, is trying to do the lot in one query acceptable, (query below,
if anyone wants to wade through it), or should I handle more in the
script and have a simpler query?
Liz
bloated query follows:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT
sa.seasonalAreaType
,sa.seasonalAreaId
,sc.seasonalCodeString
,sc.seasonalCodeActive
FROM
seasonal_areas sa
,seasonal_codes sc
WHERE
sc.seasonalAreaId = sa.seasonalAreaId
AND sc.seasonalSetId = 1
AND ( seasonalCodeType = '0_default'
OR
(seasonalCodeType = '1_annually' AND DAYOFYEAR(NOW()) BETWEEN
DAYOFYEAR(seasonalCodeStart) AND DAYOFYEAR(seasonalCodeEnd)
AND (
(DAYOFYEAR(NOW()) !=
DAYOFYEAR(seasonalCodeStart) AND DAYOFYEAR(NOW()) !=
DAYOFYEAR(seasonalCodeEnd))
OR (DAYOFYEAR(NOW()) =
DAYOFYEAR(seasonalCodeStart) AND TIME_FORMAT(NOW(), '%T') >
TIME_FORMAT(seasonalCodeStart, '%T'))
OR (DAYOFYEAR(NOW()) =
DAYOFYEAR(seasonalCodeEnd) AND TIME_FORMAT(NOW(), '%T') <
TIME_FORMAT(seasonalCodeEnd, '%T'))
)
)
OR
(seasonalCodeType = '2_monthly' AND DAYOFMONTH(NOW()) BETWEEN
DAYOFMONTH(seasonalCodeStart) AND DAYOFMONTH(seasonalCodeEnd)
AND (
(DAYOFMONTH(NOW()) !=
DAYOFMONTH(seasonalCodeStart) AND DAYOFMONTH(NOW()) !=
DAYOFMONTH(seasonalCodeEnd))
OR (DAYOFMONTH(NOW()) =
DAYOFMONTH(seasonalCodeStart) AND TIME_FORMAT(NOW(), '%T') >
TIME_FORMAT(seasonalCodeStart, '%T'))
OR (DAYOFMONTH(NOW()) =
DAYOFMONTH(seasonalCodeEnd) AND TIME_FORMAT(NOW(), '%T') <
TIME_FORMAT(seasonalCodeEnd, '%T'))
)
)
OR
(seasonalCodeType = '3_weekly' AND DAYOFWEEK(NOW()) BETWEEN
DAYOFWEEK(seasonalCodeStart) AND DAYOFWEEK(seasonalCodeEnd)
AND (
(DAYOFWEEK(NOW()) !=
DAYOFWEEK(seasonalCodeStart) AND DAYOFWEEK(NOW()) !=
DAYOFWEEK(seasonalCodeEnd))
OR (DAYOFWEEK(NOW()) =
DAYOFWEEK(seasonalCodeStart) AND TIME_FORMAT(NOW(), '%T') >
TIME_FORMAT(seasonalCodeStart, '%T'))
OR (DAYOFWEEK(NOW()) =
DAYOFWEEK(seasonalCodeEnd) AND TIME_FORMAT(NOW(), '%T') <
TIME_FORMAT(seasonalCodeEnd, '%T'))
)
)
OR
(seasonalCodeType = '4_daily'
AND TIME_FORMAT(NOW(), '%T') BETWEEN
TIME_FORMAT(seasonalCodeStart, '%T') AND TIME_FORMAT(seasonalCodeEnd, '%T')
)
OR
(seasonalCodeType = '5_one_off'
AND NOW() BETWEEN seasonalCodeStart AND seasonalCodeEnd
)
)
ORDER BY sc.seasonalCodeType
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
phew!
---------------------------------------
For unsubscribe and other options, including
the Tip Harvester and archive of TheList go to:
http://lists.evolt.org Workers of the Web, evolt !
More information about the thelist
mailing list