On Wed, Apr 30, 2008 at 8:19 AM, Gil Hale <[EMAIL PROTECTED]> 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
---
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
