SQL Server Performance

Performance Tuning required for a table which contains 1000 rows.

Discussion in 'Performance-Related Article Discussions' started by dhamu294, Mar 10, 2009.

  1. dhamu294 New Member

    Hi All I have a problem in performance. i have created a table with two columns one with int and another with varchar (8000) then i have inserted 1500 records using below query. when i give select query it is taking more than 1 min. is there any method to tune a column which contains records for varchar(8000) length. kindly let me know Script:create table test( sno int identity (1,1), descr varchar(8000))declare @a int set @a = 0while (@a update operation will fail.The statement has been terminated.Sql server 2000 with sp 2Can any one help me out. Thanks in advance
  2. Adriaan New Member

    The actual query did not get posted, so we'll have to guess a little here ...
    Is the WHILE construct there for inserting test data?
    You mention inserting 1,500 rows, and that the query is returning 1,000 rows, so your query seems to have a WHERE statement.
    First question: does the table have a primary key? If not, it is a heap table - which is to be avoided.
    Second question: if the column on which you are filtering is not the primary key, then is there an index on this column?
  3. FrankKalis Moderator

    Even with the worst possible SELECT construct, 1500 rows should be returned in less than a minute. I would suspect something else going on as well. But as Adriaan mentioned we need to see the query first.
  4. dhamu294 New Member

    create table test
    ( sno int identity (1,1), descr varchar(8000))
    declare @a int
    set @a = 0
    while (@a <= 1000)
    begin
    insert into test (descr) values ( replicate ( 'abcde', 10000))
    select @a = @a + 1
    end
    go
    select count(*) from test
    create index test_index on test (sno,descr)
    when i tryied creating index with above query got below error
    Server: Msg 1946, Level 16, State 1, Line 1
    Operation failed. The index entry of length 8004 bytes for the index 'test_index' exceeds the maximum length of 900 bytes.
    Warning! The maximum key length is 900 bytes. The index 'test_index' has maximum length of 8004 bytes. For some combination of large values, the insert/update operation will fail.
    The statement has been terminated.
    Can any one help me out.
    Thanks in advance
  5. dhamu294 New Member

    above script i used for testing after all insert when i give select * from test. it takes more than a min. for 1500 records. How to fine tune
  6. Adriaan New Member

    Inserting 1500 rows, one at a time, may be taking up the bulk of the time.
    Create the table and fill it up. Then time the SELECT query by itself.
  7. dhamu294 New Member

    Select * from test takes more than one min.. not the full query.. for testing and better understanding i gave the full query.
  8. Adriaan New Member

    You answered my second question, but not my first.
  9. FrankKalis Moderator

    [quote user="dhamu294"]above script i used for testing after all insert when i give select * from test. it takes more than a min. for 1500 records. How to fine tune
    [/quote]
    Read the error message. You can not create an index with a length of more than 900 bytes. Your statement failed and you were left with no index in place.
    You might be able to add your varchar(8000) column as an included column to the index, but again with a straight SELECT * FROM table, SQL Server will go for a scan anyway.
    I don't think such a test is really meaningful at all. Still it takes too long to return the rows. Can you post the output when you run the query with SET STATISTICS TIME ON?
  10. dhamu294 New Member

    Actually my problem is, i already have a table with similar kind of data.
    when i fetch the query using select * from <tablename > it takes more time . to simulate the situation. i wrote the above query..
    when i use select * from <tablename> i want it faster when it has varchar (8000).. this is what my question.. sorry i confused you a lot.
  11. gbd77rc New Member

    Hi
    I just ran you script in tempdb on my laptop (3GB RAM Win 2008/SQL 2008 64-bit) the select count(*) and select * both returned within a second. I have also tested this on Windows 2003/SQL 2005 Hyper-V image with 2GB of ram and the results where the same. Here are the IO statistics I get.
    64-bit System :Table 'test'. Scan count 1, logical reads 1001, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    32-bit System :Table 'test'. Scan count 1, logical reads 1001, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    In both cases the hdd are standard EIDE (no RAID involved). So have you checked your hardware to see if there is IO bottleneck anywhere, have a look at the Avg Disk Queue Length, should be less then 10 normally with busts above that of a couple of seconds.
    Regards
    Richard...
  12. dhamu294 New Member

    i run the below query..

    SET STATISTICS TIME ON
    select * from test
    i got the below statistics. and it took 56 seconds. which i feel it is a long time for 1300 records

    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server parse and compile time:
    CPU time = 0 ms, elapsed time = 0 ms.
    (1360 row(s) affected)

    SQL Server Execution Times:
    CPU time = 63 ms, elapsed time = 55446 ms.
  13. FrankKalis Moderator

    As you can see from these numbers SQL Server spent just 63 milliseconds on parsing, compiling and doing work for you. However, almost 56 seconds were spent from start of processing until its end. So, there are plenty of causes why it took that long, such as waiting for other tasks, IO bottleneck, network bottleneck, etc...

Share This Page