changing index query slow

[Anitivirus]

Hi,
I was running a query before and it was taking around 4-5 minutes to run, I changed index of one lerge table from non clustered to clustered(Primary key), the query took 15 minutes to run.
after that I change the the index to non clustered again and its stil taking 15 minutes to run.

Any help will be highly appreciated.

 

[gmmastros]

You should update statistics.

Also... if you show the query, we may be able to help you speed it up.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Anitivirus]

Thanks George for the quick reply , The statistics are set to auto update.

and the query is using two table valued function which are using another 3 table valued functions.
and those are really lerge enough to post here.

 

[gmmastros]

Do the table valued functions have a primary key in it? If so, you could add a primary key to the return table from the table valued function, which may improve performance. Like this...

Create Function dbo.Blah(@Param1 Int)
Returns @Output Table (Id Int [!]Primary Key[/!], Col2 varchar(10), Col3 VarChar(10))
As Begin
etc.....

Or, if multiple columns make the primary key....

Create Function dbo.Blah(@Param1 Int)
Returns @Output Table (Id Int, Col2 varchar(10), Col3 VarChar(10) [!]Primary Key(Id, Col2)[/!])
As Begin
etc.....

Adding a primary key to the table valued functions will essentially give you a clustered index on it, which will speed up joins and where's.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom