|
|
Posted by HC on 02/04/07 09:06
Hello, all, I started out thinking my problems were elsewhere but as I
have worked through this I have isolated my problem, currently, as a
difference between MSDE and SQL Express 2005 (I'll just call it
Express for simplicity).
I have, to try to simplify things, put the exact same DB on two
systems, one running MSDE and one running Express. Both have 2 Ghz
processors (one Intel, one AMD), both have a decent amount of RAM
(Intel system has 1 GB, AMD system has 512 MB), and plenty of GB of
free disk space. MSDE is running on the Intel system, Express is
running on the AMD system. To keep things fair I use the exact same
DB's and query on both systems. The DB's were created on MSDE so I
sp_detach_db'd them from MSDE and then sp_attach_db'd them to Express
(this is how MS says to do a "side-by-side" upgrade, so it's
acceptable to do so). After fighting problems in performance
differences in different situations I have narrowed the problem down
to this:
Executing a simple select statement with join clause on the databases
yields a difference in execution time that is quite great. Using the
Express Management program I can run the query against either system
(MSDE or Express, the two systems are connected via crossover cable to
eliminate any network problems/issues). When running the query
against the MSDE system (which is over the network) I consistently get
<20 ms response times on the query. When running the query against
the Express installation (which is in shared memory) I consistently
get 700 ms or longer response times. Both times are for the Total
Execution Time.
The query is simply this: select db1.* from db1.owner.tablename as db1
inner join db2.owner.tablename as db2 on db1.pkey = db2.someid where
db1.criteria = 3
So, gimme all the columns from one table in one DB (local to the
installation), matching the records in another DB (also local to the
installation), where one field in the first db matches a field in the
second db and where, in the first db, one column value = 3.
The first table has a total record count of 630 records of which only
12 match the where clause. The second table has a total record count
of about 2,700 of which only 12 match up on the 12 out of 630.
Even though the data is the same and I've done the detach and attach,
and even done the sp_updatestats, the difference in execution time is
remarkable, in a bad way.
Checking the Execution Plan reveals that both queries have the same
steps, but, on the MSDE system the largest consumer in the process is
the Clustered Index Scan of the 630 record table (DB1 in my query
example), using 85%. The next big consumer is a Clustered Index Seek
against the other table (2,700 rows), using 15%.
The Execution Plan against the Express system reveals basically the
exact opposite: 27% going to the Clustered Index Scan of the 630
record DB1, and 72% going to the Clustered Index Seek of the 2,700
record DB2.
I'm sorry to be stupid but I have this information but I don't know
what to do with it. The best that I can tell from this is that this
is the source of my problems. My problems are that on my current
systems that my clients use the data is returned to them faster than
they can click the mouse and that the new system (that is, when they
chose (or are forced by attrition) to move to Vista and thus Express
2005) the screen pop is like 1.5 seconds. This creates poor user
experience. Worse, one process I allow the users to do goes from
taking 14-30 seconds to over 4 minutes (all on the same machine with
the same OS and version of my program, so it's not a machine or OS or
my app problem).
Anyway, I hope someone can shed some light on this now that I've pared
it down some.
Thanks in advance.
--HC
Navigation:
[Reply to this message]
|