Optimizing queries with OR and IN

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Optimizing queries with OR and IN

Author

Topic

sfortner
Yak Posting Veteran

63 Posts

Posted - 2007-07-12 : 01:14:05

I have 4 tables I'm looking at that are Brands, Models, IRCommands and IRCommandTimes. I'm passing in @Brand and @Model variables to get results. The additional information you need to know is that our Dual Tuner is a device that actually houses two separate devices, Dual Tuner 1 and Dual Tuner 2. As a result, the Models table has all three of these entries, but only Dual Tuner 1 and Dual Tuner 2 have the Models.Parent field set to point to the Models.ID of the Dual Tuner. That's what makes it hard to get around using an OR in the first query. The pertinent schema is
Brands.ID - int
Brands.Name - nvarchar(50)
Models.ID - int
Models.Name - nvarchar(50)
Models.Parent - int pointing to Models.ID, allows nulls
IRCommands.ID - int
IRCommands.Name - nvarchar(50)
IRCommandTImes.ID - int
IRCommandTimes.Command - int pointing to IRCommands.ID
IRCommandTimes.Time - int pointing to Time.ID table

I've compared 1st query A and 1st query B with an estimated execution plan in sql server management studio's query window, and it comes back 0%, 13%, 14% and 73%. I'm surprised. The 2nd line uses clustered index seeks, the 3rd line (1st query A) uses a clustered index scan due to the OR and the 4th line (1st query B) uses a clustered index seek for everything, except the self-join with the m2 alias, which uses a clustered index scan because there's no index on Models.Parent. However, the database engine tuning advisor has no recommedations and putting an index on Models.Parent causes the time to go from 73% to 78%. The sort with the union seems to be bad. Any ideas how to improve?

The second query that gets the IRCommandTimes uses both an IN and an OR. It doesn't seem efficient to me, and uses clustered index scans for all the joins, divided up as 1% for models, 7% for IRCommands, 27% for IRCommandTimes, 11% for a Hash Match (Inner Join) on Models and IRCommands, and 54% for a Hash Match (Inner Join) between IRCommands and IRCommandTimes. Any ideas?

declare @Brand nvarchar(50), @Model nvarchar(50), @ModelID int
select @Brand = 'elan', @Model = 'dual tuner'

select @ModelID = m.ID from models m join Brands b on (m.Brand = b.ID) where m.Name = @Model and b.Name = @Brand

-- 1st query A
select distinct m.* from Models m where m.ID = @ModelID or m.Parent = @ModelID

-- 1st query B
select m.* from Models m join Brands b on (m.Brand = b.ID) where m.Name = @Model and b.Name = @Brand
union
select m2.* from Models m join Brands b on (m.Brand = b.ID) join Models m2 on (m.ID = m2.Parent) where m.Name = @Model and b.Name = @Brand

-- 2nd query
select distinct ct.* from IRCommandTimes ct join IRCommands c on (ct.Command = c.ID) where c.Model in
(select m.ID from Models m where m.ID = @ModelID or m.Parent = @ModelID)

The 1st query A and 1st query B returns:


ID  Name         Brand  Parent
90  Dual Tuner   51     NULL
91  Dual Tuner 1 51     90
92  Dual Tuner 2 51     90

and the 2nd query returns:


ID    Command Time
8069  2000    549
8070  2000    550
8071  2000    551
8072  2000    552
8073  2000    553
8074  2000    554
etc.

--Steve

sfortner
Yak Posting Veteran

63 Posts

Posted - 2007-07-12 : 16:36:56

Anyone with any ideas? Or is this already the best it can be? Due to the small size of the tables, running these two queries with others to get a "device" still only takes 343 to 393 ms, so maybe not much of an issue unless fragmentation occurs or a lot of commands are added.

--Steve

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-07-13 : 06:52:43

It is difficult to know what you are asking.
In general:
1. it is usually worth trying UNION, or if your data allows UNION ALL, when you have an OR.
2. it is worth trying to get rid of DISTINCT.
3. EXISTS is usually better than IN.

So maybe something like the following for query 2:

SELECT *
FROM IRCommandTimes ct
WHERE EXISTS (
        SELECT *
        FROM IRCommands c1
        WHERE c1.[ID] = ct.Command
            AND c1.Model = @ModelID

        UNION ALL

        SELECT *
        FROM IRCommands c2
        WHERE c2.[ID] = ct.Command
            AND EXISTS (
                    SELECT *
                    FROM Models m
                    WHERE c2.Model = m.[ID]
                            AND m.Parent = @ModelID
                )
    )

sfortner
Yak Posting Veteran

63 Posts

Posted - 2007-07-13 : 13:13:37

Good suggestions, but the query cost comparison is still 50% between my 2nd query and your new version. I even removed distinct from mine, but it didn't change anything. One thing that really bothers me, and I guess that's the core of the question, is that I can't seem to get rid of the index or clustered index scans. I put the primary key on the first ID field, and the clustered index on the name field for the Brands and Models table, and put the clustered index on the Name and Model columns for the IRCommands table (because we can have a Play command for different models, and those two columns make it unique). Additionally, we're ordering on the name field, not the ID field, and as I understand it, if you're ordering on a column, that's where you want your clustered index. I ended up putting both the primary key and the clustered index on the ID field in the IRCommandTimes table though, because that should never be ordered, or you lose the integrity of the command.

Is there anything I can do to make it automatically use index or clustered index seeks instead? Or is it not using the indexes because it's faster to scan the table? I'm going to also try forcing it to use the indexes, which is probably not a good idea, but it might be helpful for my analysis. After my initial post, I did run the database engine tuning advisor and it suggested the following index on the IRCommands table, but everything else it suggested caused it to run slower. This new IRCommands index does allow queries on the IRCommands table to use the new index, as you can see it's an index seek in the estimated execution plan. Here's the index, which is used by the query optimizer in your new query as well:


IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[IRCommands]') AND name = N'_dta_index_IRCommands_7_1557580587__K3_K2_1_4_5_6_7_8_9_10_11')
CREATE NONCLUSTERED INDEX [_dta_index_IRCommands_7_1557580587__K3_K2_1_4_5_6_7_8_9_10_11] ON [dbo].[IRCommands] 
(
	[Model] ASC,
	[Name] ASC
)
INCLUDE ( [ID]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

--Steve

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-13 : 14:51:37

Not only do you have to compare execution plans, you should also compare CPU, DURATION and READS number from Profiler.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-13 : 14:52:39

Sample data and expected output would be great!

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources