Why is my T-SQL code so slow with its output?

• chef423

  SSCertifiable

  Points: 5030

  More actions

  October 16, 2017 at 10:06 am

  #333268

  Select Invoice_Exceptions.Invoice_Number as Invoice#, FORMAT(Invoice_Exceptions.DateTime, 'MM-dd-yyyy') as Void_Date, Invoice_Exceptions.ItemNum as Item_#_Removed, Inventory.ItemName, '$'+CONVERT(varchar(10), CAST(Invoice_Exceptions.Amount as money)) as Amount,
CONVERT(varchar(10), CAST(Invoice_Exceptions.Quantity as INT)) as Quanity, Invoice_Exceptions.Reason_Code, Invoice_Exceptions.LineNum, Invoice_Exceptions.EmpName as Employee, FORMAT(Invoice_Exceptions.DateTime, 'hh:mm tt') as Void_Time
From Invoice_Exceptions
JOIN Inventory ON Invoice_Exceptions.ItemNum = Inventory.ItemNum
JOIN Invoice_Totals on Invoice_Exceptions.Invoice_Number = Invoice_Totals.Invoice_Number and Invoice_Exceptions.DateTime BETWEEN '2017-10-01 03:01:50.000' and '2017-10-31 02:58:50.000' and Invoice_Totals.Payment_Method = 'CA'
ORDER BY Invoice_Exceptions.DateTime ASC

  The Covert/Cast command?

  Any analysis appreciated. I believed Phil game me some hints last week, but I don't know another way to write this.

  Thank you,
  chef

• Thom A

  SSC Guru

  Points: 99126

  More actions

  October 16, 2017 at 10:25 am

  #1963878

  More than likely it's the FORMAT. Instead of this:
  FORMAT(Invoice_Exceptions.DateTime, 'MM-dd-yyyy')
  Try:
  REPLACE(CONVERT(varchar(10), Invoice_Exceptions.DateTime,101),'/','-')

  Hopefully that'll speed things up for you. 🙂

  Edit: Typoed Formatting number.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 16, 2017 at 10:32 am

  #1963880

  chef423 - Monday, October 16, 2017 10:06 AM

  Select Invoice_Exceptions.Invoice_Number as Invoice#, FORMAT(Invoice_Exceptions.DateTime, 'MM-dd-yyyy') as Void_Date, Invoice_Exceptions.ItemNum as Item_#_Removed, Inventory.ItemName, '$'+CONVERT(varchar(10), CAST(Invoice_Exceptions.Amount as money)) as Amount,
