|
I have some C# code that is handling some sql calls using stored procedures.
the way I am currently handling things I am opening and closing the connection for each call to the stored procedure, like this:
<br />
com.Connection.Open();<br />
com.CommandText = "exec sp_thing 0";<br />
com.ExecuteNonQuery();<br />
com.Connection.Close();<br />
<br />
com.Connection.Open();<br />
com.CommandText = "exec sp_thing 1";<br />
com.ExecuteNonQuery();<br />
com.Connection.Close();<br />
<br />
com.Connection.Open();<br />
com.CommandText = "exec sp_thing 2";<br />
com.ExecuteNonQuery();<br />
com.Connection.Close(); <br />
I am having to do this because each call to the procedure is handling close to 300,000 records at a time.
My question (for any ado.net masters out there) is if this is more efficient (memory usage-wise) than declaring a new connection every time, which would allow me to set a new timeout every time, but this seems to be a much cleaner use of code. Am I correct in thinking that i may already be using it the best way?
______________________
Mr Griffin, eleventy billion is not a number...
|
|
|
|
|
Do this:
private void MyMethod(string MyQuery)
{
com.Connection.Open();
com.CommandText = MyQuery;
com.ExecuteNonQuery();
com.Connection.Close();
}
Then call that method like this
MyMethod("exec sp_thing 0");
MyMethod("exec sp_thing 1");
MyMethod("exec sp_thing 2");
You could also process everything and then close the database and the very end using try-accepts.
|
|
|
|
|
I cant believe I spaced doing that.... Im usually a littl enutty about reusable code.
I have another question. I know that when data is put ina a dataset, it is basically being stored in memory as XML. This was the root off my original problems with this app because the shear volume of data, coupled with being used in a dataset, ate up all of the memory on the server and crashed the app.
I was wondering, If anyone happens to know, if DataTables use an xml layout to store that data, or perhaps something simpler (and less memory intensive)?
______________________
Mr Griffin, eleventy billion is not a number...
|
|
|
|
|
Vodstok wrote: I was wondering, If anyone happens to know, if DataTables use an xml layout to store that data, or perhaps something simpler (and less memory intensive)?
Data reader is the most efficient way to get data from a database. Datasets and datatables are relatively costly.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
Technically
com.Connection.Open();
com.CommandText = "exec sp_thing 1;exec sp_thing 2;exec sp_thing 3";
com.ExecuteNonQuery();
com.Connection.Close();
is the most efficient, but it only really makes a difference if the statements batched together are relatively fast. If the statements return a lot of data or are slow to execute, managing the connection isn't going to make a lot of difference.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
thanks for the responses. We (okay, one of the other programmers) discovered a REALLY quick way to upload what we need (although It was nice to get my questions answered, since that is something any programmer who uses sql should know)
He stumbled over the BULK INSERT statement. We devised a way for the users to uppload the MASSIVE csv files (almost 300,000 records, and over 20 columns) to upload the file to the webserver's filesystem, and for the sql server to then use bulk insert to upload the file to the db (which takes roughly 10 seconds, as opposed to the 8-10 minutes the old fashioned way worked)
If i get a chance to weed out any potentially compromising information, I will post how we accomplished this. it is really pretty cool. 
______________________
Mr Griffin, eleventy billion is not a number...
|
|
|
|
|
Hi
I have two store procedur for update data in two table.For example t1,t2.
t1 is realeted with t2 and t1 have id_n(pk),name columns,t2 have id_n(fk),tel(ok).But the problem is if i add data to t1 and bye some reason(exception) i don't add to t2.I will not have related name with number how to solv this problem?
1.If i make one store procedure from this two.
2.If exist pleas tell me.))
good bye
|
|
|
|
|
Yes, you can put multiple INSERT statements into a stored procedure. You can then put a BEGIN TRANSACTION before the first INSERT statement, after each INSERT check for an error, if there is an error then ROLLBACK TRANSACTION and exit the stored procedure. If there is no error then at the end of the stored procedure COMMIT TRANSACTION.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
how to check for errors???
|
|
|
|
|
At the start put:
DECLARE @currentError int After each insert put a
SELECT @currentError = @@ERROR;
IF @currentError <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
This would all be better in a stored procedure though.
Does this help?
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Super thank u!Best regards
|
|
|
|
|
hello
plz help me i want to add some new rows in database file this file has only one column which is numeric. i am implementing it in c# how i will add and how i will update my table.
like if i want to enter random numbers in this field upto 500 means i have to create 500 new rows in my database file how i will implemet it.
plz reply as soon as possible
-- modified at 9:46 Friday 24th February, 2006
|
|
|
|
|
hi look here is all answare of u question!))
ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.en/dv_raddata/html/7dc75b15-4d55-47a2-afe4-4e5fb0640c3a.htm
|
|
|
|
|
its not working plz tell me soonnnnnnnnnnn
|
|
|
|
|
Look at this.by this code u can make 500 new rows
For exp your table name is t1 and it is located in a dataset dataSet1
dataRow newRow;
For(int i=0;i<500;i++)
{
newRow=dataSet1.t1.newRow();
newRow[num]=x;
dataSet1.t1.Rows.Add(newRow);
}
Num is the name of your field in your db and x is a number.
m.t.
|
|
|
|
|
hi
How i can give data on this varable "@intA"
set @intA=select max(job_id) from maininfo is not work i need to take max values of this tabel how to do it???
good bye
|
|
|
|
|
|
ok but now i have bigger problem why is not work???
CREATE PROCEDURE sp_addTel
--@JI varchar(30),
@MB bigint,
@TG smallint,
@MF smallint,
@var1 int output
as
set @var1=(select max(job_id) from maininfo)
insert into TeleInfo(job_id,mobil_db,tele_firm,mobil_firm)
values(@var1,@mb,@tg,@mf)
GO
|
|
|
|
|
papa1980 wrote: but now i have bigger problem why is not work???
 What do you mean "not work"? That is such a vague statement.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
try this :
CREATE PROCEDURE sp_addTel
(
--@JI varchar(30),
@MB bigint,
@TG smallint,
@MF smallint,
@var1 int
)
as
select @var1= max(job_id) from maininfo
insert into TeleInfo(job_id,mobil_db,tele_firm,mobil_firm)
values(@var1,@mb,@tg,@mf)
GO
|
|
|
|
|
|
Hi
I have two db;
Name table
main_info TelInfo
///Column Table
job_id //pk job_id
fn tel pk
sn mob
pr fmob
How i can do if i exchang data in main_info automaticly to be exchanget in TelInfo specilay primary key on main_info
good bye
|
|
|
|
|
|
Hi Guys,
As of a few days ago I started to venture into the world of SQL. Subsequently I have a question on best practices relating to data population of a win form component.
For example, if you have a sql table which has a product column containing about 100 items. Now you want to pass these product items into a combo-box's drop down list ? What would be the best way of doing it ?
At the moment, I can set the data source property of the combo-box directly to this table containing the product items column. However, the product item's column changes data changes say couple of items per hour then what would be a good best pratice of keeping the combo box's drop down list updated ?
My idea would be to use data caching and apply a time expiry policy. Is my approach incorrect or too extreme for what I want to do. I would really appreciate if someone could give me guidance on this.
Thanks
Vee
|
|
|
|
|
Hi Vee. I think your expectation of production traffic would drive this question. In many of my apps for low to moderate traffic, I just use a live query at run-time to retrieve the dropDownList items without any caching. But if you found that performance suffered this way for your site, I don't think it's extreme at all to use caching - you could use the Application cache for this, or if you're using ASP.NET 2.0 you could use the new SqlCacheDependency class. Here's a link to an example[^].
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
