Then why is the performance fast if i go directly against the employer table instead of view.

For e.g.

select * from Myview where display like '%aarp%'

returns 26706 rows in 51 sec

select...

Thanks for all the suggestions.

Changing the date condition did not help improve the performance.

Here are the DDL and index details of the table

Table Case1

------------

CaseID varchar(15)

,Emp_ID varchar(15)

,Segment varchar(10)

,dischargedt date

,discharge_entered_dt date

Indexes...

Wildcard search needs to be done. If I do a value% it will do BeginWith.

discharged_entered is a date field.

Yes, that worked. Thanks!

So I created indexed views and it is working great when data is grouped by year, month or quarter.

here is my indexed view.

SELECT

a.MinorMkt as MinorMkt

...

I created a indexed view as suggested earlier and need help with that.

Here is my indexed view.

ALTER VIEW dbo.vFact WITH SCHEMABINDING

AS

SELECT a.MinorMkt

, SUM(ISNULL(admits,0)) AS...

Sorry i missed that. Here you go..

CREATE TABLE [dbo].[MinorMkt](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Region] [varchar](20) NULL,

[MinorMkt] [varchar](350) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Hosp_UID_Xref](

[UID_v3] [varchar](20) NOT NULL,

[Prov_Key] [varchar](25) NOT NULL,

[HospName] [varchar](100)...

I had posted the ddl /indexes yesterday at 10:35AM

Guys - If I run the query without any function it still takes a long time to execute. So use of function is not the issue here.

Any other suggestions?

Thanks

KK

Following is the code for the function. For the previous query i had stripped this function and was directly running the IN clause. I did not notice any difference in...

To give you an idea of the issue, if I run this query it takes 39 sec. All the required indexes are in place.

I have also attached the query plan

SELECT...

I am sorry for not posting the performance problem in the recommended way. Please see below the detailed information

-- This is the main table it contains around 6 million rows.

--...

Yes, there is a stored procedure that build dynamic sql. This query above is just a PRINT of the sql that gets executed.

If you look at the query, there are...

to give you an idea of type of queries that are being run

SELECT minormkt

,Sum(admits) as 'Total Admits' ,Sum([days]) as 'Total Days' , sum(OppAdm) as 'Opp Adm'

...