1

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:

  1. 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.
  2. 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!

  • Do you know which part is slow, inserting into temp or large-processed? Have you looked at your index/stats in large table? – SqlWorldWide Apr 22 '17 at 14:19
  • Do you have an index on column [x] ? If you are querying large table, and don't have proper indexes, it will be very slow. SELECT * INTO [temp] FROM [large] WHERE [x] = ISNULL(@i, [x]); – Greg Apr 22 '17 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.