CONVERT(varchar(10), CAST(Invoice_Exceptions.Quantity as INT)) as Quanity, Invoice_Exceptions.Reason_Code, Invoice_Exceptions.LineNum, Invoice_Exceptions.EmpName as Employee, FORMAT(Invoice_Exceptions.DateTime, 'hh:mm tt') as Void_Time
From Invoice_Exceptions
JOIN Inventory ON Invoice_Exceptions.ItemNum = Inventory.ItemNum
JOIN Invoice_Totals on Invoice_Exceptions.Invoice_Number = Invoice_Totals.Invoice_Number and Invoice_Exceptions.DateTime BETWEEN '2017-10-01 03:01:50.000' and '2017-10-31 02:58:50.000' and Invoice_Totals.Payment_Method = 'CA'
ORDER BY Invoice_Exceptions.DateTime ASC

  The Covert/Cast command?

  Any analysis appreciated. I believed Phil game me some hints last week, but I don't know another way to write this.

  Thank you,
  chef

  First, FORMAT is a dog.  Try this:
  SELECT
  [ie].[Invoice_Number]
  --, FORMAT([ie].[DateTime], 'MM-dd-yyyy')                    AS [Void_Date]
  , REPLACE(CONVERT(VARCHAR(10), [ie].[DateTime], 101), '/', '-') AS [Void_Date]
  , [ie].ItemNum                                                  AS [Item_#_Removed]
  , [inv].[ItemName]
  , '$' + CONVERT(VARCHAR(10), CAST([ie].[Amount] AS MONEY))      AS [Amount]
  , CONVERT(VARCHAR(10), CAST([ie].[Quantity] AS INT))            AS [Quanity]
  , [ie].[Reason_Code]
  , [ie].[LineNum]
  , [ie].[EmpName]                                                AS [Employee]
  --, FORMAT([ie].[DateTime], 'hh:mm tt')                      AS [Void_Time]
  , CONVERT(VARCHAR(10), [ie].[DateTime], 108)                    AS [Void_Time]
FROM
  Invoice_Exceptions AS [ie]
  JOIN inventory AS [inv]
    ON [ie].[ItemNum]            = [inv].[ItemNum]
  JOIN Invoice_Totals AS [it]
    ON [ie].[Invoice_Number]     = [it].[Invoice_Number]
       AND [ie].[DateTime]       >= '2017-10-01 03:01:50.000'
       AND [ie].[DateTime]       < '2017-10-31 02:58:50.000'
       AND [it].[Payment_Method] = 'CA'
ORDER BY
  [ie].[DateTime] ASC
GO


• chef423

  SSCertifiable

  Points: 5030

  More actions

  October 16, 2017 at 10:48 am

  #1963884

  Lynn Pettis - Monday, October 16, 2017 10:32 AM

  chef423 - Monday, October 16, 2017 10:06 AM

  Select Invoice_Exceptions.Invoice_Number as Invoice#, FORMAT(Invoice_Exceptions.DateTime, 'MM-dd-yyyy') as Void_Date, Invoice_Exceptions.ItemNum as Item_#_Removed, Inventory.ItemName, '$'+CONVERT(varchar(10), CAST(Invoice_Exceptions.Amount as money)) as Amount,
CONVERT(varchar(10), CAST(Invoice_Exceptions.Quantity as INT)) as Quanity, Invoice_Exceptions.Reason_Code, Invoice_Exceptions.LineNum, Invoice_Exceptions.EmpName as Employee, FORMAT(Invoice_Exceptions.DateTime, 'hh:mm tt') as Void_Time
From Invoice_Exceptions
JOIN Inventory ON Invoice_Exceptions.ItemNum = Inventory.ItemNum
JOIN Invoice_Totals on Invoice_Exceptions.Invoice_Number = Invoice_Totals.Invoice_Number and Invoice_Exceptions.DateTime BETWEEN '2017-10-01 03:01:50.000' and '2017-10-31 02:58:50.000' and Invoice_Totals.Payment_Method = 'CA'
ORDER BY Invoice_Exceptions.DateTime ASC

  The Covert/Cast command?

  Any analysis appreciated. I believed Phil game me some hints last week, but I don't know another way to write this.

  Thank you,
  chef

  First, FORMAT is a dog.  Try this:
  SELECT
  [ie].[Invoice_Number]
  --, FORMAT([ie].[DateTime], 'MM-dd-yyyy')                    AS [Void_Date]
  , REPLACE(CONVERT(VARCHAR(10), [ie].[DateTime], 101), '/', '-') AS [Void_Date]
  , [ie].ItemNum                                                  AS [Item_#_Removed]
  , [inv].[ItemName]
  , '$' + CONVERT(VARCHAR(10), CAST([ie].[Amount] AS MONEY))      AS [Amount]
  , CONVERT(VARCHAR(10), CAST([ie].[Quantity] AS INT))            AS [Quanity]
  , [ie].[Reason_Code]
  , [ie].[LineNum]
  , [ie].[EmpName]                                                AS [Employee]
  --, FORMAT([ie].[DateTime], 'hh:mm tt')                      AS [Void_Time]
  , CONVERT(VARCHAR(10), [ie].[DateTime], 108)                    AS [Void_Time]
FROM
  Invoice_Exceptions AS [ie]
  JOIN inventory AS [inv]
    ON [ie].[ItemNum]            = [inv].[ItemNum]
  JOIN Invoice_Totals AS [it]
    ON [ie].[Invoice_Number]     = [it].[Invoice_Number]
       AND [ie].[DateTime]       >= '2017-10-01 03:01:50.000'
       AND [ie].[DateTime]       < '2017-10-31 02:58:50.000'
       AND [it].[Payment_Method] = 'CA'
ORDER BY
  [ie].[DateTime] ASC
GO


  That works! But the Time is in miltary and not AM/PM.

  Also, why when I output 'Results to Text' i get a LOOOONG line under the headers of the date and the time. Any way to format these so they are only the width of the actual date and time, as well as the other columns?

  This would completely solve my issue if I can fix this. I just would like the ----- (dashes to be the length of the actual data)
   
  (I added a screenshot to show you what I mean)

  Thanks guys!!!

  Chris

• Thom A

  SSC Guru

  Points: 99126

  More actions

  October 16, 2017 at 10:56 am

  #1963888

  Give this a go:
  LTRIM(RIGHT(CONVERT(varchar(20),[ie].[DateTime],100),7))
  That'll give you a AM/PM. Do you also need the space?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 16, 2017 at 10:56 am

  #1963889

  chef423 - Monday, October 16, 2017 10:48 AM

  Lynn Pettis - Monday, October 16, 2017 10:32 AM

  chef423 - Monday, October 16, 2017 10:06 AM

  Select Invoice_Exceptions.Invoice_Number as Invoice#, FORMAT(Invoice_Exceptions.DateTime, 'MM-dd-yyyy') as Void_Date, Invoice_Exceptions.ItemNum as Item_#_Removed, Inventory.ItemName, '$'+CONVERT(varchar(10), CAST(Invoice_Exceptions.Amount as money)) as Amount,
CONVERT(varchar(10), CAST(Invoice_Exceptions.Quantity as INT)) as Quanity, Invoice_Exceptions.Reason_Code, Invoice_Exceptions.LineNum, Invoice_Exceptions.EmpName as Employee, FORMAT(Invoice_Exceptions.DateTime, 'hh:mm tt') as Void_Time
From Invoice_Exceptions
JOIN Inventory ON Invoice_Exceptions.ItemNum = Inventory.ItemNum
JOIN Invoice_Totals on Invoice_Exceptions.Invoice_Number = Invoice_Totals.Invoice_Number and Invoice_Exceptions.DateTime BETWEEN '2017-10-01 03:01:50.000' and '2017-10-31 02:58:50.000' and Invoice_Totals.Payment_Method = 'CA'
ORDER BY Invoice_Exceptions.DateTime ASC

  The Covert/Cast command?

  Any analysis appreciated. I believed Phil game me some hints last week, but I don't know another way to write this.

  Thank you,
  chef

  First, FORMAT is a dog.  Try this:
  SELECT
  [ie].[Invoice_Number]
  --, FORMAT([ie].[DateTime], 'MM-dd-yyyy')                    AS [Void_Date]
  , REPLACE(CONVERT(VARCHAR(10), [ie].[DateTime], 101), '/', '-') AS [Void_Date]
  , [ie].ItemNum                                                  AS [Item_#_Removed]
  , [inv].[ItemName]
  , '$' + CONVERT(VARCHAR(10), CAST([ie].[Amount] AS MONEY))      AS [Amount]
  , CONVERT(VARCHAR(10), CAST([ie].[Quantity] AS INT))            AS [Quanity]
  , [ie].[Reason_Code]
  , [ie].[LineNum]
  , [ie].[EmpName]                                                AS [Employee]
  --, FORMAT([ie].[DateTime], 'hh:mm tt')                      AS [Void_Time]
  , CONVERT(VARCHAR(10), [ie].[DateTime], 108)                    AS [Void_Time]
FROM
  Invoice_Exceptions AS [ie]
  JOIN inventory AS [inv]
    ON [ie].[ItemNum]            = [inv].[ItemNum]
  JOIN Invoice_Totals AS [it]
    ON [ie].[Invoice_Number]     = [it].[Invoice_Number]
       AND [ie].[DateTime]       >= '2017-10-01 03:01:50.000'
       AND [ie].[DateTime]       < '2017-10-31 02:58:50.000'
       AND [it].[Payment_Method] = 'CA'
ORDER BY
  [ie].[DateTime] ASC
GO


  That works! But the Time is in miltary and not AM/PM.

  Also, why when I output 'Results to Text' i get a LOOOONG line under the headers of the date and the time. Any way to format these so they are only the width of the actual date and time, as well as the other columns?

  This would completely solve my issue if I can fix this. I just would like the ----- (dashes to be the length of the actual data)
   
  (I added a screenshot to show you what I mean)

  Thanks guys!!!

  Chris

  Okay, so for time do this, replacing your datetime column for GETDATE():

  RIGHT(CONVERT(VARCHAR(19),GETDATE(),100),7)

• Thom A

  SSC Guru

  Points: 99126

  More actions

  October 16, 2017 at 10:58 am

  #1963891

  Lynn Pettis - Monday, October 16, 2017 10:56 AM

  Okay, so for time do this:

  RIGHT(CONVERT(VARCHAR(19),GETDATE(),100),7)

  Glad to see we're thinking the same things Lynn!

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 16, 2017 at 11:02 am

  #1963892

  Thom A - Monday, October 16, 2017 10:58 AM

  Lynn Pettis - Monday, October 16, 2017 10:56 AM

  Okay, so for time do this:

  RIGHT(CONVERT(VARCHAR(19),GETDATE(),100),7)

  Glad to see we're thinking the same things Lynn!

  Everything I have heard about FORMAT, I try to stay away from it.

• Thom A

  SSC Guru

  Points: 99126

  More actions

  October 16, 2017 at 11:05 am

  #1963894

  Lynn Pettis - Monday, October 16, 2017 11:02 AM

  Everything I have heard about FORMAT, I try to stay away from it.

  When it first came out in SQL 2012 I remember trying it. I soon learned not to. 🙂

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• chef423

  SSCertifiable

  Points: 5030

  More actions

  October 16, 2017 at 11:05 am

  #1963895

  Thom A - Monday, October 16, 2017 10:56 AM

  Give this a go:
  LTRIM(RIGHT(CONVERT(varchar(20),[ie].[DateTime],100),7))
  That'll give you a AM/PM. Do you also need the space?

  No, that works perfectly...TY!!!

• chef423

  SSCertifiable

  Points: 5030

  More actions

  October 16, 2017 at 11:15 am

  #1963897

  Thom A - Monday, October 16, 2017 10:56 AM

  Give this a go:
  LTRIM(RIGHT(CONVERT(varchar(20),[ie].[DateTime],100),7))
  That'll give you a AM/PM. Do you also need the space?

  Ok this: Ok this: LTRIM(RIGHT(CONVERT(varchar(20),[ie].[DateTime],100),7)) worked for AM/PM and for trimming down my formatting that I needed.

  So just apply ' LTRIM(RIGHT' to all my other fields and im good to go?

  EDIT: I applied this: LTRIM(RIGHT(CONVERT(VARCHAR(10),[ie].[DateTime],101),7)) AS [Void_Date]

  but it doesnt give me the month for some reason...
  

• chef423

  SSCertifiable

  Points: 5030

  More actions

  October 16, 2017 at 11:28 am

  #1963900

  Thom A - Monday, October 16, 2017 10:25 AM

  More than likely it's the FORMAT. Instead of this:
  FORMAT(Invoice_Exceptions.DateTime, 'MM-dd-yyyy')
  Try:
  REPLACE(CONVERT(varchar(10), Invoice_Exceptions.DateTime,101),'/','-')

  Hopefully that'll speed things up for you. 🙂

  Edit: Typoed Formatting number.

  How do I add the LTRIM command to this code you provided?

  REPLACE(CONVERT(varchar(10), Invoice_Exceptions.DateTime,101),'/','-')

  Thank you

• Thom A

  SSC Guru

  Points: 99126

  More actions

  October 16, 2017 at 11:34 am

  #1963902

  The reason for the LTRIM is because of the extra space that might be returned when the hours is below 10. For example, without LTRIM the value might be:
  ' 6:26PM'
  Notice the white space at the start.

  You don't need to use RIGHT on your CONVERT on this one. The reason on the last one was because CONVERT code 100 returns a much larger datetime that just the time (it includes the date too). As we're only interested in the time, we need to take the right characters, and then trim the left side.

  Both Lynn and I showed what you need to do with the date, using a combination of CONVERT and REPLACE. what was wrong with that answer?

  Edit: You don't need the LTRIM on the date, as there is no whitespace to trim.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• chef423

  SSCertifiable

  Points: 5030

  More actions

  October 16, 2017 at 11:40 am

  #1963905

  Thom A - Monday, October 16, 2017 11:34 AM

  The reason for the LTRIM is because of the extra space that might be returned when the hours is below 10. For example, without LTRIM the value might be:
  ' 6:26PM'
  Notice the white space at the start.

  You don't need to use RIGHT on your CONVERT on this one. The reason on the last one was because CONVERT code 100 returns a much larger datetime that just the time (it includes the date too). As we're only interested in the time, we need to take the right characters, and then trim the left side.

  Both Lynn and I showed what you need to do with the date, using a combination of CONVERT and REPLACE. what was wrong with that answer?

  Edit: You don't need the LTRIM on the date, as there is no whitespace to trim.

  The problem is the formatting of the output...

  

  On the DATE...2nd column, its 2 screens wide. It was that way on the time as well, until the LTRIM code was applied.

  Even the Employee column is much longer than it needs to be: (see the dashes...Why so long??)
  

  How to trim these down to display one nice, neat, closely formatted report?

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 16, 2017 at 11:46 am

  #1963906

  chef423 - Monday, October 16, 2017 11:40 AM

  Thom A - Monday, October 16, 2017 11:34 AM

  The reason for the LTRIM is because of the extra space that might be returned when the hours is below 10. For example, without LTRIM the value might be:
  ' 6:26PM'
  Notice the white space at the start.

  You don't need to use RIGHT on your CONVERT on this one. The reason on the last one was because CONVERT code 100 returns a much larger datetime that just the time (it includes the date too). As we're only interested in the time, we need to take the right characters, and then trim the left side.

  Both Lynn and I showed what you need to do with the date, using a combination of CONVERT and REPLACE. what was wrong with that answer?

  Edit: You don't need the LTRIM on the date, as there is no whitespace to trim.

  The problem is the formatting of the output...

  

  On the DATE...2nd column, its 2 screens wide. It was that way on the time as well, until the LTRIM code was applied.

  Even the Employee column is much longer than it needs to be: (see the dashes...Why so long??)
  

  How to trim these down to display one nice, neat, closely formatted report?

  Why are you using SSMS for reporting?  If I need to use SSMS to provide reporting (and I have), I send everything to a grid then copy/paste from SSMS to EXCEL.