Post reply

Get better Performance for Querys in SQL

  • November 27, 2003 at 2:46 pm

    #160229

    Hi All,

    I have a DB in SQL2000, one table of this DB have aprox. 900,000 rows. When I try to make multiple query in one sentence using the commands SELECT, WHERE, AND, OR and ORDER BY for this table ... the query it takes too much time to show me the results of that query. How can I make to give more performance to the Server for that query or any other process run fast ??? Is it a parameter in the SQL or query that can run to give more performance to the server ??? The problem that I have is that I can't drop rows for this table for the content type of the table ... Thanks for your support.

  • November 27, 2003 at 3:32 pm

    #483947

    We would be glad to provide you all the help you need.

    How about posting the actual query you're running and the table structure.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    Edited by - phillcart on 11/27/2003 3:33:21 PM

    --------------------
    Colt 45 - the original point and click interface

  • November 28, 2003 at 12:30 am

    #483965

    Same as above.

    We would need the index definitions too to be able to determine if your query uses them correctly

    Bye

    Gabor



    Bye
    Gabor

  • November 28, 2003 at 1:57 am

    #483969

    900,000 rows isn't generally a problem for sql server 2000.

    The issues involved may include any of the following:

    1) The actual hardware that SQL Server 2000 is running on. Processor speed and RAM. Is this mainly used for SQL Server.

    2) you said there is only one table that you're querying? is this a flat file or a normalised database. Are the records very long? Are datatypes used such as TEXT etc

    3) the columns involved in the Where clause, are they indexed/Clustered?

    4) how complex is the query? Is there a requirement for the query to run within a certain time-frame.

    David Reynolds

    Technical Lead

    imagin' solutions

    Scotland. UK.


    David Reynolds
    Technical Lead
    imagin' solutions
    Scotland. UK.

  • November 28, 2003 at 2:04 am

    #483971

    Actually without having the DDL of your query and the table, any advice will be like a shot in the dark.

    I assume, you've already tried the index tuning wizard?

    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • December 2, 2003 at 5:54 am

    #484256

    the problem is the these tables are Locked .. so make the query run with no lock

    this is done by follow: with each table name in the query, add this statement "with (nolock)" .. so you read data as it is (not recently updated = not commited transaction), and you don't wait for locks to be free and you don't lock these tables, and any other transaction will complete successfully (for you and others) 🙂 .

    you will write something like this on your SQL Server:

    select Invoices.*, Situation.*

    from Situation with (nolock) INNER JOIN Invoices with (nolock)

    ON Situation.SituationID = Invoices.Situation

    where Situation.SituationID =1

    Disadv:

    - when using "with (nolock)", you will not seen comitted transaction (changed one) at this moment ... but you can see it at any other time.

    Adv:

    - no hang at all

    - very fast Response

    - Little summary for Locks in SQL Log file.

    also : you may need to add more Memory (RAM) when server still hangs a little after transfering your queries to path-through queries.... becuase every transaction or query is done on server first, then processed data is passed to your PC.

    ALSO:

    try to put the DATA and the LOG file of SQL Database on a partition that is Formatted with 32 K or 64 K Cluster Size (not the defalut = 4 K)

    because the default Page size in SQL in memory is 8K ,

    And If u use the defulat Cluster Size while formating partioin, then to get 1 page (8K), you need 2 hits for the HardDisk (4 K each)

    But If u use 32 K Cluster Size while formatiing partioin, then on 1 hit for the HardDisk (32 K each) , you get 4 pages into Memory (more faster ... because the slowest thing that slow the whole system is to read/write from Hard Disk)

    I use 32 K not 64 K.. because with 64 K you may have a lot of empty pages in memory ( lost in memeory and your DB may be graw more)

    this of course will help you to solve the problem of hang (Long Running Query) more and more .. bye

    Alamir Mohamed

    Alamir_mohamed@yahoo.com


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

  • December 2, 2003 at 4:58 pm

    #484388

    Like Frank said, without DDL and DML, ANYTHING anyone says is a shot in the dark. You may get some best practices trivia but to get actual help for your problem, more info is needed.

    Ray Higdon MCSE, MCDBA, CCNA

    ------------

    Ray Higdon MCSE, MCDBA, CCNA

  • December 5, 2003 at 1:13 pm

    #484904

    Yes, there are so many variables that without more information about the setup there's not much advice to offer.

    Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.

    Patrick

    Quand on parle du loup, on en voit la queue

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply