How to make query use an Indexed View ?

[Connatic]

I had a problem query (taking to long to execute) that uses the Like operator, and have found that when i copied all my existing tables into a new database & Ran the Index Tuning Wizard, this solved my speed issue with my query.

My problem is, running the index tuning wizard on my original tables does nothing !!! (and yes all the tables, relationships, indexes e.t.c are identical, believe me i have checked)

If i could make my original database use the Indexed View that has been created (by the index Tuning Wizard) for my query, all my problems would be solved, unfortunatly i don't know how!!

Does anybody have any ideas, or suggestions on what i could do about this ????

n.b - I am more than happy to post Execution plans for the queries (the same query, but different execution plan, depending on which database it is being run on)

Any help or suggestions would be grately appreciated !

 

[HRoarke]

Script out the CREATE TABLE SQL for the tables used by the view from the new database. Then, execute the CREATE INDEX statements against the same tables in your original DB. This will create the same indexes on the original table that the tuning wizard created on your new DB.


<bw>
"I swear by my life and my love of it that I will never live for the sake of another man, nor ask another man to live for mine."
— John Galt
Atlas Shrugged

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[Connatic]

Have tried your suggestion HROARKE, but it seems that thier is something different about 1 server we are using from the others.

You see i have even gone as far as detaching the database and re-attaching it on two different servers now!, and yet my query runs in 1 second on one server and 6 seconds on two other differn't servers!

Still haven't figured out why yet though, (and i don't think that it's the spec of the server, as i have tried re-attaching the db to 1 of the Live servers which which are of a High spec and the query still runs at 6 seconds !!

Wierd Hey!