You are here: Re: Optimizing a big query « MsSQL Server « IT news, forums, messages
Re: Optimizing a big query

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

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация