SQL Server inserts and select taking long time

We have a table with about 20 columns as shown below:

We need to do 1000 records insert and select later also produces about 1000 records.

inserts were tried to b开发者_StackOverflow社区e done in 2 ways:

• parallel via parallel.For c# loop
• sql adapter inserting whole dataset filled with 1000 records.

Inserts in both cases are taking over 30 seconds. We even tried doing this in a fresh clean table. How can this be sped up ?

[Earlier for normal 10 column table we have done 2 million record inserts via parallel.for in about 60 seconds.]

Select (tested from SQL mgmt studio) returning 2000 records is also taking more than 30 seconds, even in a clean table.

Time is variable as per:

1. mgmt studio was running since many days: 17-30 seconds
2. closed and reopened - 1st select returns in 1 sec. - 2nd and consequent selects about 7-10 seconds to retrieve all rows.

Does variable size or upper limit fixed size make lot of difference in columns VARCHAR(SIZE) ?

[disk is good speed one(RAID ? not sure) and dedicated for this database]

Table schema: (No PK)

varchar(50)
varchar(2)
smallint
varchar(2048)
int
int
varchar(2048)
varchar(MAX)
varchar(MAX)
varchar(MAX)
smallint
varchar(500)
varchar(500)
varchar(MAX)
smallint
smallint
bigint
bigint
bigint
varchar(2048)
smallint
varchar(MAX)
varchar(MAX)
varchar(2048)
datetime

Index:

Index is on varchar(50) , non-unique non-clustered

SELECT statement:

select * 
from table 
where varchar(50) = 'value1' 
  and varchar(2) = 'value2' 
  and smallint = 'value3'

The composition is each unique varchar(50) has 5 unique varchar(2) entries and for each varchar(2) further, 1-3 smallint entries.

Have a look at the SqlBulkCopy class. I did a comparison a while back about high performance loading of data from .NET to SQL Server, comparing SqlBulkCopy vs SqlDataAdapter with the bottom line being, to load 100,000 rows:

SqlDataAdapter: 25.0729s
SqlBulkCopy: 0.8229s

Blogged about it here

UPDATE:
In terms of SELECT performance, try an index on the 3 fields being queried on - that will allow an index seek to be performed. At present, with just an index on the VARCHAR(50), it will be doing a scan. As you are doing a SELECT * to return ALL columns, it will then have to go off and lookup the rest of the data from those other columns as they would not be included in the index. This could be expensive, so you should consider NOT doing the SELECT * and only return the columns you actually need (if you don't actually need them all). The ones you do really need, name explicitly in the SELECT and you can then INCLUDE them in the index you created on the 3 fields in the WHERE clause. (see MDSN ref on INCLUDE: http://msdn.microsoft.com/en-us/library/ms190806.aspx)

To speed up queries:

• don't make a VARCHAR(50) your primary (and thus: clustering) key; use something narrower, and something that is fixed in size. INT IDENTITY works the best

• why do you have VARCHAR(8000) in your table?? That poses a lot of pressure on the table - why not just make those VARCHAR(MAX) as well??

• analyse your queries and create the proper non-clustered indices on columns that can be indexed