Prev: setting [] in like operator
Next: What is the best way to write a sp to insert a row having more than 200 columns?
From: SnapDive on 11 Jul 2010 20:15


I have the following query from a single table with 50,000 rows

select col1,col2,col3,col4,col5,col6 from mytable
where
col3<2 and col4 is null
or col3<2 and col4 is not null and col5 is null and
datediff(hour,col6,sysdatetimeoffset() ) > 1

col3 is type int (could be a smallint or a tinyint, but it is an int)
col4 is type datetimeoffset
col5 is type datetimeoffset
col6 is type datetimeoffset

col3 usually has a value between 0 and 10
col4/5/6 are nullable and usally have a value between 3 months ago and
3 months from now.


I would like to rewrite my query to get the best filtering, but the
datetimeoffset types and nullability are throwing me off.

Can anyone suggest some ideas?

Thanks.



From: Erland Sommarskog on 12 Jul 2010 04:24
SnapDive (SnapDive(a)community.nospam) writes:
> I have the following query from a single table with 50,000 rows
>
> select col1,col2,col3,col4,col5,col6 from mytable
> where
> col3<2 and col4 is null
> or col3<2 and col4 is not null and col5 is null and
> datediff(hour,col6,sysdatetimeoffset() ) > 1
>
> col3 is type int (could be a smallint or a tinyint, but it is an int)
> col4 is type datetimeoffset
> col5 is type datetimeoffset
> col6 is type datetimeoffset
>
> col3 usually has a value between 0 and 10
> col4/5/6 are nullable and usally have a value between 3 months ago and
> 3 months from now.
>
>
> I would like to rewrite my query to get the best filtering, but the
> datetimeoffset types and nullability are throwing me off.

Why is the condition on col3 there twice?

select col1,col2,col3,col4,col5,col6 from mytable
where col3<2
and (col4 is null or
col4 is not null and
col5 is null and
datediff(hour,col6,sysdatetimeoffset() ) > 1)

Is probably as good as you can get. This assumes that there is an index
on col3 and that the index is selective enough.

Possibly a rewrite with UNION ALL could help, but that is far from certain.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: setting [] in like operator
Next: What is the best way to write a sp to insert a row having more than 200 columns?