3

I currently use the following statement, for 10,000 rows it takes about 150 seconds. I tried removing the index on the target table, but this didn't help. Running the loop without the INSERT INTO takes less then 50ms. I need it to update about 300 million rows and I can't really wait 52 days (!) for it to complete.

Bottom line of the following update query is that I need to go over each row, perform calculations on a VARBINARY and extract proper values from it (we need to get rid of the packed VARBINARY fields), and store that in a new table.

FETCH NEXT FROM LocCities INTO @LocCity 
WHILE (@@FETCH_STATUS = 0)
BEGIN
    -- several sets, removed calculations for clarity
    SET @LocationId = Calculation1()
    SET @CityId = Calculation2()

    IF(@LocCity <> 0)
    BEGIN
        -- left out an inner loop here on the VARBINARY based on its length
        INSERT INTO LocationCities (LocationId, CityId)
        VALUES (@LocationId, @CityId)
    END
    FETCH NEXT FROM RespCursor INTO @TuningRow
END

I understand that I can use the WITH keyword with table hints, but I am not sure what to use. I expect the final update query to run in several hours, and hope there's a way to do that. I really can't wait almost two months ;).

Isn't there something similar like BULKINSERT that I can use?

Improve this question
5
  • 2
    You need to replace this RBAR approach with a set-based solution if you can--you're doing 10,000 individual inserts, and that's going to cost you. What's in the cursor?
    – mdoyle
    Commented May 6, 2014 at 12:32
  • @mdoyle, I have considered that, but don't think it is possible. The cursor contains a single field of a row of the source table, which is a varbinary of varying length. I need to loop over that field and create the many-to-many relation it has with its internal values. I.e., suppose the varbinary has 0x010734 and 0x030735040736, the first row will create 1 row with int values (1, 1844), the second row will create 2 rows with int values (3, 1845) and (4, 1846) respectively. The depth of the varbinary field can be over 300 corresponding rows, and I need to flat-to-hier that.
    – Abel
    Commented May 6, 2014 at 12:41
  • @AaronBertrand: I have created a separate question for that, I considered it too much text to include in this question, plus I think it is a separate subject: dba.stackexchange.com/questions/64557/…
    – Abel
    Commented May 6, 2014 at 13:13
  • @Abel I'm not sure I understand the point of the other question. Have I not shown below how you extract multiple values from one row into a set of rows? What additional information are you looking for?
    – Aaron Bertrand
    Commented May 6, 2014 at 13:56
  • @AaronBertrand, yes you did. But I wrote that other question while you were writing your answer, we basically cross-posted. I've removed the other question now, as it apparently did not add to clarity.
    – Abel
    Commented May 6, 2014 at 14:02

1 Answer 1

Reset to default
5

I really don't think table hints or BULKINSERT are going to help you here - your approach is still to process each varbinary value one at a time, and this will be your downfall regardless - especially when you discard the idea of set-based queries because you "don't think it's possible."

Here's a set-based approach with no awful loops or cursors. This assumes that the pattern is always the same (LocationID is the first byte, and CityID is the next two).

DECLARE @x TABLE(x VARBINARY(32));

INSERT @x VALUES(0x010734),(0x030735040736),(0x030742050743060712);

;WITH n(n) AS 
(
  SELECT TOP (300) (number*3)+1 
  FROM [master].dbo.spt_values -- your own Numbers table is better
  WHERE [type] = N'P' ORDER BY number
)
-- INSERT dbo.LocationCities(LocationId, CityId)
SELECT 
  x.x,      -- comment this out before insert 
  LocationID = CONVERT(INT, SUBSTRING(x.x, n, 1)),
  CityID     = CONVERT(INT, SUBSTRING(x.x, n+1, 2))
FROM @x AS x INNER JOIN n ON LEN(x) > n.n;

Results:

x                        LocationID    CityID
---------------------    ----------    ------
0x010734                 1             1844
0x030735040736           3             1845
0x030735040736           4             1846
0x030742050743060712     3             1858
0x030742050743060712     5             1859
0x030742050743060712     6             1810

Some articles that will help you understand numbers tables and why generating sets in SQL Server is far superior to even the most efficient loop you can derive.

Improve this answer
3
  • Oh boy, this really is an eye opener. I heard of number tables, but never considered it to be useful in my scenarios. This may very well be the answer I was looking for the past few days, I'll start experimenting. Why do you say "your own numbers table is better"?
    – Abel
    Commented May 6, 2014 at 13:20
  • 2
    @Abel a variety of reasons. You can control indexing etc., use compression, not need filters, not rely on three-part naming to reach out to master, add SCHEMABINDING to referencing views/functions, not worry about future changes to that table (e.g. maybe it disappears), not be limited by the number of rows in that table (which varies by version), you could even put the numbers table in memory in SQL Server 2014. There are probably several other reasons not on the tip of my tongue...
    – Aaron Bertrand
    Commented May 6, 2014 at 13:54
  • I meanwhile managed to apply it to my actual scenario, it's much easier than the cursor-approach. After adding REVERSE to the cast (because of endianness), it works like a charm. I think the real "trick" here is the INNER JOIN n ON LEN(x) > n.n. Thanks.
    – Abel
    Commented May 6, 2014 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.