|
Hello,
I have got to do a lot of request with a database.
I would like to know if it is more efficient to use a permanent connection or to create a new connection each time i have to got to make a request ?
Will i lose a lot of performance by using the second system
Thanks
|
|
|
|
|
It depends on several things, like number of users, are you using connection pooling etc etc. It used to be standard practice to keep connections open, but mostly these days (assuming connection pooling is being used) then connections are opened and closed as required. The best bet is to run a eries of benchmarks as its easy enough to implement both methods.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Depending on the DBMS you are using, the number of concurrent connections may be limited.
|
|
|
|
|
Best practice depends on how often your going to the database to retrieve stuff and the number of users you have.
Connections to SQL Servers are expensive, money wise that is, so hanging onto a connection for the life of your apps runtime can be VERY expensive. If you did this, say you have 10 users who run your app. If all 10 run at the same time, you need 10 connection licenses on your SQL Server, multiplied by the cost of your connection licenses.
Now, if you use best practice, and only open a connection for as long as you need to retrieve a block of data or do an update, then you can get away with using fewer connection licenses. An application spends 95%+ of its life idle, not doing anything with the database. So, that same 10 users running your app could share, say 1 to 3 connection licenses. It just become much cheaper to run your application.
What you do depends on your applications requirements. You need to find a happy balance between these two and the performance benefits you gain, from your own testing of course.
|
|
|
|
|
Use a Data Access Layer (DAL) to manage your connection to the DB and service the data requirements for your app.
Use pooling in your connection
Create a standard sql credential for your app (userid/password) and not integrated.
We close a connection after each operation, this puts the connection into the pool for approx 8 minutes before killing the connection.
Use data adaptor.fill instead of data reader.
The above are recommendations only, we use them in our corporate apps.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Use data adaptor.fill instead of data reader.
Why?
Do you realise that the DataAdapter.Fill method uses a DataReader, so you can't really use the DataAdapter.Fill method without using a DataReader?
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
Guffa wrote: Do you realise that the DataAdapter.Fill method uses a DataReader
Sure but a datareader locks the connection while the reader is active ie if you process on the reader say to fill a combo box then the connection is locked during the processing. Data adaptor releases the connection as soon as you complete the read, any subsequent processing is on a disconnected data adapter. Adaptor costs more but I never have a locked connection.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Sure but a datareader locks the connection while the reader is active ie if you process on the reader say to fill a combo box then the connection is locked during the processing.
Of course you should read the data from the DataReader into some data structure, and then work on the data once you closed the connection. The data structure doesn't have to be a bloated DataTable object, though.
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
I was trying to have a newbie avoid the trap of locking a connection with a reader, it certainly happenned to me and it was a painful experience chasing down the problem in MSDN. It was long before I had discovered Google.
And that is why I put the recommendation comment on the reply.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
What are you telling me this?? Tell the OP!
I know all this. I just addressed the very narrow question he asked. "Is it better to open a connection for the life of an app?"
|
|
|
|
|
Sorry - replied to the wrong message, hopefully the OP will peruse all the messages. 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Dear All,
I use VB 2005 and i want to make a Excel report like that.
[]A B C ....
1 1 2 3 .... 28 or 29 or 30 or 31
2 8 4 5 ..... 4 ...
3 5 3 5 ..... 2 ...
. . .
. . .
N 4 7 9 ..... 5 ...
Total: T T T ..... T ... ( T is total from Person1 to PersonN of each day, N is number)
As excel formula "=Sum(A1:AN)"
So i have to write all of each day
but i want to use look for replace it, how can i do ?
Can we do any else beside "=Sum(A1:AN)" like "=Sum((1,1):(1,N)) ?
Example:
For i=1 to X '(X is number of month)
wkSheet.Cells(i, N+1) = "=Sum(" & (i, N + 1) & ":" & N &")"
Next '(this loop not work) because invalid formula'
I think that maybe i descript not clear but hope you see what i mean.
Hope it's able to solve.
Best Regards
VB.Net
|
|
|
|
|
That forumlas doesn't work in Excel, so it's not going to work from your code either.
All you do is put the "Sum(A1:An)" formula in the first row, then "Sum(B1:Bn) in the next and so on.
|
|
|
|
|
Thanks Dave,
I would like to tell you again about "=Sum(A1:An)"
Yes "A1" is first row, then the An is the under row of the first row which we do not respect what its number.
But i want to write code to do that with control "For Loop" and need some else to replace "A1:An", "B1:Bn",... in code.
Example:
For i =1 to An
wkSheet.Cells(1, i) = "=Sum(A1:A"& i &")"
Next
But i want to need some else replace "=Sum(A1:A"& i & ")" to for example: "=Sum("& number &":" & number &")"
Is it possible or not ? Can we replace it ?
Thanks you again for your help...
Best Regards
Sovann
VB.Net
|
|
|
|
|
That doesn't make any sense at all.
To build a range like this for Excel, is really just simple string manipulation. What number you put in the n placeholder is up to you. Your explanation is impossible to follow. But, no matter what you're trying to say, yes you can build forumlas and put them in the required cells. I just can't figure out what forumlas your trying to build.
|
|
|
|
|
Ok thanks you so much, Dave.
I try to find more solution which easy than that.
Thanks again.
Wish you good luck, success in work and life.
Best Regards,
Sovann
VB.Net
|
|
|
|
|
i am declaring a class with a function.i will not create any instance for that function in my main form.i need to create the instance only at the runtime..can i do that?
Any suggestions???
Regards...
Selva Shankar
|
|
|
|
|
I am not sure I understand what you are trying to achieve, but if I am guessing correctly, you need to look into late-binding.
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
At a guess try declaring the function as "shared".
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i have found a method...i created a dll...i declared it in runtime using reflection.
Thanks for your informations...Keep posting new ideas...
Regards...
Selva Shankar
|
|
|
|
|
Finally.,i have done this with reflection...Thanks for all your responses...
Regards...
Selva Shankar
|
|
|
|
|
Hi all,
since I found only C#/C++ stuff on my problem, I have to ask you guys for better ways of populating a TreeView control in VB with only a part (!) of the directory structure.
General purpose: I need to simulate a folder explorer which
a) ignores the files in the folder (large number would make it very slow)
That's also the reason why I can't just use FolderBrowserDialog object.
b) only populates subfolders once a parent folder it is expanded (same reason - performance)
By now I have the following solution (which basically works):
Private Sub TreeView_BeforeExpand(ByVal sender As Object, ByVal e As System.Windows.Forms.TreeViewCancelEventArgs) Handles TreeView.BeforeExpand
'avoid errors when access denied, i.e. system folders
On Error Resume Next
For Each ChildNode As TreeNode In TreeView.SelectedNode.Nodes
'For Each ChildNode As TreeNode In TreeView.SelectedNode.Nodes
' Mehrfaches Füllen vermeiden!
If ChildNode.Nodes.Count > 0 Then Exit Sub
Dim di As DirectoryInfo = New DirectoryInfo(ChildNode.FullPath)
Dim subDirs() As DirectoryInfo = di.GetDirectories("*", SearchOption.TopDirectoryOnly)
For i = 1 To subDirs.Length
Dim actSub As New TreeNode(subDirs(i).ToString)
ChildNode.Nodes.Add(actSub)
actSub.ImageIndex = Ico_FldClosed
Next
Next
End Sub
Problem: It refers to "TreeView.SelectedNode.Nodes", so that the subfolders aren't filled with their subfolders (i.e. grandchildren) in case the user expands the tree by clicking on the "+" symbol without selecting the node first.
Which other property or method could I use? Maybe someone even has a much better solution... I'd really appreciate every good advice!
Thanks
Michael
|
|
|
|
|
Your "Problem" description isn't very clear. But, what you normally do in this situation is add each subfolder and populate it with a dummy blank node so the folder gets a "plus" next to it. When the user clicks the plus, your code has to check if the dummy node is there, and if it is, delete it, then populate the folder appropriately.
|
|
|
|
|
Thanks for the idea, Dave. Let me try to make it clearer:
- On the first level I only show the drives (e.g. "C:\") in the tree view, populated with nodes for existing subdirectories (e.g. "C:\Test", "C:\Real" etc.) in the "form_Load" event. This way, the nodes only have a "plus" once there are subdirectories existing! A drive without subdirectories (e.g. empty removable) doesn't have a "plus".
- Once the user selects and expands a node (e.g. "+ C:\"), I use the the "TreeView_BeforeExpand" event to populate the child nodes with the next level of subdirectories (grand children --> e.g. "C:\Test\TestDir1", "C:\Test\TestDir2" etc.). Again: If "C:\Real" doesn't have any subdirectories, it's not populated with "grandchild" nodes and therefore doesn't have a "plus" when the "C:\" node expands, while the other node ("+ C:\Test") has a "plus".
Other words: The event or the parent node populates all the child nodes with probable grandchild nodes before the child nodes are shown (without "plus" if they don't have subdirectories).
- For performance reasons I ONLY populate the children of the node that the user wants to expand. Not knowing it any better, I use the "SelectedNode" property in my code to find out which node to populate.
Up to here it works fine - and I think even better than with dummies, because adding dummies I have the "plus" even for folders without subfolders... don't I?
But here's my real problem:In case the user wants to expand the node by clicking on the "plus" ---> without explicitly selecting (or double-clicking) the drive letter (!) --- then my whole code doesn't populate the subfolders because my "for ... next" loop says "..each ChildNode ... in Treeview.SelectedNode".
Obviously clicking the "plus" doesn't select the relevant node! Which event is raised when the user clicks "plus" in the TreeView control?
It's a bit hard for me to explain... hope you know what I mean... otherwise I'd try again 
modified on Thursday, October 16, 2008 5:46 PM
|
|
|
|
|
Michael Schäuble wrote: Up to here it works fine - and I think even better than with dummies, because adding dummies I have the "plus" even for folders without subfolders... don't I?
Yep. The subfolder isn't populated until the user clicks on the plus. So, if there are an excessive number of sub-subfolders, you're not wasting time populating folders that the user isn't intersted in.
Michael Schäuble wrote: my "for ... next" loop says "..each ChildNode ... in Treeview.SelectedNode".
The event you're looking for is BeforeExpand. With it, you get the node that's expanding. You don't need SelectedNode at all.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
