Moving sql 2000 to sql 2005- update query run slower

Earlier I posted this query in SQL Server Database Engine forum. I am reposting it here.
We have upgraded our database server from SQL 2000 to SQL 2005. To migrate the DB I attached the mdf file in SQL 2005. After migration website loads quicker than earlier but records updation ( no of records are 20k) got slowdown. I have found that update query run very slow in SQL 2005 as compared to SQL 2000. Although other select query run very faster. Query is called from ASP (active server page) page.

Changing of fill factor option from 0 to 70 also did not work. I also have set the competible leve to 90.

OLD Sever Config: dual xeon 1GHz, 512MB RAM, window 2000, MS SQL 2000

New server Config: Dual Core Xeon with 2 CPU, 10 GB RAM, window 2003, MS SQL 2005, SQL 2005 SP1

size of mdf file is 16GB

Rebuilting of index did not improved the performance.

Here is the code

SET rsMailQ = objConn.Execute("SELECT * FROM mailqueue WHERE date_sent IS NULL")

While NOT rsEmails.EOF

Set objMailer = Server.CreateObject("Persits.MailSender")
objMailer.Host = MailServer

objMailer.Charset = "UTF-8"
objMailer.ContentTransferEncoding = "Quoted-Printable"
objMailer.From = rsEmails("From")
objMailer.FromName = rsEmails("FromName")
objMailer.AddAddress rsEmails("To")
objMailer.Subject = rsEmails("Subject")
objMailer.Body = rsEmails("msg")

objMailer.Queue = True
objMailer.Send

strSQL = "UPDATE MailQueue SET date_sent = GETUTCDATE() " _
& "WHERE mail_queue_id = " & rsEmails("mail_queue_id")

Set rsUpdate = CreateObject("ADODB.Recordset")
rsUpdate.Open strSQL, objConn

Wend

Thanks in advance

Hi,

Have you update Statistics?

Is MDAC version are same on both the Servers and Client Machine?!

Have you check query running from QA ? What about Execution Plan of Both Servers? I would suggest you to refer relavent thread http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=17451 and some performance Tunning tips on SSP sote http://www.sql-server-performance.com/statistics_io_time.asp & http://www.sql-server-performance.com/transact_sql.asp

HTH

Hemantgiri S. Goswami

|||

Can you post the code for the creating the objConn, specifically are you using a client or server side cursor.

I also not your are not using the recordset returned. In which case you would be better of not storing the results in a recordset.

objConn.execute strSQL

Is this code running on a seperate machine.

What is the CPU usage on the 2 machines?

How many records are in the loop?

|||

Thanks Simon for your help.

Yes this code is running on seperate machine. New sql server ( i.e sql 2005) has better machine as mentioned in my question posted above. It is running slow even for 100 records, in live environment records will be arround 20k or more

here is the code to open connection

Set objConn = Server.CreateObject("ADODB.Connection")
'20030930 JDR 01 - Set 10 minute timeout
objConn.CommandTimeout = 600
objConn.Open(strConnection)

Thanks

|||

My problem got solved after using two connection objects one connection for reading and another for updating the records. Here is the new code...

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.CommandTimeout = 600
objConn.Open(strConnection)

Set objConn2 = Server.CreateObject("ADODB.Connection")
objConn2.CommandTimeout = 600
objConn2.Open(strConnection)

SET rsMailQ = objConn.Execute("SELECT * FROM mailqueue WHERE date_sent IS NULL")

While NOT rsEmails.EOF

Set objMailer = Server.CreateObject("Persits.MailSender")
objMailer.Host = MailServer

objMailer.Charset = "UTF-8"
objMailer.ContentTransferEncoding = "Quoted-Printable"
objMailer.From = rsEmails("From")
objMailer.FromName = rsEmails("FromName")
objMailer.AddAddress rsEmails("To")
objMailer.Subject = rsEmails("Subject")
objMailer.Body = rsEmails("msg")

objMailer.Queue = True
objMailer.Send

strSQL = "UPDATE MailQueue SET date_sent = GETUTCDATE() " _
& "WHERE mail_queue_id = " & rsEmails("mail_queue_id")

Set rsUpdate = CreateObject("ADODB.Recordset")
rsUpdate.Open strSQL, objConn2

Wend