Forum Replies Created

Viewing 15 posts - 136 through 150 (of 10,143 total)

  • RE: Query tuning with conditional aggregation

    Next a couple of queries, both built dynamically - one with preaggregation, one without:

    -------------------------------------
    -- test with 500 columns
    DECLARE @stmt VARCHAR(MAX) = '';
    WITH
     E1...
    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

    January 25, 2019 at 3:41 am

    #2020079

  • RE: Query tuning with conditional aggregation

    Once you've experimented enough to know how it's done, building a sandbox for testing can be quick enough that it doesn't impact on your work. So here's a sandbox for...
    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

    January 25, 2019 at 3:30 am

    #2020078

  • RE: Are the posted questions getting worse?

    rodjkidd - Thursday, January 24, 2019 8:28 AM

    Is it too early to mention SQL Bits?
    Who from the Thread are attending?

    I see Grant,...

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

    January 24, 2019 at 8:37 am

    #2019975

  • RE: Query tuning with conditional aggregation

    ScottPletcher - Thursday, January 24, 2019 8:12 AM

    ChrisM@Work - Thursday, January 24, 2019 6:36 AM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

    January 24, 2019 at 8:22 am

    #2019967

  • RE: Query tuning with conditional aggregation

    Grant Fritchey - Thursday, January 24, 2019 7:50 AM

    We're on 2008, so this won't help, however, a possible Hail Mary on this...

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

    January 24, 2019 at 7:56 am

    #2019956

  • RE: Query tuning with conditional aggregation

    ScottPletcher - Tuesday, January 22, 2019 10:42 AM

    You need to either:

    Cluster the table by timestamp (if that's how you (almost) always query...

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

    January 24, 2019 at 6:36 am

    #2019943

  • RE: Are the posted questions getting worse?

    Grant Fritchey - Thursday, January 24, 2019 6:21 AM

    This other one does the same. I know an MVP they convinced to...

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

    January 24, 2019 at 6:30 am

    #2019939

  • RE: Union with co-related subquery

    Untested:
    SELECT
     e.FirstName,
     e.SecondName,
     x.Pay_Date
    FROM Employee e
    CROSS APPLY ( -- x
     SELECT Pay_Date = MAX(Pay_Date)
     FROM ( -- d
      SELECT e.Pay_Date
      UNION ALL
      SELECT MAX(Pay_Date)...

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

    January 24, 2019 at 1:42 am

    #2019913

  • RE: Fuzzy match based on T-SQL only

    Here are two functions - the first is a very close approximation of the Levenshtein Distance Algorithm, the second is a stripped-down slicked-back twin carburetor version which in practice produces...
    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

    January 22, 2019 at 2:57 am

    #2019600

  • RE: Creating and using inline table-valued functions

    Excellent article, very thorough. Thanks for sharing this.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

    January 22, 2019 at 1:40 am

    #2019595

  • RE: Are the posted questions getting worse?

    frederico_fonseca - Thursday, January 17, 2019 4:10 PM

    Grant Fritchey - Thursday, January 17, 2019 12:48 PM
    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

    January 18, 2019 at 6:21 am

    #2019352

  • RE: Are the posted questions getting worse?

    Lynn Pettis - Thursday, January 17, 2019 1:40 PM

    Grant Fritchey - Thursday, January 17, 2019 12:48 PM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

    January 18, 2019 at 6:19 am

    #2019351

    • Viewing 15 posts - 136 through 150 (of 10,143 total)