SQL Slow from MSDE 2000 to SQL Express

#11

On Feb 4, 5:10 am, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

HC (hboo... (AT) gte (DOT) 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... (AT) sommarskog (DOT) 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

#12

HC wrote:

Quote:

I posted this in one of the VB forums but I'm starting to think it
might be more appropriate to have it here, since it really seems to be
a SQL server (MSDE/Express 2005) problem:

Hey, all, I have a problem with queries against a SQL server instance
that I just found and is causing me fits. I hope someone can point
me
in the right direction, please. TIA.

Basically, I got a Vista OS machine to test my VB6 app on it as some
of my clients will be switching over in the coming months. I went to
a local Circuit City during early business hours in the middle of the
week and I installed my application on each of 5 PC's on the new
Vista
OS (Tuesday, when it was released). I had read that MSDE 2000, which
I normally use as my DB is not supported on Vista so I had downloaded
and was using SQL Express 2005. Each system had at least a 1.9 GHz
dual core processor and 1 GB of RAM. One process in my program finds
records in one table that do not match records in another table and
then reports those un-matched entries. On my development machine
(laptop with 1 GB of RAM, XP Pro SP2, MSDE 2000 (current SP), 2 GHz
Centrino (IIRC)) the process takes less than 30 seconds consistently.
On each of those 5 systems at Circuit City the process took 5 minutes
(on each of 3 HP machines, a1700n, a1720n, a1730n, and 11 minutes on
each of two Gateway systems (the model numbers of which I forget at
the moment). Each of these computers should be much faster than my
laptop, and some had twice the RAM, and all had SATA or SATA II
drives
instead of my piddly 5400 laptop drive, I would have thought they'd
all be faster but were abysmally slow.

So, seeing a huge difference in the time, and to try to keep this
short and sweet, I fired up another computer I have, running XP SP2,
on 512 MB RAM, AMD Athlon 2300+. First I loaded MSDE 2000 and my
application and ran the process. < 30 seconds on each of multiple
runs. Second, I unloaded MSDE 2000 and installed SQL Express 2005
and
moved the DB to it (sp_attach_db) which caused some upgrading
(messages reported in OSQL about update/upgrade). When it was done I
rebooted, to be sure, and the ran the program and the process again.
On the same data, on the same computer, the process took 7-9 minutes
consistently on each of several runs. This makes this part of the
application unusable, and even the simple stuff like grabbing a
single
record from the DB (maybe 5 columns of no more than 500 bytes total)
is noticeably slower on the SQL Express 2005 than on MSDE 2000.

So, the problem seems to be with my interaction with the DB. I am
using ADO 2.8 in VB 6 (SP 6). I use DSN-less connections with a
connection string like: Driver={SQL Server};server=(local)
\caredata;database=caredata;Uid=sa; Pwd=<password

Why are you using an ODBC connection string? OLEDB or the SQL Native
Client should be faster.

http://www.carlprothman.net/Default....erForSQLServer

<snip>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SQL Slow from MSDE 2000 to SQL Express

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Re: SQL Slow from MSDE 2000 to SQL Express - 02-04-2007 , 10:29 AM

Re: SQL Slow from MSDE 2000 to SQL Express - 09-13-2007 , 01:12 PM