SQL Server Performance Forum – Threads Archive

Opinion on my database

Hi, Would like to help some opinions.. I got a database that is recently increasing in size. My window application use SELECT query with inner join of different tables.
Now the execution of this query is getting slower due to more and more records. I got three ideas to speeding the process..
1) Create a View table
2) Create Indexes for each tables
3) Create Stored Procedure Which one will be a better choice?

---

Hey, you should know better! [<img src=’/community/emoticons/emotion-5.gif’ alt=’‘ />]<br />Please post some more informations like structures… [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

---

http://www.sql-server-performance.com/tuning_joins.asp for information on performance of join. Memory contention is another throughput-related issue that can cause problems when an application runs with many concurrent connections. Check if your queries are using Parameters and this may cause performance issues. If the queries are complex and/or the data distribution on the columns against which the parameter is compared vary, the cost of different plan choices can change. A plan that is optimal for one parameter value may perform poorly for another value. The query optimizer still needs to estimate the selectivity and cardinality of predicates using a parameter value. This section describes how this process works in more detail. 1) View is a good point to regulate the required columns for your reports.
2) Before that I suggest to run PROFILER while running resource intensive queries and submit to Index Tuning Wizard for a recommendation on indexes (though it is not accurate), this gives some idea about what you can use to achieve the performance.
3) For what you want to use SP?
Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Thanks to your speedy reply <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br />I had currently use View table. The performance improved well.

---

So, problem solved for now? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

---

Yes, thanks!

---

……..

---

can you please explain which scenaril it happened..even iam also facing the same problem SURYA

---

What do you mean? Madhivanan Failing to plan is Planning to fail

---

If you don’t use SPs, i would say start creating stored procedures even for simple select queries and call them from client machines.

---

2) Create Indexes for each tables
———————————-
more indexes for one table is effect e.m. (insert update delete).
so one table dont over six indexs. my english is poor,please sorry

---

Which one will be a better choice?
——————————-
2 and 3

---

Please give some information… Ganesan B.
Well defined problem is half way solved.

---

(1) A regular view doesn’t help performance. (2) If your tables do not have indexes, then response times will be slower. (3) If your client application is issuing ad-hoc queries, then changing them to stored procedures will probably improve performance. But of course in case you have a client app built on ODBC connections, you would need to rewrite (part of) it since stored procedures do not return editable recordsets.

---

]]>