June 2, 2003 at 8:39 am
Hi,
We have a SQL Server DB on win 2000 server, Can some one guide me on how much effect would correct indexing have on the performance and how to use pages and what is their effect on the performance.
I am basically looking for increase times in save and edit. We are using this DB through an application written in Delphi, for which I have the source code also, so if required I can also make changes in the application to speed up save and edit on a SQL Server DB.
June 2, 2003 at 10:11 am
Hey,
Your posting is a bit vague. It's a bit hard to give you a decent answer unless you have a specific question....But I'll get the ball rolling with a few suggestions...
First off, I'll say that correct indexing will boost performance of your database. However, on the flip side, incorrect indexing or too many indexes can have the reverse effect and slow down queries!
How do you run your queries? Are they all within SP's or do you execute queries direct from your code?
Have you anaylsed the queries using execution plan as well as profiler?
Is your locking strategy correct? IE, are you locking only when you need to...?
Also, check your statistics are up to date!
I am sure others can contribute a lot more also, but if you have a specific area of concern, then let us know, that will help us narrow down the problem!
Clive Strong
June 2, 2003 at 11:24 am
In the app, check what the join columns are. If you want to speed up queries you can index those columns. But there is a balance, because if you are using UPDATES or INSERTS they may slow down because the database must also add to the indexes.
Bonne chance!
Patrick
Quand on parle du loup, on en voit la queue
June 3, 2003 at 7:55 pm
I agree with the others. Also, you may want to look at your WHERE clauses to ensure that your are using Indexes.
Run Profiler and see if you have some HUGE READ/WRITES. If you do look at the code and try and figure out a better mouse trap
Good Luck,
AJ Ahrens
SQL DBA
Revenue Assurance Management - AT&T
Good Hunting!
AJ Ahrens
webmaster@kritter.net
June 4, 2003 at 11:18 am
Shotgun approach:
Put indexes on columns of tables referenced by in the join clause.
Index columns which are selective and referenced in the where clause.
Use Graphical Show Plan: (Highlight your query & ctrl-L) go after the higher % tasks and add indexes where neccessary.
Remember the SQL optimizer will often ignore indexes that it determines will not enhance performance.
A couple reasons why an index will not help:
The first key in a composite index is not referenced in the query.
The first key in an index is not selective.
The index key is referenced by a funtion call: "where isnull(c1, 0) = 12"
hth,
joe
June 5, 2003 at 9:50 am
Thanks for your help every one, I have applied what you all wrote and it seems to be working. Just one more question:
I was using SQL Profiler based on a workload that I gave it. The indexes that it had put in there were different than what really are making the difference. Is there a specific way to use to profiler, I used the workload tracer with three days of routine work load.
Am I using it right..?
Thanks
June 5, 2003 at 11:03 am
Sounds to me like you are using it correctly. You may want to look at the queries that are using indexes that you think are not correct and review the joins/where clauses. It may be a simple re-arrangement of the where to start using the proper indexes.
Good Luck
AJ Ahrens
SQL DBA
Revenue Assurance Management - AT&T
Good Hunting!
AJ Ahrens
webmaster@kritter.net
June 5, 2003 at 2:44 pm
The profiler probably also asked you to update statistics. Always a good idea.
Yes, you are using the profiler correctly.
Patrick
Quand on parle du loup, on en voit la queue
June 6, 2003 at 4:21 am
Profiler can make good determination but I only use as suggestions not always appropriate for final production. Even thou it can apply indexes and makes good suggestions based on the workload it sees that was just a feature to make it easier for novice customers. Always look at Index Tuning Wizard as a suggestion of potential indexes.
June 6, 2003 at 5:44 am
You said in your original post that you wanted to "increase times in save and edit" in your application. Updating and Inserting rows (I assume this is what you meant by "save and edit") are not usually positively improved by indexes. Indexes help the dbms search through large quantities of rows in table(s) by providing an alternative to a complete tablescan. When you insert a record, indexes can actually HURT performance because now the system has to maintain all those indexes you created when adding a row. Same with updating when indexes columns are effected. Assuming you're where clause for an update includes the PK, additional indexes won't help updating either.
June 6, 2003 at 7:49 am
Indexes effecting the save and edit times does make sense. As far the Application its self is concerned we have made all possible efforts to speed up the process of save and edit (I mean memory leaks, object destruction and creation and instantiation at proper times etc), that is why I was now concentrating on ways to make it better through indexes or any other means possible.
While what you said makes sense but following what others have helped me with also made considerable effect on the speed.
Which I think must be because:
"Before it actually saves the record it has to go through thousands of records to find the appropriate ID and its classtype (which we determine through a ClassTypeID) and then make sure that its using the correct ID Sequence to save the record. The lookup took a long time, which was saved due to correct indexes ( I Think )".
Any comments..?
Thanks.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply