3

I have a dashboard web application that involves users logging in to query large amounts of data (MS SQL Server 2016) and view charts of the results. The data also changes frequently (say every hour). Performance is particularly important.

To improve performance I've found that I can create a temp table once, which handles a bulk amount of the processing, then re-query that table multiple times (eg. grouping by different fields) to produce different charts. When the user first logs in I can use this method to quickly pre-caclulate a lot of the chart data for a user in one go. This is much more efficient than doing the whole query each time (ie. doing "temp table + group by" separately for each of the 20 charts).

However, once the user has logged in, there are other actions he can take where he could re-use that temp table data. Ideally i don't want to recreate the temp table on each subsequent request, so i'd like to re-use it for the lifetime of the client login.

I don't think I could use a global table, as I'd need a separate one for each user. Also SQL Server wouldn't know when to delete the table if the user's session timed out on the client, potentially leading to a build-up of lots of old temp tables & massive amount of data.

Ideally I'd like a session temp table that would expire say every hour, so the client could access the data for multiple subsequent requests and it wouldn't have to be recreated that often.

What are my options for doing this?

rwatson89
  • 61
  • 4
  • Given that temp tables only last for the duration of the connection, your idea might not actually work. – Dan Bracuk Jan 10 '18 at 19:14
  • @Dan Bracuk Initailly what I'm doing is creating the temp table & running 20 group by re-queries on it in a single stored proc (i.e in one request), that returns 20 results sets back to the client in one go. That bit works fine. I want to re-use that initial temp table for subsequent requests – rwatson89 Jan 10 '18 at 20:06
  • 1
    Why is "quickly pre-caclulate a lot of the chart data for a user in one go" faster than querying the source data? Sounds like all of this is over-engineering to avoid creating the right indexes. If you dump 500,000 rows into a #temp table and then group by multiple times on those 500,000 rows, that should be *slower* than if you had proper indexes to work on those 500,000 rows directly without all the moving data back and forth to a temp table. – Aaron Bertrand Jan 10 '18 at 20:09
  • There are certainly cases where dumping an intermediate data set to a #temp table can make the *optimizer's* work easier in coming up with an efficient execution plan, but this doesn't sound like that. It sounds a lot more like there is a missing index or two that would alleviate the performance issue. Can you provide more details about table/index structure, row count, the way you get the relevant data (and how many rows) into a #temp table, and the queries you then run against that #temp table? I'm fairly certain with enough info you can get better guidance than what you're explicitly asking – Aaron Bertrand Jan 10 '18 at 20:15
  • @Aaron Because the initial query that generates the temp table involves lots of joins, calculations, function calls, cross-applys, and filtering on multiple fields. This takes around 8s to complete. Each group by on that data takes around 0.5s to complete. – rwatson89 Jan 10 '18 at 20:15
  • Sounds like you're focused on optimizing the wrong part then... who even waits 8s for that initial "optimal" query? – Aaron Bertrand Jan 10 '18 at 20:16
  • could be right but there's a lot of data (millions of rows) and already got that time down a lot (it was way more than 8s when i inherited!). There's a number of where clauses doing "where fieldID in ( 800 ids )" – rwatson89 Jan 10 '18 at 20:22
  • I have to agree with Aaron here. 8s is an awfully long time for a query to run. You say it is pretty complex with functions and such all over the place. I would bet my hat that the performance issue is in that main query and/or in those functions. – Sean Lange Jan 10 '18 at 20:31
  • if you did not try this before then just fire up sql server profiler and examine there all queries that are in use. then if some are slow try to analyze them and create proper indexes – Dejan Dozet Jan 10 '18 at 22:40

1 Answers1

0

The only time I would suggest using temp tables for quick retrieval of any data would be data that is final. Meaning nobody would change it. An obituary would be a good listing for that. If you do this then potentially each person would end up with different data in their charts. Person A queries and gets a temp table of data and creates charts. Person B queries and gets a temp table of all new and modified data and creates charts. Person A queries again and gets the original data from their 1st query and never sees what person B sees. I would not suggest this approach.

edjm
  • 4,830
  • 7
  • 36
  • 65
  • Sorry not following this - that sounds like what i want. Person A does request, stored proc creates temp table and runs 20 group by queries on it and returns 20 result sets back to client in one go. Person B does the same and gets their own set of 20 result sets back. That's the desired behaviour. My question is, if person A does another request later, I want them to be able to re-use their temp table data for other purposes. Is that possible with temp tables, or another approach? – rwatson89 Jan 10 '18 at 20:12