April 5, 2002 at 12:40 am
Dear all,
My Name is Mahesh DK, Working for Siemens,Bangalore Development Centre,India as Technical Support Engineer, if any one of you know about the how to Improve database performance, like using methodology such as optimisation of Row lenght,Creating the Indexes and primary keys, optimising Stored procedure.
kindly send your valuable articles and suggestions to my e-mail id:dk.mahesh@sisl.co.in
Mahesh DK
Mahesh DK
April 5, 2002 at 5:16 am
Sent in email:
quote:
There are many various factors that you must consider with a database to get an idea of what can help it. I suggest you followup thru the site with more information so more people can help you and they all have an idea of what has been suggested. Things like current setup, what performance problem you are having, db reads and writes (how often), size and special conditions. I will post this same message back to the sqlservercentral.com.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 5, 2002 at 11:12 am
April 5, 2002 at 12:02 pm
Here is the email he sent back to me if anyone wants to throw in, haven't said anything myself yet.
Dear James,
Thanks for your response,since i am a new user and i made a survy in many other sites but information was not sufficent.
We are developing software product for Health care sector, usually database sizes varies from various sites depending upon the patient flow usually it ranges from 2GB to 60GB,there are generally 100 to 150 concureent users using our applications across 30 to 40 sites,our database is built upon SQL 7.0 and applications on VB 6.0.
Now we are facing lot of performance related problem and we want information on following topics:
1.Performance tool, i heared about LOAD RUNNER, basically we need tool which should tell how many transactions are passing (how many writes and reads)on tables happens and how much each transaction takes time.
2.In order to improve performance of database, i ve thought on some guide lines, such as
a.Optimizing the Row space
b.Defining Indexes(where ever necessary as per our database design)
c.Defining the Primary keys(where ever necessary as per our database design)
d.Handling the transaction preferably by Stored procedure.
Well these are above points where i need exactly some information, if you have any information on this or any web site address kindly let me know.
Thanks and Regards,
Regards,
Mahesh DK
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 5, 2002 at 1:11 pm
Hi,
I guess I got answers for some of your questions. But I am not sure how far it will be useful for your work. First of all, it is very hard to tell the optimization steps to take, without knowing the schema. But there are some general steps that can be followed to optimize virtually any database.
1) Each and every table in a database should have a primary key, probably with a clustered index.
2) If a table is mostly used for analytical purposes, try to use maximum indexes possible without hindering the performance.
3) If a table is mostly used for intense transaction purpose, try to use less indexes and get rid of composite indexes.
4) Since you use VB, try using 3-tier architecture and deploy the business layer as a COM object under MTS. This is the best way to achieve peak performance (MTS takes care of connection pooling and lot other stuffs).
5) Try to do each and every SQL statement of your application in stored procedure even if it is a one line select statement. The reason is SQL server should prepare execution plan each and every time if you execute a query from your front end application or from a VB function. Wrap everything up in a stored procedure and call it from your VB app or your front end.
6) Try to cleanup the database by moving the log file to backup, shrinking the database and re-org the data and indexes, once in a month.
Prasath.
Prasath.
April 5, 2002 at 1:23 pm
Good suggestions. If you have the app, I'd also do the following:
Run profiler and track the longest running queries that take more than some value (1sec, 10 sec. whatever) to complete. Then order them by freuency and work on optimizing those queries. Use a clustered index for range queries (date range, cost range, something that returns lots of grouped records). Use a non-clustered index for fairly unique queries, like select * from orders where orderid = xxx.
2. Physically, separate out the database from the logs on different physical devices.
3. Use datatypes that match up well with the data stored. Don't choose char(4) for a 4 digit number if it always a number, use a smallint or int.
Steve Jones
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply