Home Forums SQL Server 2014 Development - SQL Server 2014 Extra join is killing query RE: Extra join is killing query

  • August 5, 2015 at 6:11 am

    #1817463

    DavidDroog (8/5/2015)

    Hi Guys

    Here is a zipped up bak (SQL Server 2008 R2) file filed with sample data and the proc. It is 17 meg so too large to post here and I have used dropbox.

    ...

    I have changed the proc slightly, but the illogical use of the #tmp table is still required. I have put comments in the proc to explain it's logic, but basically it is a dynamic query that returns a list of clients eligible for targeting to the application grid. To make the list the client must be of a high enough profile or be a target already. These two separate criteria are combined with the union clause.

    Good luck, I would be happy if someone can show me a better way of writing the query, but the main thing that interests me is WHY DO I NEED THAT DAMN #TMP TABLE?

    Regards

    David

    Awesome, thanks.

    Can you answer the questions in this post please - it would give us a head start.

    “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