|
btsrinath wrote: For getting 7000 records from DB it takes ~1hr 20mins. So you can just Imagine how many records are there in DB.
Or (possibly) how poorly configured the indexes are.
btsrinath wrote: If I regularly update the dataset and data adapter by methods AcceptChanges and Update methods resepectively would the memory usage would reduce ?
Unlikely. As a general rule I simply don't use DataSets becuase of the memory overheads. I only use them for coding things in a quick and dirty way where speed of coding is preferred.
btsrinath wrote: Can please suggest an good way of solving my problem ?
If you are doing any complex joins, judicious use of the tempDB can improve performance. Pulling data in to temp tables then performing Joins between these temp tables with a fraction of the rows is much faster than the same join on the original table. A few years ago I speeded up a query that was taking 20+ minutes down to 7 seconds this way. (There were billions of rows in the table, but my calculation only required joining a few thousand of them)
However, without knowing more about what you are doing any advice I give may be completely useless to you.
|
|
|
|
|
You should really look at your data. I would try and find some way to query just a fraction of the data from the database. Often these issues can be solved by looking at your database from another angle. How many records at a time are you processing, can they be processed in smaller batches?
Really look at your data.
Also, you might want to try and right the results back to the main db from time to time, anything to free up resources. It sounds like you are trying to do too much at one time.
I hope this helps.
_____________________________________________________________________
Our developers never release
code. Rather, it tends to escape, pillaging the countryside all around.
The Enlightenment Project (paraphrased comment)
Visit Me at GISDevCafe
|
|
|
|
|
If you are processing one record at a time, ie. 1 million customer orders, use a data reader to scan through and a forward only manner using less memory.
If you are using aggregate results SQL is a powerful language designed to work on large sets of data very efficiently.
Last note: if you have to work with large chucks of data in memory find a natural divider and use smaller sets.
File Not Found
|
|
|
|
|
I need to open an Excel file that has been stored in the Access Database using the insert Object functionality of MSAccess manually.
What i am aware of is that i cant just read the field containing the Excel File into a Byte Array and pass it to the Excel object in C#,as the file is wrapped in the OLE Wrapper used by Access while inserting the file in database.
I have tried locating the Header of Excel file from the byte array and read the file from there on but it is not working.
Can some one please tell me as to how i can open the Excel File.
|
|
|
|
|
Shaurya_Rastogi wrote: I need to open an Excel file that has been stored in the Access Database using the insert Object functionality of MSAccess manually.
I'm not sure I know what you mean by, "...insert Object functionally of MSAccess..."
I'm aware of 'Insert-->Object' in Excel, but the only thing I can think of in Access that is even close is the OLE datatype.
“Some have an idea that the reason we in this country discard things so readily is because we have so much. The facts are exactly opposite - the reason we have so much is simply because we discard things so readily. We replace the old in return for something that will serve us better.”--Alfred P. Sloan
|
|
|
|
|
:(Please anybody help me,
i m having leavedate column in my employee database,
Currently working employee's leave date may be empty,
it is not having problem when i m inserting row into databse with empty leavedate
but when i m going to fill record in my vb.net (winforms) form , it is giving me following error->
Conversion from type 'MySqlDateTime' to type 'Date' is not valid.
I am using datareader to read each value.
Please help me soon.
-Thanks for any help in advance.
priya
|
|
|
|
|
Check the value before trying to convert it. Any column that has a null value will be DBNull.Value
|
|
|
|
|
I have a populated data set (loaded from an XML file) that I want to put in a SQL database.
Most of the tables in the DataSet are related, so I can't use a DataAdapter to update because I don't know the update order.
All the examples I've seen about updating an SQL database only target one table or know the 'reverse-order' in which to update.
Is there an easy way to store a .NET multi-table DataSet to a SQL database.
|
|
|
|
|
If you are using sql 2005 server then you could look at SqlServer Management objects(SMO)
transfer<code> method. This will allow you to move the whole schema and tables over at one time. SMO is not always the easiest to implement, but with a little monkeying around you should be able to get it to work. Check out an article I posted a couple weeks ago to get started, as well as search the site for SMO. There are not allot of resources on it but it might help.<br />
<br />
Aaron <br />
<br />
<div class="ForumSig">_____________________________________________________________________<br />
<br />
Our developers never release <br />
code. Rather, it tends to escape, pillaging the countryside all around.<br />
<br />
The Enlightenment Project (paraphrased comment)<br />
<br />
Visit Me at <a href="http://www.gisdevcafe.com" rel="nofollow">GISDevCafe</a></div>
|
|
|
|
|
Using ADO.NET how do I iterate through each record in a result set, test a field or fields for the presence of certain values then update the field if those values exist? We take in large amounts of records (millions of rows) from clients which are loaded into a SQL Server database - I have noticed some of the columns have unwanted characters in them (tabs, linefeeds, odd graphic characters etc) so I need to vist each row in turn, iterate through all the columns checking for these unwanted values, remove them and update the data.
I don't want to use a DataSet as I need to iterate through 10s of millions of rows. I can easily visit each row using OleDbDataReader and then iterate through the columns to test for the values but how can I then update the columns where the data has changed?
|
|
|
|
|
If you just want to remove or replace tab characters then I would advise something like:
update MyTable set MyColumn = Replace(MyColumn, Char(9), ' ')
where MyColumn like '%' + Char(9) + '%' That way you won't have millions of rows coursing across your network.
Andy
|
|
|
|
|
It's not just tabs though, it can be any number of different undesirable characters and I often don't want to do a straight replace. Dragging the rows through the nextwork isn't much of an issue as I intend to run it overnight when the server is quiet.
|
|
|
|
|
RugbyLeague wrote: Dragging the rows through the nextwork isn't much of an issue as I intend to run it overnight when the server is quiet.
You are still best to do all of it in SQL if you can. If nothing else it means that any future batch processes you need to perform overnight will have time to operate.
|
|
|
|
|
I realise it is best to do it SQL but in this instance (and others) I would like to do it in C# fo reasons which aren't relevant to the question.
|
|
|
|
|
RugbyLeague wrote: I realise it is best to do it SQL but in this instance (and others) I would like to do it in C# fo reasons which aren't relevant to the question.
Then just issue an update command back to the database. You can write pretty much any SQL that the target database will understand in the various flavours of command object.
e.g.
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "UPDATE MyTable "+
"SET SomeColumn = @SomeValue "+
"WHERE PrimaryKey = @id";
cmd.Parameters.Add("@SomeValue", updatedValue);
cmd.Parameters.Add("@id", reader.GetInt32(0));
cmd.ExecuteNonQuery();
|
|
|
|
|
Thanks, that is pretty much what I am doing. I was just wondering if there is a less verbose way of doing it - I guess sometimes one just has to do some typing 
|
|
|
|
|
If it is something that you'll repeatedly use from various different bits of code, you could create a method for it and pass the id and the updated value. Then the method does the work and the various bits of code that need it only have to call the one method.
|
|
|
|
|
Hopefully it's not something I will be doing a lot (if ever again) - as has been said, its often best to do such work in SQL.
This sort of processing I normally do on the raw text files we receive but on this occasion the data was loaded into SQL Server before the anomalies were noticed.
|
|
|
|
|
after you read a record, then simply check for your required condition and accodrding ly give your update query. Everthing comes in while(dr.read)....
if(...).....update....
Gautham
|
|
|
|
|
|
Hi
I m tryring to configure report server the server is not initializing anyone can provide help regarding to configure report server or what is the appropriate forum for this.
Thanks
Shahzad Aslam
|
|
|
|
|
Hello C# coder,
Lets straight to the point;
I'm trying to get the schema of a table in Database (SQL Server, Access) using GetSchemaTable() method.
With same table structure (both in SQL, Access), I got different result of schema. For example, Field that has attribute Required=True in my table, the schema of SQL DB showed AllowDBNULL = FALSE but in Access showed AllowDBNULL = TRUE.
These are the snippets of my code
<br />
OleDbConnection conn1 = new OleDbConnection("Provider=SQLNCLI.1;Datasource=DEV06\\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=JatiDB");<br />
<br />
conn1.Open();<br />
OleDbCommand command = new OleDbCommand("select * from parent",conn1);<br />
OleDbDataReader reader = command.ExecuteReader();<br />
DataTable schema = reader.GetSchemaTable();<br />
conn1.Close();<br />
In my program, I've changed the value of ConnectionString for accessing Ms. Access, and works.
So my question is;
1. Does GetSchemaTable() results different output for each database?
2. If so, is there another alternative to get the schema with same output?
Thanks, any suggestion is greatly appreciated.
TIA.
Jati Indrayanto.
Everything is possible.
|
|
|
|
|
There are several methods on the oledbconnection class (including GetOLEDBSchemaTable) that may be useful.
|
|
|
|
|
Is there any form generator or template to create search forms? I just want to display data from my sql server 2005 tables using drop down menu or search field options.
I'll greatly appreciate any help.
|
|
|
|
|
What is the SQL commands(script) to get a table size?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
