Posted by Erland Sommarskog on 02/04/07 23:38

HC (hboothe@gte.net) writes:
> 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?

There should be an index in resid. If the major part of the queries
against tbl_medicationorders are against resid, then it's probably a
good idea to cluster on this column. (You would then have to drop
the primary key, and the reapply it as nonclustered.)

> 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.

Well, since you only return data from tMO, this may be even better:

SELECT tMO.*
FROM caredata_aa.dbo.tbl_medicationorders tNO
WHERE tMO.resid = 43
AND EXISTS (SELECT *
FROM caredata.dbo.tbl_medications tM
WHERE tMO.medid = tM.pkey)

Not that it is likely to affect performance, but you would avoid
returning the same row twice from meditioncationorders.

> 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.

I'm afraid that they appear as somewhat cryptic to me. I guess that I
could copy them into a text editor and try to decode them, but I'm
lazy. Couldn't you just upload the output as text files there.

> 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.

Ehum, this design sounds dubious. I get the impression that if this was
all in the same database, and the same tables, this could be done in a
single query.

Or is there any particular reason you have multiple databases?

Anyway, it occurred to me, there is one thing to check for. Run this query:

select name from sys.databases where is_auto_close_on = 1

For all databses that appear do:

ALTER DATABASE db SET AUTO_CLOSE OFF




--
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]