Insert into table is REALLY slow! 
Author Message
 Insert into table is REALLY slow!

Hi,

    I use SQL 7 with NT4 and IIS 4.  I got a table used for a Webboard.
When I do a select, the query is fast but when I do an insert, it takes
about 3 minutes!  I have text fields varchar name, email, url and
numeric fields like id and key.  I also have a text field for the
message.  I created indexes on id and key which are used on my where
clauses.

    When I insert in my table (approx. 200 000 records) it takes 3
minutes.  When I insert in my old copy of that table (40 000 records) it
takes less than a second.

    When I drop all indexes, it goes faster.  But 5 minutes later, the
problem occurs again.

Does anyone have an idea or a suggestion??

Thanks
--
Stphane Gurin
Analyste / Programmeur ASP SQL

http://www.***.com/
http://www.***.com/



Fri, 04 Apr 2003 03:00:00 GMT
 Insert into table is REALLY slow!

A few questions:

Are you using a fillfactor on your indexes? How many indexes on the table?
What is your disk config/raid level?
Have you looked at NT Perfmon counters?

john s. MCSE,MCDBA,OCP

Quote:

>Hi,

>    I use SQL 7 with NT4 and IIS 4.  I got a table used for a Webboard.
>When I do a select, the query is fast but when I do an insert, it takes
>about 3 minutes!  I have text fields varchar name, email, url and
>numeric fields like id and key.  I also have a text field for the
>message.  I created indexes on id and key which are used on my where
>clauses.

>    When I insert in my table (approx. 200 000 records) it takes 3
>minutes.  When I insert in my old copy of that table (40 000 records) it
>takes less than a second.

>    When I drop all indexes, it goes faster.  But 5 minutes later, the
>problem occurs again.

>Does anyone have an idea or a suggestion??

>Thanks
>--
>Stphane Gurin
>Analyste / Programmeur ASP SQL

>http://www.francite.com
>http://www.webd.org



Mon, 07 Apr 2003 03:00:00 GMT
 Insert into table is REALLY slow!
Recently we (me and my staff) have notice the same problem ..... but in
query analizer we hadn't the problem .... so we have compared our Session
opened my application with session opened by Query analizer and the result
was that ARITHABORT was OFF in our session, so we have use SET ARITHABORT ON
in our session and we have increase tremendously the speed!.

Can you say me if this is your problem too ?



Quote:
> Hi,

>     I use SQL 7 with NT4 and IIS 4.  I got a table used for a Webboard.
> When I do a select, the query is fast but when I do an insert, it takes
> about 3 minutes!  I have text fields varchar name, email, url and
> numeric fields like id and key.  I also have a text field for the
> message.  I created indexes on id and key which are used on my where
> clauses.

>     When I insert in my table (approx. 200 000 records) it takes 3
> minutes.  When I insert in my old copy of that table (40 000 records) it
> takes less than a second.

>     When I drop all indexes, it goes faster.  But 5 minutes later, the
> problem occurs again.

> Does anyone have an idea or a suggestion??

> Thanks
> --
> Stphane Gurin
> Analyste / Programmeur ASP SQL

> http://www.francite.com
> http://www.webd.org



Tue, 08 Apr 2003 03:00:00 GMT
 Insert into table is REALLY slow!
Try inserting them in the order of one of the keys.
That may help a bit.

Quote:
> Hi,

>     I use SQL 7 with NT4 and IIS 4.  I got a table used for a Webboard.
> When I do a select, the query is fast but when I do an insert, it takes
> about 3 minutes!  I have text fields varchar name, email, url and
> numeric fields like id and key.  I also have a text field for the
> message.  I created indexes on id and key which are used on my where
> clauses.

>     When I insert in my table (approx. 200 000 records) it takes 3
> minutes.  When I insert in my old copy of that table (40 000 records) it
> takes less than a second.

>     When I drop all indexes, it goes faster.  But 5 minutes later, the
> problem occurs again.

> Does anyone have an idea or a suggestion??

> Thanks
> --
> Stphane Gurin
> Analyste / Programmeur ASP SQL

> http://www.francite.com
> http://www.webd.org



Fri, 11 Apr 2003 03:00:00 GMT
 Insert into table is REALLY slow!
Stphane,

Make sure that SQL Server statistics are up to date.

If they are not then the optimiser may be doing table scans instead of using
your indexes.  For the smaller (40000) row table this would make little
difference, but on the larger one this would result in lots of unnecessary
disk activity.



Sun, 27 Apr 2003 09:20:17 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Ontape being really, really, really SLOW...

2. Really, Really slow printing.

3. Really slow Fact table load--Please help

4. Really slow fact table load---Please help

5. Unique Index gets slower and slower and slower for inserts

6. Insert into SQL Server slow, slow, slow...

7. FTI is really really slow; what am I doing wrong?

8. Really simple question: How to insert a table with only one column (also identity)

9. Problem: I am trying to insert a query into a table

10. Really, Really big tables

11. Really, really, REALLY need an answer

12. am I really running out of file handles


 
Powered by phpBB® Forum Software