can you write this query some other manner.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

can you write this query some other manner.

Author

Topic

rajadadi
Starting Member

30 Posts

Posted - 2010-05-04 : 05:20:42

can you write this query some other manner.

Use ARSystem_dev

SELECT

Distinct AST.Configuration_Item_ID AS [Configuration item ID]

, AST.External_System_Number AS [External system number]

, STA.Value AS [CI Status]

, AST.Status_Reason AS [Status Reason]

, AST.Impact AS [Impact]

, AST.Urgency AS [Urgency]

, AST.On_Loan AS [On Loan]

, AST.Quantity AS [Quantity]

, AST.Quantity_Used AS [Quantity used]

, AST.Quantity_Remaining AS [Quantity remaining]

, AST.Tag_Number AS [Tag Number]

, AST.Serial_Number AS [Serial Number]

, AST.Name AS [CI Name]

, AST.System_Roles AS [CI Class]

, AST.System_Type AS [CI Type]

, AST.Configuration_Item_Role AS [CI Role]

, AST.Description AS [Description]

, AST.Environment AS [Environment]

, BCI.Value AS [Baseline CI]

, BUN.Value AS [Bundle]

, AST.Company AS [Company]

, AST.Product_Categorization_Tier_1 AS [Product Categorization Tier 1]

, AST.Product_Categorization_Tier_2 AS [Product Categorization Tier 2]

, AST.Product_Categorization_Tier_3 AS [Product Categorization Tier 3]

, AST.Product_Name AS [Product Name]

, AST.Product_Model_Version AS [Product Model]

, AST.Manufacturer AS [Manufacturer]

, AST.Specific_SLA_Criteria AS [Specific SLA criteria]

, AST.Company AS [Location Company]

, AST.Region AS [Region]

, AST.Site_Group AS [Site group]

, AST.Site AS [Site]

, AST.Site_Zone AS [Site Zone]

, AST.Additional_Location_Details AS [Additional Location Details]

, D.Association_type01 AS [Contact association type]

, D.Request_description01 AS [Contact name]

, D.Request_ID01 AS [Contact ID]

, D.Request_type01 AS [Contact type]

, dbo.cgi_fn_ConvertDateTime(AST.Request_Date, 120) AS [Requisition date]

, dbo.cgi_fn_ConvertDateTime(AST.PO_Date, 120) AS [Purchase date]

, dbo.cgi_fn_ConvertDateTime(AST.Acquisition_Date, 120) AS [Acquisition date]

, dbo.cgi_fn_ConvertDateTime(AST.Submit_Date, 120) AS [CMDB Submit date]

, dbo.cgi_fn_ConvertDateTime(AST.Configuration_Date, 120) AS [Configuration date]

, dbo.cgi_fn_ConvertDateTime(AST.Inventory_Date, 120) AS [Inventory date]

, dbo.cgi_fn_ConvertDateTime(AST.In_Service_Date, 120) AS [In Service date]

, dbo.cgi_fn_ConvertDateTime(AST.Out_of_Service_Date, 120) AS [Out of Service date]

, dbo.cgi_fn_ConvertDateTime(AST.Retired_Date, 120) AS [Retired date]

, dbo.cgi_fn_ConvertDateTime(AST.Disposal_Date, 120) AS [Disposed date]

, dbo.cgi_fn_ConvertDateTime(AST.Last_Audit_Date, 120) AS [Last Audit Date]

, dbo.cgi_fn_ConvertDateTime(AST.Next_Audit_Date, 120) AS [Next Audit Date]

, AST.Submitter AS [Submitter]

, AST.Last_Modified_By AS [Last Modified By]

, dbo.cgi_fn_ConvertDateTime(AST.Last_Modified_Date, 120) AS [Last modified date]

FROM dbo.AST_Configuration_Item__CI_ AST (nolock)

LEFT OUTER JOIN dbo.AST_CTM_Associations D (nolock) ON D.Request_ID02 = AST.Configuration_Item_ID

LEFT OUTER JOIN dbo.ARSchema SST (NOLOCK) ON SST.Name = 'AST:Configuration Item (CI)'

LEFT OUTER JOIN dbo.field_enum_values STA (NOLOCK)

ON STA.schemaid = sst.schemaid AND STA.fieldid = 7 AND AST.CI_Status = STA.enumID

LEFT OUTER JOIN dbo.field_enum_values BCI (NOLOCK)

ON BCI.schemaid = sst.schemaid AND BCI.fieldid = 1000001592 AND AST.Baseline_CI = BCI.enumID

LEFT OUTER JOIN dbo.field_enum_values BUN (NOLOCK)

ON BUN.schemaid = sst.schemaid AND BUN.fieldid = 1000004090 AND AST.Bundle = BUN.enumID

WHERE 1=1

AND AST.Company = 'CGI.CANRGM'

AND AST.Product_Categorization_Tier_1 = 'Hardware'

rajesh

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-04 : 05:26:02

What is your problem with this query?

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-04 : 05:53:19

The "WHERE 1 = 1" leads me to the conclusion this query is dynamically built in the front-end application.
And as such, the variations may be way too many to cover here.

I suspect it is the performance of the query you want help with?

N 56°04'39.26"
E 12°55'05.63"

rajadadi
Starting Member

30 Posts

Posted - 2010-05-04 : 05:55:07

Yes Performance problem. can you write another manner

rajesh

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-04 : 06:56:31

Maybe the function "dbo.cgi_fn_ConvertDateTime(AST.Request_Date, 120)" is an approach for speeding up.
Can you show the source of the function and an example of the given data like Request_Date?

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 13:00:52

where you able to find out bottleneck cause? have you had a look at execution plan?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-05-04 : 15:58:48

The things that scare me about this query, at first glance, are:

SELECT DISTINCT - usually symptomatic of a poorly built query (i.e. the fact that duplicates are knowingly being selected and then SQL has to SORT the results in order to remove the duplicates)

The use of the NOLOCK hint - other than skilled DBAs I haven't met any DEVs that actually understand the dangers of using this, so I draw the conclusion that in this example its been put in to solve some other problem - without proper understanding of how dangerous it can be.

All JOINs are OUTER joins - is that really necessary? are there no relational constrains in your application that mean you can safely use INNER JOINs?

No care about formatting of your query; most places I've been where that is the case code is thrown together, rather than "engineered". There is even no consistency for whether JOIN Condition columns for the table-being-joined are placed on the left or the Right; Case is inconsistently applied - sometimes its "ID" sometimes its "id"; one table has "__" (Double-underscore) in its name - I suppose there is reason? rather than it being a typo at some point that everyone has just lived with ever since, rather than fixing?

I would expect to see a comment (at the very least) on code like:


AND BCI.fieldid = 1000001592

or does everyone there instinctively know what "1000001592" is?

If you are going to do that type of thing(*) then I would have thought it better to use the NAME of the column, rather than a Magic-Number, or even a VIEW to encapsulate it.

(*) using a generic "field_enum_values" table for lookup is debatable; I don't have strong views, but plenty of learned people do.

cgi_fn_ConvertDateTime() suggests that you are doing Date Formatting server-side, rather than client-side which is a much better place to do it.

I've made a number of assumptions there, you're welcome to debunk any that I have got wrong.

Do you have indexes on all the key columns used in JOINs?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources