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.

[Back to original message]