SQL Response slow

[nads260]

Please excuse my english, I'm a french person....
I got Intranet with a lot of reports with crystal asking stored proc on my SQL server. I 've got problems with the response when asking the repports (too much slow). How can I look if it's my Repports the problem or if it's the application base on the data base.

 

[SQLSister]

YOu could test the stored procedures by running them in Query Analyzer. If they are still slow, the stored procedure needs to be fixed. Otherwise the problem could be in the connection to the database or the reports. Some things to think about in terms of increasing speed.

Good indexes on the tables/views can speed up a query in a table that has a lot of records.

Avoid cursors, they are notoriously slow. Usually they can be replaced by set oriented SQL statements once the programmer gets over thinking in procedural terms.

SQL statements that are called from the usesr interface without using a stored porocedure are slower.

Don't ask to return more results than you need. This slows up the system by clogging it with more data packets than necessary. It doesn't seem like much when it is one user, but, you get into a big multiuser enviroment and these things add up.

Check to see how busy the network is, sometimes other network traffic is what is slowing you down. This is always most noticible in a database environment because databses make frequent calls through the network to the data, where as other applications like word processors are only slow when they save. I once worked in a place with a network designed for 200 people that actually had 400 people. They kept complaining the database was slow, but itwas really the network.

 

[nads260]

Thank you for your answer
I got problem now with all the applications on the network. Can you help me finding how to prove it !!

 

[SQLSister]

Just the fact that it is all applications tends to prove it is a network problem. I would suggest you have your network admin look at his logs and chaeck the performance of the network using tools like the Performance monitor that windows 2000 has to see if he sees a problem. Also, you can run some of the stored procedures directly on the server and then from a network connection (or the user interface on a network machine)and compare the execution times.

If this is a sudden change from past performance, I've found the most effective way to isolate the proble is to find out what changes have recently been made to the network or program. Problem almost always disappears if the change is removed, so the new thing is often causing the problem even when it apprears to be somewhere else from the symptoms