10

Why is Query Version 2 so much faster?

I suspect the DB Engine is calling the Table-Valued-Function "GetUsageStatistic" multiple times, so is there a way to tell the engine that "GetUsageStatistic" is deterministic and should be called only once?

Query Version 1

--Takes ~10 minutes
select *
from RosterLevel r
left join GetUsageStatistics( @mindate, @maxdate ) usage on r.UserID = usage.UserID;

Query Version 2

--Takes ~10 seconds
select * into #usage from  GetUsageStatistics( @mindate, @maxdate );
select *
from RosterLevel r
left join #usage on r.UserID = #usage.UserID;
Triynko
  • 18,766
  • 21
  • 107
  • 173
  • What does the body of `GetUsageStatistics` look like? Is it a single query, or multiple queries leading to a single resultset? – Will A Jun 02 '11 at 21:43
  • GetUsageStatistics is an inline-table-valued-function (ITVF), which selects from the GetWeeklyUsage, which is itself an ITVF that selects from GetDailyUsage (also an ITVF). It's basically a simple chain of deterministic ITVFs that each return a level of aggregate results with SUM, AVG, and COUNT functions in their output columns. – Triynko Jun 02 '11 at 21:48
  • @Triynko - definitely worth getting Actual Execution Plans for both queries and comparing them to see whether your multiple-execution of TVF theory is correct. – Will A Jun 02 '11 at 21:50
  • Could I add table locks or something to the select statements or functions to help SQL Server figure out the whole function chain is deterministic? Or is it incapable of figuring out such a thing when inline-table-valued-functions are nested? – Triynko Jun 02 '11 at 21:51
  • Yeah, I was going to get the execution plans, but I couldn't find the button in SSMS, lol. – Triynko Jun 02 '11 at 21:52
  • As long as you are going to do that, you might consider if you cen imporve performance still further by indexing #usage.UserID – HLGEM Jun 02 '11 at 21:53
  • 1
    I'd be interested in knowing if changing the LEFT JOIN in the first query to LEFT HASH JOIN has a positive effect. – Will A Jun 02 '11 at 21:53
  • The EP is actually way complex, but to summarize... the left join appears to be a Hash Match. The thing it's hashing (i.e. the ITFV) is a direct chain of Compute Scalar, Hash Match (Aggregate), Parallelism (Repartition Streams), ...(repeat all three steps, three more times, representing nested function calls)..., Compute Scalar, Clustered Index Seek (Clustered). – Triynko Jun 02 '11 at 22:00
  • It's actually difficult to even get the EP for the full-blown query, because the query first creates two global temp tables and drops them at the end. Because those tables don't already exist, it can't generate an EP, so I have to pull out those two lines that create the global temp tables and run them separately... then generate the EP, otherwise I get an error that says "Invalid object name '##global_temp_table_name'" :/ – Triynko Jun 02 '11 at 22:03
  • I did get an interesting message in the generated EP for Query 1 (the actual query... not the simplified version I posted in my example): "The Query Processor estimates that implementing the following index could improve the query cost by 97.847%." It suggests I create it on [dbo].[LogUserActivity] ([UserID],[Timestamp]), but I'm pretty certain that particular CLUSTERED index already exists on that table/fields. – Triynko Jun 02 '11 at 22:10
  • 2
    @Will A. WOW, yes, if I change the left join to a left HASH join, then I no longer get that index suggestion, and the execution plan for query 1 looks just like the one for query 2, and runs just as fast. Amazing. I have never heard of "left hash join". – Triynko Jun 02 '11 at 22:14
  • 1
    @Triynko - I wouldn't necessarily stick with the LEFT HASH JOIN, though - the HASH is a join hint - and including such a hint prevents the query optimizer from performing join reordering - which could be a killer if more tables are added to the query. The LHJ is (I believe) forcing the TVF to be "executed" once, rather than the possible 'once for each row in the left hand table' that you theorized on. – Will A Jun 02 '11 at 22:17
  • That's ok. I'm not worried about join reordering because it's very straightforwardly crafted, with the main source table followed by a series of left joins, on exactly the tables I want, in exactly the order I want the joins to occur. I'm basically selecting a roster, then tacking on extra information with left joins, one row per user id. It struck me as strange, however, that it was suggesting I put a non-clustered index exactly where there already is a clustered index. – Triynko Jun 02 '11 at 22:22
  • Correction, there is actually NOT an index on (UserID,Timestamp) already. There is a clustered index on (Timestamp) only. It's starting to make a little more sense now. – Triynko Jun 02 '11 at 22:26

2 Answers2

1

As mentioned in the comments, the best answer is to analyze whatever execution plan is spit out. Barring that, your intuition is probably right, but aside from whatever caching SQL Server automatically attempts, there's not much coming to my head in the way of query hints you can provide to indicate that the function is deterministic, but you're welcome to try a few things out mentioned in the Query Hints MSDN page. My first tests would probably draw on Table Hints.

Taylor Gerring
  • 1,825
  • 1
  • 12
  • 17
  • Is the tag "SQL" a Microsoft tag? – Tim Jun 07 '11 at 00:26
  • 1
    @Tim: No. Yet many people seem to mistake it for being so, as a matter of fact. One other reason for assuming SQL Server could be that the OP's script is obviously T-SQL (from `#`-names). But probably the actual reason is that the OP has mentioned SSMS in one of their comments. – Andriy M Jun 07 '11 at 06:33
  • @Tim: @Andriy kind of right -- 'obvious' is subject though, as it only applies to people who already know what TSQL looks like, and that SSMS is MS. But such snobbery aside :-), you're right, this is not a general SQL question, it's a TSQL question, and that makes a difference as far as who can answer it, and who would be interested in searching for it / reading it in the first place. – Chains Jun 14 '11 at 17:35
0

If you use the function in your first example, it is called many times -- once for each record in your RosterLevel table. It returns a (potentially) different table each time, depending on the join field.

If you use the function in your second example, it is only called once. From there, the table variable is in memory, and you're not having to do a read over and over.

Chains
  • 12,541
  • 8
  • 45
  • 62