slow application

[nnmmss]

I have programmed an application which every thing on the my pc . i mean the application and also sql server . after testing i should use it on the network, but i think it is just a little slow. now i want to know is there any software or policy which i can get where on the program cause slowness?
or is there a site which shows me some tricks to making fast an application

thanks

 

[macleod1021]

There's a lot of ways to increase the efficiency of your code. Without seeing exact details, it's hard to say what will work. If you're running SQL and your app on the same machine you're probably experiencing a slow down of your own causing. Install your app and database on different machines and see if that helps. Typically you'll get faster responses that way, unless you have a high-end machine with enough resources to handle both.

Some things to watch out for in your code is pulling more information than you really need in your queries. If you only need a handful of records, then don't query the whole table and loop through. Add a "where" clause to it so that the server can shorten the list for you.

Once you have the systems seperated (or you feel that your computer can handle it), do several test runs and find out where you think it should be faster. Then, post that code here and we can give you some ideas to make it faster(if possible).

 

[chiph]

Solving performance problems involves a loop like this:
1. Find the slowest-performing part of app, using timing instrumentation or a profiler
2. Fix that part of the code.
3. Repeat until it runs fast enough.

The tricky part is step #1, and if you don't have a profiler available, it usually involves putting a lot of logging statements in the code to write to a file how long it takes to perform certain actions.

At a previous job, we had some database inserts that were taking 30 seconds. Following this procedure we were able to get it under 2 seconds.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[gmmastros]

I ditto chip's remarks. Just last week i took a query from 20 minutes down to 3 seconds. All I needed to do was to add indexes to a couple tables.

You must first find where your app is taking the most amount of time.

As macleod states, ONLY return data from the database that you are using. Not just with WHERE clauses, but also columns of data. If you have ANY 'select * from...' then you need to change it to ONLY return the columns you need.

All of your database interaction should occur through stored procedures, using the ADO Command object.

Your database should be properly indexed. Many books have been written on the topic of indexing. I suggest you start by making sure fields all primary keys are indexed (they are already with SQL Server), and all Foreign Keys should indexed. If performance still isn't good enough, then try indexing fields that appear in where clauses.

Try minimizing round trips to the database. By using stored procedures, you can almost always reduce the round trips to 1. Sometimes you need to get creative, but this should be your goal. If you have any loops in your VB code that call the database within the loop, this should be your first priority.

On the VB side, make sure you are NOT doing a lot of string manipulations. If you are, and need to do string manipulations, consider using arrays to store smaller strings. Then manipulate those.

If you are returning large recordset from the database, and you've already eliminated unnecessary columns and rows, then consider using ADODB.Field object to access the data. In some cases, this can speed things up by 40%.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[nnmmss]

ok i'l ask you my question. i am tracing the my app and i'll send you each part that has a slowness. the first part is that i have a list of projects which are shown in a treeview. when i am getting the list i use to keep the project information in 3 collection , i use these information later.after this is done.i call a procedute to check if the project had specified progress to get money from client or not. this is like this

CreateNodes is procedure which Get the list of projects and its specification
i have seperated all the section should do something on database on different project which would be a dll file at last named "PrjPRF.clsPRF"
Public Sub CreateNodes()
Set objPRF = CreateObject("PrjPRF.clsPRF")
Set rst = objPRF.GetAllRecPrj("TblProject", "PrjFinish,PrjName")
While Not rst.EOF
st = "N_" & rst("PrjId")
Set nodex = tvwMDI.Nodes.Add(, , st, rst("PrjName"))
ProjectPath.Add Item:=rst("PathName"), Key:=st
PrjFinish.Add Item:=rst("PrjFinish"), Key:=st
PInterval.Add Item:=rst("PrjInterval"), Key:=st
rst.MoveNext
Wend
End Sub


afte that secion i should get the existing progress of projects from their excel file( which could be updated at any time)

Private Function CalcPercent(ByVal PrjPath As String)
Dim oXL As Object
Dim oBook As Object ' Excel workbook
Dim WS1 As Object
Dim varstr As String
Set oXL = CreateObject("Excel.application")
Set oBook = oXL.Workbooks.Open(PrjPath)
Set WS1 = oXL.Worksheets(1)
CalcPercent = WS1.Cells(1, 1)
hWndXl = FindWindow("XLMAIN", oXL.Caption)
Set WS1 = Nothing
Set oBook = Nothing
Set oXL = Nothing

sKillProcess 'Kill the excel file
End Function

by getting these values i will understand what is the situation of project is, this is a part that i know is just a little slow.

which part of this coding have a problem?

thanks

 

[chiph]

Insert logging statements at the start and end of both methods. You can then determine which of the two are slower.

Some suggestions:
1. When adding a large number of items to a treeview or list control, call the Win32 API LockWindowUpdate() method on it to disable painting until it's completely loaded.
2. Opening Excel in inherently a slow process.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first