September 24, 2012 at 1:13 pm
I just took a moment to get an appreciation of CTEs, found and article by Robert Sheldon, with examples that make it clear.
Jeff - What do you mean by the "+1"?
Paul - Can you give me an example of what you mean by 'break such queries up'? Maybe my example, or something simpler.
I think I mentioned before that we created a 2nd DB, call it ERPPlus, to for our 'add-ons' to the ERP system. I do NOT want to change, in any way, what is in the DB that the software vendor has - nothing added, deleted, or modified (period!). Otherwise, they can claim we 'damaged' something and back out on resolving an issue. Since I can write a SQL statement in our ERPPlus which reference the ERP DB, I imagine I can create SP's and other objects that reference the ERP DB.
Note - The SQL statement I included is probably a one-time request.
I'm thinking forward now ... That said, it sounds like (correct me if I'm wrong) instead of creating a SQL statement like I did I can create an SP - including CTEs and other things I can't do within an Access Pass Through. The Access Pass Through would reference the SP?
Now that I think about it ... As my Homework assignment ... Paul, If you meant CTEs by your 'break such queries up' then I could, modify my SQL statement and use CTEs. If you meant doing something else, then,
(a) I can still create CTEs,
(b) If you give me a little guidance I could modify my SQL statement to how you would construct it.
I like this continued dialog - you all are "leading me to water". I appreciate your giving me the time and knowledge.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
September 24, 2012 at 2:06 pm
EdA ROC (9/24/2012)
Jeff - What do you mean by the "+1"?
Some forums allow you to rate an answer by clicking the "+1" button to add a vote of agreement. This one doesn't but a lot of us use "+1" as shorthand for "I agree, thanks for posting the answer, an/or I like it".
--Jeff Moden
Change is inevitable... Change for the better is not.
September 24, 2012 at 6:33 pm
EdA ROC (9/24/2012)
Note - The SQL statement I included is probably a one-time request.
I'm wondering how much code I've written that was a result of a one-time request that ended up running in Production for years. A lot I think.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 24, 2012 at 7:06 pm
LOL - I know what you mean.
If this customer follows their pattern, next year they will have a request with something else that is different. It's always been YTD Sales ... then the columns are different.
However, the technique will be needed again, sooner or later.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
September 25, 2012 at 3:28 am
EdA ROC (9/24/2012)
Paul - Can you give me an example of what you mean by 'break such queries up'? Maybe my example, or something simpler.
No, not CTEs; I'm referring to the idea of computing parts of the final result, storing them in temporary tables and performing a final query that operates on the partial results held in the temporary tables. If you can provide an execution plan from a real execution of your query* I might be able to put together an example for you. You won't be able to use it in a pass-through query though, as we discussed earlier.
* Run the query with 'actual execution plan on', right-click on the graphical plan shown after the query completes, save it to a *.sqlplan file and attach it to your reply.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 25, 2012 at 4:35 am
EdA ROC (9/24/2012)
...Paul - Can you give me an example of what you mean by 'break such queries up'? Maybe my example, or something simpler.I think I mentioned before that we created a 2nd DB, call it ERPPlus, to for our 'add-ons' to the ERP system. I do NOT want to change, in any way, what is in the DB that the software vendor has - nothing added, deleted, or modified (period!). Otherwise, they can claim we 'damaged' something and back out on resolving an issue. Since I can write a SQL statement in our ERPPlus which reference the ERP DB, I imagine I can create SP's and other objects that reference the ERP DB.
Excellent - that means you can create an sp...
I'm thinking forward now ... That said, it sounds like (correct me if I'm wrong) instead of creating a SQL statement like I did I can create an SP - including CTEs and other things I can't do within an Access Pass Through. The Access Pass Through would reference the SP?
...and yes, use SPT to call it.
As an example of what Jeff and Paul meant by breaking up queries, your original query ain't too bad a place to start: lift out the whole left-joined derived table piece and resolve it out into a local temp table. The result set can be indexed just like a permanent table for performance;
SELECT JobOpsList.JobNum
,CASE jobopslist.prestamp WHEN 0 THEN '' ELSE 'Y' END AS PreStamp
,CASE jobopslist.Decorative WHEN 0 THEN '' ELSE 'Y' END AS Decorative
,CASE jobopslist.Emboss WHEN 0 THEN '' ELSE 'Y' END AS Emboss
INTO #OpsUsed
FROM ( -- JobOpsList
SELECT
JobList.JobNum
,SUM(CASE ops.Mach_No WHEN 288 THEN 1 ELSE 0 END) AS PreStamp
,SUM(CASE ops.Mach_No WHEN 290 THEN 1 ELSE 0 END) AS Decorative
,SUM(CASE ops.Mach_No WHEN 292 THEN 1 ELSE 0 END) AS Emboss
FROM ( -- JobList
SELECT
JOB_NUMBER AS JobNum,
CASE ISNULL(o.COMBO_NO, '') WHEN '' THEN o.SPEC_NO ELSE o.COMBO_NO END AS MfrSpec
FROM ORDERS o
WHERE o.CSCODE = '2507'
AND ORDER_DATE >= '1/1/2012'
AND COMPLETION_FLG <> 'X'
GROUP BY JOB_NUMBER,
CASE ISNULL(o.combo_no, '') WHEN '' THEN o.SPEC_NO ELSE o.COMBO_NO END
) JobList
LEFT JOIN OPERATIONS ops ON ops.SPEC_NO = JobList.MfrSpec
WHERE ops.MACH_NO IN (288,290,292) -- turns LJ into INNER JOIN
GROUP BY JobNum
) JobOpsList
CREATE UNIQUE CLUSTERED INDEX UCX_JobNum ON #OpsUsed (JobNum)
SELECT o.JOB_NUMBER AS Job#
,ORDER_NO AS Order#
,FORM_NO AS Form
,CUST_IDENT AS SpecID
,QTY_ORDERED AS QtyOrdered
,ORD_PRICE AS UnitPrice
,PRICING_METH AS UOM
,s.COLOR_DESC AS Colors
,opsused.PreStamp
,opsused.Decorative
,opsused.Emboss
,DESIGN_NO AS Drawing
,CAST(CAST(s.dim_A AS DECIMAL(9, 3)) AS VARCHAR(7)) + ' x ' + CAST(CAST(s.DIM_B AS DECIMAL(9, 3)) AS VARCHAR(7)) + ' x ' + CAST(CAST(s.DIM_C AS DECIMAL(9, 3)) AS VARCHAR(7)) AS Dimensions
,gf.DESCR AS Board
,DUE_DATE AS DueDate
,COMPLETION_FLG AS OrderStatus
FROM ORDERS o
LEFT JOIN SPECS s ON s.SPEC_NO = o.SPEC_NO
LEFT JOIN GRADE_FILE gf ON gf.GRADE_CD = s.GRADE_CD
LEFT JOIN #OpsUsed OpsUsed ON OpsUsed.JobNum = o.JOB_NUMBER
WHERE o.CSCODE = '2507'
AND ORDER_DATE >= '1/1/2012'
AND COMPLETION_FLG <> 'X'
Plonk this into a stored procedure and you’ve killed two birds with one stone – a performance lift, and a big improvement in readability.
But that’s not all. Hoiking out that chunk of code makes it easier to see that the constraints upon the rows returned from table OPERATIONS (apart from the filter on ops.MACH_NO) already exist in the main body of the query. That means you can grab the data from OPERATIONS by correlating to ORDERS.COMBONO (or SPECNO) in the main query, something like this:
SELECT o.JOB_NUMBER AS Job#
,ORDER_NO AS Order#
,FORM_NO AS Form
,CUST_IDENT AS SpecID
,QTY_ORDERED AS QtyOrdered
,ORD_PRICE AS UnitPrice
,PRICING_METH AS UOM
,s.COLOR_DESC AS Colors
,CASE jobopslist.prestamp WHEN 0 THEN '' ELSE 'Y' END AS PreStamp
,CASE jobopslist.Decorative WHEN 0 THEN '' ELSE 'Y' END AS Decorative
,CASE jobopslist.Emboss WHEN 0 THEN '' ELSE 'Y' END AS Emboss
,DESIGN_NO AS Drawing
,CAST(CAST(s.dim_A AS DECIMAL(9, 3)) AS VARCHAR(7)) + ' x ' + CAST(CAST(s.DIM_B AS DECIMAL(9, 3)) AS VARCHAR(7)) + ' x ' + CAST(CAST(s.DIM_C AS DECIMAL(9, 3)) AS VARCHAR(7)) AS Dimensions
,gf.DESCR AS Board
,DUE_DATE AS DueDate
,COMPLETION_FLG AS OrderStatus
FROM ORDERS o
LEFT JOIN SPECS s ON s.SPEC_NO = o.SPEC_NO
LEFT JOIN GRADE_FILE gf ON gf.GRADE_CD = s.GRADE_CD
CROSS APPLY (
SELECT
SUM(CASE ops.Mach_No WHEN 288 THEN 1 ELSE 0 END) AS PreStamp,
SUM(CASE ops.Mach_No WHEN 290 THEN 1 ELSE 0 END) AS Decorative,
SUM(CASE ops.Mach_No WHEN 292 THEN 1 ELSE 0 END) AS Emboss
FROM OPERATIONS ops
WHERE ops.SPEC_NO = CASE ISNULL(o.COMBO_NO, '') WHEN '' THEN o.SPEC_NO ELSE o.COMBO_NO
AND ops.MACH_NO IN (288,290,292)
) jobopslist
WHERE o.CSCODE = '2507'
AND ORDER_DATE >= '1/1/2012'
AND COMPLETION_FLG <> 'X'
Right there, you’ve got a third reason for chunking a big query – it’s much easier to spot opportunities for simplification.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 26, 2012 at 3:55 am
Wow! 🙂
There's a lot I have to learn.
Some of what you mentioned was over my head. That's OK, just letting you know.
Did a cursory read of the Forum Etiquette. Will go over it again to comprehend the steps.
Interesting, my initial question was to elicit how to organize statement construction to keep the pieces straight and it's morphed into all this - which is an answer to my question, just not what I expected. (ha, ha) Don't get me wrong, this is good, very good! I realize I'm about to take my skills up a (big) notch. I'm going to have to view this as a course/lesson and organize my time accordingly. This is definitely homework and not some quick and dirty thing I can squeeze in at work alone. So, please be patient as my responses may not come immediately.
Have a fun day!
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
September 27, 2012 at 10:50 am
CELKO (9/27/2012)
The only REFERENCES keyword I know is in creating foreign keys and since foreign keys can be NULL, I'm not sure how that would eliminate the LEFT OUTER JOIN. Unless of course your suggestion is to create the foreign key and make the column NOT NULL.
Imagine you have a ZIP code column in a table, say, Customers. Instead of doing a LEFT OUTER JOIN to the table that matches a ZIP to a city_name and code, do a "zip_code REFERENCES Postal_Map (zip_code) ON UPDATE CASCADE" in the table. This will prevent bad or missing address data from getting into Customers. And it will save execution time and not block the optimizer.
And you can do that in a SELECT? I've never come across that before.
September 27, 2012 at 10:56 am
LightVader (9/27/2012)
CELKO (9/27/2012)
The only REFERENCES keyword I know is in creating foreign keys and since foreign keys can be NULL, I'm not sure how that would eliminate the LEFT OUTER JOIN. Unless of course your suggestion is to create the foreign key and make the column NOT NULL.
Imagine you have a ZIP code column in a table, say, Customers. Instead of doing a LEFT OUTER JOIN to the table that matches a ZIP to a city_name and code, do a "zip_code REFERENCES Postal_Map (zip_code) ON UPDATE CASCADE" in the table. This will prevent bad or missing address data from getting into Customers. And it will save execution time and not block the optimizer.
And you can do that in a SELECT? I've never come across that before.
No... it's two parts. The LEFT OUTER JOIN is done in the SELECT. The part about "REFERENCES" is DRI in the form of an active foreign key that will automatically update the customer table if the ZipCode in the "Zip_Code" column of the "Postal_Map" table that Joe speaks of is ever updated.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 27, 2012 at 11:03 am
Jeff Moden (9/27/2012)
LightVader (9/27/2012)
CELKO (9/27/2012)
The only REFERENCES keyword I know is in creating foreign keys and since foreign keys can be NULL, I'm not sure how that would eliminate the LEFT OUTER JOIN. Unless of course your suggestion is to create the foreign key and make the column NOT NULL.
Imagine you have a ZIP code column in a table, say, Customers. Instead of doing a LEFT OUTER JOIN to the table that matches a ZIP to a city_name and code, do a "zip_code REFERENCES Postal_Map (zip_code) ON UPDATE CASCADE" in the table. This will prevent bad or missing address data from getting into Customers. And it will save execution time and not block the optimizer.
And you can do that in a SELECT? I've never come across that before.
No... it's two parts. The LEFT OUTER JOIN is done in the SELECT. The part about "REFERENCES" is DRI in the form of an active foreign key that will automatically update the customer table if the ZipCode in the "Zip_Code" column of the "Postal_Map" table that Joe speaks of is ever updated.
That's what I originally thought - setting up a foreign key. And the question I was trying to ask in the first place (guess I didn't express that too well). It doesn't however eliminate a LEFT OUTER JOIN in all cases though.
For example - in a parent/child relationship where the parent can have 0 or more children. If you're querying the parent but need a piece of information from the child, where available, an INNER JOIN can't do that for you. You would need either a LEFT OUTER JOIN or an INNER JOIN and a UNION.
September 27, 2012 at 11:38 am
Stop The Presses... My Thread has been hijacked. 🙂
Would you please take this REFERENCES conversation to another thread?
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
September 27, 2012 at 11:42 am
EdA ROC (9/27/2012)
Stop The Presses... My Thread has been hijacked. 🙂Would you please take this REFERENCES conversation to another thread?
Sorry about that. :blush: We don't need a new thread. I'm satisfied with the answer.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply