|
|
Posted by Erland Sommarskog on 02/04/07 12:12
HC (hboothe@gte.net) writes:
> 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.
Is there any index on the criteria column? It does not sound like
that, since you get a clustered scan on that table.
But before you apply any index, can you run the queries in both servers
preceded by
SET STATISTICS PROFILE ON
and then post the output? Since the output is very wide, it's not good
if you put directly into the article body, please put it in an attachment.
I see that you post from Google. I don't know if they permit attachments,
but if they don't, maybe you could put the output on a web site and just
post a URL?
> 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).
I suppose this process is against some completely different tables?
14 seconds on the table sizes you mentioned sounds absymal to me.
--
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]
|