SQL Server Performance Forum – Threads Archive

sequentially selecting records from keywords

I currently have a series of tables in a database, which contains a series of keywords and related information. I would like to be able to sequentially select keywords from the table, equally distributing the retrival of them amongst the entire list. I currently am doing this by putting a counter field in the table; which is updated and incremented by one each time that keyword is pulled. I’m afriad that the contact update command is creating excessive locks on the tables, and upon heavy loads causing the database server to bog down and become unavailable. Is there a better way to acomplish this? The method call that does this is: public void updateHitsSQL(int keyID, string strKeydb, int iHits)
{
int rowsAffected = 0;
SqlConnection myConnection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["keywords"].ToString());
SqlTransaction transaction;
iHits = iHits + 1;
string strSql = "UPDATE " + strKeydb + " SET hits=" + iHits + " WHERE keywordID = " + keyID + ";";
myConnection.Open();
transaction = myConnection.BeginTransaction();
new SqlCommand(strSql, myConnection, transaction).ExecuteNonQuery();
transaction.Commit();
myConnection.Close();
myConnection = null;
}
Here is the code where I query the table: public keywords getKeySQL(string strKeydb)
{ keywords responseObj = new keywords(); SqlConnection myConnection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["keywords"].ToString());
string strSql = "select top 1 * from dbo." + strKeydb + " order by hits";
SqlCommand myCommand = new SqlCommand(strSql, myConnection);
SqlDataReader myReader = null;
myConnection.Open();
myReader = myCommand.ExecuteReader();
myReader.Read(); responseObj.terms = myReader.GetString(1);
responseObj.keyID = myReader.GetInt32(0);
responseObj.keyHits = myReader.GetInt32(2);
responseObj.feed = myReader.GetString(4);
//Check to see if photoKey is a number or file name
if (myReader.GetString(3).ToString().Length < 2)
responseObj.photoKey = Convert.ToInt32(myReader.GetString(3).ToString());
else
responseObj.photoFile = myReader.GetString(3).ToString(); myReader.Close();
myConnection.Close(); myReader = null;
myConnection = null; updateHitsSQL(responseObj.keyID, strKeydb, responseObj.keyHits); return responseObj; }

---

Have you tested the execution plan for your SQL queries in this case, as I can see the queries are originated from applicaiton using above code and I do not have much experience in fine tuning the above code. First thing to check is indexes for the relevant tables in the order of preference they are called. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

]]>