-1

We have the following code:

select * from View1 where (Timestamp >= @x) and (SomeCode like 'ABC%')

It runs terribly slow. But the code

select * from View1 where (Timestamp >= @x)      (*)

runs pretty fast. Also SomeCode like... filter runs pretty fast on the previous (*) code. So, it's fast when two-phased. (View1 is a CLR computed view.)

Question: how to advice SQL Server 2008 R2 to make the query (more precisely, the two filters) in two phases, that is, first the Timestamp filter and then the SomeCode filter.

Note: Nesting the queries doesn't work for us, it's slow as well.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Cartesius00
  • 23,584
  • 43
  • 124
  • 195
  • 1
    Is `View1` indexed? If not, are the tables underlying `View1` indexed? – Jodrell Oct 22 '12 at 15:22
  • and what does `(*)` mean in a `where` clause? – Jodrell Oct 22 '12 at 15:24
  • (*) is a label in the text, not SQL. – Cartesius00 Oct 22 '12 at 15:24
  • Indexes won't help: LIKE alwasy has to do a full table scan. – egrunin Oct 22 '12 at 15:24
  • 1
    What is a "CLR Computed View" http://www.google.co.uk/#hl=en&output=search&sclient=psy-ab&q=CLR+Computed+View&oq=CLR+Computed+View&gs_l=hp.3...1203.6188.0.6594.17.15.0.2.2.0.281.2421.1j11j3.15.0.les%3B..0.0...1c.1.57XFV1eLfCk&pbx=1&bav=on.2,or.r_gc.r_pw.r_qf.&fp=f7f26bd304355f44&bpcl=35466521&biw=1280&bih=841&safe=active – Jodrell Oct 22 '12 at 15:25
  • 1
    @egrunin, that is not correct. Using a `starts with` like will allow you to use indexes. If he were doing a `contains` or `ends with` like you would be correct. – Abe Miessler Oct 22 '12 at 15:28
  • @egrunin http://stackoverflow.com/questions/1388059/sql-server-index-columns-used-in-like – Jodrell Oct 22 '12 at 15:28
  • 1
    @egrunin - Not true. If the `LIKE` is used without the `%`, then an index can be used, cause is identical to an `=` (`WHERE a LIKE 'hello'`), it may even use an index if the first part is missing an `%` (`WHERE a LIKE 'hello%'`) – Lamak Oct 22 '12 at 15:29
  • I misspoke: *wildcards* - which his example uses - force a table scan. – egrunin Oct 22 '12 at 15:43
  • 1
    @egrunin - And then, it should be *wildcards at the beggining of the string* force a table scan – Lamak Oct 22 '12 at 15:52
  • @egrunin: again: **NO**. Since it's a `LIKE 'ABC%'` **without** a leading `%` - an index on that column **will definitely** be used - if it makes sense. `LIKE` is only bad when using leading `%` - `LIKE '%ABC%'` would be horribly bad... – marc_s Oct 22 '12 at 16:00
  • 2
    @marc_s : very clear. I'll leave my erroneous remarks in as an example for others to see :) – egrunin Oct 22 '12 at 17:22

1 Answers1

-2

There are probably better ways, but this one works for me:

SELECT * from (SELECT * FROM View1 WHERE Timestamp >= @x) x
WHERE field_x LIKE 'ABC%'

This forces the fast query to be run first.

Responding to the comments:

Maybe "force" isn't the right word, but I've definitely observed it making a difference. I'll revise this if someone has a better suggestion.

Responding to your comment:

"Doesn't work" = still slow?

In which case: is the Timestamp column indexed? Is SQL Server running short on resources (memory, disk space) with the larger query? Have you looked at the execution plan?

What happens if you really force the issue:

SELECT * into #tmp FROM View1 WHERE Timestamp >= @x
SELECT * from #tmp WHERE field_x LIKE 'ABC%'
egrunin
  • 24,650
  • 8
  • 50
  • 93