Output results to a table

Question

Output results to a table

water490

SSC Eights!

Points: 967
More actions
November 15, 2021 at 10:21 pm

#3953343

Hi everyone
One of my queries is running too slow so I would like to output the results of the subquery into a table and then use that table in the main query. Not sure if this will improve performance or not but it is worth a shot. I know it made a difference in Access so it may also make a difference in SQL Server. In Access I used a Make Table query to create the table. SQL Server has many options. What is the best option for me? I plan on adding indexes to the resulting table. That table will also be used for other calculations in different queries.
Any suggestions would be much appreciated. I am new to SQL Server so if you can also provide some code snippet as a starting point that would be awesome too.
Thank you so much everyone

Viewing 7 posts - 1 through 6 (of 6 total)

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

ratbak Ten Centuries Points: 1380 More actions · Answer 1

It is often the case that persisting working data can improve performance, and probably won't take long to test this anecdotally, so I wouldn't discourage trying it. But long term you will want to learn to take advantage of the information you can get from execution plans to determine more systematically what factors are causing performance issues. That's a big deep topic, but well worth it in the long run.

SQL Server has temp tables for such purposes. A session-scoped temp table is created when one inserts into a table prefixed with one "#" (pound sign to old timers; hashtag to younger folks). Temp tables prefixed w/ "##" are global, and rarely advisable (what happens if two queries are updating that table simultaneously?).

You can predefine a temp table w/ a create table statement -- which may be a good idea if later queries on that table can take advantage of an index. -- e.g,

CREATE TABLE #importWork
    (ID INT  NOT NULL PRIMARY KEY,
    Value decimal(9,5) NOT NULL,
    CreatedUTC datetime2 NOT NULL
    );

INSERT INTO #importWork
SELECT ID, Value, CreatedUTC
FROM dbo.SourceTable
WHERE Criteria = 'ImportMe';

Otherwise, one can simply use the select into syntax to implicitly create the temp table (without indexes) -- e.g.,

SELECT ID, Value, CreatedUTC
INTO #importWork
FROM dbo.SourceTable
WHERE Criteria = 'ImportMe';

water490 SSC Eights! Points: 967 More actions · Answer 2

Thank you so much!

A few comments...SQL Server is definitely a very powerful program and it will take me years to really learn it well. I am ok with that. Life is all about learning. The DB I have now is using a small subset of data. It is about 1 GB with about 4 million records. The end goal is to get the full dataset but that dataset is close to 600 GB in size. It goes without saying...efficiency is super important. I won't know the true performance until I get the full dataset and upgrade my hardware. In the mean time, I am working with the smaller dataset to make sure my queries are running "reasonably" well. Long term goal ... learn about SQL Server and how to use it's many features to optimize my queries. Like you said, its a very deep subject.

I am not too concerned about physical storage. The DB has about 4 million records. After the query is run, there are about 250 records which is less than 0.01%.

In terms of performance, which is a better option? store it as a temp like you suggested or store it physically?

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

It depends.

You will come to hear that term for every question in SQL Server.

To fully understand the correct answer if temp tables or to persist it physically you will need to test as it is dependent on many factors primarily disk IO/RAM.

Use things like setting stats time and IO on before running the query to see where the time and the IO bottlenecks are.

SET STATISTICS TIME, IO ON

In terms of performance overall, you need to be looking at the execution plan, which is how the query optimiser got the routes to get the data.

I would recommend this book from Grant.

https://www.sqlservercentral.com/books/sql-server-execution-plans-third-edition-by-grant-fritchey

Then you need to indexing and statistics and maintenance of said objects more importantly the statistics.

https://www.sqlservercentral.com/stairways/stairway-to-sql-server-indexes

https://www.sqlservercentral.com/stairways/stairway-to-columnstore-indexes

https://www.sqlservercentral.com/books/performance-tuning-with-sql-server-dynamic-management-views

If you’re more of a watcher of people doing a task rather than reading books, I would urgently say to get training funding from your company so you can purchase at least the fundamentals bundle from Brent while his Black Friday sale is on. If you can get mastering too great but as a starter for 10 fundamentals of indexing, query optimising etc.

Also look out for his “How to think like….” series too

https://www.brentozar.com

Also take a look through the books and stairways section of the site there is a wealth of knowledge on performance and everything related to SQL, especially the troubleshooting guide for the accidental DBA which I still use even with 15 years of SQL under my belt.

Michael L John One Orange Chip Points: 27060 More actions · Answer 4

Can you publish the code?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

water490 SSC Eights! Points: 967 More actions · Answer 5

Thank you so much! this is great information. I have been watching the execution plan and it is a great resource. I will look into your resources to learn more about this.

Michael L John One Orange Chip Points: 27060 More actions · Answer 6

water490 wrote:

Hi everyone
One of my queries is running too slow so I would like to output the results of the subquery into a table and then use that table in the main query. Not sure if this will improve performance or not but it is worth a shot. I know it made a difference in Access so it may also make a difference in SQL Server. In Access I used a Make Table query to create the table. SQL Server has many options. What is the best option for me? I plan on adding indexes to the resulting table. That table will also be used for other calculations in different queries.
Any suggestions would be much appreciated. I am new to SQL Server so if you can also provide some code snippet as a starting point that would be awesome too.
Thank you so much everyone

If you can publish the code, we can likely make specific suggestions.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/