Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Unhappy Unanswered: 3000 rows = 45 sec

    I've got a table <Person> with 70 columns
    that contains about 3000 rows

    It takes 45 sec in the QA to run <Select * From Person>

    3000 rows = 45 sec = Poor performances



    So how in the heck do you all do to run
    <Select * From MillionPersonsTable>


    You've got superdoopers alien machines ???



    (sorry, i've got terrible perf problems)

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Check for activity, locks on your server, try just select count(*) from your table or select one column from your table. It is impossible to have such performance on sql server.
    Last edited by snail; 12-09-03 at 10:21.

  3. #3
    Join Date
    Dec 2003
    Location
    Ohio
    Posts
    9
    Do you servers have dual processors, and multiple hard drives?

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    pentium III or IV

  5. #5
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    select count(*) takes 3 secs
    select column takes 3 secs too

    I tought that I had poor perfs on Sybase
    But they're practically the same on SQL Server

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    How much data are you returning to the client?

    select dpages/128 as KB
    from sysindexes
    where id = object_id('yourtable')
    and indid in (0, 1)

    70 columns sounds awfully wide.

  7. #7
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    1 KB

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Heh. Small problem with units. Guess that's why I don't work at NASA....


    You are returning somewhere between 1 and 2MB of data to the client. Still 45 seconds sounds a bit long for 1MB of data to wiggle its way through your cabling. Is the server in your building, or remote?

  9. #9
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    the server is in the building

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The select you did above on a single column. Was that an indexed column? If so, how log does it take to return an unindexed column?

  11. #11
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    3 secs ...
    for indexed or non indexed columns

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    OK. Last thought.

    Open a QA session to the server, and note the SPID number (bottom right). In this window run the select * from table query. In a second window run the following:

    select spid, cpu, physical_io, memusage, waittype, lastwaittype, blocked, waitresource
    from master..sysprocesses
    where spid = (your spid)

    Blocked should always be 0 (so if it is not, you have an answer right there).
    Lastwaittype is the type of waiting the connection did last, so it will not change often.
    Waittype is a binary field that is the current waittype of the connection. You can find some of the definitions in KB article Q244455. 0x800 is network IO, I believe.
    Physical_IO is the one I suspect you will see jump. If this jumps early in the query, then you are reading the table from disk. Table scans tend to recylcle memory this way, to keep more efficient tables in memory. If you have outrageous physical_io, you can look at trying to break up the table, or simply not do select * on it without a where clause.

  13. #13
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    I'll check that tomorrow.

    Thank you for all your time

    Caroline

  14. #14
    Join Date
    Feb 2002
    Posts
    2,232
    Are you running any traces ? Is sql server agent running ?

  15. #15
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Run dbcc showcontig on the table, I bet you have high fragmentation. If you do not have a clustered index on the table, you should. There are rarely times that a table should not have a clustered index.

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •