Post reply

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

  • October 16, 2017 at 11:49 am

    #1963907

    The dashes are as wide as the column is. When returning results in SSMS using the Text format, it returns the data in Fixed Width format. Thus, if you have a column that is the data type varchar(100), but the longest entry is 2 characters, it'll still have a width of 100 in the text view.

    I admit, the date should return as 10 wide, but it's not. This seems to be a "quirk" of SSMS, as the data type is still definitely varchar(10). You could still a further CONVERT at the front to force SSMS to realise it's a varchar(10). With your Employee column, that means that your column is "too wide" in your table. You should try to use a varchar size that is beffitting of your data. If the maximum length of a value is going to be no more than 20, then make it a varchar(20). Don't make it a varchar(50) (or more), as it's wasted.
    CONVERT(varchar(10),REPLACE(CONVERT(varchar(10), Invoice_Exceptions.DateTime,101),'/','-'))

    Thom~

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

  • October 16, 2017 at 11:54 am

    #1963910

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

    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.

    I need this to pop up in a readable (reasonable) format for clients. They cannot (and wont) copy and paste into Excel.
    🙁

  • October 16, 2017 at 11:55 am

    #1963911

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

    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.

    I assume the TRIM function will get me closer to my solution for all the other columns?

  • October 16, 2017 at 11:58 am

    #1963912

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

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

    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.

    I need this to pop up in a readable (reasonable) format for clients. They cannot (and wont) copy and paste into Excel.
    🙁

    Clients? Using SSMS?  Not my choice of reporting tool.  In fact, I personally would fight that saying they need something else for reporting.  Whether it is me running the script and creating the EXCEL spreadsheet for them, or using SSIS to create the spreadsheet, or using Reporting Services.  Just saying.

  • October 16, 2017 at 12:08 pm

    #1963914

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

    I assume the TRIM function will get me closer to my solution for all the other columns?

    TRIM was introduced in SQL Server 2017, you'll have to make do with LTRIM and RTRIM.  But no, it'll have no effect. A Trimmed varchar(100) is still a varchar(100).

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

    I need this to pop up in a readable (reasonable) format for clients. They cannot (and wont) copy and paste into Excel.
    🙁

    Then someone else should do it for them. Like Lynn said, Clients shouldn't be using SSMS. SSMS is an administration tool, it's not for clients to be given SQL to run; which, might I add, they'll be copy and pasting anyway! What difference does it make if it's Excel or Notepad/Wordpad some other text editor.

    Thom~

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

  • October 16, 2017 at 12:48 pm

    #1963919

    Thom A - Monday, October 16, 2017 12:08 PM

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

    I assume the TRIM function will get me closer to my solution for all the other columns?

    TRIM was introduced in SQL Server 2017, you'll have to make do with LTRIM and RTRIM.  But no, it'll have no effect. A Trimmed varchar(100) is still a varchar(100).

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

    I need this to pop up in a readable (reasonable) format for clients. They cannot (and wont) copy and paste into Excel.
    🙁

    Then someone else should do it for them. Like Lynn said, Clients shouldn't be using SSMS. SSMS is an administration tool, it's not for clients to be given SQL to run; which, might I add, they'll be copy and pasting anyway! What difference does it make if it's Excel or Notepad/Wordpad some other text editor.

    Guys, I understand this. But for this report to look good elsewhere, I need to have the formatting correct, so IF I do decide to output to TXT file, it will look pretty.

    Notepad would be fine, I guess I am assuming the formatting will carry over into a TXT file?

    Thank you.

  • October 16, 2017 at 1:01 pm

    #1963921

    Helped you get the output you need, I really can't help further other than recommending output to grid, copy/paste to EXCEL, format as needed, send to clients.

  • October 16, 2017 at 1:10 pm

    #1963923

    Lynn Pettis - Monday, October 16, 2017 1:01 PM

    Helped you get the output you need, I really can't help further other than recommending output to grid, copy/paste to EXCEL, format as needed, send to clients.

    I have all that, don't let me crappy SQL coding abilities reflect my other IT abilities. I really appreciate you guys.

    Thanks again, gentleman!

  • October 16, 2017 at 1:45 pm

    #1963925

    Lynn Pettis - Monday, October 16, 2017 1:01 PM

    Helped you get the output you need, I really can't help further other than recommending output to grid, copy/paste to EXCEL, format as needed, send to clients.

    SELECT
    LTRIM(RIGHT(CONVERT(varchar(20), [ie].[Invoice_Number],100),7)) AS [Invoice#]
    --, FORMAT([ie].[DateTime], 'MM-dd-yyyy')       AS [Void_Date]
    , CONVERT(varchar(10),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]
    , LTRIM(RIGHT(CONVERT(varchar(20), [ie].[Reason_Code],100),14)) AS [Reason_Code]
    , LTRIM(RIGHT(CONVERT(varchar(20), [ie].[LineNum],100),4))  AS [Line_Item]
    , LTRIM(RIGHT(CONVERT(varchar(20), [ie].[EmpName],100),7))  AS [Employee]
    --, FORMAT([ie].[DateTime], 'hh:mm tt')        AS [Void_Time]
    , LTRIM(RIGHT(CONVERT(varchar(20), [ie].[DateTime],100),7))  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'
    ORDER BY
    [ie].[DateTime] ASC

    Boom.

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply