Hi.
We have developed as quite simple ASP.Net webpage that fetches a number of information from a SQL 2005 database. We are having some problems though, becuase of a firewall that is beetween the webserver and the SQL server, and I think this is because of bad code from my part. I'm not that experiensed yet, so I'm sure that there is much to learn.
Usualy when I do a query against a SQL database, I do something like this:
Function GO_FormatRecordBy(ByVal intRecordByAs Integer)
Dim dbQueryStringAs String
Dim dbCommandAs OleDbCommand
Dim dbQueryResultAs OleDbDataReader
dbQueryString ="SELECT Name FROM tblRegistrators WHERE tblRegistratorsID ='" & intRecordBy & "'"
dbCommand = New OleDbCommand(dbQueryString, dbConn)
dbConn.Open()
dbQueryResult = dbCommand.ExecuteReader(CommandBehavior.CloseConnection)
dbQueryResult.Read()
dbConn.Close()
dbCommand = Nothing
Return dbQueryResult("Name")
End Function
Now, lets say that I have a DataList that I populate with Integer values, and I want to "resolve" the from another table, then i do a function like the one above. I guess that this means that I open and close quite alot of connections against the database server when I have a large tabel. Is there any better way of doing this? Chould one open a database connection globaly in lets say the ASA fil? Whould that be a better aproch?
When I added the CommandBehavior.CloseConnection to the ExecuteReader statment, I noticed that it was a bit faster, and I think there was fewer connections in the database, so maby there is more to the "closing connections" then I usualy do.
Any tips on this?
Best reagrds,
Johan Christensson
The thing with DataReaders is that you have to explicitly close them when you have finished with them. The CommandBehaviour.CloseConnection option makes sure that the connection is closed when you close the DataReader.
Since you are only retrieving one value in the example you have shown, you should use ExecuteScalar(), which doesn't need a DataReader. That would be the most efficient way of accomplishing this particular task.
Also, in answer to your question about global connections, forget you ever thought about it. That is a very poor idea. It forces everyone who accesses your app to use the same connection, and as traffic increases, delays occur as requests are queued.
I don't quite understand what you mean when you talk about resolving records from another table, but my instinct is that nested datalists or join queries might be a better way to go.
|||Thanks for your awnser.
What I mean with "resolving records fro another tabel" is that lets say that I have one table that consists, in this case of a SiteID and a TechnicanID, when the DataList is generated, I invoke a function where the output in the DataList is looked up in another table and resolved as a name. I guess that this is quite stupid, since I guess that it would generate an extra query, in this case two querys for each row in the datalist. Right now I have a datalist/asp page that generated about 400 connections to the database, and because of this, the firewall blocks the webserver.
I guess that this would better be done using some kind of SQL statement, but I'm not sure on how. Lets say that I have the following SQL string:
dbQueryString ="SELECT SiteID, TechnicanID FROM tblRegistrators WHERE tblRegistratorsID ='" & intRecordBy & "'"
And I want the values from SiteID and TechnicanID to be the result from a query in another table, how do I do this?
Best reagrds,
Johan Christensson
I think this will work for you:
dbQueryString = "Select tblSites.SiteName, tblTechnicians.TechnicianName from tblRegistrators inner join tblSites on tblRegistrators.SiteID = tblSites.SiteID inner join tblTechnicians on tblRegistrators.TechnicianID = tblTechnicians.TechnicianID where tblRegistrators.ID = '"&intRecordBy&"'"
you may want to look up some information on Joins (Inner and Outer primarily) they make life much simpler, and more complicated at the same time. Trying to sort out queries with upwards of 20 joins is one of the reasons I have so much grey hair
Hope this helps
-madrak
|||Madrak has given you a solution using Joins in your SQL as I suggested earlier, but another approach could be to select all from the SiteID and TechnicianID tables into separate DataSets on one connection. Once the dataset has been populated, you can close the connection and work with it in a disconnected fashion, looping though it to your heart's content.
You may well have trouble getting to grips with Joins to start with, so I suggest that you make use of the Query Designer in SQL Management Studio (Express) to write the query for you. Simply add the 3 tables to the designer, then drag foreign keys onto primary keys to create relationships eg SiteID in tblRegistrators would be a foreign key and should be dragged to SiteID (primary key) in what I presume might be called tblSites. As soon as you do that, you will see that the textual part of the query builds the default inner join into itself.
But in essence, your instinct is correct. Hammering a database repeatedly in a loop is poor practice - especially if you aren't closing DataReaders correctly
After some googeling I figured out that I should do some kind of nested statment, but the anwser from Madrak actualy was in a format that I understod it. :) Not only does it work, is fast as lightning. It took som trial and error in teh Query Designer before I got hold of it. The way I have done this previusly, always was very slow, so I have some tweeking to do i guess on some other webpages.
So thanks a milion.
A followup question on Mikesdotnetting awnser:
What is best here? To do the complete table using nested SQL statements or do it the DataSet way? I guess that would depend on the load on the webservers verses the SQL server, but is we ignore that right now, and say that you have a some application that you run on you computer. What way would you go then?
Thanks again.
Best regards,
Johan Christensson
JohanCh:
A followup question on Mikesdotnetting awnser:
What is best here? To do the complete table using nested SQL statements or do it the DataSet way? I guess that would depend on the load on the webservers verses the SQL server, but is we ignore that right now, and say that you have a some application that you run on you computer. What way would you go then?
Oh, without a doubt I would use Joins in my SQL. I'm a firm believer in making as few requests of the database as possible. The Join approach requires just one request. DataSets require 3. You might think 3 is nothing compared to the 35,000 you were making (
