SQL Server Performance

Opinion on my database

Discussion in 'T-SQL Performance Tuning for Developers' started by smy, Jun 1, 2006.

  1. smy New Member

    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?
  2. FrankKalis Moderator

    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>)
  3. satya Moderator

    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.
  4. smy New Member

    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.
  5. FrankKalis Moderator

  6. smy New Member

    Yes, thanks!
  7. guanjm New Member

    ........
  8. vsnreddi New Member

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

    SURYA

  9. Madhivanan Moderator

    quote:Originally posted by guanjm

    ........
    What do you mean?

    Madhivanan

    Failing to plan is Planning to fail
  10. ranjitjain New Member

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


    quote:Originally posted by smy

    Hi,
    I got three ideas to speeding the process..
    3) Create Stored Procedure
    Which one will be a better choice?
  11. guanjm New Member

    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
  12. guanjm New Member

    Which one will be a better choice?
    -------------------------------
    2 and 3
  13. balaganesh2k Member

    quote:Originally posted by guanjm

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

    Please give some information...

    Ganesan B.
    Well defined problem is half way solved.
  14. Adriaan New Member

    (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.

Share This Page