Dear Sir.,

Can anyone help me to give sample code to make fast queries to database specifically MS SQL dbase. There are
cases when there are many users trying to insert or update data in the database the tendency of the my vb application becomes slow. Is there any code for inserting or updating data in the database which would make it fast for the application to respond?

That all depends upon how you are accessing the database and upon your code. If you are doing something like...

Rs.AddNew
Rs.Fields("SomeField") = SomeValue
Rs.Update

or

Rs.Edit
Rs.Fields("SomeField") = SomeValue
Rs.Update

these are slower than the sql counterparts...

strSQL = "INSERT INTO sometable(somefield) VALUES(somevalue)"

strSQL = "UPDATE sometable SET somefield = somevalue WHERE someuniquefield = someuniquevalue"

Also, when querying information from the server if you do not need everything from the table, then don't use the * use the field names and if you can limit it even further by using the where clause do so where appropriate. Say you have a lookup table with the fields of ID and value that you use to populate a combobox with (for user selection)

Doing this...

strSQL = "SELECT * FROM sometable"

is slower than this (because of extra data being the ID field)

strSQL = "SELECT fieldname FROM sometable"

However, if you need that ID for a foreign key relationship then go ahead and use the * but I hope you get what I mean from larger tables where you might only display a portion of the fields of the table.

But as things go you can even go a step further since you are using SQL Server. You can speed things up even further by using stored proceedures for everything.

So, in the end the advice is, when querying, only pull the data you need and if you can use a stored proceedure to do it with then so much the better.
When updating, update only those fields in that singular record that is identified by its ID field. (If you need to do batch updates, update an entire table, or a large portion of it, try to schedule these during employee down times (Lunch, after work, etc)) and if you can use a stored proceedure for it...
Same can be said for inserting records also.

Then there are some other tricks to optimize the server you should know about. If you constantly query against specific fields you should put indexes upon those fields both singularly and in combination when you query against more than one field. (Where field1 = somevalue and field2 = someothervalue).




Good Luck

Thank you very much sir. A great Advice!