September 21, 2012 at 2:56 pm
I just finished a complex SQL statement; and, yes, I know there are even more complex ones.
When I am faced with a complex statement I try to create the pieces, then embed them in the main query. There are times when I have trouble differentiating where one ends and another begins. A few times I removed a part of the statement, or not enough, when I make changes. My only fall back is I always keep a backup of the last working statement.
The statement I just finished is below. I'm guessing that those of you who have developed the skills can probably see where pieces begin and end.
Is my approach the effective way?
Does anyone have suggestions on how to keep "the pieces straight"?
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 (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
FROM (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 (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 )
GROUP BY JobNum) JobOpsList) OpsUsed
ON OpsUsed.JobNum = o.JOB_NUMBER
WHERE o.CSCODE = '2507'
AND ORDER_DATE >= '1/1/2012'
AND COMPLETION_FLG <> 'X'
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
September 21, 2012 at 3:11 pm
Really what you are talking about is formatting. This can be subject to personal preference. There is a free site http://poorsql.com/[/url] that goes a pretty decent job.
Here are the results of your code from that site.
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 (
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
FROM (
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 (
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
)
GROUP BY JobNum
) JobOpsList
) OpsUsed ON OpsUsed.JobNum = o.JOB_NUMBER
WHERE o.CSCODE = '2507'
AND ORDER_DATE >= '1/1/2012'
AND COMPLETION_FLG <> 'X'
One thing in your query jumps right off the page as a major flaw.
CAST(CAST(s.dim_A AS DECIMAL(9, 3)) AS VARCHAR(7))
You are asking for trouble with that. You explicitly cast you column to a decimal type that can be as long as 10 characters, but in the same breath you attempt to truncate it to 7 characters.
Consider this code.
declare @MyDec decimal(9,3) = 123456.789
--all is fine here. Your decimal has as many places as it can hold.
select @MyDec
--kapow!!! Arithmetic overflow because the value is too big.
select CAST(@MyDec as varchar(7))
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 21, 2012 at 3:40 pm
If you are talking about formatting your code into a more readily readable layout then there are several "tools/addins" that will allow you to do this...along with allowing you to fine tune the output to your personal preference.
One I have used (no connection) is ApexSQL refactor.....last time I looked it was free
(http://www.apexsql.com/sql_tools_refactor.aspx)
Please note that there are several tools like this ...take a Google and choose what you feel happy with.
these can take adhoc layout (as written) and format code from something like this (deliberately brutalised 🙂 )
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 AS o LEFT JOIN SPECS AS s ON s.SPEC_NO = o.SPEC_NO
LEFT JOIN GRADE_FILE AS gf ON gf.GRADE_CD = s.GRADE_CD
LEFT JOIN( 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 FROM( 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
(
SELECT JOB_NUMBER AS JobNum ,
CASE ISNULL( o.COMBO_NO , '' ) WHEN '' THEN o.SPEC_NO ELSE o.COMBO_NO
END AS MfrSpec FROM ORDERS AS 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 )AS JobList LEFT JOIN OPERATIONS AS ops ON ops.SPEC_NO = JobList.MfrSpec
WHERE ops.MACH_NO IN( 288 , 290 , 292 )
GROUP BY JobNum )AS JobOpsList )AS OpsUsed ON OpsUsed.JobNum = o.JOB_NUMBER
WHERE o.CSCODE = '2507' AND ORDER_DATE >= '1/1/2012' AND COMPLETION_FLG <> 'X';
to something like this...in a click
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 AS o LEFT JOIN SPECS AS s ON s.SPEC_NO = o.SPEC_NO
LEFT JOIN GRADE_FILE AS gf ON gf.GRADE_CD = s.GRADE_CD
LEFT JOIN(
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
FROM(
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
(
SELECT JOB_NUMBER AS JobNum ,
CASE ISNULL( o.COMBO_NO , '' )
WHEN '' THEN o.SPEC_NO ELSE o.COMBO_NO
END AS MfrSpec
FROM ORDERS AS 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 )AS JobList LEFT JOIN OPERATIONS AS ops ON ops.SPEC_NO = JobList.MfrSpec
WHERE ops.MACH_NO IN( 288 , 290 , 292 )
GROUP BY JobNum )AS JobOpsList )AS OpsUsed ON OpsUsed.JobNum = o.JOB_NUMBER
WHERE o.CSCODE = '2507'
AND ORDER_DATE >= '1/1/2012'
AND COMPLETION_FLG <> 'X';
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 21, 2012 at 9:56 pm
I not only use formatting to help organize things, I also include comments for every subquery. I also make sure that every column returned by every query and subquery have proper table aliases and that the aliases actually mean something especially when self-joins are present.
Depending on the performance of the code, I may also break the code up into smaller and tighter pieces and store the interim results in a TempTable or two so I don't have to work with such large data sets. Sometimes, the performmance improvement is quite dramatic. I recently took such an "all-in-one" query (35 tables joined including some self joins) that virtually crippled the server it was running on for 45 minutes and divided it up in such a manner. The code now runs so fast that it usually doesn't even show up on the Task Manager performance chart.
Getting back on topic, the splitting up of the code also allows for more pertinent comments for each section of the code.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 22, 2012 at 2:10 am
Jeff Moden (9/21/2012)
Depending on the performance of the code, I may also break the code up into smaller and tighter pieces and store the interim results in a TempTable or two so I don't have to work with such large data sets. Sometimes, the performmance improvement is quite dramatic. I recently took such an "all-in-one" query (35 tables joined including some self joins) that virtually crippled the server it was running on for 45 minutes and divided it up in such a manner. The code now runs so fast that it usually doesn't even show up on the Task Manager performance chart.
I agree wholeheartedly with this. It is not a magic bullet by any means, but smaller (and more relational) queries are very often easier for the optimizer to reason about. The availability of statistics (on the temporary tables used to hold intermediate results) can also be a big factor. Large all-in-one queries are also generally harder for humans to understand and maintain, regardless of how well you lay them out on screen.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 22, 2012 at 8:25 am
First! Thank you all for taking the time to reply. I appreciate all the feedback.
A little background about me and my situation. We have a 3rd party ERP system, SQL Server based. My primary job is to use Access to develop reports. Since starting I have added dumping data/information into Excel, which is becoming more and more frequent. No formal SQL Server or SQL education, all via books and the internet - like SQL Server Central. I'm at a stage where I am using more and more Pass Through Queries in Access. Along with that I find I start solving most query problems by using SSMS instead of Access Query Design. Especially when the request is one of those ad hoc ones for the results in Excel.
This "little project" was a customer request that got modified after I gave them a 1st pass. The problem I encountered was the statement was on so many lines I had to scroll up and down to select parts and selected at wrong points. Then I would have to start over - good thing I keep a copy of the last good statement. :crazy:
Sean - Regarding the CAST and VARCHAR(7). I know the values will not exceed 48.999 and the decimal will be in 1/8's of an inch increment. So, I guess 5,3 would have been "better". I perked up reading your comment - I visualized that it 'jumped out at you'. That's Cool! 🙂
J - I wasn't asking about the formatting, per se, except that I do realize it does make a big difference in readability, which is a first step of the answer to my question. I've been using Instant SQL Formatter, considering purchasing Pretty Printer. Thanks for the tip about ApexSQL. I'll add that to my list of candidates.
Jeff - Comments embedded in the statement. Sheesh! (To myself) It never occurred to me.
Breaking up code - this will be new territory for me. Do you (anyone reading this) have any suggested reading materials? Either internet or books. There's a statement on my resume that's so true, "The road to success is always under construction."
Paul - Thanks for echoing support to Jeff's comments. It's good to hear when someone does that - seems to drive the point home.
Again, thanks everyone for your help!
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
September 23, 2012 at 5:43 am
CELKO (9/22/2012)
Does that help?
It might; but it would have been more likely to if you had read this before replying:
EdA ROC (9/22/2012)
A little background about me and my situation. We have a 3rd party ERP system, SQL Server based. My primary job is to use Access to develop reports.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 23, 2012 at 5:58 am
EdA ROC (9/22/2012)
A little background about me and my situation. We have a 3rd party ERP system, SQL Server based. My primary job is to use Access to develop reports.
I'm no Access expert, but I think it is true to say that pass-through queries have to be a single SELECT statement. If you are able to create objects in the SQL Server, you could consider creating a stored procedure there. Access can call a stored procedure, and SQL Server stored procedures can contain multiple statements and use temporary objects like table variables and temporary tables.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 23, 2012 at 6:59 am
🙂
So much to learn! And so little time. (ha, ha)
This job has been quite the learning experience for me. Before 2004, when I started, I had developed two custom ERP packages over the span of 24 years. Both with what I call linear programming languages - an obscure INFO by HENCO, and then a Pick like language. When I started here I had little knowledge of Access and none of OOP and SQL. I am getting better all the time, but I still haven't mastered thinking OOP (I catch myself still translating from linear to OOP). Pretty pleased with myself that I'm creating more and more SQL statements without Access Design, it's challenging, but fun!
Celko - Thanks for your comments.
I understand "the rule" about leaving formatting to the presentation layer. But, like driving a car, we break rules for convenience ... a little over the speed limit, not leaving the recommended distance between cars, etc. The "losing the count" was no problem, the request was for whether or not the operations existed. To me, what I did was quicker than waiting until the data was copied into Excel then ... create a column, add =IF(F1>0,"Y",""), fill down, insert another column, copy & paste special:values, and finally deleting the initial column.
I like your advise about the steps for constructing a statement. I copied them down on an index card, will tape it to my monitor at work and follow the steps. One part I already do - identifying the data sources before beginning the assembly process.
As for names? As I mentioned, the database isn't my design. You should see some of the things they do. For example, A Job (JOB_NUMBER) consists of one or more Orders (ORDER_NO). Yet, in some tables they use ORDER_NO for a Job#. You should see all the notes I have in my Data Dictionary.
Hey Everyone! Thanks again and have a fun week.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
September 23, 2012 at 12:43 pm
EdA ROC (9/22/2012)
Breaking up code - this will be new territory for me. Do you (anyone reading this) have any suggested reading materials? Either internet or books.
To be honest, I've never looked for one. It's more of an art than a science, I guess.
The way I do it is to start by looking at the execution plan for arrows that have rowcounts larger than the table they're drawing from which normally means there's some form of many-to-many join going on (For things like a Tally Table, that might not always be a bad thing) and start whittling there.
The other thing I do is to look for a "driver". That is, the table(s) and any related joins that drive or are considered to be the core for the rest of the query. These "drivers" can usually be isolated in a Temp Table for an improvement in performance.
I believe I have a simple example I can provide. I just have to find it in my archives.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 23, 2012 at 3:12 pm
LOL - The first thing that pops into my head is that "10,000 Hours Rule" (Malcolm Gladwell, more familiar as a quote by Bill Gates).
Thanks for the advise. ... I am getting some tips that I hadn't anticipated when I posed the question.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
September 23, 2012 at 11:33 pm
I'm not sure why no one mentioned this (maybe I missed it?) but my favorite way to keep the pieces of a complex query understandable is Common Table Expressions.
I think that indented and nested derived tables are obscure, obtuse and obfuscating to the task at hand.
Excuse my (feeble) attempt at doing this with your query. I'm not sure it works as is but at least it should give you an idea of what I'm talking about.
;WITH
-- Comment JobList CTE here
JobList AS (
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),
-- Comment OpsUsed CTE here
OpsUsed AS (
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
FROM (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
LEFT JOIN OPERATIONS ops
ON ops.SPEC_NO = JobList.MfrSpec
WHERE ops.MACH_NO IN ( 288, 290, 292 )
GROUP BY JobNum) JobOpsList)
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
ON OpsUsed.JobNum = o.JOB_NUMBER
WHERE o.CSCODE = '2507'
AND ORDER_DATE >= '1/1/2012'
AND COMPLETION_FLG <> 'X'
Note that you can now comment each of the CTEs.
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 8:47 am
CELKO (9/22/2012)
...For example, when you say “LEFT OUTER JOIN Grade_File AS GF ON GF.grade_cd = S.grade_cd” probably should have been done with a REFERENCES in the DDL and not with a join at run time that will be repeated hundreds of thousands of times.
Can you elaborate on this a little more?
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.
September 24, 2012 at 10:42 am
dwain.c (9/23/2012)
I'm not sure why no one mentioned this (maybe I missed it?) but my favorite way to keep the pieces of a complex query understandable is Common Table Expressions.
CTEs certainly can make a large query easier to comprehend for humans, though some people do find nested derived tables easier to debug, because you can highlight a section (or several nested sections) and run the query or look at the query plan directly. Personally I can work with either, so long as it is well laid out and commented.
Unless the circumstances are very special, I still look to break such queries up to help the optimizer find a good plan now and in the future when data distribution changes. Large queries with deep query trees are prone to cardinality estimation errors and tend to make the optimizer rely more on heuristics than detailed analysis.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 24, 2012 at 12:13 pm
SQL Kiwi (9/24/2012)
dwain.c (9/23/2012)
I'm not sure why no one mentioned this (maybe I missed it?) but my favorite way to keep the pieces of a complex query understandable is Common Table Expressions.CTEs certainly can make a large query easier to comprehend for humans, though some people do find nested derived tables easier to debug, because you can highlight a section (or several nested sections) and run the query or look at the query plan directly. Personally I can work with either, so long as it is well laid out and commented.
Unless the circumstances are very special, I still look to break such queries up to help the optimizer find a good plan now and in the future when data distribution changes. Large queries with deep query trees are prone to cardinality estimation errors and tend to make the optimizer rely more on heuristics than detailed analysis.
+1
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply