date and time diffs: rows

Showing posts with label rows. Show all posts

Sunday, March 25, 2012

Dataset′s

Im trying to make a dataset from a stored procedure. My problem is that RS wont use all of the rows returned.

My SP returns 9 rows. Some of them are dublicates and some ar on of a kind. And it seeems that RS just displays one of each kind. No mater how many there are i the resultset.

Ive read that RS cant handle more than one resultset, but i think that the result returned is just one resultset.

If a run the SP in the Data tab it works just fine, it displays all rows. But when im using it in previewmode its not.

The SP is built around a cursor. It makes a temporary table with info. Then just JOINs it with a real table. Would not that create a SINGLE resultset witch RS should read?

Thanks in advanceIf it is working in preview, you are doing something in the layout of your report. Maybe you are grouping by some repeating value which will cause for the number of rows to be reduced. Try to start with a blank slate and drop the fields in the detail row of a table.|||

Thanks, it workt. I started of with a blank report. You where right, it was somehing in the layout section that flawed.

Dataset.Clear() doesn't mark rows as deleted

Ok, I've spent a good amount on time on debugging an unupdating scenario in my application. Finally I knew the reason which is very annoying. Either I'm missing something really obvious (I hope so,) or this is a bug.

to reproduce what I'm talking about:
1- create a new win forms application using VS2005 sp1
2- add some SQL Compact Edition data file that have some records from the data menu, you'll get the designer to generate the dataset and everything..
3- drag a table from the data sources window, you'll get the data grid and the navigator on the form
4- add a button and have this in the click event handler:
datasetname.Clear();
TableAdapterName.Update(datasetname);

launch the program, click the button, you'll see the grid get wiped out as it supposed to do. close the program and relaunch. the data is there again (this has nothing to do with the copy always, copy if newer infamous stupidity)

now:
5- edit the click event handler and change it so something like this:
foreach (datasetname.TableRow row in datasetname.Table)
{
row.Delete();
}
TableAdapterName.Update(datasetname);

Launch the program, hit the button, grid wiped out. exit and relaunch. You'll see no data (i.e. the update on table adapter worked alright)

You can also try the GetChanges method on the dataset rightafter you use the clear method and you'd get no deleted records at all.

So, in 100,000+ records dataset, if i need to wipe the thing out and add some new records do i have to loop over every record and call delete (which will take LOTS of time).
I do hope that I'm missing something obvious.

Any help would be highly appreciated.

Thanks.

Yes, that's because Clear() is not supposed to mark rows as deleted, it suppose to clear tables of all row regardless of the row states.

Besides there’s no magic way to mark all rows as deleted except by looping through them, so should Clear() actually do that it would take a while anyway.

If you need to delete all the rows and then add some here’s how to do that efficiently:

1. Execute SQL query(ies) to delete rows strait from the database.

2. Create new empty DataSet with the same schema and use it instead of existing one (which would be collected).

3. Add rows you need.

4. Execute update using new DataSet.

Thursday, March 22, 2012

Dataset rows?

Hello Team

i want to insert more than one row to the dataset before update the sqladapter for ex i want to insert rows for orderlines then i send them all to sql by updating adapter

is it done by javascript ? because when i press the button a postback hapend then it clears the dataset so the new row clears the old one

any idea Thanks lot

Hi,

this probably relates to that the DataSet instance isn't preserved over postbacks? E.g you have a DataSet to which (a DataTable in it) you add the row when Button is clicked, but since DataSet isn't using anything as store to survive over postbacks, it gets recreated. E.g standard member variables do not surive postbacks in ASP.NET, they need to use something to keep them alive (ViewState, Session, cache etc)

DataSet rows being deleted, but after the update , the sql database is not updated. The de

Stepping thru the code with the debugger shows the dataset rows being deleted.

After executing the code, and getting to the page presentation. Then I stop the debug and start the

page creation process again ( Page_Load ). The database still has the original deleted dataset rows.

Adding rows works, then updating works fine, but deleting rows, does not seem to work.

The dataset is configured to send the DataSet updates to the database. Use the standard wizard to create the dataSet.

cDependChildTA.Fill(cDependChildDs._ClientDependentChild, UserId);

rowCountDb = cDependChildDs._ClientDependentChild.Count;

for (row = 0; row < rowCountDb; row++)
{

dr_dependentChild = cDependChildDs._ClientDependentChild.Rows[0];
dr_dependentChild.Delete();

//cDependChildDs._ClientDependentChild.Rows.RemoveAt(0);

//cDependChildDs._ClientDependentChild.Rows.Remove(0);
/* update the Client Process Table Adapter*/
// cDependChildTA.Update(cDependChildDs._ClientDependentChild);
// cDependChildTA.Update(cDependChildDs._ClientDependentChild);

}

/* zero rows in the DataSet at this point */

/* update the Child Table Adapter */
cDependChildTA.Update(cDependChildDs._ClientDependentChild);

Hi,

You should use AcceptChanges method after using delete in order to update the data. The following link may be helpful to you.

http://msdn2.microsoft.com/en-us/library/ms233823(VS.80).aspx

Thanks.

DataSet Performance

Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrennerUse a stored procedure to do the update. This sounds very much like you
are using a table as an array though, which isn't generally a good way
to model data in SQL.
David Portas
SQL Server MVP
--|||I've already tried it with stored procedures and the performance isn't
better.
The problem on the data model is that the 100 rows and 100 columns are
representing a fincance plan. So each column must save additional
information (like formats, formula, ...).
So I have a table for each row (called "Position") and this table references
another table which stores the columns (called "PosVal") of the row. With
this data model I've the possibility that the table "PosVal" can reference
other tables which contains the format, formulas...
Or this there any other way to model this?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109333983.806317.283120@.g14g2000cwa.googlegroups.com...
> Use a stored procedure to do the update. This sounds very much like you
> are using a table as an array though, which isn't generally a good way
> to model data in SQL.
> --
> David Portas
> SQL Server MVP
> --
>|||It seems like you are trying to model an abstraction ("rows", "columns"
and "formulae" from a hypothetical spreadsheet) instead of modelling
the actual data. Isn't your metadata static enough to create a proper
relational representation of it? If not then I suggest you need a
middle tier to present this data. The back end may be largely
irrelevent - I'm not sure just what benefit you are hoping to get from
using SQL Server as the data store for this.
If you do have some real data to model, then A) Normalize your tables,
B) post a CREATE TABLE statement and your stored proc. Since your proc
can update an entire row at a time I would have expected 100 updates to
outperform 10,000 but that largely depends on how you are doing the
updates and what your data looks like.
David Portas
SQL Server MVP
--|||Klaus Aschenbrenner wrote:
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter,
> this generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which
> reduces the UPDATE statements? Or how can I handle such big updates
> with SQL Server?
>
Are you using the CommandBuilder to generate the code? It probably makes
more sense to write the code yourself.
You stated in a subsequent reply that you created a stored procedure to do
the update but that it did not improve performance. Could you elaborate on
what the procedure did? I'm assuming you created a procedure that accepted
parameters for each of the 100 columns and did the update for an entire row
at a time, requiring 100 calls to the procedure instead of 10000 calls to a
procedure that did 1 column at a time...
Is that correct?
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Klaus,
You can use SQLXML that comes with MDAC to reduce the number of hits going
to your database. You can then send an updategram. With 2.0, you have a
graceful upgrade to the Managed SQLXML driver, so it's not a deadend.
This is obviously SQL Server specific, but Oracle has other such equivalent
solutions.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:#hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
by using the tool correctly and using the native enhancements to boost
performance. That is not to say that an RDBMS can not be fast, on the
contrary; however, that is not its chief purpose.
If performance is your ONLY concern, use a flat file or an XML file.
You use an RDBMS to MODEL THE DATA, so that others can query it in a myriad
of ways and garauntee that there results are accurate. Therefore, you have
to use the RELATIONAL rules to model your data before you build the physical
database and constrain it in order to provide DATA INTEGRITY. It is this
integrity that you build on an RDBMS system. The system is optimized for
performance, but only after providing the foundation, a relational database
properly constrained.
Sincerely,
Anthony Thomas
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner|||You must be a consultant. :)
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||Klaus,
The rows are updated depending on the rowstate.
It can be that when you start that the rowstate are set to "added" while you
don't want to update them all. (The dataadapter.fill set them automaticly
to unchanged when you have not set the property for that to false, however
when you load them by hand, by instance using a datareader they are all set
to "added".).
You can by instance in the case of that filling with the datareader set the
rowstate of all rows to unchanged by ds.acceptchanges
Maybe this helps?
Cor|||If only consultants would be so highly critical.
Sincerely,
Anthony Thomas
"Sahil Malik" <contactmethrumyblog@.nospam.com> wrote in message
news:%23Phk7IcHFHA.2984@.TK2MSFTNGP15.phx.gbl...
You must be a consultant. :)
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>sql

DataSet Performance

Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at, www.anecon.com
http://weblogs.asp.net/klaus.aschenbrennerUse a stored procedure to do the update. This sounds very much like you
are using a table as an array though, which isn't generally a good way
to model data in SQL.
--
David Portas
SQL Server MVP
--|||I've already tried it with stored procedures and the performance isn't
better.
The problem on the data model is that the 100 rows and 100 columns are
representing a fincance plan. So each column must save additional
information (like formats, formula, ...).
So I have a table for each row (called "Position") and this table references
another table which stores the columns (called "PosVal") of the row. With
this data model I've the possibility that the table "PosVal" can reference
other tables which contains the format, formulas...
Or this there any other way to model this?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at, www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109333983.806317.283120@.g14g2000cwa.googlegroups.com...
> Use a stored procedure to do the update. This sounds very much like you
> are using a table as an array though, which isn't generally a good way
> to model data in SQL.
> --
> David Portas
> SQL Server MVP
> --
>|||It seems like you are trying to model an abstraction ("rows", "columns"
and "formulae" from a hypothetical spreadsheet) instead of modelling
the actual data. Isn't your metadata static enough to create a proper
relational representation of it? If not then I suggest you need a
middle tier to present this data. The back end may be largely
irrelevent - I'm not sure just what benefit you are hoping to get from
using SQL Server as the data store for this.
If you do have some real data to model, then A) Normalize your tables,
B) post a CREATE TABLE statement and your stored proc. Since your proc
can update an entire row at a time I would have expected 100 updates to
outperform 10,000 but that largely depends on how you are doing the
updates and what your data looks like.
--
David Portas
SQL Server MVP
--|||Klaus Aschenbrenner wrote:
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter,
> this generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which
> reduces the UPDATE statements? Or how can I handle such big updates
> with SQL Server?
>
Are you using the CommandBuilder to generate the code? It probably makes
more sense to write the code yourself.
You stated in a subsequent reply that you created a stored procedure to do
the update but that it did not improve performance. Could you elaborate on
what the procedure did? I'm assuming you created a procedure that accepted
parameters for each of the 100 columns and did the update for an entire row
at a time, requiring 100 calls to the procedure instead of 10000 calls to a
procedure that did 1 column at a time...
Is that correct?
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Klaus,
You can use SQLXML that comes with MDAC to reduce the number of hits going
to your database. You can then send an updategram. With 2.0, you have a
graceful upgrade to the Managed SQLXML driver, so it's not a deadend.
This is obviously SQL Server specific, but Oracle has other such equivalent
solutions.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:#hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at, www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
by using the tool correctly and using the native enhancements to boost
performance. That is not to say that an RDBMS can not be fast, on the
contrary; however, that is not its chief purpose.
If performance is your ONLY concern, use a flat file or an XML file.
You use an RDBMS to MODEL THE DATA, so that others can query it in a myriad
of ways and garauntee that there results are accurate. Therefore, you have
to use the RELATIONAL rules to model your data before you build the physical
database and constrain it in order to provide DATA INTEGRITY. It is this
integrity that you build on an RDBMS system. The system is optimized for
performance, but only after providing the foundation, a relational database
properly constrained.
Sincerely,
Anthony Thomas
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at, www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner|||You must be a consultant. :)
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at, www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||Klaus,
The rows are updated depending on the rowstate.
It can be that when you start that the rowstate are set to "added" while you
don't want to update them all. (The dataadapter.fill set them automaticly
to unchanged when you have not set the property for that to false, however
when you load them by hand, by instance using a datareader they are all set
to "added".).
You can by instance in the case of that filling with the datareader set the
rowstate of all rows to unchanged by ds.acceptchanges
Maybe this helps?
Cor|||If only consultants would be so highly critical.
Sincerely,
Anthony Thomas
"Sahil Malik" <contactmethrumyblog@.nospam.com> wrote in message
news:%23Phk7IcHFHA.2984@.TK2MSFTNGP15.phx.gbl...
You must be a consultant. :)
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at, www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>

DataSet Performance

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>
|||Klaus,
The rows are updated depending on the rowstate.
It can be that when you start that the rowstate are set to "added" while you
don't want to update them all. (The dataadapter.fill set them automaticly
to unchanged when you have not set the property for that to false, however
when you load them by hand, by instance using a datareader they are all set
to "added".).
You can by instance in the case of that filling with the datareader set the
rowstate of all rows to unchanged by ds.acceptchanges
Maybe this helps?
Cor
|||If only consultants would be so highly critical.
Sincerely,
Anthony Thomas

"Sahil Malik" <contactmethrumyblog@.nospam.com> wrote in message
news:%23Phk7IcHFHA.2984@.TK2MSFTNGP15.phx.gbl...
You must be a consultant.

DataSet Performance

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||Klaus,
The rows are updated depending on the rowstate.
It can be that when you start that the rowstate are set to "added" while you
don't want to update them all. (The dataadapter.fill set them automaticly
to unchanged when you have not set the property for that to false, however
when you load them by hand, by instance using a datareader they are all set
to "added".).
You can by instance in the case of that filling with the datareader set the
rowstate of all rows to unchanged by ds.acceptchanges
Maybe this helps?
Cor|||If only consultants would be so highly critical.
Sincerely,
Anthony Thomas
"Sahil Malik" <contactmethrumyblog@.nospam.com> wrote in message
news:%23Phk7IcHFHA.2984@.TK2MSFTNGP15.phx.gbl...
You must be a consultant.

Wednesday, March 21, 2012

dataset into a table or matrix with fixed number of columns, variable rows

I have a dataset with 2 columns, a rownumber and a servername - eg

rownumber servername

1 server1

2 server2

....

15 server15

I want to display the servernames in a report so that you get 3 columns - eg

server1 | server2 | server3

server4 | server5 | server6

...

server13 | server14 | server15

I have tried using multiple tables and lists and filtering the data on each one but this then makes formating very hard - i either end up with a huge gap between columns or the columns overlap

I have also tried using a matrix control but cant find a way to do this.

Does anybody know an easy way to do this? The data comes from sql 2005 so i can use a pivot clause on the dataset if somebody knows a way to do it this way. The reporting service is also RS2005

Thanks

Anthony

dont you hate it when you figure out the solution just after you have asked the question.

Here is how i managed to do it if anybody else is interested.

First put the data in a matrix control so you have all the servernames on one row.

Then add a list control and put the matrix in the list control.

Finally set the grouping expression on the list control to be

=Ceiling(RowNumber(Nothing)/3)

Anthony

Monday, March 19, 2012

Datareader Query Timeout

Hello,

I am running a query via a ado.net data flow source. It works great for a small number of rows, but if I try to execute a long running query it times out with a communication error. I have looked through the doc and every property sheet that I can find in my package, but I can't find anywhere that a timeout is specified. Any help on finding this would be appriciated. The query is running against DB2 on z/os and I know it is timing out on the server side because I can watch the query run on z/os and it continues to run after SSIS gets the error.

Thanks!
HarryHave you checked in the connection manager? Specifically, on the "All" page?|||Yes, I have checked and there is nothing there. Please someone help Tongue Tied |||I'm looking at the Advanced Editor of the Datareader Destination and there is a ReadTimeout property. Is this not what you want?|||The error occurs on a datareader source and I can't find any timeout properties on that component?|||DataReaderSrc does not timeout, as long as the ADO.Net connection manager is still receiving data from the server, it will pull out all and pass them to its downstream dataflow components - I tried to read 27million rows (4GB) in DataReaderSrc(using HIS provider for DB2) and I did not see any problems reading out all rows.
The issue does not seem to me like a SSIS problem. What provider you were using? you only got the timeout, no other error info? And, just a wild guess, was deadlock possible when you ran your query -were there any other concurrent transactions accessing the same sources?
Thanks
Wenyang|||The DataReader source can and does time out. I tried to sort and read 26 million rows (8 GB) with no indexes from a SQL Server 2000 database and it failed with a timeout on the DataReader. Changing the timeout on the DataReader destination has no effect.

I can us an OLE DB Source with a command timeout, but then there is no way to tell it that a column is sorted so no merge join later in the process.

There's a hole in the bucket, dear Liza...

pjp|||

Thanks for the post, Preston. I'll appreciate if you can provide us more info on the followings. Thanks in advance.

>The DataReader source can and does time out. I tried to sort and read 26 million rows (8 GB) with no indexes from a SQL Server 2000 database and it failed with a timeout on the DataReader.
When did you get the timeout error - at design time or at execution time? DataReaderSrc adapter itself, based on the current design, has no timeout related property to expose the command timeout control over to the customers. Can you post here the full timeout error info you received so we can look into this?

>Changing the timeout on the DataReader destination has no effect.
Why the DataReaderDest is realted?

>I can us an OLE DB Source with a command timeout, but then there is no way to tell it that a column is sorted so no merge join later in the process.
Actually you can.
1) The easiest is to use Sort transform at OLEDBSrc downstream to sort on certain columns before leading the dataflow to MergeJoin.
2) Or, in advanced UI of OLEDBSrc, change the "IsSorted" property of the output to true, the "SortKeyPosition" of those sorted output columns to 1,2,3...respectively, then you can directly hook OleDbSrcs to merge join.

Wenyang

|||

Sorry for the delay in responding. I did not get notified of a response for some reason. Anyway, the error is at runtime. The command is "SELECT * FROM [BigTable] ORDER BY [SomeColumn]." It is easily fixed by adding an index to the table but the fact remains: a DataReader source will timeout.

BTW, I would not expect the DataReaderDest to be related; I made the comment because someone else had mentioned it.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Error: 0xC0047062 at Data Flow Task, DataReader Source 2 [8143]: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

at System.Data.SqlClient.SqlDataReader.get_MetaData()

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()

at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)

Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "DataReader Source 2" (8143) failed the pre-execute phase and returned error code 0x80131904.

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "DataReaderDest" (8965)" wrote 0 rows.

Task failed: Data Flow Task

Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

|||Preston, thanks for your detailed error information.
I've looked this over, currently it is by design that the customers are not allowed to change the timeout at DataReaderSrc, but you are right, that could cause DataReaderSrc to experience command timeout at certain special circumstances. I have logged a request on your behalf for a design change to expose a command timeout property to the users, hopefully it can be addressed in our SP1 release.
You mentioned about your work around for this in your post already, other work arounds may include relieve the workload at DataReaderSrc to its downstream components e.g. Aggregate,Sort, rather than doing everything in one query at DataReaderSrc
Thank you
Wenyang|||After days of misery I finally found this thread ... Thanks Preston and Wenyang for the accurate and descriptive information contained.

I experience a similar problem with my Source connection - the problem being it is to an Oracle database on the other side of the world. I normally expect poor connectivity and response times to this database and so I thought I'd set up an overnight SSIS process to download the relevant data to a local SQL Server database for analysis rather than trying to analyse remotely.

The problem is that I have a relatively complex query to retrieve only the pertinent information from Oracle, and so it is normallt 1-2 minutes from the time I send the query until I begin to receive results (depending on dataabse utilization and network performance). Since I cannot set the timeout property anywhere for the source (DataReaderSrc) I am not able to persue this solution at the moment. I am using a Config file for my database connections since I have not been able to find any other way of sending my password to an Oracle dataabse. I have not found any way to set the dataabse timeout.

Has there been any update - can I set a Timeout on the data source?

The relevant error from teh Log events is as follows:

System.Data.Odbc.OdbcException: ERROR [HYT00] [Oracle][ODBC][Ora]ORA-01013: user requested cancel of current operation

at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)
|||

You can find CommandTimeOut property on the Advanced Editor page under Component properties tab.

|||

Jia,

That seems to work for OLE DB sources, but I also have no CommandTimeOut property on the Advanced Editor page under Component Properties when using a DataReader source and I have the same timeout problem. Simply trying to pull data from an average sized Teradata data warehouse table (~10M rows) using just select field1, field2... from table

Seems like sql server 2005 can't pull data via odbc if the query takes longer than 30 seconds?

Any help would be appreciated.

|||I have installed SP1 and now have the ability to set the timeout in the data reader. I have been able to resolve most of the issues I was facing. The first was to use an OLE DB data source to read from Oracle, then this one to set an appropriate timeout limit (0 is not 'infinite, it is 30 seconds, so I upepd it to 600 seconds). I had another issue to do with dates - Australian date formats are not handled very well between SQL Server and Oracle. I had to embed oracle "to_date" functions in the selection criteria.