Re: transaction disabling
From: Andrew J. Kelly (sqlmvpnoooospam_at_shadhawk.com)
Date: 03/23/04
- Next message: ronald: "Help!"
- Previous message: Thirumal: "Access vs. SQL"
- In reply to: Glenn: "Re: transaction disabling"
- Next in thread: Glenn: "Re: transaction disabling"
- Reply: Glenn: "Re: transaction disabling"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 22 Mar 2004 20:35:06 -0600
Glenn,
That just confuses the heck out of me<g>... A lot of time in simplifying an
example you loose a lot of the actual detail. A change like an employee to
a different department should only affect a few rows in a properly
normalized design. Again I can't tell from the cryptic column names and no
actual data but it doesn't look like a very normalized design. When your
query scans the 5 million rows how many of those will actually meet the
WHERE clause? If your talking more than a just a few % of the total a table
scan is usually the more efficient route. Part of your problem could be
that you are getting blocked by other users during this operation. I would
definitely see if you can do the updates in small batches though.
-- Andrew J. Kelly SQL MVP "Glenn" <g.e.r@comcast.net> wrote in message news:OKYlUAFEEHA.1240@TK2MSFTNGP10.phx.gbl... > Andrew: Thanks, it is not what you think regarding the update: here, > simplified is what I am doing > > LargeTabe: (lower level in a hierarchy) SmallLookupTables (higher > hierarchy level) > LowKey1 HighKey > LowKey2 LowFK1 > OtherDate LowFX2 > StoredHighKey HighKeyName > > There is actually three other look up tables with higher hierarchy key > values I want to store > (from subgroups to group, from fiscal week keys to fiscal month keys, etc). > > In the vast majority of my queryies in any project just joining on the > tables and filtering on the Higher level key is fine, even for large tables, > but for whatever reason (and I have played around with all sorts of index > arrangements) the optimizer is failing and my queries are just using table > scans on the 5 or 6 million rows and taking forever. If I change to filter > to use the stored HighKey field, it takes seconds. > > Unfortunately some of the relationships to the higher levels change > frequently (a sub group now is associated with a different main group (like > an employee now is in a different dept,etc.) and so I have to update the > stored keys frequently. This query I quit on after 5 hours of running and > will try your suggestion of limiting the rows. > > Thanks for you time. > > > > > "Andrew J. Kelly" <sqlmvpnoooospam@shadhawk.com> wrote in message > news:u99Kxn7DEHA.3080@TK2MSFTNGP10.phx.gbl... > > Glenn, > > > > Still not 100% sure I understand what your doing but it sounds bad. It > > sounds like you are changing the rows in the lookup tables including their > > PK with new values and then need to update the FK's in the larger tables > to > > match. You should never change a PK. If you need to change the > description > > that's OK there is no need to change the PK and that should not > necessitate > > the need to change the parent table. Hopefully I am just not > understanding > > why your doing this. In either case you can usually do the small batches > by > > using a WHILE loop and ROWCOUNT. > > > > SET ROWCOUNT 10000 > > > > WHILE 1 = 1 > > BEGIN > > > > UPDATE YourTable SET FK = x WHERE FK = y > > > > IF @@ROWCOUNT = 0 > > BREAK > > END > > > > SET ROWCOUNT 0 > > > > -- > > Andrew J. Kelly SQL MVP > > > > > > "Glenn" <g.e.r@comcast.net> wrote in message > > news:OmqDHi5DEHA.2088@TK2MSFTNGP10.phx.gbl... > > > 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 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
- Next message: ronald: "Help!"
- Previous message: Thirumal: "Access vs. SQL"
- In reply to: Glenn: "Re: transaction disabling"
- Next in thread: Glenn: "Re: transaction disabling"
- Reply: Glenn: "Re: transaction disabling"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
