• patrickmcginnis59 10839 - Thursday, April 19, 2018 9:16 AM

    Theres a reason for instance that Excel or Word doesn't store their data in a relational database, because spreadsheets would then take forever to recalculate, and word documents would take forever to repaginate. Obviously these are extreme examples but they illustrate the mismatch very well. Designing arbitrary data structures and serializing them are often not thought of in terms of rows, tables and constraints.

    Are you sure?
    The source code of this web page (before I added my comment) contains 358k characters (including spaces), which makes up nearly 22k words, 136 pages.
    With the size of the whole database < 0.5 MB I don't know how bad must be SQL code to do repagination slower than it happens in MS Word.

    As for Excel.
    Let's create a simple spreadsheet:
    Put "1" into A1 and "=A1+A$1" into A2.
    Copy A2 to all the cells below, up to the last one: 1048576.
    Then, if I change the value in A1 from 1 to 4, it takes about 2 seconds to recalculate the spreadsheet on my laptop.

    Now, I create a similar table in SQL Server:
    create table #Temp (
        Value decimal(19,4)
        )
        
    insert into #Temp
    select N
    from dbo.TallyGenerator(1,null, 1048576, 1)

    And now let's do recalculation:

    set statistics time on
    select TOP 200 T1.Value + T2.Value Result
    from #Temp T1
        cross apply (select top 1 Value from #Temp order by Value) T2
    where T1.Value < 100000
    order by Result desc

    WHERE clause together with TOP emulates viewing a specific range of rows in Excel.
    SQL Server Execution Times:
     CPU time = 548 ms, elapsed time = 159 ms.

    When it comes to saving the data, It took about 4 seconds for Excel to save the spreadsheet.
    And it took <1 second to run this code:
    select T1.Value + T2.Value Result
    INTO #Temp2
    from #Temp T1
        cross apply (select top 1 Value from #Temp order by Value) T2
    order by Result
    checkpoint

    same laptop, same hard drive.

    You can compare performance of reading the data from disk for yourself.
    As you can see, Excel data format does not give any advantage in terms of performance of data manipulations.
    SQL Server is times faster in every aspect.

    _____________
    Code for TallyGenerator