Carl Federl (6/8/2009)

---

For the materialized view, you indicate that the actual execution plan is using the base tables rather than the materialized view, which indicates that you are using SQL...

Here are the results of applying Carl's recommendations:

1. Materialized view:

I Created a view with schemabinding option combining id,CountryCode and CityNameAlias. Created clustered index on (CountryCode,CityNameAlias). There was no performance improvement...

Carl,

Thank you very much for your last post. Every single word makes perfect sense.

It's obvious that dealing with your recommendations will take some time, so I'll post the results in...

Carl,

The tables are fresh...

Carl Federl,

The statistics you requested are available here.

I'm ready to post them is CSV/XLS format if required. Just let me know.

Thanks in advance.

Carl Federl,

I'll run the statements later today and provide the result.

Lowell,

as I understand it, the LIKE '%pattern%' will always require a tablescan, since parts of the string are not indexed.

if the pattern is 'pattern%', so the value always starts...

Wayne,

The actual execution plan of the query you propose is the same as with the two quries I posted. Same poor performance ;-(