|
|
Posted by Russ Rose on 03/03/07 13:37
"SQL Learner" <excelmodeling@gmail.com> wrote in message
news:1172814400.851724.168130@j27g2000cwj.googlegroups.com...
> Hi Russ,
>
> To you guess, how much faster would that be? Would it be
> significant?
>
> - Grasshopper -
>
My guess would have been a factor of 10 since SQL server is far more
efficient in its memory management.
My expirimentation has produced the following results on a dual Xeon with 2
GB memory:
I have a working notes table that contains 9.6 million rows.
--3 minutes to copy 9.6 million rows into an expirimental table
--Added 3 varchar columns to hold expirimental results
--20 minutes to split column into two fields
UPDATE dbo.WNTemp SET Split1 = SUBSTRING(Comments, 1, 3), Split2 =
SUBSTRING(Comments, 4, 3)
--12 minutes to nullify columns, so 8 minutes for SUBSTRING split function.
UPDATE dbo.WNTemp SET Split1 = NULL, Split2 = NULL
--Export to Access mdb yields 2GB file
--Same query using Mid function in place of SUBSTRING function
--Access at 3 minutes gave modal warning that no undo is possible
--At 34 minutes gave error and stopped at 8.1 million records.
--According to Task Manager Access read 3.8GB and wrote 1.6 GB during
partial update
My conclusion is that Access is only moderately slower, but I would guess
that the difference would grow if the width of the table was larger.
Although a partial update is nice in this circumstance, I believe it would
be a negative result in a production environment.
Navigation:
[Reply to this message]
|