Posted by HC on 02/04/07 19:59

On Feb 4, 12:47 pm, "HC" <hboo...@gte.net> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

Okay, after messing with indexes and some other stuff I'm no better
off than I was. I did a select * into tbl_2 to make copies of the
tables inside the same server (Express). I then ran the query against
those two new tables and the first time I did it they were blazing
fast. I ran they query in Management Express with the stats displayed
and it ran in < 100ms several times. I ran the query against the
original tables (in the same DB) and they were similarly fast. I
dropped the second (new) tables and re-created them in the same way
and ran the query but it's slow (> 1000 ms). I've tried so many
angles I'm losing track of them.

So, with the copies of the tables (presumably with fresh indexes and
stats) not being any faster I thought I'd mess with the indexes.

I dropped the primary key constraint on the med orders table (the one
with 630 rows) and applied a clustered index on the resident id (which
is the piece I'm using in the WHERE clause) and a non-clustered index
on the med id (which I'm referencing in the ON clause. So, two new
indexes on what should be the items that are being sought. No
difference in performance. I ran them several times in case there
might be some caching that might make a difference but no change.

I don't know what else to index the med table on, the only criteria I
seek it on in this query is the Primary Key for the table which is, by
it's very nature, a clustered index.

--HC

[Back to original message]