Posted by Dan Guzman on 02/09/07 13:04

I expect that the normalized approach can perform much better than parsing
ConcatenatedHistory using LIKE as long as you have the proper indexes in
place and tune your queries. However it's difficult to make recommendations
without the actual DDL of your existing objects.

> 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';

You might be able to reformulate this query as something like the example
below:

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'
UNION
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.Code = '2007X';


--
Hope this helps.

Dan Guzman
SQL Server MVP

"Merennulli" <maross@sdf.lonestar.org> wrote in message
news:1170979522.693854.323490@s48g2000cws.googlegroups.com...
> To start with, I'll give a simplified overview of my data.
>
> BaseRecord (4mil rows, 25k in each Region)
> ID | Name | Region | etc
>
> OtherData (7.5mil rows, 1 or 2 per ID)
> ID | Type(1/2) | Data
>
> ProblemTable (4mil rows)
> ID | ConcatenatedHistory
>
> The concatenated history field is a nvarchar with up to 20 different
> pipe delimited date/code combinations, eg. '01/01/2007X|11/28/2006Q|
> 11/12/2004Q|'
>
> Using left outer joins (all from base, the rest optional) I've got a
> view something like:
>
> View (4mil rows)
> ID | Name | Region | etc | Data | Data2 | ConcatenatedHistory
>
> 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%' ;
>
> Or to do this:
> Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory
>>From View
> Where Region = 58;
>
> But this takes over a minute:
> Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory
>>From View
> Where Region = 58
> and ConcatenatedHistory like '%test%' ;
>
> What puzzles me most is that it's taking longer to return nothing.
>
> I've tried normalizing this concatenated field into it's own table, or
> into 20 and 40 denormalized fields. The denormalized fields were
> nightmarishly long queries for a web interface at 5-6 minutes.
>
> The normalized table should have roughly 25mil records, but cutting it
> down to just the most relevant years let me play with it at 9.5mil
> records. This shifted the results to where it took 35-40 seconds to do
> ANY query against that table.
>
> 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 also tried reducing this table down to a linking table between ID
> and Code, and pushing the date off to another table, but that only
> made things worse.
>
> ~~~
> Going back to what worked best (the intial View), the Execution Plan
> shows 93% on a Clustered Index Scan on the ID field's index in the
> ConcatenatedHistory table for the problem query, but spreads out the
> load fairly evenly among indexes on the successful query. I'm trying
> to figure out a way to improve performance, and more importantly, make
> "0 records found" responses be a bit more forthcoming.
>
> If it's relevant, I'm on SQL Server 2005 Standard, and I've already
> taken care of the memory, CPU and drive optimization.
>

[Back to original message]