I have a 9GB table with 25M rows and I need to run complex select queries to clean and add variables. This is a part of an ETL process prior to some data analysis. Queries are on my laptop running SQL Server Developer version. Data split between laptop and external USB hard-drive. I am only 'user'.
Problem: Queries are very slow (hours) and sometimes fail.
One solution: Data can be divided nicely into smaller similar-sized Chunks based on values in a single column. I can write a loop to take each Chunk in turn, perform complex query and append to output table. This seems to work - queries complete, still take many minutes but that's ok.
Questions / Issues:
- I used to do this - take each chunk - using Dynamic SQL but this can be difficult to debug so now plan to do same thing with Stored Procedure (see below). Again, it works but feels somewhat messy and I wondered whether using Table Partitions could achieve same performance gains.
- Sometimes you don't know what you don't know - maybe (hopefully!) I am unaware of some ways of improving this process.
Here's the stored procedure that does work on each Chunk:
IF OBJECT_ID('sqlSubset') IS NOT NULL DROP PROCEDURE sqlSubset GO CREATE PROCEDURE sqlSubset (@i SMALLINT) AS BEGIN -- get subset of large table by filtering on natural partition -- NOTE: use of ISNULL means stored procedure can run with NULL value for @i SELECT * INTO [temp] FROM [large] WHERE [x] = ISNULL(@i, [x]); -- do more stuff to [temp] subset here that takes a long time with original large table END GO
Here's the code that calls the stored procedure for each Chunk:
DECLARE @j SMALLINT; SET @j = 1; WHILE @j <= 5 BEGIN -- call stored procedure EXEC sqlSubset @j; -- if output table not yet created then create using first temp subset IF OBJECT_ID('dbo.large-processed', 'U') IS NULL SELECT * INTO [large-processed] FROM [temp] -- otherwise append each temp subset to output table ELSE INSERT INTO [large-processed] SELECT * FROM [temp] -- move to next value of j SET @j = @j + 1; -- clean up by deleting temp IF OBJECT_ID('dbo.temp', 'U') IS NOT NULL DROP TABLE [temp]; END
Any thoughts? More or less self-taught with this stuff so please be kind!