SQL Server Performance

How to speed up SELECT Queries

Discussion in 'Performance Tuning for DBAs' started by maxali, Aug 31, 2005.

  1. maxali New Member

    Hi,

    I have a table with 600,000 rows and 30 columns.

    1. If I make a SELECT Query (Select * from MyTable) it takes 7 Minutes to done.
    2. Also I have a procedure that filters the data with 6 SELECT statments having conditions. This procesdure spends more than 1 Hour to done it.


    Please help me if you have tips and advices to make this more faster.

    This is big problem with me, please help.

    Thanks in advance.
  2. Madhivanan Moderator

    You should use Proper index for the columns
    Post the table structure

    Madhivanan

    Failing to plan is Planning to fail
  3. Adriaan New Member

    Looks like perhaps the table doesn't have a primary key defined. And if it has any indexes then they are not appropriate for the search criteria that are being used.
  4. Akthar New Member

    hi,<br />have a look at you execution plan, is there an Index or Table Scan.<br /><br />I would suggest to run the query against then Index Tuning Wizard to know which typeof index to create and where[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />AKTHAR DILMOHAMUD<br />65 BENARES ST<br />PORT LOUIS<br />MAURITIUS
  5. maxali New Member

    Thanks for this speedy reply.

    Yes, the table does not have a primary key.

    I will try to make indexes for it.
    I will post the result.


  6. ghemant Moderator

  7. maxali New Member

    quote:Looks like perhaps the table doesn't have a primary key defined. And if it has any indexes then they are not appropriate for the search criteria that are being used.


    quote: You should use Proper index for the columns

    I have made a Primary Key and also Clustered Index in my table but the problem still a live.

    Is there any recommendations or suggestions to speed up my query.

    please help!!!!!!!!
  8. deepakontheweb New Member


    Can you post table structure and index information along with query that is taking more time.

    This may help to diagnose properly and suggest in better way.

    Deepak Kumar

    --An eye for an eye and everyone shall be blind
  9. Luis Martin Moderator

    Did you try Index Tuning Wizard to find appropiate indexes?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  10. ranjitjain New Member

    Have you created non-clustred index on any column and any other covering indexes which will cover your search conditions
  11. FrankKalis Moderator

  12. kvkumar2000 New Member

    I have got a error message that is

    1205 : 0 : Transaction [ process id 84 ] dead lock was occured

    from my vb application connected with sql server 2000.

    Please explain about the problem and how to prevent it and sovling the same without any restart of the server.

    Thanking you,

    with regards,

    K.Vijayakumar

    k.vijayakumar
  13. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by kvkumar2000</i><br /><br />I have got a error message that is <br /><br />1205 : 0 : Transaction [ process id 84 ] dead lock was occured<br /><br />from my vb application connected with sql server 2000.<br /><br />Please explain about the problem and how to prevent it and sovling the same without any restart of the server.<br /><br />Thanking you,<br /><br />with regards,<br /><br />K.Vijayakumar<br /><br />k.vijayakumar<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Please start a new thread. Your problem isn't related to this thread. [<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 />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  14. kvkumar2000 New Member

    Dear Sir,

    I have a problem in my sql server database while I have visited to see the properties of my database, I found the following details saying that
    Under the General tab
    Database
    Size is showing properly but the space available is shown as 0.00MB even though I have plenty of gb free space.

    I would like to know the reason and how to solve the problem.

    Expecting your reply,

    K.Vijayakumar

    k.vijayakumar
  15. ghemant Moderator

    hi K.Vijaykumar,<br /><br />please run dbcc updateusage ('yourdatabase') -- this will update usage and sp_updatestats and read BOL for more information , and please start new thread to ask question.<br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by kvkumar2000</i><br /><br />Dear Sir,<br /><br /> I have a problem in my sql server database while I have visited to see the properties of my database, I found the following details saying that <br /> Under the General tab<br /> Database<br /> Size is showing properly but the space available is shown as 0.00MB even though I have plenty of gb free space.<br /><br /> I would like to know the reason and how to solve the problem.<br /> <br /> Expecting your reply,<br /><br /> K.Vijayakumar<br /><br />k.vijayakumar<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami<br />
  16. satya Moderator

Share This Page