On Wed, Apr 30, 2008 at 8:19 AM, Gil Hale <gil (at) gilhale .DOT com> wrote:
> > I disagree with the SPEED statement, and if you find it difficult
> > to manage
> > it's complexities then .........
>
> Actually, I have found overall speed is not so much the issue unless there
> is a non-optimized piece in the SQL-SELECT code. In that case it seems
> the
> speed is greater if I can isolate the non-optimized SQL-SELECT statement.
> And, I am in total agreement with Kristyne re: it being easier to
> read/debug
> SQL-SELECT when it is broken into several steps. It is not that I can't
> deal with the complexities. It is more that years after I cut the code it
> is easier for me to "get it again" if the SQL-SELECT code is broken into
> logical steps - much less for someone less familiar with my code than I.
>
> Usually once I get the results I want I am tempted to just put all the
> statements into one big, honking SQL-SELECT statement and let 'er rip.
> But
> in almost every case I opt to leave it as is since it is working, and it
> does make for easier understanding when I come back to the code many years
> later. Further, if I want to use just one piece of the code elsewhere it
> is
> easy to isolate what I want to pull out.
>
----------------------------------------------------------------------
I don't understand how your steps are taking place?
" Usually once I get the results I want I am tempted to just put all the
statements into one big, honking SQL-SELECT statement and let 'er rip. "
Are you talking about multi segments in a large sql script?
Or getting a first filter set and then using the output of that in a join to
another table for secondary processing?
Is this complex?:
SELECT dbo.SubmittedOrderDocument.guid,
dbo.SubmittedOrderDocument.[order], dbo.SubmittedOrderDocument.quantity,
dbo.SubmittedOrderDocument.sheetCount,
dbo.SubmittedOrderDocument.friendlyID, dbo.SubmittedOrder.submittedDate,
dbo.SubmittedOrderDocument.plex,
dbo.SubmittedOrderDocument.printColor, dbo.SubmittedOrder.friendlyID AS
OrderFriendlyID,
dbo.SubmittedOrder.shippingExpectedCompleteDeliveryDate,
dbo.SubmittedOrder.priority,
dbo.SubmittedOrderDocument.blackAndWhiteImpressionCount,
dbo.SubmittedOrderDocument.colorImpressionCount,
dbo.SubmittedOrderDocument.tabCount,
dbo.SubmittedOrderDocument.slipSheetCount,
dbo.SubmittedOrderDocument.bindingType,
dbo.SubmittedOrder.expectedShipDate
/*
,
(SELECT
MIN(shippingExpectedCompleteDeliveryDate)
FROM SubmittedOrderRecipient
WHERE SubmittedOrderRecipient.[ORDER] =
SubmittedOrderDocument.[ORDER]) AS
earliestShippingExpectedCompleteDeliveryDate,
dbo.SubmittedOrderDocument.status,
(SELECT COUNT(*)
FROM SubmittedOrderRecipient
WHERE SubmittedOrderRecipient.[ORDER] =
SubmittedOrderDocument.[ORDER]) AS recipientCount,
(SELECT COUNT(*)
FROM
SubmittedOrderDocumentStatusHistoryPreFlightedView
WHERE SubmittedOrderDocument.guid =
SubmittedOrderDocumentStatusHistoryPreFlightedView.document) AS preFlighted,
(SELECT COUNT(*)
FROM SubmittedOrderChangeOrder
WHERE SubmittedOrderChangeOrder.[ORDER] =
SubmittedOrder.guid AND SubmittedOrderChangeOrder.productionAttributes IS
NULL)
AS orderChangeOrderCount,
(SELECT COUNT(*)
FROM SubmittedOrderDocumentChangeOrder
WHERE
SubmittedOrderDocumentChangeOrder.document = SubmittedOrderDocument.guid AND
SubmittedOrderDocumentChangeOrder.productionAttributes IS NULL) AS
documentChangeOrderCount,
(SELECT TOP 1 *
FROM SubmittedOrderRequiresKittingView
WHERE SubmittedOrder.guid =
SubmittedOrderRequiresKittingView.[ORDER]) AS hasKitting,
dbo.SubmittedOrderRecipinetPackageInternationalShippingView.[order] AS
InternationalPackageOrderID,
(SELECT TOP 1 *
FROM
dbo.SubmittedOrderDocumentRequiresInventoryView
WHERE SubmittedOrderDocument.guid =
dbo.SubmittedOrderDocumentRequiresInventoryView.guid) AS
hasDocumentInventory,
(SELECT TOP 1 displayName
FROM
dbo.SubmittedOrderDocumentUnlistedBindingChangeView
WHERE SubmittedOrderDocument.guid =
dbo.SubmittedOrderDocumentUnlistedBindingChangeView.guid) AS
unlistedBinding,
(SELECT COUNT(*)
FROM SubmittedOrderSpecialInstruction
WHERE
SubmittedOrderSpecialInstruction.[ORDER] = SubmittedOrder.guid) AS
OrderSpecialInstructionCount,
(SELECT COUNT(*)
FROM
SubmittedOrderDocumentSpecialInstruction
WHERE
SubmittedOrderDocumentSpecialInstruction.document =
SubmittedOrderDocument.guid) AS DocumentSpecialInstructionCount,
*/
, dbo.SubmittedOrderDocument.simpleDocumentXML,
dbo.ADAPCalculatedPriority.calculatedPriority,
dbo.ADAPCalculatedPriority.bwQueueCalculatedPriority,
dbo.ADAPCalculatedPriority.colorQueueCalculatedPriority,
dbo.ADAPCalculatedPriority.collationQueueCalculatedPriority,
dbo.ADAPCalculatedPriority.bindingQueueCalculatedPriority,
dbo.ADAPCalculatedPriority.packagingQueueCalculatedPriority,
dbo.ADAPCalculatedPriority.attribute, dbo.ADAPCalculatedPriority.documentID,
dbo.ADAPCalculatedPriority.guid AS ADAPGuid
FROM dbo.SubmittedOrderDocument INNER JOIN
dbo.SubmittedOrder ON
dbo.SubmittedOrderDocument.[order] = dbo.SubmittedOrder.guid INNER JOIN
dbo.ADAPCalculatedPriority ON
dbo.SubmittedOrderDocument.guid = dbo.ADAPCalculatedPriority.documentID LEFT
OUTER JOIN
dbo.SubmittedOrderRecipinetPackageInternationalShippingView ON
dbo.SubmittedOrder.guid =
dbo.SubmittedOrderRecipinetPackageInternationalShippingView.[order]
--ORDER BY dbo.SubmittedOrder.priority DESC,
dbo.SubmittedOrder.expectedShipDate, dbo.SubmittedOrder.submittedDate
That is a view I need to adjust candidates for shipping date changes.
I have others that take an abortion like that as the first step and apply
it's output to a secondary process.
I hate those!
--
Stephen Russell
Sr. Production Systems Programmer
Mimeo.com
Memphis TN
901.246-0159
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
©2008 Stephen Russell |
|
