[dba-VB] [dba-SQLServer] [AccessD] Apples to Oranges

Stuart McLachlan stuart at lexacorp.com.pg
Mon Oct 4 17:39:23 CDT 2010


At 1/2 hour / field, it should only take about 11.25 days :-)

But my question is, do you have to do it a single field at a time? can't you
update all the fields in a single pass? Something like:

Update mytable set 
field1 = case field1
                    when " "  then Null
                    else field1
             end,
field2 = case field2
                    when " "  then Null
                    else field2
             end,
field3 = case field3
                    when " "  then Null
                    else field3
             end,
...   

-- 
Stuart

On 4 Oct 2010 at 18:05, jwcolby wrote:

> One pass of the query that updates the ' ' (space) to null value takes
> about 5 minutes on the SSD, whereas it takes about 30 minutes minutes
> on rotating media.
> 
> I am not pursuing actually doing this on the SSD over nagging concerns
> about hot spot wear.
> 
> However a actually need to do this for about 540 fields.  At 1/2 hour
> / field... this will be running for the next month.
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> On 10/4/2010 5:36 PM, jwcolby wrote:
> > With an index, both databases took 2 seconds to return the results.
> >
> > John W. Colby
> > www.ColbyConsulting.com
> >
> > On 10/4/2010 5:16 PM, jwcolby wrote:
> >> I On the new server, have the same database on my SSD (two drive)
> >> raid 0 and a rotating media (2 drive) Raid 0.
> >>
> >> There is a clustered index on the PK as the index key.
> >> There is no index on the FieldX, forcing a field scan.
> >>
> >> I did a simple count PK Group By FieldX on both database files.
> >>
> >> The SSD returned the counts in 1:31
> >> The rotating media returned the counts in 8:58
> >>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> 





More information about the dba-VB mailing list