SQL Server Performance Forum – Threads Archive

Pop slow in VB Application

Hi all! I wanted to know from you guys where am i going wrong. I have a VB application which assess SQL2 DB in an LAN scenario. As the number of users exceeds say around 100, i face a problem like my VB form do pop up after a certain amount of time ( 20 sec ) My database consists of Customer table with record count of around 1,20,000 and in my VB application i’m using recordset to open "select * from cutomer where code = " .
Code column is having an primary index.
Using recordset i’m displaying the information & at the end i’m updating the customer again. As the customer table increased i have started facing the problems. U’r suggestions would be appreciated. Thanks & Regards,
Manoj. Thanks for your suggestions. I would more elaborative this time, sorry for not being earlier. Well, I open my database connection at the start of my application & keep it open as long as application is running. I just open temporary ADODB recordset and closing it just after its use. Though when i was opening my recordset on above query, i just found that i was not closing the recordset. I hope that would reduce the problem & also i would change my query from "select * " to select selected records as required. Also as i said my code column is CLUSTERED PRIMARY INDEX in the customer table. So will above changes solve the issues????? Thanks Again from Manoj.

First tip to reduce network bandwidth is to only select the fields you need, instead of using select *. This is a good habit to get into anyway, since it means later schema changes to the table have less risk of breaking existing client app code. How selective is your ‘code’ field ? Ie, how many records would a typical code query expect to recieve back? You say you have a primary index on code, not sure whether you mean a primary key. If you have no other indices on the table consider ensuring the index on code is a clustered index. Can you replicate the performance problems by doing the same query in query analyser? This would ensure that the problem does relate to your database design and not a bottleneck in the connectivity technology youre using. (Im assuming youre using ADO?) If you can replicate the problem in query analyser, examine the execution plan of the query. Many articles on this site guide you through how to interpret execution plans and to help decide your steps after this. My other thought is that it could be due to concurrency issues, such as record locking. Monitor what sql locks are in use in the system, using profiler and perfmon. The problem may not be due to this query (which appears quite simple), but could be manifesting itself due to problems in other areas of the application accessing the DB.

Also you say you are updating the records after selecting them into a recordset. How do you issue youre update ? My recommendation would be to issue a seperate update ADOquery statement, rather than rely on any databinding controls which ADO might offer. Also, you might like to consider switching to stored procedures rather than embedding sql into your client app. Since youre using sql 2000 this is more of a maintenance benefit, rather than a performance one, since sql2000 now caches execution plans for adhoc paremeterised queries, unlike sql 7 which could sometimes struggle to reuse these sort of plans.

Tadd to what Chappy said.
How are you openning the connection? are you using ODBC or OLEDB? How many records are returned by your select statement?
Are you making sure you are closing the recordset object and the connection object as soon as possible?
What kind of index do you have on field code? If for example it is a clustered index and is updatable, you might consider changing it to a non-clustered.
Are you updating statistics and buiding indexes (DBCC dbreindex(‘table_name’)
Consider (if cannot be updated by more than one user at the same time) using the NOLOCK hint in the SELECT statment (select fields from table_name WITH (NOLOCK))
When updating consider using a ROWLOCK or a PAGELOCK. Bambola.

In addition to what has been said above you can make your application seem more responsive to users by first loading your form and throwing up a loading msg, prehaps in a label, then retrieve the records, it doesn’t make anything faster but from the users perspective at least the application is responding and they’re informed that something is in progress. Another thing you may want to look at is not holding the connection for the duration of the application but only connecting when you are actually going to work with the database, this is more for scalability but can help performance in the long run as your application recieves more users. Cheers
Shaun World Domination Through Superior Software

]]>