Two inserts per second - too slow?

Question

Two inserts per second - too slow?

mchid

SSC Rookie

Points: 35
More actions
July 15, 2009 at 3:58 am

#208341

I have a relatively simple database (10 tables or so) running on an ok-spec server.
I have one table in this form:
PK (Guid)
FK1 (Guid)
FK2 (Guid)
FK3 (Guid)
int A
int B
int C
I have non-unique non-clustered indexes on FK1, FK2 and FK3.
There are 1.5 million records in this table.
Inserts are taking about 0.5 second.
Any thoughts on how I can increase this performance?
Thanks in advance!

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

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

Randy-574768 Default port Points: 1435 More actions · Answer 1

Have you looked at the activity monitor? What kind of waits are you seeing? It could be something related to hardware or database configuration slowing you down (CXPACKET or PAGEIOLATCH waits) or maybe its being blocked by something else.

Randy

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

It could be anything, from db related (indexes, triggers, blocking processes) to system related (disk queues, high cpu load, network traffic).

I suggest you narrow the search by adding SET STATISTICS IO ON before running the INSERT statement and then post here the results.

-- Gianluca Sartori

mchid SSC Rookie Points: 35 More actions · Answer 3

Thanks guys - will post some results as suggested. I'm pretty new to this so may take a while to get some meaningful data...

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

Without a lot more details, it's hard to know for sure, but on a guess, you've got the GUID clustered. That's going to lead to huge amounts of page splits as inserts occur. With a GUID being completely random, inserts occur all over the place. By and large, you want a clustered index to be on ordered values. That's why the NEWSEQUENTIALID is a good choice if you really need to use GUIDs. It will generate ordered data, which will improve the performance of the inserts.

"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

Randy-574768 Default port Points: 1435 More actions · Answer 5

I have a question on that: is it better to have a clustered index on a high selectivity value like a date field where you might have 1000+ rows with the same date, or on an ID like the one mentioned, or even an Identity?

Randy

mchid SSC Rookie Points: 35 More actions · Answer 6

Thanks Grant. So should I use a clustered index on the Guid FKs then?

My investigations are now with the code itself where I have found some issues (LINQ to SQL - calling SubmitChanges more frequently than required etc).

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

Randy (7/15/2009)
I have a question on that: is it better to have a clustered index on a high selectivity value like a date field where you might have 1000+ rows with the same date, or on an ID like the one mentioned, or even an Identity?

There's no one right answer unfortunately. There are trade-offs though. An integer field will work better than a GUID because it's going to generally be ordered (although not necessarily) and it's smaller. But there are valid reasons for using a GUID, so you may need to make it work. Purely random GUID entries are very problematic, especially in clustered indexes, from a performance stand point. 2005 introduced the sequentialid to help solve that issue.

Any index needs to be as selective as possible. Clustered indexes work best for whatever is the primary means of accessing the data, meaning, if you search most of the time on date ranges, make that the clustered index. But if you search most of the time based on the PK (pretty common, which is why it's the default in SSMS) then that's a better approach. It really depends.

The general rule, as narrow as you can, and preferably incrementally ordered (dates, identity, sequential guids), make the better clustered indexes. I think Gail Shaw[/url] has a post over on her blog somewhere that describes it better than I'm doing currently.

"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

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

mchid (7/15/2009)
Thanks Grant. So should I use a clustered index on the Guid FKs then?
My investigations are now with the code itself where I have found some issues (LINQ to SQL - calling SubmitChanges more frequently than required etc).

I don't know that I'd go there. That will make for an even wider clustered index and it's still not likely to be ordered.

You should have a clustered index, and I'm of the opinion that it should be on the most common access path. For this table then, do you access the data mostly by FK or mostly by PK? Whichever one it is, that's where the cluster should be.

"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

Randy-574768 Default port Points: 1435 More actions · Answer 9

Yep - I've been meaning to check out Gail's blog. Unfortunately, it's blocked to me from work and no access at home.

Randy