Regarding indexes and INSERTs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Regarding indexes and INSERTs

Author

Topic

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-07-31 : 05:09:53

Hi guys and gals.

Have been fiddling around with a table which will be required to handle a lot of inserts from concurrent users. It looks like this:

create table tbehanaval
(
behanaid int,
behanadefid int,
value varchar(8000)
)

What will happen is that when a user triggers an operation from a web page, a proc will be run about 500 times, inserting a value into the table each time (had to be done this way due to the way the page is working).

I tried with three different approaches to indexing:

1) No index. Inserts took between 1.2 and 1.5 seconds
2) Non-clustered index on behanaid, behanadefid. Inserts took between 4 and 5 seconds.
3) Clustered index on behanaid, behanadefid. Inserts took between 0.8 and 1 second.

Is this normal? I though insertions with no index was the fastest.

Can it be because behanaid is normally larger than the values already in the table?

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-31 : 07:53:31

I'd really like to understand why a single page does 500 inserts - 500 round trips between the ASP and SQL?

I'm surprised about the Clustered Index performance too. There are no primary keys, unique constraints or otherwise?

How about posting the CREATE TABLE ?

Sam

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-07-31 : 08:09:55

I already posted the create table statement above... Here is the create index statement for the clustered index:

create clustered index ix_tbehanaval on tbehanaval (behanaid, behanadefid)

The 500 roundtrips are for a form which contains a dynamic number of fields, i.e. it should be easy to change the number of fields without too much db work. I will try a couple of other solutions too, but this is the one which is in place now.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-31 : 08:32:21

Take a look at these:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11150
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11019
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=13935

And check the links inside them too.

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-07-31 : 08:43:26

I will try other methods... Thanks for the replies.

But I still wonder why it's faster inserting with a clustered index on the table than without it.

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-31 : 08:56:34

Have you considered passing a variable number of fields using a single parameter containing comma delimited text?

You might be able to reduce interaction to a single round-trip with a technique like this.

There's a few good articles on passing CSV parameters on this site.

Sam

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-07-31 : 10:25:49

Yeah... Will probably try that and see if it makes a difference. Also looking at sending in the entire POST data to the database for parsing. The problem with that is the special characters which are represented by codes such as %A0.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-31 : 12:25:48

Wasn't this the EXACT problem SQL Team had that made it so slow...multiple round trips to complete essentially 1 operation?

quote:

The 500 roundtrips are for a form which contains a dynamic number of fields

And the problem is you don't know which column contains which data perhaps?

Why not use tags or something (just a guess)

[tag],data,[tag],data,[tag],data

In a single string?

Is that it?

Brett

8-)

SELECT POST=NewId()

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-07-31 : 13:10:03

Yeah that's about it... At the moment though I'm experimenting with sending the whole POST data to the SQL Server. It looks like this: 'tag=data&tag=data&tag=data...' The string is larger than 8k (might be up to 50k) so I have to split it up into several parameters...

The problem is as I mentioned before, that I have to keep track of the special chracter codes, which I don't have to do with the roundtrips, because the special chars are translated then.

Request.Post gives the whole string with codes for spec. chars

Request.Post("tag") gives one value with spec.chars translated and ready to go

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-31 : 13:15:02

That's a lot of data..

can't be user entered can it?

If not where does it come from.

Can you only transmit new or altered data?

how big would that be?

And what does ehanaval mean?

Brett

8-)

SELECT POST=NewId()

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-07-31 : 13:28:47

Hi Brett... It is user entered. Basically, it's a huge form (500 fields), which the users go through with clients. They fill in the parts which apply to the client (50k data is an extreme amount, they would have to fill out all the fields to the maximum). Don't bash me about it because I didn't design the app, I'm only supposed to work out how to store the data in a database.

If I go with the roundtrip variant or build a csv string I can save only altered and non-empty data, which would be good.

The next step will be to look at concurrency issues, but I think I have that in hand.

Ehh don't ask me what it means... It's an abbreviation of swedish business-language...

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-07-31 : 13:55:33

This thing sounds like the blob (that ate New York).

500 Fields? only parts ar used?

Sounds like a lot off wasted resources.

I am not bashing,of course you may wish to bash the app designer.

If there is any possiblity to revamp this I would suggest takeing it back to the design stage. Breaking up the app and table into the relivent parts.

(gess the App designer never heard of a Relational Database design)

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-31 : 14:22:59

quote:
Originally posted by Andraax
Don't bash me about it because I didn't design the app, I'm only supposed to work out how to store the data in a database.

Was I bashing? Soory...I felt more empathy than anything...

Here's a thought (though maybe not a good one).

Can you take all of the data dump in to a file (zip it if you have to) and place it on a drive.

The call a sproc that'll bcp it in to a table, then go from there..

Really reaching now....

Brett

8-)

SELECT POST=NewId()

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-07-31 : 15:34:29

Just did some more testing here... The roundtrip save method takes about 1 sec with 15k of data. With the whole POST data parsed it takes about 0.7 sec... With only me testing of course. Not as big difference as I thought, but maybe with some concurrent users it will get worse with the roundtrip version.

The bcp idea: Don't think I'll bother with trying it... 1 sec is acceptable response time for this app. Besides I think it would take longer to export to a file and then bcp it in... But thanks for the ideas.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-31 : 15:53:45

Close eyes...pull trigger....