Slow on Server but fast on local pc  
Author Message
rpostrel





PostPosted: Mon Nov 06 00:07:23 CST 2006 Top

SQL Server Developer >> Slow on Server but fast on local pc

I'm running a script with a cursor; appending, updating inserting rows in
multiple tables. on my local pc it takes about 3 minutes in sql server 2005
Query screen. It takes 20minutes on the server. The database is in 2005
converted to from 7.0 to 8.0. on the server it's converted from 7.0 to 9.0.
Same database. i just did a restore of a backup from sql server 7.0

is there a setting i missed?

SQL Server25  
 
 
Uri





PostPosted: Mon Nov 06 00:07:23 CST 2006 Top

SQL Server Developer >> Slow on Server but fast on local pc mike
> converted to from 7.0 to 8.0. on the server it's converted from 7.0 to
> 9.0.
What does the above mean? Converted from SQL Server7 to SQL Server2000 and
from SQLServer7 to SQLServer2005?

Did you run update statistics after upgraiting? Have you compare execution
plans for both scripts?






> I'm running a script with a cursor; appending, updating inserting rows in
> multiple tables. on my local pc it takes about 3 minutes in sql server
> 2005
> Query screen. It takes 20minutes on the server. The database is in 2005
> converted to from 7.0 to 8.0. on the server it's converted from 7.0 to
> 9.0.
> Same database. i just did a restore of a backup from sql server 7.0
>
> is there a setting i missed?
>
>


 
 
mike





PostPosted: Mon Nov 06 00:47:14 CST 2006 Top

SQL Server Developer >> Slow on Server but fast on local pc The version on the server i Microsoft SQL Server 2005 - 9.00.2047.00" and on
my local pc the version is "Microsoft SQL Server 2000 - 8.00.2039 (Intel
X86)"

thanks. yes, i updated stats. i haven't looked at a plan yet.

The script is exactly the same. why should there be a difference between
machines?



mike
> converted to from 7.0 to 8.0. on the server it's converted from 7.0 to
> 9.0.
What does the above mean? Converted from SQL Server7 to SQL Server2000 and
from SQLServer7 to SQLServer2005?

Did you run update statistics after upgraiting? Have you compare execution
plans for both scripts?






> I'm running a script with a cursor; appending, updating inserting rows in
> multiple tables. on my local pc it takes about 3 minutes in sql server
> 2005
> Query screen. It takes 20minutes on the server. The database is in 2005
> converted to from 7.0 to 8.0. on the server it's converted from 7.0 to
> 9.0.
> Same database. i just did a restore of a backup from sql server 7.0
>
> is there a setting i missed?
>
>



 
 
Uri





PostPosted: Mon Nov 06 00:57:32 CST 2006 Top

SQL Server Developer >> Slow on Server but fast on local pc Mike

But you have different version of SQL Server itself. How would you compare
perfomance of two cars engine capacity 1.6 and 2.0 ?




> The version on the server i Microsoft SQL Server 2005 - 9.00.2047.00" and
> on
> my local pc the version is "Microsoft SQL Server 2000 - 8.00.2039 (Intel
> X86)"
>
> thanks. yes, i updated stats. i haven't looked at a plan yet.
>
> The script is exactly the same. why should there be a difference between
> machines?
>


> mike
>> converted to from 7.0 to 8.0. on the server it's converted from 7.0 to
>> 9.0.
> What does the above mean? Converted from SQL Server7 to SQL Server2000
> and
> from SQLServer7 to SQLServer2005?
>
> Did you run update statistics after upgraiting? Have you compare execution
> plans for both scripts?
>
>
>
>


>> I'm running a script with a cursor; appending, updating inserting rows in
>> multiple tables. on my local pc it takes about 3 minutes in sql server
>> 2005
>> Query screen. It takes 20minutes on the server. The database is in 2005
>> converted to from 7.0 to 8.0. on the server it's converted from 7.0 to
>> 9.0.
>> Same database. i just did a restore of a backup from sql server 7.0
>>
>> is there a setting i missed?
>>
>>
>
>
>


 
 
mike





PostPosted: Mon Nov 06 01:05:41 CST 2006 Top

SQL Server Developer >> Slow on Server but fast on local pc
restore restore converts one database to 9.0 and the other pc leaves it at
8.0.

the process takes 3 minutes using sql 2000 and 2005 with the database at
8.0.

do you think it's a conversion problem when sql restores the database?



Mike

But you have different version of SQL Server itself. How would you compare
perfomance of two cars engine capacity 1.6 and 2.0 ?




> The version on the server i Microsoft SQL Server 2005 - 9.00.2047.00" and
> on
> my local pc the version is "Microsoft SQL Server 2000 - 8.00.2039 (Intel
> X86)"
>
> thanks. yes, i updated stats. i haven't looked at a plan yet.
>
> The script is exactly the same. why should there be a difference between
> machines?
>


> mike
>> converted to from 7.0 to 8.0. on the server it's converted from 7.0 to
>> 9.0.
> What does the above mean? Converted from SQL Server7 to SQL Server2000
> and
> from SQLServer7 to SQLServer2005?
>
> Did you run update statistics after upgraiting? Have you compare execution
> plans for both scripts?
>
>
>
>


>> I'm running a script with a cursor; appending, updating inserting rows in
>> multiple tables. on my local pc it takes about 3 minutes in sql server
>> 2005
>> Query screen. It takes 20minutes on the server. The database is in 2005
>> converted to from 7.0 to 8.0. on the server it's converted from 7.0 to
>> 9.0.
>> Same database. i just did a restore of a backup from sql server 7.0
>>
>> is there a setting i missed?
>>
>>
>
>
>



 
 
Uri





PostPosted: Mon Nov 06 01:27:39 CST 2006 Top

SQL Server Developer >> Slow on Server but fast on local pc
> restore restore converts one database to 9.0 and the other pc leaves it at
> 8.0.

Because , as you said you have two diffrenent vesrions of SQL Server ,
right?







> restore restore converts one database to 9.0 and the other pc leaves it at
> 8.0.
>
> the process takes 3 minutes using sql 2000 and 2005 with the database at
> 8.0.
>
> do you think it's a conversion problem when sql restores the database?
>


> Mike
>
> But you have different version of SQL Server itself. How would you compare
> perfomance of two cars engine capacity 1.6 and 2.0 ?
>
>


>> The version on the server i Microsoft SQL Server 2005 - 9.00.2047.00" and
>> on
>> my local pc the version is "Microsoft SQL Server 2000 - 8.00.2039 (Intel
>> X86)"
>>
>> thanks. yes, i updated stats. i haven't looked at a plan yet.
>>
>> The script is exactly the same. why should there be a difference between
>> machines?
>>


>> mike
>>> converted to from 7.0 to 8.0. on the server it's converted from 7.0 to
>>> 9.0.
>> What does the above mean? Converted from SQL Server7 to SQL Server2000
>> and
>> from SQLServer7 to SQLServer2005?
>>
>> Did you run update statistics after upgraiting? Have you compare
>> execution
>> plans for both scripts?
>>
>>
>>
>>


>>> I'm running a script with a cursor; appending, updating inserting rows
>>> in
>>> multiple tables. on my local pc it takes about 3 minutes in sql server
>>> 2005
>>> Query screen. It takes 20minutes on the server. The database is in 2005
>>> converted to from 7.0 to 8.0. on the server it's converted from 7.0 to
>>> 9.0.
>>> Same database. i just did a restore of a backup from sql server 7.0
>>>
>>> is there a setting i missed?
>>>
>>>
>>
>>
>>
>
>
>


 
 
mike





PostPosted: Mon Nov 06 07:49:18 CST 2006 Top

SQL Server Developer >> Slow on Server but fast on local pc i'll investiage the versions. but, what about the speed of the store proc.
the execution plans look the same.




> restore restore converts one database to 9.0 and the other pc leaves it at
> 8.0.

Because , as you said you have two diffrenent vesrions of SQL Server ,
right?







> restore restore converts one database to 9.0 and the other pc leaves it at
> 8.0.
>
> the process takes 3 minutes using sql 2000 and 2005 with the database at
> 8.0.
>
> do you think it's a conversion problem when sql restores the database?
>


> Mike
>
> But you have different version of SQL Server itself. How would you compare
> perfomance of two cars engine capacity 1.6 and 2.0 ?
>
>


>> The version on the server i Microsoft SQL Server 2005 - 9.00.2047.00" and
>> on
>> my local pc the version is "Microsoft SQL Server 2000 - 8.00.2039 (Intel
>> X86)"
>>
>> thanks. yes, i updated stats. i haven't looked at a plan yet.
>>
>> The script is exactly the same. why should there be a difference between
>> machines?
>>


>> mike
>>> converted to from 7.0 to 8.0. on the server it's converted from 7.0 to
>>> 9.0.
>> What does the above mean? Converted from SQL Server7 to SQL Server2000
>> and
>> from SQLServer7 to SQLServer2005?
>>
>> Did you run update statistics after upgraiting? Have you compare
>> execution
>> plans for both scripts?
>>
>>
>>
>>


>>> I'm running a script with a cursor; appending, updating inserting rows
>>> in
>>> multiple tables. on my local pc it takes about 3 minutes in sql server
>>> 2005
>>> Query screen. It takes 20minutes on the server. The database is in 2005
>>> converted to from 7.0 to 8.0. on the server it's converted from 7.0 to
>>> 9.0.
>>> Same database. i just did a restore of a backup from sql server 7.0
>>>
>>> is there a setting i missed?
>>>
>>>
>>
>>
>>
>
>
>



 
 
bobk





PostPosted: Sun Nov 12 18:24:01 CST 2006 Top

SQL Server Developer >> Slow on Server but fast on local pc I'm seeing a dramatic speed difference with cursors also.
- Processing speed for table-scope operations is equivalent
- Same version: Microsoft SQL Server 2005 - 9.00.1399.06
- Same OS: 2003 Server
- Databases are restored from a common backup file

I'm not saying the problem is cursor related, it's just the easiest way to
manifest the problem. I actually found the problem in an ado update that was
processing a record at a time. Thus the problem seems to be related to
iterative processing,(single record inserts in my case).