Posted by Erland Sommarskog on 02/10/07 16:55

Merennulli (maross@sdf.lonestar.org) writes:
> Querying it, it takes about 15-20 seconds to do this:
> Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory
>>From View
> Where Region = 58
> and ConcatenatedHistory like '%11/28/2006%' or ConcatenatedHistory
> like '%2007X%' ;

I completely agree with Dan that normalising ConcatenatedHistory into
its own table, would give you better performance. But not know the
tables or indexes its difficult to say why your attempt failed.

The one thing I can suggest to improve the speed of the current
query is that you add a COLLATE clause to force a binary collation:


ConcatenatedHistory LIKE '%2007%' COLLATE Latin1_General_BIN

this is particular important if you use a Windows collation or your
column is varchar.

> Select View.ID, View.Name, View.Region, View.etc, View.Data,
> View.Data2, History.Date, History.Code
> From View inner join History on History.ID = View.ID
> Where View.Region = 58
> and History.Date = '11/28/2006' or History.Code = '2007X';

I don't really know what this code is. Isn't that just a date or
rather a period? And is that really the WHERE clause? Or should it
be:

Where View.Region = 58
(and History.Date = '11/28/2006' or History.Code = '2007X')



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]