Posted by HC on 02/04/07 16:29

On Feb 4, 5:10 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> HC (hboo...@gte.net) writes:
> > Erland, I copied all the DB files from the MSDE 2000 installation to
> > the SQL Express 2005 system. The EXE (my program) is exactly the same
> > on both systems, the databases are identical (I did a fresh copy over
> > this morning in preparation for more testing so I'm quite certain they
> > are the same). The files I copied are the MDF and the LDF files and I
> > referenced them both in the sp_attach_db statement (sp_attach_db
> > 'dbname', 'c:\dbdatafile.mdf', 'c:\dblogfile.ldf').
>
> Great. I just want to make sure that you had not lost the SQL 2000
> databases, so that you still have those to compare with.
>
> > I'm sorry to be ignorant but I do not recognize the parameters you
> > mention about the update stats, I will check BOL for this. I ran this
> > on each db (use <db>, go, SP_UPDATESTATS, go).
>
> You run UPDATE STATISTICS tbl WITH FULLSCAN for each table. sp_updatestats
> runs UPDATE STATISTICS for table, but without FULLSCAN, which means that
> it only samples data. For the small sets of data you mention, FULLSCAN or
> not may be be a big deal.
>
> Another way is to reindex all tables. When you reindex a table, statistics
> are updated as with fullscan, as SQL Server has to read all rows anyway.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Erland, thank you for your reply and for the command.

I have done the UPDATE STATISTICS tbl WITH FULLSCAN for each of two
tables that I have been able to reproduce the slowness on but it did
not make a difference in the performance. I started another thread
about that problem now that I think I've isolatedthe problem from my
program and a huge query. A simple query (join 12 records from one
table of 630 records to one table of 2,700 or so records, one-to-many
from the second table, so it only matches 12 records from there) takes
over a second, even after update statistics.

The same query, run from that same machine against my MSDE 2000 system
takes well under 100 ms.

Thank you for your help.

--HC

[Back to original message]