big update problem!

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:
> 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?|||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...
> 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:
>> 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?|||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 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...
> > 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:
> >
> >> 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?
>
>|||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.
>
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 strategies
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 of
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:
> > 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.
> >
> 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?
>|||and correction, its only .4 a second for updates per record.
Its just that at the point of production we will have
157,000 records which if this was all we were doing it would be fine. but
this one item is taking up more then 50% of the time for our data conversion.
"Sean" wrote:
> Basically none of the above and its safe to assume no optimization strategies
> 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 of
> 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:
> > > 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.
> > >
> >
> > 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?
> >

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 generate Excel from SQL Server?

I have a requirement to generate a moderately complex spreadsheet containing
multiple worksheets and formulas from within SQL Server. I'm interested in
what is the recommended way of doing this. I will need to organize the data
within a stored procedure, generate the excel output and save it in a file,
which the application, which is web-based, will ship to the client's browser
to hopefully pop up into Excel. I've looked into generating HTML and XML
output, which can be loaded into Excel and functions as a spreadsheet, but i
t
would be better if I could generate an xls file in native Excel format. That
is because the end user should be able to work with these files over time,
and I would not want to have to deal with incompatibility issues.
Other options I'm considering:
1. Analysis Services - I need to research this more
2. Reporting Services - Ditto
3. Writing a procedure in C# to take the logic outside of SQLServer and
thereby having better access to Office API.
Thanks for any recommendations.Hi Steve,
Without thinking over so much this kind of task seems very affordable
by means of DTS along with some custom tasks done with VbScript (or
something like that)
Let me know your doubts or concerns with this.
Regards,
"Steve Elliott" wrote:

> I have a requirement to generate a moderately complex spreadsheet containi
ng
> multiple worksheets and formulas from within SQL Server. I'm interested in
> what is the recommended way of doing this. I will need to organize the dat
a
> within a stored procedure, generate the excel output and save it in a file
,
> which the application, which is web-based, will ship to the client's brows
er
> to hopefully pop up into Excel. I've looked into generating HTML and XML
> output, which can be loaded into Excel and functions as a spreadsheet, but
it
> would be better if I could generate an xls file in native Excel format. Th
at
> is because the end user should be able to work with these files over time,
> and I would not want to have to deal with incompatibility issues.
> Other options I'm considering:
> 1. Analysis Services - I need to research this more
> 2. Reporting Services - Ditto
> 3. Writing a procedure in C# to take the logic outside of SQLServer and
> thereby having better access to Office API.
> Thanks for any recommendations.|||I've had a project with similar requirements.
What I did was encapsulate my complex queries in stored procedures.
I called these stored procedures via classic asp and rendered the output in
excel format.
I then created an excel macro for the user to do any requisite formatting.
"Steve Elliott" wrote:

> I have a requirement to generate a moderately complex spreadsheet containi
ng
> multiple worksheets and formulas from within SQL Server. I'm interested in
> what is the recommended way of doing this. I will need to organize the dat
a
> within a stored procedure, generate the excel output and save it in a file
,
> which the application, which is web-based, will ship to the client's brows
er
> to hopefully pop up into Excel. I've looked into generating HTML and XML
> output, which can be loaded into Excel and functions as a spreadsheet, but
it
> would be better if I could generate an xls file in native Excel format. Th
at
> is because the end user should be able to work with these files over time,
> and I would not want to have to deal with incompatibility issues.
> Other options I'm considering:
> 1. Analysis Services - I need to research this more
> 2. Reporting Services - Ditto
> 3. Writing a procedure in C# to take the logic outside of SQLServer and
> thereby having better access to Office API.
> Thanks for any recommendations.|||there are lot way ways to get report into Excel.. the simple way is just
create the query to create view then open excel sheet then import the view
into excel by using Data Menu then Getexternal Data.
hope this will help u.
Regards
S Kaliyan
"Steve Elliott" <SteveElliott@.discussions.microsoft.com> wrote in message
news:C2E9EF12-A3F4-477B-A882-113412E03845@.microsoft.com...
> I have a requirement to generate a moderately complex spreadsheet
containing
> multiple worksheets and formulas from within SQL Server. I'm interested in
> what is the recommended way of doing this. I will need to organize the
data
> within a stored procedure, generate the excel output and save it in a
file,
> which the application, which is web-based, will ship to the client's
browser
> to hopefully pop up into Excel. I've looked into generating HTML and XML
> output, which can be loaded into Excel and functions as a spreadsheet, but
it
> would be better if I could generate an xls file in native Excel format.
That
> is because the end user should be able to work with these files over time,
> and I would not want to have to deal with incompatibility issues.
> Other options I'm considering:
> 1. Analysis Services - I need to research this more
> 2. Reporting Services - Ditto
> 3. Writing a procedure in C# to take the logic outside of SQLServer and
> thereby having better access to Office API.
> Thanks for any recommendations.