Re: transaction disabling

From: Glenn (g.e.r_at_comcast.net)
Date: 03/21/04 

Date: Sun, 21 Mar 2004 17:59:59 -0500

Andrew:

Thanks. All this is internal to the database. The lookup tables are just
small tables in the database that a user can change. The keys of these
tables I want to add directly to a large table in the database. Every row in
the large table will have these fields updated to the joined foreign key
fields.

 I cannot delete the records in the large tables, I just need to update a
couple of fields to the new/changed lookup key values. Thus I am running
an update sproc joining the large table to the lookup tables.

Just FYI, I am doing this as I have a dynamic query building form that users
can build queries off of many base queries hitting many different large
tables that join the same lookup tables. The filter keys are higher in the
hierarchy than the large base tables and for some reason the query optimizer
is not able propigate down to the large tables and the queries were running
poorly. By filtering the large table directly by using these stored keys
the queries go down from 40 minutes to 5 seconds.

How can I limit the number of rows in a transaction in a query? Just run
the update query in a loop filtering on key ranges?

Best,
Glenn

"Andrew J. Kelly" <sqlmvpnoooospam@shadhawk.com> wrote in message
news:uhx$RS5DEHA.580@TK2MSFTNGP11.phx.gbl...
> You can not disable the logging but you can under certain circumstances do
a
> minimally logged load. Not really sure what your up against without DDL
or
> samples but here are some suggestions. If this is one or a few tables
that
> are strictly lookup tables I would create a text file with the values you
> want the table to be first. Then truncate the existing table and BCP the
> file into the table. The BCp should be able to happen in a few minutes at
> most if done correctly. Check out the section in BOL under "minimally
> logged bulk copy" for the details. If this isn't feasible I would try
doing
> the updates in smaller batches of say 10000 rows or so at a time. This
will
> keep your log file from filling and should dramatically increase the speed
> of the overall operation. Last I suggest you manually increase the log
file
> to larger than you think it needs for the operation before you start.
Make
> sure the log file is on a separate physical drive (preferably a RAID 1)
from
> the data drive. The growing can be very expensive and time consuming and
> will only slow down your operation.
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Microsoft Public" <g.e.r@comcast.net> wrote in message
> news:uDDYZ64DEHA.624@TK2MSFTNGP10.phx.gbl...
> > Is it possible to suspend/disable transaction logging on a query? I
have
> a
> > static database that I need to update a table of @ 5 million rows
through
> a
> > single query every couple of days that updates some static lookup fields
> > based on other small tables which may have changed.
> >
> > The recovery mode is simple, but the update runs about 3 hours which
> seems
> > rediculously long even though the server is not that fast (450MH) and
> memory
> > only 750MB, though there is plenty of disk space.
> >
> > Is there a better way of doing this, a bulk update mode? The log file
> grows
> > to about 2 GB. I have it set up to grow by 250MB chunks.
> >
> > Thanks.
> >
> > Best,
> > Glenn
> >
> >
>
>

Relevant Pages

  • Re: Join to one of two tables (based on a value in the source table)
    ... > yourself a query that returns apples or octopi. ... or the database in which it resides). ... for items not found in that lookup ... is set to 1, joining tableLists.UnitName to tblLists_lookup2.UNITNM. ...
    (microsoft.public.sqlserver.programming)
  • Re: Join to one of two tables (based on a value in the source table)
    ... >> yourself a query that returns apples or octopi. ... > or the database in which it resides). ... > table to store similar information, for items not found in that lookup ... I need data from a table that doesn't belong to me ...
    (microsoft.public.sqlserver.programming)
  • Re: Row cannot be located for updating error (ADO)
    ... "DBEdit1 the field double clicked) in the OnDoubleClick event is writing ... directly to the database but the data aware controls are writing to the ... >>lookup value) dependant on the users selection this is then saved to the ... > I assume your query is a Join using fields that come from the table your ...
    (borland.public.delphi.database.ado)
  • Re: transaction disabling
    ... query scans the 5 million rows how many of those will actually meet the ... > (from subgroups to group, from fiscal week keys to fiscal month keys, ... The lookup tables are ... By filtering the large table directly by using these stored ...
    (microsoft.public.sqlserver.server)
  • Re: transaction disabling
    ... The lookup tables are just ... The keys of these ... > can build queries off of many base queries hitting many different large ... > hierarchy than the large base tables and for some reason the query ...
    (microsoft.public.sqlserver.server)