x

Will this make my query faster or slower?

I have a table where the data I really want is being stored in a text field (which I am getting use "Like"). However you can filter to a super-set of that data with a straight number comparison. If I have both filters in my where clause, do I have any control over the order in which the filters are applied. (IE: Have it filter to numbers then do the text search?)

more ▼

asked Jan 15, 2010 at 03:24 PM in Default

Aaron Bush gravatar image

Aaron Bush
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

It is the optimizers job to decide which is the fastest method to return the data. So no you dont get control directly , but you will influence the decisions it makes by using indexes etc.

more ▼

answered Jan 15, 2010 at 03:33 PM

dave ballantyne gravatar image

dave ballantyne
928 1 1 4

Agreed. If you index that number field, the optimizer will undoubtedly choose it over scanning a text field. I know I would. :)
Jan 15, 2010 at 05:15 PM Tom Staab
It depends! The index on the INT column will only be used if the number field is selective enough. I am sure sql wouldn't use the index if it contains only two distinct values.
Jan 18, 2010 at 06:23 PM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

Actually you can force a specific execution plan in at least two ways:

  • with a table hint "INDEX ( index_value [ ,...n ] )"
  • forced execution plan with "USE PLAN N'xml_plan'" (SQL server 2008)

But i do not recommend you to try to force SQL to ignore the "optimizer" unless you know you are much more intellegent than the developers of the SQL optimizer. It is easy to think that this execution plan is the best, but are you sure? What will happen when your data is changed? Maybe your number column is not selective enough and you force it to use that index? Then you end up with an execution plan that is outperformed of other possible execution plans that SQL optimizer wants to use.

for more information see:

http://msdn.microsoft.com/en-us/library/cc917694.aspx

http://technet.microsoft.com/en-us/library/ms187373.aspx

more ▼

answered Jan 18, 2010 at 06:38 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 35 37 48

(comments are locked)
10|1200 characters needed characters left

I would agree with our friends. Before I made a comment I would like to see you T-SQL statement. Can you please share your query with us?

Raj

more ▼

answered Jan 16, 2010 at 04:01 AM

Raj gravatar image

Raj
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x371
x36

asked: Jan 15, 2010 at 03:24 PM

Seen: 1934 times

Last Updated: Jan 15, 2010 at 03:24 PM