Posted by Erland Sommarskog on 04/24/07 22:28

Dmitri (nienna.gaia@gmail.com) writes:
> I have a stored procedure that takes 22 minutes to run in one
> environment, that only takes 1 sec or so to run in another
> environment. Here is the exact situation:
>
> Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
> exactly the same, and the tables and index structures are exactly the
> same. Implicit transactions are turned off on both databases.

Apparently the query plans are different. This could be because there
are differences in statistics between the databases. Fragmentation
could also matter. I would recommend that you run DBCC DBREINDEX on
the tables in both environments. If you are lucky, the query runs
quickly in both databases. If you are less lucky, the query will now
run slowly in both databases.

If the machines has a different number of processors, this could also
matter. Maybe one machine is a single-CPU machine, whereas the other
is an 8-way box, so there is a parallel plan on server and a non-parallel
plan on the other. Parallel plans are sometimes really amazing -
either amazingly fast or amazingly slow.

> --step 3
> UPDATE a
> SET rating=AVG(someValues)
> FROM myTable a
> JOIN otherTable b
> ON a.column1=b.column1
> GROUP BY someColumns

Not that it matters for the discussion since I don't see the table
definition and the indexes, but this syntax is not legal.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]