April 29, 2009 at 3:26 am
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.
April 29, 2009 at 4:43 am
Hi,
What kind of the data type used (for the records) to insert?
And show the existing table schema
ARUN SAS
April 29, 2009 at 4:52 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 29, 2009 at 4:55 am
As said by others, details required.
It will be better to avoid un-necessary indexes.
April 29, 2009 at 5:06 am
table has column with all data types.
Int
varchar
Datetime
etc.
April 29, 2009 at 5:11 am
thanks grant and other members.
grant, can you please send me detail / guidelines / link on this topic (Indexes)
it will be a great help
April 29, 2009 at 5:32 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 29, 2009 at 6:26 am
thanks for help and this information. your blog detail will also give me great help. kindly give
April 29, 2009 at 6:35 am
It's right there in the signature line, The Scary DBA.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 29, 2009 at 6:48 am
Grant,
Great help
Thanks a million for this.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply