July 5, 2008 at 4:34 am
Hi everyone,
I want to improve the performance of following query...Its taking more time in executing..Pls help me.
SELECT dbo.QUOTATION.QuotationID, dbo.Gurantee.GuranteeDESC, dbo.TypeofSupply.TypeofSupplyDESC, dbo.QUOTATION.PackingForwardingText,
dbo.QUOTATION.InsuranceText, dbo.WARRANTY.WARSTDTEXT, dbo.DELIVERYSTD.DELIVERYSTDTEXT, dbo.FreightTerms.FreightTerms,
dbo.PaymentTerm.PaymentTerm, dbo.TaxStructureType.TaxStructureTypeDESC, dbo.vwpricetype.OutPriceTypeDesc, dbo.ITEM.ITEMNAME,
dbo.Charges.ChargesDESC, ITEM_1.ITEMNAME AS lanCostItem, dbo.CURRENCIES.CURRENCYNAME AS LandCostCurrency,
dbo.QuotationDET15.PrincipalsPrice, ScopeOfSupplyMaster_1.ScopeOfSupplyMasterDESC AS Accessories, ScopeOfSupplyMaster_1.Make,
dbo.InstallationAndComm.InstallationAndCommDESC, dbo.NotesType.NotesTypeDESC, dbo.QUOTATION.TaxStructure1ID,
dbo.QUOTATIONDET8.EnclosureID, dbo.QUOTATION.TaxStructureID, dbo.QUOTATIONDET1.ScopeOfSupplyMasterID,
dbo.QUOTATIONDET12.ListofExclusion, dbo.QUOTATIONDET13.SalientFeatures
FROM dbo.InstallationAndComm RIGHT OUTER JOIN
dbo.QUOTATIONDET13 RIGHT OUTER JOIN
dbo.QUOTATION ON dbo.QUOTATIONDET13.QuotationID = dbo.QUOTATION.QuotationID LEFT OUTER JOIN
dbo.QUOTATIONDET12 ON dbo.QUOTATION.QuotationID = dbo.QUOTATIONDET12.QuotationID LEFT OUTER JOIN
dbo.QUOTATIONDET1 ON dbo.QUOTATION.QuotationID = dbo.QUOTATIONDET1.QuotationID LEFT OUTER JOIN
dbo.QUOTATIONDET8 ON dbo.QUOTATION.QuotationID = dbo.QUOTATIONDET8.QUOTATIONID LEFT OUTER JOIN
dbo.QuotationDET19 ON dbo.QUOTATION.QuotationID = dbo.QuotationDET19.QuotationID LEFT OUTER JOIN
dbo.QuotationDET17 ON dbo.QUOTATION.QuotationID = dbo.QuotationDET17.QuotationID LEFT OUTER JOIN
dbo.QuotationDET18 ON dbo.QUOTATION.QuotationID = dbo.QuotationDET18.QuotationID LEFT OUTER JOIN
dbo.QuotationDET15 ON dbo.QUOTATION.QuotationID = dbo.QuotationDET15.QuotationID LEFT OUTER JOIN
dbo.QUOTATIONDET5 ON dbo.QUOTATION.QuotationID = dbo.QUOTATIONDET5.QUOTATIONID LEFT OUTER JOIN
dbo.NotesType ON dbo.QuotationDET19.NotesTypeID = dbo.NotesType.NotesTypeID ON
dbo.InstallationAndComm.InstallationAndCommID = dbo.QuotationDET17.InstCommission LEFT OUTER JOIN
dbo.ScopeOfSupplyMaster AS ScopeOfSupplyMaster_1 ON
dbo.QuotationDET18.Accessories = ScopeOfSupplyMaster_1.ScopeOfSupplyMasterID LEFT OUTER JOIN
dbo.TaxStructureType RIGHT OUTER JOIN
dbo.TaxStructureType AS TaxStructureType_1 RIGHT OUTER JOIN
dbo.TaxStructure ON TaxStructureType_1.TaxStructureTypeID = dbo.TaxStructure.TaxStructureTypeID ON
dbo.TaxStructureType.TaxStructureTypeID = dbo.TaxStructure.TaxStructureTypeID ON
dbo.QUOTATION.TaxStructureID = dbo.TaxStructure.TaxStructureID LEFT OUTER JOIN
dbo.CURRENCIES ON dbo.QuotationDET15.Currencyid = dbo.CURRENCIES.CURRENCIESID LEFT OUTER JOIN
dbo.ITEM AS ITEM_1 ON dbo.QuotationDET15.ItemId = ITEM_1.ITEMID LEFT OUTER JOIN
dbo.Charges ON dbo.QUOTATIONDET5.CHARGESID = dbo.Charges.ChargesID LEFT OUTER JOIN
dbo.PaymentTerm ON dbo.QUOTATION.PaymentTermID = dbo.PaymentTerm.PaymentTermID LEFT OUTER JOIN
dbo.FreightTerms ON dbo.QUOTATION.Freight = dbo.FreightTerms.FreightTermsID LEFT OUTER JOIN
dbo.DELIVERYSTD ON dbo.QUOTATION.DeliveryID = dbo.DELIVERYSTD.DELIVERYSTDID LEFT OUTER JOIN
dbo.WARRANTY ON dbo.QUOTATION.WarrantyID = dbo.WARRANTY.WARRANTYID LEFT OUTER JOIN
dbo.vwpricetype RIGHT OUTER JOIN
dbo.QUOTATIONDET ON dbo.vwpricetype.OutPriceTypeDETID = dbo.QUOTATIONDET.PriceType LEFT OUTER JOIN
dbo.ITEM ON dbo.QUOTATIONDET.ITEMID = dbo.ITEM.ITEMID ON dbo.QUOTATION.QuotationID = dbo.QUOTATIONDET.QuotationID LEFT OUTER JOIN
dbo.TypeofSupply ON dbo.QUOTATION.TypeOfSupplyID = dbo.TypeofSupply.TypeofSupplyID LEFT OUTER JOIN
dbo.Gurantee ON dbo.QUOTATION.GuaranteeId = dbo.Gurantee.GuranteeID
July 5, 2008 at 6:24 am
just a little remark:
avoid using left and right outer joins together.
Choose one and go with that. (I prefer left join because then you're result is formed from top to bottom of the query, so it's more easy to read)
Rewrite your query to use only one form of outer join.
If you can't read the query easily, chances are its performance is likewise.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 5, 2008 at 7:16 am
Reformated the SQL using http://www.sqlinform.com/ to allow easier analysis. Looks like there are some joins that do not have a corresponding "ON" statement, which will result in these joins being changes to "cross joins"
To check, reformat the SQL so that the join/on has the style of:
FROM A
JOIN B
ON B.column_name = A.column_name
Check for any missing join criteria.
Check that the join inlcudes all primary key columns.
As ALZDBA recommended, mixing left and right outer joins usally indicates a logic problem with the SQL.
Check that there are no "branches", will will cause cross-joins such as:
FROM A
JOIN B
ON B.column_name = A.column_name
JOIN C
ON C.column_name = A.column_name
If the join from A to B is by B's foreign key columns AND tye join from A to C is by C's foreign key columns, then there is a cross join between tables B and C.
SELECT dbo.QUOTATION.QuotationID ,
dbo.Gurantee.GuranteeDESC ,
dbo.TypeofSupply.TypeofSupplyDESC ,
dbo.QUOTATION.PackingForwardingText ,
dbo.QUOTATION.InsuranceText ,
dbo.WARRANTY.WARSTDTEXT ,
dbo.DELIVERYSTD.DELIVERYSTDTEXT ,
dbo.FreightTerms.FreightTerms ,
dbo.PaymentTerm.PaymentTerm ,
dbo.TaxStructureType.TaxStructureTypeDESC ,
dbo.vwpricetype.OutPriceTypeDesc ,
dbo.ITEM.ITEMNAME ,
dbo.Charges.ChargesDESC ,
ITEM_1.ITEMNAME AS lanCostItem ,
dbo.CURRENCIES.CURRENCYNAME AS LandCostCurrency ,
dbo.QuotationDET15.PrincipalsPrice ,
ScopeOfSupplyMaster_1.ScopeOfSupplyMasterDESC AS Accessories,
ScopeOfSupplyMaster_1.Make ,
dbo.InstallationAndComm.InstallationAndCommDESC ,
dbo.NotesType.NotesTypeDESC ,
dbo.QUOTATION.TaxStructure1ID ,
dbo.QUOTATIONDET8.EnclosureID ,
dbo.QUOTATION.TaxStructureID ,
dbo.QUOTATIONDET1.ScopeOfSupplyMasterID ,
dbo.QUOTATIONDET12.ListofExclusion ,
dbo.QUOTATIONDET13.SalientFeatures
FROM dbo.InstallationAndComm
RIGHT OUTER JOIN dbo.QUOTATIONDET13
RIGHT OUTER JOIN dbo.QUOTATION
ON dbo.QUOTATIONDET13.QuotationID = dbo.QUOTATION.QuotationID
LEFT OUTER JOIN dbo.QUOTATIONDET12
ON dbo.QUOTATION.QuotationID = dbo.QUOTATIONDET12.QuotationID
LEFT OUTER JOIN dbo.QUOTATIONDET1
ON dbo.QUOTATION.QuotationID = dbo.QUOTATIONDET1.QuotationID
LEFT OUTER JOIN dbo.QUOTATIONDET8
ON dbo.QUOTATION.QuotationID = dbo.QUOTATIONDET8.QUOTATIONID
LEFT OUTER JOIN dbo.QuotationDET19
ON dbo.QUOTATION.QuotationID = dbo.QuotationDET19.QuotationID
LEFT OUTER JOIN dbo.QuotationDET17
ON dbo.QUOTATION.QuotationID = dbo.QuotationDET17.QuotationID
LEFT OUTER JOIN dbo.QuotationDET18
ON dbo.QUOTATION.QuotationID = dbo.QuotationDET18.QuotationID
LEFT OUTER JOIN dbo.QuotationDET15
ON dbo.QUOTATION.QuotationID = dbo.QuotationDET15.QuotationID
LEFT OUTER JOIN dbo.QUOTATIONDET5
ON dbo.QUOTATION.QuotationID = dbo.QUOTATIONDET5.QUOTATIONID
LEFT OUTER JOIN dbo.NotesType
ON dbo.QuotationDET19.NotesTypeID = dbo.NotesType.NotesTypeID
ON dbo.InstallationAndComm.InstallationAndCommID = dbo.QuotationDET17.InstCommission
LEFT OUTER JOIN dbo.ScopeOfSupplyMaster AS ScopeOfSupplyMaster_1
ON dbo.QuotationDET18.Accessories = ScopeOfSupplyMaster_1.ScopeOfSupplyMasterID
LEFT OUTER JOIN dbo.TaxStructureType
RIGHT OUTER JOIN dbo.TaxStructureType AS TaxStructureType_1
RIGHT OUTER JOIN dbo.TaxStructure
ON TaxStructureType_1.TaxStructureTypeID = dbo.TaxStructure.TaxStructureTypeID
ON dbo.TaxStructureType.TaxStructureTypeID = dbo.TaxStructure.TaxStructureTypeID
ON dbo.QUOTATION.TaxStructureID = dbo.TaxStructure.TaxStructureID
LEFT OUTER JOIN dbo.CURRENCIES
ON dbo.QuotationDET15.Currencyid = dbo.CURRENCIES.CURRENCIESID
LEFT OUTER JOIN dbo.ITEM AS ITEM_1
ON dbo.QuotationDET15.ItemId = ITEM_1.ITEMID
LEFT OUTER JOIN dbo.Charges
ON dbo.QUOTATIONDET5.CHARGESID = dbo.Charges.ChargesID
LEFT OUTER JOIN dbo.PaymentTerm
ON dbo.QUOTATION.PaymentTermID = dbo.PaymentTerm.PaymentTermID
LEFT OUTER JOIN dbo.FreightTerms
ON dbo.QUOTATION.Freight = dbo.FreightTerms.FreightTermsID
LEFT OUTER JOIN dbo.DELIVERYSTD
ON dbo.QUOTATION.DeliveryID = dbo.DELIVERYSTD.DELIVERYSTDID
LEFT OUTER JOIN dbo.WARRANTY
ON dbo.QUOTATION.WarrantyID = dbo.WARRANTY.WARRANTYID
LEFT OUTER JOIN dbo.vwpricetype
RIGHT OUTER JOIN dbo.QUOTATIONDET
ON dbo.vwpricetype.OutPriceTypeDETID = dbo.QUOTATIONDET.PriceType
LEFT OUTER JOIN dbo.ITEM
ON dbo.QUOTATIONDET.ITEMID = dbo.ITEM.ITEMID
ON dbo.QUOTATION.QuotationID = dbo.QUOTATIONDET.QuotationID
LEFT OUTER JOIN dbo.TypeofSupply
ON dbo.QUOTATION.TypeOfSupplyID = dbo.TypeofSupply.TypeofSupplyID
LEFT OUTER JOIN dbo.Gurantee
ON dbo.QUOTATION.GuaranteeId = dbo.Gurantee.GuranteeID
SQL = Scarcely Qualifies as a Language
July 5, 2008 at 12:04 pm
Well my first suggestion is to stop using Query Wizard, because it makes it really easy to make really bad and ugly queries like this.
Secondly, to do anything with this, we need to see the table definitions and the table relationships. In fact, read this areticle to see how to get better response from these forums:http://www.sqlservercentral.com/scripts/tables/62545/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 5, 2008 at 12:22 pm
rbarryyoung (7/5/2008)
Secondly, to do anything with this, we need to see the table definitions and the table relationships.
And for performance problems, the index definitions are also very important. In addition the query execution plan (in xml format) will also be useful. Save the exec plan as a .sqlplan file, zip and attach to your post.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 5, 2008 at 1:10 pm
rbarryyoung (7/5/2008)
Well my first suggestion is to stop using Query Wizard, because it makes it really easy to make really bad and ugly queries like this.Secondly, to do anything with this, we need to see the table definitions and the table relationships. In fact, read this areticle to see how to get better response from these forums:http://www.sqlservercentral.com/scripts/tables/62545/
Ummm... I think you pasted the wrong URL... I think the article you actually wanted to point to is...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
--Jeff Moden
Change is inevitable... Change for the better is not.
July 5, 2008 at 1:22 pm
OK, I've cleaned up the syntax of this so that it is much easier to read, including removing all of the "dbo."'s that were just eye clutter and changing all of the RIGHT OUTER JOINs to LEFT, which rationalizes the set logic and makes it much clearer what is going on here and what you are trying to do.
One of the things that was immediately clear after I did this is that the TaxStructureType relationships we almost certainly wrong, so I cleaned that up as well:
SELECT QUOTATION.QuotationID
, Gurantee.GuranteeDESC
, TypeofSupply.TypeofSupplyDESC
, QUOTATION.PackingForwardingText
, QUOTATION.InsuranceText
, WARRANTY.WARSTDTEXT
, DELIVERYSTD.DELIVERYSTDTEXT
, FreightTerms.FreightTerms
, PaymentTerm.PaymentTerm
, TaxStructureType.TaxStructureTypeDESC
, vwpricetype.OutPriceTypeDesc
, ITEM.ITEMNAME
, Charges.ChargesDESC
, ITEM_1.ITEMNAME AS lanCostItem
, CURRENCIES.CURRENCYNAME AS LandCostCurrency
, QuotationDET15.PrincipalsPrice
, Scope.ScopeOfSupplyMasterDESC AS Accessories
, Scope.Make
, InstallationAndComm.InstallationAndCommDESC
, NotesType.NotesTypeDESC
, QUOTATION.TaxStructure1ID
, QUOTATIONDET8.EnclosureID
, QUOTATION.TaxStructureID
, QUOTATIONDET1.ScopeOfSupplyMasterID
, QUOTATIONDET12.ListofExclusion
, QUOTATIONDET13.SalientFeatures
FROM QUOTATION
LEFT OUTER JOIN QUOTATION1DET3 ON QUOTATION.QuotationID = QUOTATIONDET13.QuotationID
LEFT OUTER JOIN QUOTATIONDET12 ON QUOTATION.QuotationID = QUOTATIONDET12.QuotationID
LEFT OUTER JOIN QUOTATIONDET1 ON QUOTATION.QuotationID = QUOTATIONDET1.QuotationID
LEFT OUTER JOIN QUOTATIONDET8 ON QUOTATION.QuotationID = QUOTATIONDET8.QUOTATIONID
LEFT OUTER JOIN QuotationDET19 ON QUOTATION.QuotationID = QuotationDET19.QuotationID
LEFT OUTER JOIN NotesType ON QuotationDET19.NotesTypeID = NotesType.NotesTypeID
LEFT OUTER JOIN QuotationDET17 ON QUOTATION.QuotationID = QuotationDET17.QuotationID
LEFT OUTER JOIN InstallationAndComm ON InstallationAndComm.InstallationAndCommID = QuotationDET17.InstCommission
LEFT OUTER JOIN QuotationDET18 ON QUOTATION.QuotationID = QuotationDET18.QuotationID
LEFT OUTER JOIN ScopeOfSupplyMaster AS Scope ON QuotationDET18.Accessories = Scope.ScopeOfSupplyMasterID
LEFT OUTER JOIN QuotationDET15 ON QUOTATION.QuotationID = QuotationDET15.QuotationID
LEFT OUTER JOIN CURRENCIES ON QuotationDET15.Currencyid = CURRENCIES.CURRENCIESID
LEFT OUTER JOIN ITEM AS ITEM_1 ON QuotationDET15.ItemId = ITEM_1.ITEMID
LEFT OUTER JOIN QUOTATIONDET5 ON QUOTATION.QuotationID = QUOTATIONDET5.QUOTATIONID
LEFT OUTER JOIN Charges ON QUOTATIONDET5.CHARGESID = Charges.ChargesID
LEFT OUTER JOIN PaymentTerm ON QUOTATION.PaymentTermID = PaymentTerm.PaymentTermID
LEFT OUTER JOIN FreightTerms ON QUOTATION.Freight = FreightTerms.FreightTermsID
LEFT OUTER JOIN DELIVERYSTD ON QUOTATION.DeliveryID = DELIVERYSTD.DELIVERYSTDID
LEFT OUTER JOIN WARRANTY ON QUOTATION.WarrantyID = WARRANTY.WARRANTYID
LEFT OUTER JOIN TypeofSupply ON QUOTATION.TypeOfSupplyID = TypeofSupply.TypeofSupplyID
LEFT OUTER JOIN Gurantee ON QUOTATION.GuaranteeId = Gurantee.GuranteeID
LEFT OUTER JOIN TaxStructure ON QUOTATION.TaxStructureID = TaxStructure.TaxStructureID
LEFT OUTER JOIN TaxStructureType ON TaxStructureType.TaxStructureTypeID = TaxStructure.TaxStructureTypeID
LEFT OUTER JOIN QUOTATIONDET ON QUOTATION.QuotationID = QUOTATIONDET.QuotationID
LEFT OUTER JOIN ITEM ON QUOTATIONDET.ITEMID = ITEM.ITEMID
LEFT OUTER JOIN vwpricetype ON vwpricetype.OutPriceTypeDETID = QUOTATIONDET.PriceType
Fixing the TaxStructureType stuff will probably help a small amount, unless that's a 1-to-many from QUOTATION to TaxStructureType, in which case it will help a lot (but it is likely many-to-1, instead).
Now my guess is that QUOTATION's relationship to most of these tables is either Many-To-1 or 1-to-(0 or 1), because otherwise, what Jason said applies: all of the 1-to-many relationships in this query will multiply with each other, which is almost certainly NOT what you want.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 5, 2008 at 2:28 pm
Heh... I thought you said it was cleaned up... it has leading commas in it 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
July 5, 2008 at 4:47 pm
While that is a personal preference of mine, I do have practical reasons for it also. 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 5, 2008 at 5:10 pm
Ok... here we go... other than personal preference, what are the practical reasons why you do this?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 5, 2008 at 5:53 pm
Well, off the top of my head: I always put the Operators in front (and comma is the column-list concatenation operator) because:
1) in some cases, like FROM clauses it makes it easier to "rationalize" the appearance (the OP's reworked query is a good example) with the structure through indentation because the subordinate operand is usually the latter one (usually true with LEFT JOINS).
2) This also allows me to keep all subordinate syntactic elements (join-type, table-expression, ON clause) together often on the same line. This makes it easier to comment and uncomment these as logically separated elements. So in my version of the OP's query this can be done easily. Putting the JOINS on the back-side would not work the same unless I preferred RIGHT JOINS, which I do not.
3) For consistency, I do the same in cases where these reasons would work just as well with the operator on the end (like commas in the column list). But also because:
4) Code-Generators: I write a lot of them for my projects, and I like them to look good (not like that awful stuff the query wizard generates) so I try to incorporate formatting rules consistent with what I write by hand and vice-versa.
5) When code-generating lists of things in SQL you usually have to put "infix" operators between them (i.e: "X+Y+Z+...", or "A,B,C,..." or "Table1 Join Table2 ON {expr} Join Table3 On {expr} ..."). This means that there is one more operator than operand, so one of the operation strings generated for the list has to be "different".
There are lots of ways to handle this, but IMHO the easiest way is to just special case COUNT=1, which is far easier than special casing COUNT={however I figure out how long this list is while I am still generating it} or the other alternative, which to modify the first or last element of the list after it is generated (much more error-prone, IMHO).
Because I make the first element is special, all of the other elements are formatted the same: Operand first then Operator (followed by by any subordinate clauses or expressions). So naturally it's easier to generate my Operators first on the line and then my Operands, with my line-break either always at the beginning or always at the end. Sure, I could still insert the line-break in the middle to reverse the appearance of the operator & operand, but why bother?
6) This also allows me to incorporate the additional special-casing of putting the first list element on the same line as the clause Keyword (SELECT, FROM, etc.), so that's a twofer.
But that's what works for me, you might be different... 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 5, 2008 at 7:53 pm
rbarryyoung (7/5/2008)
Well, off the top of my head: I always put the Operators in front (and comma is the column-list concatenation operator) because:
I agree that operators should start a line (and keywords should start a line), but lookup "operators" and "keywords" in Books Online and see if you find comma as either an operator or a keyword... and commas do not indicate concatentation... + does.
This makes it easier to comment and uncomment these as logically separated elements.
Heh... Horse muffins... leading commas only make it easier to comment out the last in a list... just like trailing commas make it easier to comment out the first in a list. Either way, the intermediates in the list are identically easy to comment out.
4) Code-Generators: I write a lot of them for my projects, and I like them to look good (not like that awful stuff the query wizard generates) so I try to incorporate formatting rules consistent with what I write by hand and vice-versa.
So why do you think it's any more difficult to write a pretty code generation with trailing commas?
5) When code-generating lists of things in SQL you usually have to put "infix" operators between them (i.e: "X+Y+Z+...", or "A,B,C,..." or "Table1 Join Table2 ON {expr} Join Table3 On {expr} ..."). This means that there is one more operator than operand, so one of the operation strings generated for the list has to be "different".
There are lots of ways to handle this, but IMHO the easiest way is to just special case COUNT=1, which is far easier than special casing COUNT={however I figure out how long this list is while I am still generating it} or the other alternative, which to modify the first or last element of the list after it is generated (much more error-prone, IMHO).
Because I make the first element is special, all of the other elements are formatted the same: Operand first then Operator (followed by by any subordinate clauses or expressions). So naturally it's easier to generate my Operators first on the line and then my Operands, with my line-break either always at the beginning or always at the end. Sure, I could still insert the line-break in the middle to reverse the appearance of the operator & operand, but why bother?
COUNT = 1???? Sounds a lot like RBAR... you know there are ways to avoid that and the very method (you spke of) of making the first one "special" can just as easily make trailing commas...
USE AdventureWorks
DECLARE @SQL1 VARCHAR(8000)
DECLARE @SQL2 VARCHAR(8000)
DECLARE @SQL3 VARCHAR(8000)
DECLARE @EoL VARCHAR(2)
SET @EoL = CHAR(13)
SELECT @SQL1 = ' SELECT '
SELECT @SQL2 = COALESCE(@SQL2+','+@Eol+SPACE(8),'')+Column_Name
FROM Information_Schema.Columns
WHERE Table_Schema = 'Person'
AND Table_Name = 'Address'
SELECT @SQL3 = @Eol + ' FROM AdventureWorks.Person.Address'
PRINT @SQL1+@SQL2+@SQL3
6) This also allows me to incorporate the additional special-casing of putting the first list element on the same line as the clause Keyword (SELECT, FROM, etc.), so that's a twofer.
Heh... what did I just do? Same thing...
But that's what works for me, you might be different... 🙂
That's what makes life so interesting... 🙂 Thanks for sharing your thoughts with me, Barry. My counterpoints are not to suggest you change or to suggest they're better (well, except for the COUNT = 1 thing :hehe: )... just showing the way I do it.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 5, 2008 at 9:30 pm
Jeff Moden (7/5/2008)
rbarryyoung (7/5/2008)
Well, off the top of my head: I always put the Operators in front (and comma is the column-list concatenation operator) because:I agree that operators should start a line (and keywords should start a line), but lookup "operators" and "keywords" in Books Online and see if you find comma as either an operator or a keyword... and commas do not indicate concatentation... + does.
Well then, we will soon be in agreement 🙂 ... but more on that in a minute. First, BOL has a list of "Reserved" Keywords, as you might guess, that means that there are unreserved keywords also, so that list is not all of the keywords.
But we were talking about commas and operators, and whether the current BOL's list them or not. Commas are operators, just not explicit ones. Commas in SQL are (as they are in most computer languages) what are called "list concatentation operators", which is to say, when you make lists in SQL, like column lists, or parameter lists, commas are how you concatenate the individual items of that list into one big list. It's just been implicit to the language syntax up until now, so they do not bother to list it explictly in BOL. However, as I recall more formal syntax descriptions of the SQL language have listed it as such for some time now.
And here's the good news: SQL 2008 finally has explicit column lists as intermediate objects, so I fully expect BOL to finally explicitly list comma as an operator (though I haven't looked it up yet, so I could be wrong). So hopefully, we will soon be in full agreement! 😀
This makes it easier to comment and uncomment these as logically separated elements.
Heh... Horse muffins... leading commas only make it easier to comment out the last in a list... just like trailing commas make it easier to comment out the first in a list. Either way, the intermediates in the list are identically easy to comment out.
Ah, ah. You're taking me out of context there. I was talking about JOIN clauses, not commas. I tied it back to commas under consistency and already acknowledged that this point did not apply to commas on its own.
5) When code-generating lists of things in SQL you usually have to put "infix" operators between them (i.e: "X+Y+Z+...", or "A,B,C,..." or "Table1 Join Table2 ON {expr} Join Table3 On {expr} ..."). This means that there is one more operator than operand, so one of the operation strings generated for the list has to be "different".
There are lots of ways to handle this, but IMHO the easiest way is to just special case COUNT=1, which is far easier than special casing COUNT={however I figure out how long this list is while I am still generating it} or the other alternative, which to modify the first or last element of the list after it is generated (much more error-prone, IMHO).
Because I make the first element is special, all of the other elements are formatted the same: Operand first then Operator (followed by by any subordinate clauses or expressions). So naturally it's easier to generate my Operators first on the line and then my Operands, with my line-break either always at the beginning or always at the end. Sure, I could still insert the line-break in the middle to reverse the appearance of the operator & operand, but why bother?
COUNT = 1???? Sounds a lot like RBAR... you know there are ways to avoid that...
Oh Jeff, I thought you knew me better than that. 🙁 And now you're accusing me of using (shudder) Cursors?!? :crying: Well, let me put it this way: I've only know about "Tally" tables for 5 months now, but I've been using "Numbers" tables for over 8 years. So allow me to translate my statments into the proper Modensims:
There are lots of ways to handle this, but IMHO the easiest way is to just special case Tally.N=1, which is far easier than special casing Tally.N={however I figure out how long this list is while I am still generating it} or the other alternative, which to modify the first or
Hopefully, that makes it clearer 🙂
and the very method (you spoke of) of making the first one "special" can just as easily make trailing commas...
"Can"? Yes. "Just as easily"? No. Sometimes, in special cases, but generally, no. I find my way to be easier.
USE AdventureWorksDECLARE @SQL1 VARCHAR(8000)
DECLARE @SQL2 VARCHAR(8000)
DECLARE @SQL3 VARCHAR(8000)
DECLARE @EoL VARCHAR(2)
SET @EoL = CHAR(13)
SELECT @SQL1 = ' SELECT '
SELECT @SQL2 = COALESCE(@SQL2+','+@Eol+SPACE(8),'')+Column_Name
FROM Information_Schema.Columns
WHERE Table_Schema = 'Person'
AND Table_Name = 'Address'
SELECT @SQL3 = @Eol + ' FROM AdventureWorks.Person.Address'
PRINT @SQL1+@SQL2+@SQL3
This would be an unsual case for my work. It only generates one list for one table. Generally, i have to generate one or more lists, per table, for hundreds of tables at a time. Now I suppose I could stop in between every table or list and reset @SQL2 to NULL just so that I could use the Coalesce trick to get my commas in back, but that would be RBA... well you know, the R-word.
Besides, I only use the accumulating Varchar(MAX) techinique about half the time, the other half I'm doing INSERTS instead.
But that's what works for me, you might be different... 🙂
That's what makes life so interesting... 🙂 Thanks for sharing your thoughts with me, Barry. My counterpoints are not to suggest you change or to suggest they're better (well, except for the COUNT = 1 thing :hehe: )... just showing the way I do it.
[/quote]
Yeah, me too. I'm not a style-evangelist like many, which is why I don't usually jump into the mosh pit about "which style is best".
I have my reasons and my methods and they work for me. If they might apply to or work for someone else, that's great, but if not, that's fine too.:cool:
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 6, 2008 at 8:15 pm
All that and a helmet with good grill-work! 😀 Thanks for the feedback, Barry! 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply