|
I have a function that uses the SqlDataReader to get data from a SQL Server 2000 database. The function works pretty fine until the point where the SqlDataReader will be closed. This can take a minute or more (see a piece of the log file).
2006-03-13 08:43:06 ; GetJobByID: Beginning of function
2006-03-13 08:43:06 ; GetJobByID: Start opening DB connection
2006-03-13 08:43:06 ; GetJobByID: Finished opening DB connection
2006-03-13 08:43:06 ; GetJobByID: Start SQL execution
2006-03-13 08:43:06 ; GetJobByID: Finished SQL execution
2006-03-13 08:43:06 ; GetJobByID: Finished creation of job object
2006-03-13 08:43:06 ; GetJobByID: Close reader
2006-03-13 08:44:16 ; GetJobByID: Close connection
2006-03-13 08:44:16 ; GetJobByID: End of function
As you can see the "Close Reader" command takes 1 minute and 10 seconds and I have no clue why. Additional info: During the function the process "System" runs with about 70 percent.
Thanks in advance,
CADMIN
|
|
|
|
|
HI CADMIN,
Well i am not that of an expert but why dont you close the SqlConnection with out closing the SqlDatareader and the re-open the SqlConnection when ever needed.
i hope you can try this and see if its working, just a test.
Thanks
|
|
|
|
|
Thanks, but this does not speed it up. Now it takes a lot of time to close the database connection.
|
|
|
|
|
I suspect that the SqlDataReader has to read all the rows that you've selected before it can be closed - SQL Server is in what's termed 'firehose mode' when using a SqlDataReader, where it simply sends all the data selected to the socket and relies on the connection's flow control to get data. TCP 'graceful disconnect' requires that you read all the data buffered at the other end.
The solution is to only SELECT the rows you actually need, rather than aborting a loop early. I've never observed this problem.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thank you, Mike! Finally I found the problem in the SELECT statement. I had a huge table in the FROM statement which I did not really need for my query. After I removed it the query was as fast as usual.
Once again the user was the problem 
|
|
|
|
|
Hi There!
I have a FoxPro database that has a memo field which is filled with binary data and normal text. How can a person read this binary data?
|
|
|
|
|
How to execute a complex query in ado?when a finish a query words like "Select * From X.DB where conditon and condition..",how we explict the records?also adapt "m_pRecordset->putcollection("ID");
m_pRecordset->movenext();
?
I do not need all records but matching the conditon.
thanks.
Lee
|
|
|
|
|
Hi All!
My Dilema is as follows : I'm developing a WinForms application as front end (GUI) and an MS Access database as back end. In the middle there are a bounch of calsses (Middle Tier) that do data accessing (through the use of ADO.NET), input validations, Calculations (It's a tax calculating application) and so forth.
These classes expose data fields as properties (for instance, the "Firm" class exposes a "AnnualIncome" property which simply returns the "AnnualIncome" field of the current DataRow of the "tblFirms" Datatable).
What I want to do is bind this property to the "txtAnnualIncome" TextBox on the "frmFirm" form.
Suppose the property is declared as follows :
Public int Property AnnualIncome ()
get
{
return tblFirms.AnnualIncome;
}
The problem is that if the content of tblFirms.AnnualIncome is null an exception is raised. I can avoid the exception by using a try-catch block but then I get a 0 (zero) in the TextBox. I would prefer a blank TextBox for a null valued field.
I can also use an un-typed property that returns an object reference but I prefer not to (if there's an alternative).
Any sugestions anyone?
Thanks very much.
Walk the way. Top down.
nata2005.
|
|
|
|
|
nata2006 wrote: I would prefer a blank TextBox for a null valued field.
Hi nata will why dont you use the try-cach block an let the catch put set the textbox to blank
//sample
try
{
//...your code here
}
catch
{
//your code..plus
txtAnnualIncome.Text = "";
}
I hope this can solve it.
|
|
|
|
|
Thank you cbhkenshin for replying but it doesn't entirely solve my problem.
The thing is I want to create binding between the TextBox's txtAnnualIncome.Text property to the objFirm.AnnualIncome Property on the frmFirm_Load event. In this way, I can avoid initializing the TextBox to the saved value on the Form's startup and setting the value whenever it changes using txtAnnualIncome_Changed() event procedure.
The updating of the objFirm.AnnualIncome is done automaticly whenever the txtAnnualIncome.Text changes without a txtAnnualIncome_Changed() event procedure.
But then, of course, I can't use a try-catch block. I simply don't have where to put it.
I can give up binding all together but it's a very easy and powerfull mechanism and I'd like to use it if there's a work-around to solve the problem of the null valued data fields.
Hope I didn't mix things up too much.
nata2006.
Walk the way. Top down.
nata2005.
|
|
|
|
|
I want to add the names of the staffs, in the dropdownlist, who's login is not yet created.
Eg. consider 2 tables staff, and login
staffs: A,B,C,D,E
login: B,E
the logins are created for B and E so in the dropdownlist it must display A,C,D
i tried the following query but it is not working.
select sname from staff,login where staff.sname!=login.[name]
It leads to duplicated records.....
Please help!!
|
|
|
|
|
You're using some very old join syntax there. The following should work for you:
SELECT sname
FROM staff
LEFT JOIN login
ON staff.sname = login.[name]
WHERE [name] IS NULL How it works: the LEFT JOIN outputs all rows from the left-hand side joined to the right-hand side where the ON condition evaluates to true. If there is no match, it outputs the left-hand side row with NULLs in all the fields from the right-hand side. Because we want the rows which don't have a match, we select only the rows which have NULLs in the right-hand side.
If we wanted only the rows that did match, I'd use an INNER JOIN. That gives only rows where the ON condition evaluates to true.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
select staff.* , login.* from staff,login where staff.sname <> login.name
Proud to be Albanian
|
|
|
|
|
This will also work, if I understand your question correctly (i.e. you want to know what staff members do not have a corresponding record in the login table):
SELECT
s.sname
FROM
staff s
WHERE
s.sname NOT IN (SELECT name FROM login)
or this...
SELECT
s.sname
FROM
staff s
WHERE
NOT EXISTS (SELECT 1 FROM login l WHERE l.name = s.sname)
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
From what I understand a foreign key is a unique number which can be used to cross reference items in multiple tables.
How is the foreign key created? Is it created by SQL or does the programmer have to create it and enter it into the table? If this is the case then how can you be sure that the key is unique?
|
|
|
|
|
Let's start with the primary key. The primary key is any combination of values which uniquely identify a particular row in the table. To have the database engine enforce this, you can define a primary key constraint, defining the column or combination of columns which will contain the values which make up the key. The word 'constraint' is often missed off, making the term 'primary key' mean both the collection of columns and the actual values of the row.
If the key is made up from the values of multiple columns, that's often referred to as a compound key. Sometimes compound keys can be unwieldy, so to simplify queries and/or improve performance, a replacement identifier column is defined - often simply a number - which may be referred to as a surrogate key.
A foreign key is basically wherever the values of a primary key for one table appear in another table, hence foreign. By defining a foreign key constraint you can have the database engine enforce the requirement that a matching row (with matching primary key values) exists in the referenced table when inserting or updating data into the table with the constraint. A table can have multiple foreign key constraints. With some database engines, you can also have the database engine automatically delete or update related rows when a row in the referenced table is deleted or updated or, conversely, prevent rows being updated or deleted if those rows are referenced.
See the CREATE TABLE documentation for how to create these constraints if you're creating your tables in SQL. If you're using Enterprise Manager on SQL Server 2000, select 'Relationships' from the context menu in the Design Table window.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Enterprize Manager-->Right click-->Relationships-->
New-->Select Primaty key table from dropdown
-->Select Correspoding Primary key
Select Foreign key table-->Select Corresponding foreign key column
Data base engine it self checks for the foreign key relations
The key can be repeated Primary key is unique key for foeign key table
"I find that the harder I work, the more luck I seem to have."
|
|
|
|
|
Hi
I have a Table T(ID int, FName varchar(50))
What is difference between these commands:
SELECT ID FROM T ORDER BY FName
SELECT ID , FName FROM T ORDER BY FName
???
|
|
|
|
|
The first will return a table consisting of one column, ID's sorted by FName.
The second will return a table consisting of two columns, ID's and FName's, sorted by FName.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
What's faster to execute?
No self-coded ADO.NET transaction:
SqlCommand[] commands = ...;
foreach(SqlCommand command in commands)
{
command.ExecuteNonQuery();
}
One ADO.NET transaction for all of them:
SqlCommand[] commands = ...;
SqlTransaction xaction = connection.BeginTransaction();
foreach(SqlCommand command in commands)
{
command.ExecuteNonQuery();
}
xaction.Commit();
Is there any performance benefit to the second bit, where all the commands are executed within a single transaction? Or would this negatively hurt performance?
Tech, life, family, faith: Give me a visit.
I'm currently blogging about: Moral Muscle
The apostle Paul, modernly speaking: Epistles of Paul
Judah Himango
|
|
|
|
|
Judah Himango wrote: Is there any performance benefit to the second bit, where all the commands are executed within a single transaction?
Not more than you'd expect from the equivalent T/SQL-statements. Now, I'm no .NET wizard or anything, but anything that would deviate from the behaviour of T/SQL would be kind of stupid. Especially so since there are SQL-server tailored Sql* classes. Right? 
|
|
|
|
|
Jörgen Sigvardsson wrote: Not more than you'd expect from the equivalent T/SQL-statements.
That's different, isn't it? I've read that a single ADO.NET transaction results in a single handshake between the calling code and SQL, whereas the first code snippet would have many handshakes between the two. Is that accurate or was I reading a programming myth?
|
|
|
|
|
Judah Himango wrote: Is that accurate or was I reading a programming myth?
I'm not sure. I do know that if you deal with OLEDB (COM), you open a connection to the database once. Then you create commands which can operate upon that connection. Furthermore, once you connect to the SQL-server, you create a session, much like the Query Analyzer does for you. That means that each command with a side effect, such as BEGIN TRANSACTION, is visible for all successive commands.
I find the idea of automatic transactions a bit worrysome. Suppose you have to call a stored procedure, which arent transaction neutral. With automatic transactions, life would become hard(er). 
You can always test this by making a stored procedure return @@trancount, and retrieve that through ADO.NET. If it's not zero, and you know you haven't started any transactions manually, then ADO.NET is doing something behind your back...
|
|
|
|
|
By the way, "no manual transaction" - what do you mean by that? All single commands in SQL are atomic, and are transactions in themselves. Either an INSERT completes successfully, or it doesn't.
When dealing with database transactions, you have to specify an isolation level. Depending on what you set, you may get different penalty hits.
|
|
|
|
|
I meant I'm not coding for a transaction specifically. That said, stored procedures (which is what my SqlCommands are calling) that do more than just single actions are not 100% failure or 100% success; that has to be done on manually either by the sproc or the calling code (such as ADO.NET).
My question is really regarding ADO.NET-based trasactions: Is is better to start a transaction from ADO.NET, execute many sprocs, then commit the transaction? Or is it better to just execute a SqlCommand one at at time, letting SQL and ADO.NET handle everything else?
Tech, life, family, faith: Give me a visit.
I'm currently blogging about: Moral Muscle
The apostle Paul, modernly speaking: Epistles of Paul
Judah Himango
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
