|
|
Posted by HC on 02/04/07 18:47
On Feb 4, 6:12 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> HC (hboo...@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, 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- Hide quoted text -
>
> - Show quoted text -
Hmm, I thought I posted this...but it has not shown up in the last 30
minutes so I'm going to send it again, I'm sorry if it winds up
showing twice...
Hello, Erland. I have no user-defined indexes (indices?) in any of my
tables or databases. The only indexes that would exist in any of the
database tables would be those that are by default with primary key
constraints. The actual query I'm running is:
select tMO.* from caredata.dbo.tbl_medications as tM inner join
caredata_aa.dbo.tbl_medicationorders as tMO on tMO.medid = tM.pkey
where tMO.resid = 43
the join clause tMO.medid is matched against the primary key of the tM
table. There is no index for tMO.medid, nor is there one for
tMO.resid. Would it help to index them since the Clustered Index Seek
is being performed against the tbl_Medications table? In my program I
do a LEFT OUTER JOIN instead of an INNER JOIN, but the performance is
the same: slow. So, I just left it at an INNER JOIN to try to keep
things simple.
I ran the query as you asked, against both database engines and have
posted them online. I was having problems with my regular website so
I had to cobble one together for this purpose. It was done through a
web-interface so I just UUE encoded a ZIP file of the text file with
the results. Each db engine's results are in a different file. I
included the query (as seen above), and the statistics. You can find
them here: http://home.earthlink.net/~hboothe/ On that lame page
you'll see links on the left side for the pages dealing with each db
engine.
If you have any problems with the files lemme know and I'll see if I
can find another place to put them.
The 14 second process I refer to (that takes over 4 minutes on
Express) is using the same tables. What I am trying to do is to pare
this stuff down to the smallest piece that exhibits the unwanted
behavior so that the situation is not obfuscated unnecessarily. I
think that once the problem with the little query is fixed, the
problem with the big query will be fixed. The process that takes 14
seconds on these little tables in MSDE and over 4 minutes in Express
is nasty. I have multiple databases. One database is the parent of
the whole system, containing information that is relevant across each
of the other databases. You may notice in the query I've posted that
the tMO table is from one db (caredata_aa.dbo.tbl_MedicationOrders)
and tM is from another (caredata.dbo.tbl_Medications). My program
(system) can support many of the _aa databases (_aa, _ab, _ac, etc.).
The process that takes 14 seconds on MSDE is supposed to report to the
users those medications that are not in use by any medication order in
any of the databases (so, no match from tM.pkey to tMO.medid from any
tMO of which there may be as many as 4 (maximum number currently
used)). Each record must not match across each of multiple db's.
I'm clearly not a SQL Server expert, I'm just a VB programmer, but
since I have my own little business I have to do all the functions.
Since this worked on MSDE so well I thought I'd done a good job of
designing the queries and working with the DB. Obviously since it's
not working well on Express there is a good chance I've just done a
poor job of designing the DB or the queries. Maybe there's something
really simple I've overlooked. Now that I've narrowed the problem
down to one little query that does one simple join, I have eliminated
a lot of the initial potential problem points (VB6, ADO, connection
strings, recordset types, etc.). I'm going to go play with adding
indexes to the tables to see if that makes any difference.
Thank you for your help.
--HC
Navigation:
[Reply to this message]
|