MS SQL Server - SQL 2005 Performance Issues, Help!

Author

SQL 2005 Performance Issues, Help!

Bill Cohagan

2006-03-30, 3:23 am

I've got a c# 2005 Windows app that is populating a couple of tables in a
SQL 2005 database. One of the tables has around 5 columns and starts off
empty. It eventually receives ~ 500,000 entries. My code does this via a
stored procedure call (for each row) and the procedure does an INSERT.

The problem is that the program is literally taking HOURS to run. Originally
the table in question had, in addition to a primary key field, a couple of
non unique indexes, but I've not dropped those in an attempt to speed up the
processing. I've also dropped the foreign key constraints. It may or may not
have helped, but the program is still slogging through wet cement.

I'm using SQLExpress on a 1.3GHz Pentium w/1GB of RAM under Windows XP SP2.

Surely this is not expected performance for SQL 2005, right? What can I do
to speed this up?

Thanks in advance for any suggestions or pointers.

Bill

Uri Dimant

2006-03-30, 3:23 am

Bill
Did you get the database to SQL Server 2005 from SQL Server 2000 or was it
originally created in SQL Server 2005?

1)
If you got it from SS2000 ,please check the compatibility level (must be
90) and run UPDATE STATISTICS on the database

2)
As you said you perform an INSERT for each row, did you meat each INSERT
does 500000 INSERTING into the table?
Can you INSERT 500000 within one transaction?

3)
Well , try remove all indexes and run your batch.

"Bill Cohagan" <cohagan@nospam.nospam> wrote in message
news:OR5TQF7UGHA.5468@TK2MSFTNGP14.phx.gbl...
> I've got a c# 2005 Windows app that is populating a couple of tables in a
> SQL 2005 database. One of the tables has around 5 columns and starts off
> empty. It eventually receives ~ 500,000 entries. My code does this via a
> stored procedure call (for each row) and the procedure does an INSERT.
>
> The problem is that the program is literally taking HOURS to run.
> Originally the table in question had, in addition to a primary key field,
> a couple of non unique indexes, but I've not dropped those in an attempt
> to speed up the processing. I've also dropped the foreign key constraints.
> It may or may not have helped, but the program is still slogging through
> wet cement.
>
> I'm using SQLExpress on a 1.3GHz Pentium w/1GB of RAM under Windows XP
> SP2.
>
> Surely this is not expected performance for SQL 2005, right? What can I
> do to speed this up?
>
> Thanks in advance for any suggestions or pointers.
>
> Bill
>
>

Steven Cheng[MSFT]

2006-03-30, 7:23 am

Hi Bill,

Welcome to use MSDN Managed Newsgroup Support.

From your description, my understanding of this issue is: When you try to
insert about 500000 records into a table, your C# application runs about
hours. If I misunderstood your concern, please feel free to point it out.

I have tested on my side. My PC is P4 2.8G Hz with 1G Ram. My SQL Server is
SQL 2005 Enterprise.

I try to insert 500000 records in a table. It takes me about 3 mins.

Since performance issue related to too much things, you should firstly
check if you run the application to a different database server, does this
issue occur?

Then, you should check what's your stored procedure doing. Does this SP
doing something else after insert into the table.

And also, I think you should check your application. Do you use Sqlcommand
to execute the stored procedure in your application?

You may try to use SQL Profiler to trace the operation to find the root
cause.

Performance issues can be difficult to troubleshoot and resolve in a
newsgroup setting due to the number of variables and the amount of time
required to narrow down possible causes and observe the effects. We will
assist as best as we can, but you may wish to consider contacting CSS for a
more timely resolution for these type issues.

To obtain the phone numbers for specific technology request please take a
look at the web site listed below.

http://support.microsoft.com/defaul...US;PHONENUMBERS

If you are outside the US please see http://support.microsoft.com

for regional support phone numbers.

Thank you for your patience and understanding.

Sincerely,

Wei Lu
Microsoft Online Community Support

====================
====================
==========

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

====================
====================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.

Bill Cohagan

2006-03-31, 3:23 am

Uri
Thank you for your response and suggestions. The problem does indeed
appear to be the compatibility level. I changed it to 90 and it runs *much*
faster now.

Regards,
Bill

"Uri Dimant" <urid@iscar.co.il> wrote in message
news:eKxuAJ9UGHA.2156@tk2msftngp13.phx.gbl...
> Bill
> Did you get the database to SQL Server 2005 from SQL Server 2000 or was
> it originally created in SQL Server 2005?
>
> 1)
> If you got it from SS2000 ,please check the compatibility level (must be
> 90) and run UPDATE STATISTICS on the database
>
> 2)
> As you said you perform an INSERT for each row, did you meat each INSERT
> does 500000 INSERTING into the table?
> Can you INSERT 500000 within one transaction?
>
> 3)
> Well , try remove all indexes and run your batch.
>
>
>
>
>
>
> "Bill Cohagan" <cohagan@nospam.nospam> wrote in message
> news:OR5TQF7UGHA.5468@TK2MSFTNGP14.phx.gbl...
>
>

Sponsored Links