Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

How Do You Keep The "Pieces" Of A Complex Statement "Organized" Expand / Collapse
Author
Message
Posted Friday, September 21, 2012 2:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, April 14, 2013 12:31 PM
Points: 112, Visits: 394
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'



"The Road To Success Is Always Under Construction."
Post #1363003
Posted Friday, September 21, 2012 3:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:12 PM
Points: 14,572, Visits: 14,347
Really what you are talking about is formatting. This can be subject to personal preference. There is a free site http://poorsql.com/ 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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1363013
Posted Friday, September 21, 2012 3:40 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 2,196, Visits: 23,254
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
Post #1363021
Posted Friday, September 21, 2012 9:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:54 AM
Points: 38,369, Visits: 35,291
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1363064
Posted Saturday, September 22, 2012 2:10 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, August 18, 2015 7:23 PM
Points: 9,932, Visits: 11,288
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
SQLblog.com
@SQL_Kiwi
Post #1363081
Posted Saturday, September 22, 2012 8:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, April 14, 2013 12:31 PM
Points: 112, Visits: 394
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.

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!



"The Road To Success Is Always Under Construction."
Post #1363107
Posted Sunday, September 23, 2012 5:43 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, August 18, 2015 7:23 PM
Points: 9,932, Visits: 11,288
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
SQLblog.com
@SQL_Kiwi
Post #1363215
Posted Sunday, September 23, 2012 5:58 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, August 18, 2015 7:23 PM
Points: 9,932, Visits: 11,288
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
SQLblog.com
@SQL_Kiwi
Post #1363216
Posted Sunday, September 23, 2012 6:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, April 14, 2013 12:31 PM
Points: 112, Visits: 394

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.


"The Road To Success Is Always Under Construction."
Post #1363225
Posted Sunday, September 23, 2012 12:43 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:54 AM
Points: 38,369, Visits: 35,291
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1363278
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse