Strange query optimizer behavior

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 2000 Forums

SQL Server Development (2000)

Strange query optimizer behavior

Author

Topic

kinggheedora
Starting Member

2 Posts

Posted - 2007-08-08 : 02:37:14

I have two tables: user, and user info. They both have the same primary key, consisting of two columns. We have two concepts: clients, and users. Clients can each have their own users. We store all users in the same database table. Let's say the main user table is "User", and the other table with additional user info is "UserInfo". Kind of vertical partitioning.

the PK & clustered index for both tables consists of of the columns client_id, and user_Id.

We have a view that looks like this:

create view UserInformation
as
select
u.client_id
, u.user_id
, u.some_columns
, ui.some_user_info_columns
from
user u
left outer join user_info ui
on u.client_id = ui.client_id
and u.user_id = ui.user__id

Clients can have custom fields defined, and the user_info table has generic varchar columns that mean different things depending on the client_id. Metadata for each client's config of this table is kept elsewhere. This works about 95% of the time.

But we have the problem that some clients store a date in one of the generic varchar columns, and they do a query like this:

select * from userInformation where client_id = @client_id and convert(datetime, <varchar_column_containing_dates>) = '01/01/2007'

We get datetime conversion errors, even though all the values for the specified client_id in the <varchar_column_containing_dates> column can be successfully cast to datetime when queried directly from the user_info table.

I verified that the error was because sql server is not using the clustered index, and this is true when i looked at the query plan it was using. I was able to run the same query by updating the view to also include the client_id column from the user_info table in the view results (under a different name so it wouldn't collide with the client_id from the user table). And in the query the client is trying to run, i add an additional condition to the where clause:

and isnull(ui_client_id,@client_id) = @client_id

This seemed to get SQL server to be able to run the query using the clustered index on user_info (therefore only looking at records for the specified client_id, and therefore not trying to convert data from clients who store non-datetime data in the same column).

Is there a more elegant way of avoiding this problem? I'm worried that if I deploy this "fix" to production, it will work for the examples i've seen, but the query optimizer will start acting up and decide not to use the clustered index (even though it's specified in the on clause of the join, i suspsect the outer join has something to do with this, but not sure why).

Thanks!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-08 : 11:28:12

ALWAYS try to avoid using functions on column names. SQL Server may not use indexes.

Andy why is a date value sitting in a varchar column? There is a datetime datatype available?

Also in your view:


create view UserInformation
as
select 
u.client_id
, u.user_id
, u.some_columns
, ui.some_user_info_columns
from 
user u
left outer join user_info ui
on u.client_id = ui.client_id
and u.user_id = ui.client_id  --<--- Is this a typo? Should it be u.user_id = ui.user_id?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

kinggheedora
Starting Member

2 Posts

Posted - 2007-08-08 : 13:14:19

quote:
Originally posted by dinakar

ALWAYS try to avoid using functions on column names. SQL Server may not use indexes.

Andy why is a date value sitting in a varchar column? There is a datetime datatype available?

Also in your view:

create view UserInformation
as
select
u.client_id
, u.user_id
, u.some_columns
, ui.some_user_info_columns
from
user u
left outer join user_info ui
on u.client_id = ui.client_id
and u.user_id = ui.client_id --<--- Is this a typo? Should it be u.user_id = ui.user_id?

Yes, that was a typo, I fixed the typo in my post.

Any idea why this is happening, or how to prevent it? There is no function used on any column with an index on it, so I don't think that's the problem. As a matter of fact, the opposite is the case -- when I add client_id from the table being left outer joined into the result columns of the view, and then query the view with isnull(client_id, <hardcoded_value>) on that column, the proper index is used.

Datetime is being used because this is a generic table, this is a crm type system and clients can add custom columns to their accounts, columns can be of any type. I didn't write this app, if I did I would have created separate tables for each possible datatype.

Any ideas on this problem? Is my solution stable?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-08 : 13:58:30

You could put an index hint and observe it for a few days, then remove it again and see if the query plan uses the right index after removing. Generally SQL Server corrects its query plans but not always. So forcing it to use an index hint might help.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources