big update problem!

ADDED:
I am also able to create and populate a fairly large table in a matter of
minutes however updating to this one table is costing a lot of time.
Now granted this table in question is much larger then most tables in the DB
and larger then the tables I create but not 10 times larger!
Also, this table gets a lot of activity. Could our problem be the table
itself?
"Sean" wrote:
[vbcol=seagreen]
> I have a mini .net application that I have created to make some pretty
> complex transformationsdatabase.
> All the code, .net datatables and logic etc takes 3 minutes to run on the
> entire database. However, when I put in the update statements the same
> process takes
> 3 1/2 hours!
> So I know its not becuase I am using datatables or that my select statemen
ts
> are poor etc etc etc. Its 100% the update statements.
> Any clues? is this normal? are updates statements the most expensive in time?[/vbc
ol]Yes, and it could be a hardware problem or the phase of the moon. With no
information about what the table looks like, what you are trying to do and
what else is running, there's no way to tell. This is like calling your
mechanic and saying it takes to long for me to get to work, what should I
do? The main difference between update statements and select statements is
they take update locks and write to disk. I would look at blocking waiting
to lock something that's in use first but that's just idle speculation.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:6E7394B4-A544-4111-A35B-4661DF0C459C@.microsoft.com...[vbcol=seagreen]
> ADDED:
> I am also able to create and populate a fairly large table in a matter of
> minutes however updating to this one table is costing a lot of time.
> Now granted this table in question is much larger then most tables in the
> DB
> and larger then the tables I create but not 10 times larger!
> Also, this table gets a lot of activity. Could our problem be the table
> itself?
> "Sean" wrote:
>|||Well there is currently only 3 people accessing the server and only one (me)
accessing this particular database.
by my estimations it is taking about 1-3 seconds to update a row and in this
context each row has to be updated seperately.
Having said all that I think the functionality we are gaining might not be
worth the dev time which is a subject I will have for our meeting.
"Roger Wolter[MSFT]" wrote:

> Yes, and it could be a hardware problem or the phase of the moon. With no
> information about what the table looks like, what you are trying to do and
> what else is running, there's no way to tell. This is like calling your
> mechanic and saying it takes to long for me to get to work, what should I
> do? The main difference between update statements and select statements i
s
> they take update locks and write to disk. I would look at blocking waitin
g
> to lock something that's in use first but that's just idle speculation.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Sean" <Sean@.discussions.microsoft.com> wrote in message
> news:6E7394B4-A544-4111-A35B-4661DF0C459C@.microsoft.com...
>
>|||I have a mini .net application that I have created to make some pretty
complex transformationsdatabase.
All the code, .net datatables and logic etc takes 3 minutes to run on the
entire database. However, when I put in the update statements the same
process takes
3 1/2 hours!
So I know its not becuase I am using datatables or that my select statements
are poor etc etc etc. Its 100% the update statements.
Any clues? is this normal? are updates statements the most expensive in time
?|||ADDED:
I am also able to create and populate a fairly large table in a matter of
minutes however updating to this one table is costing a lot of time.
Now granted this table in question is much larger then most tables in the DB
and larger then the tables I create but not 10 times larger!
Also, this table gets a lot of activity. Could our problem be the table
itself?
"Sean" wrote:
[vbcol=seagreen]
> I have a mini .net application that I have created to make some pretty
> complex transformationsdatabase.
> All the code, .net datatables and logic etc takes 3 minutes to run on the
> entire database. However, when I put in the update statements the same
> process takes
> 3 1/2 hours!
> So I know its not becuase I am using datatables or that my select statemen
ts
> are poor etc etc etc. Its 100% the update statements.
> Any clues? is this normal? are updates statements the most expensive in time?[/vbc
ol]|||Yes, and it could be a hardware problem or the phase of the moon. With no
information about what the table looks like, what you are trying to do and
what else is running, there's no way to tell. This is like calling your
mechanic and saying it takes to long for me to get to work, what should I
do? The main difference between update statements and select statements is
they take update locks and write to disk. I would look at blocking waiting
to lock something that's in use first but that's just idle speculation.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:6E7394B4-A544-4111-A35B-4661DF0C459C@.microsoft.com...[vbcol=seagreen]
> ADDED:
> I am also able to create and populate a fairly large table in a matter of
> minutes however updating to this one table is costing a lot of time.
> Now granted this table in question is much larger then most tables in the
> DB
> and larger then the tables I create but not 10 times larger!
> Also, this table gets a lot of activity. Could our problem be the table
> itself?
> "Sean" wrote:
>|||Well there is currently only 3 people accessing the server and only one (me)
accessing this particular database.
by my estimations it is taking about 1-3 seconds to update a row and in this
context each row has to be updated seperately.
Having said all that I think the functionality we are gaining might not be
worth the dev time which is a subject I will have for our meeting.
"Roger Wolter[MSFT]" wrote:

> Yes, and it could be a hardware problem or the phase of the moon. With no
> information about what the table looks like, what you are trying to do and
> what else is running, there's no way to tell. This is like calling your
> mechanic and saying it takes to long for me to get to work, what should I
> do? The main difference between update statements and select statements i
s
> they take update locks and write to disk. I would look at blocking waitin
g
> to lock something that's in use first but that's just idle speculation.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Sean" <Sean@.discussions.microsoft.com> wrote in message
> news:6E7394B4-A544-4111-A35B-4661DF0C459C@.microsoft.com...
>
>|||Sean wrote:
> Well there is currently only 3 people accessing the server and only one (m
e)
> accessing this particular database.
> by my estimations it is taking about 1-3 seconds to update a row and in th
is
> context each row has to be updated seperately.
> Having said all that I think the functionality we are gaining might not be
> worth the dev time which is a subject I will have for our meeting.
>
1-3 seconds to update a record? How many indexes are on this table? Is
there a clustered index? Is the column you're updating part of the
clustered index key? Are there update triggers on the table?|||Sean wrote:
> Well there is currently only 3 people accessing the server and only one (m
e)
> accessing this particular database.
> by my estimations it is taking about 1-3 seconds to update a row and in th
is
> context each row has to be updated seperately.
> Having said all that I think the functionality we are gaining might not be
> worth the dev time which is a subject I will have for our meeting.
>
1-3 seconds to update a record? How many indexes are on this table? Is
there a clustered index? Is the column you're updating part of the
clustered index key? Are there update triggers on the table?|||Basically none of the above and its safe to assume no optimization strategie
s
have been done for this database.
We have a primary key on this table but that is it really.
I have studied some on clustering because I wanted to go from MCAD to MCSD
but I dropped that database study so in short I am database stupid outside o
f
basic T-SQL statements.
If you guys think clustering etc will help a lot I will look into it because
this table is getting big.
Thanks
"Tracy McKibben" wrote:

> Sean wrote:
> 1-3 seconds to update a record? How many indexes are on this table? Is
> there a clustered index? Is the column you're updating part of the
> clustered index key? Are there update triggers on the table?
>

Big Log file that won't shrink....

I have a fairly simple DB, One Log file, one data file, its got half a dozen tables in it that are used for a web based app that allows users to look each other up (think online telephone directory with a few extra bits and you are there), there's a data feed that adds new lines each day as users data changes or new people get added. But this is a couple of hundred or so lines a day, the tables hold about 40,000 data lines as we never delete data just retire it.

The problem is the main data file is about 200Mb, the Log file however is 3.8Gb. Now we did import and re-import various bits initially and clear down tables and such. But I don't understand why i can't shrink the log file. I've tried everything I can think of. I've even detached and re-attached the DB. Still no joy.

The file size/usage is

data file 200Mb size in use 117Mb

log file 3749.99Mb size in use 3719.89Mb

Anyone have any suggestions for things to look at?

many thanks

Steve

Hi,

look here:

http://www.aspfaq.com/show.asp?id=2471

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

BI Crashing - Untrapped error

Visual Studio is just disappearing without displaying any messages in the cube editor for a fairly complex cube every time I switch to the Calculations, Perspectives, or Translations tabs. The Cube Structure, Dimension Usage, KPIs, Actions, Partitions, and Browser tabs are fine. I recently applied SP2 but I don't think that is the issue as a simple cube works correctly. More likely, the problem was caused by a change in the dimension key for our time dimension. I was testing changing the grain of the time dimension because we have measure groups at different grains of the time dimension (day and month) and this design decision was rendering Report Builder essentially useless. Modifying all of our measure groups to associate at the month grain resolved the Report Builder problem but now I can't create perspectives to simplify the user presentation.

What's unique about these three tabs and what can I do to fix the problem? Any ideas?

Not sure if this is an actual coolprint in your case, but once I saw similar problem when switching to the Calculation tab when I had mismatched versions of the following files: msmdlocal.dll and msmgdsrv.dll. You might want to search for those files on your drive, since setup installs a few copies of them.|||I looked at that but that was not the cause. It doesn't happen on all cubes. I uninstalled SQL Server and reinstalled and the same strange error is still occurring. It would be nice if the error was trapped so I could give you more information.|||

The symptoms described in the following KB article describe exactly what I'm seeing.

http://support.microsoft.com/default.aspx/kb/926421

I do not have Office 2007 installed and have applied SP2. Any ideas?

Best way to handle existing rows

I need to create a fairly simple package that copies data from one database using a sql querie and inserts it into another. what's the best way to handle rows that already exist in the destination table? The package needs to run once a day and ignore rows inserted from previous runs.You can pass the data through a lookup transform and compare them to the destination dataset. The rows that don't match will be marked as errors so click on the "configure the error output" and set it to redirect rows instead of failure. Then you can connect the red arrow to your destination and map the rows. There will be two extra columns but you don't need to worry about them as they are just for error tracking and you don't technically have errors.|||

The method brent describes is discussed in more detail here: http://www.sqlis.com/default.aspx?311

Its also worth saying that if the data in the source is timestamped when it canges then you can use that timestamp to only pull out data changed since previous load. i.e. Reduce the amount of data that you have to process.

-Jamie

Best way to compare two ENTIRE rows in seperate tables?

Hi folks, I've got a fairly easy one here me thinx. I'm looking for the best way to compare two entire rows from two seperate tables which have the same primary key.

Here's the basic lowdown:

I get some data every night from an external system (cache') via DTS. This is more or less my "master" data which drives my application. I have just been informed of an interesting constraint. If any of the data changes in the external system, those changes do not become effective until the first day of the ensuing fiscal quarter.

I'm solving this by running the DTS as normal, but populating a "duplicate" table which I will evaluate once per quarter for any changes. This is also the preferred solution because they would like to see a snapshot between current cache' data and the data my application is currently working with.

So, I end up with two identically structured tables. both tables have the same primary key and can be linked by an id field with relative ease. What I would like to do is a full row comparision once this join is established.

Right now I explicitly check the value of each column. ie:

WHERE t1.field1 <> t2.field1 OR t1.field2 <> t2.field2 OR t1.field3 <> t2.field3 ... etc

I'm hoping there is something buried in TSQL that I just don't know about that can handle comparing entire rows and tell me if they're different. Or perhaps there's another approach all-together.

Any thoughts?in my opinion, a JOIN with a column-by-column comparison is the best way to go

WHERE t1.field1 <> t2.field1 OR t1.field2 <> t2.field2 OR t1.field3 <> t2.field3

another approach:select pkey
from (
select * from table1
union
select * from table2
) u
group
by pkey
having count(*) = 2 since UNION removes duplicate rows, this query will give you all the pkeys which have something different

of course, then you gotta dive back into the tables to see the actual data, so ...|||I'm hoping there is something buried in TSQL that I just don't know about that can handle comparing entire rows and tell me if they're different. Or perhaps there's another approach all-together.There is.select a.pkey
from (select pkey, binary_checksum(*) as checkvalue from a) checksumsa
inner join (select pkey, binary_checksum(*) as checkvalue from b) as checksumb
on checksumsa.pkey = checksumsb.pkey
where checksumsa.checkvalue <> checksumsb.checkvalueBUT...binary_checksum values are not guaranteed to be different 100% of the time. There is a slight chance that the data could be modified in a way that leaves the checksum value unchanged, though the chances are slim.
Have you considered placing a trigger on your secondary table to set a flag when a record is updated? In the end, that may be your best approach.|||Sounds like Delta processing

http://weblogs.sqlteam.com/brettk/archive/2004/04/23/1281.aspx|||When using binary checksums (which is the way I do this comparison across several databases and tables we have) you also have to be aware that there can be differences reported if one of the servers being used in the comparison is a LINKED server...
http://www.dbforums.com/showthread.php?t=1213716|||Good stuff guys! I'll be leveraging a bit of what Rudy and Brett posted for what I need. I was positive there had to be a more elegant method than what I was doing...

Thanks duders!