record insertion

Question

Post reply

record insertion

ritesh.saluja

SSCommitted

Points: 1704
More actions
April 29, 2009 at 3:26 am

#133553

my SQL table contains lot of record.
in the same table , i have requirement to insert lot of record per minute thru application UI.
What kind of table design / index should be there for good speed.

Viewing 10 posts - 1 through 9 (of 9 total)

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

arun.sas SSChampion Points: 11831 More actions · Answer 1

Hi,

What kind of the data type used (for the records) to insert?

And show the existing table schema

ARUN SAS

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

Without details it's not possible to give you specific advice. In general, make sure you have a clustered index on the table. Make it as narrow as you can, but be sure that it's on data that will be distributed well so that you don't experience excessive blocking (people usually just use an INT on an IDENTITY column, which works, but isn't optimal for all situations). Be very judicious in adding other indexes. There is a lot of very old information that suggests that heaps are faster than clustered indexes for inserts. That was true at one time, but SQL Server has been optimized around storage on the clustered index. You should have at least that.

Other than that, I can't say because I don't know your situation well enough.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 3

As said by others, details required.

It will be better to avoid un-necessary indexes.

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

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

ritesh.saluja SSCommitted Points: 1704 More actions · Answer 4

table has column with all data types.

Int

varchar

Datetime

etc.

ritesh.saluja SSCommitted Points: 1704 More actions · Answer 5

thanks grant and other members.

grant, can you please send me detail / guidelines / link on this topic (Indexes)

it will be a great help

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 6

There are great articles right here on SQL Server Central. Just type something like 'index design' in the search box and then limit the scope to articles. There are a number there that should help you out.

Gail Shaw has written several great fundamental explanations on how indexes work, how to pick them, etc. Swing by her blog, SQL In The Wild[/url], for details.

I've got a few articles on indexes on my blog, but not specifically in the area you're interested in. My book covers topics like this, although not this precise question.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning