patrickmcginnis59 10839 - Thursday, April 19, 2018 9:16 AM
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