March 28, 2013 at 1:49 pm
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
CaseID Primary Key
Emp_ID (non-unique, non-clustered)
Segment (non-unique, non-clustered)
Discharge_Entered_Dt (non-unique,non-clustered)
Table Employer
---------------
Emp_Id varchar(15)
Segment varchar(10)
name varchar(100)
PrimaryKey on EmpID+Segment
March 28, 2013 at 1:50 pm
kk1173 (3/28/2013)
Wildcard search needs to be done. If I do a value% it will do BeginWith.
Then performance is never going to be very good for this. This is looking through the phonebook to find all last names that contain the letters 'ets'. You have to look at each and every name in the phone book.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2013 at 2:13 pm
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 * from Employer where name like '%aarp%'
returns 1836 rows in 4 sec.
Is it because of the table row counts?
select count(*) from [case1] c
--8615900 rows
select count(*) from [case1] c where c.dischargedt is not null and c.discharge_entered_dt >= cast(DATEADD(DAY, -14, getdate()) as date)
--58269 rows
select count(*) from Employer_Xref
--1008593
In the view i am joining to case1 which has 8615900 rows
March 28, 2013 at 2:22 pm
kk1173 (3/28/2013)
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
CaseID Primary Key
Emp_ID (non-unique, non-clustered)
Segment (non-unique, non-clustered)
Discharge_Entered_Dt (non-unique,non-clustered)
Table Employer
---------------
Emp_Id varchar(15)
Segment varchar(10)
name varchar(100)
PrimaryKey on EmpID+Segment
This isn't DDL, it is a simple description of what we requested. DDL is the CREATE TABLE statements for the tables and the CREATE INDEX statements (if not included as part of creating the tables) for each of the indexes on the tables. We can't cut/paste/run the information you posted in SSMS.
March 28, 2013 at 2:25 pm
kk1173 (3/28/2013)
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 * from Employer where name like '%aarp%'
returns 1836 rows in 4 sec.
Is it because of the table row counts?
select count(*) from [case1] c
--8615900 rows
select count(*) from [case1] c where c.dischargedt is not null and c.discharge_entered_dt >= cast(DATEADD(DAY, -14, getdate()) as date)
--58269 rows
select count(*) from Employer_Xref
--1008593
In the view i am joining to case1 which has 8615900 rows
Think about looking in a phone book for any last names that contain the letters aarp. If you have a small phone book (26706 rows) it will take some time but if you look at a different phone book (8615900 rows) it going to take a LOT longer to look at each and every single row. If you have to use wildcards at the beginning you should look into using fulltext indexing instead of wildcard like searches.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2013 at 2:50 pm
Full Index search will need an index on the view.
And Indexed view will not be a good idea here since the data in case1 table is frequently updated.
March 28, 2013 at 3:01 pm
kk1173 (3/28/2013)
Full Index search will need an index on the view.And Indexed view will not be a good idea here since the data in case1 table is frequently updated.
Then you are kind of stuck with a slow search.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2013 at 3:01 pm
Here you go
CREATE TABLE [case1](
[CaseID] [varchar](15) NULL,
[Emp_ID] [varchar](15) NULL,
[Segment] [varchar](10) NULL,
[dischargedt] [date] NULL,
[discharge_entered_dt] [date] NULL
) ON [PRIMARY]
GO
/****** Object: Index [IX_PK] Script Date: 03/28/2013 13:58:55 ******/
CREATE UNIQUE CLUSTERED INDEX [IX_PK] ON [case1]
(
[CaseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
/****** Object: Index [IX_EmpID] Script Date: 03/28/2013 13:58:47 ******/
CREATE NONCLUSTERED INDEX [IX_EmpID] ON [case1]
(
[Emp_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
/****** Object: Index [IX_Discharge] Script Date: 03/28/2013 13:58:34 ******/
CREATE NONCLUSTERED INDEX [IX_Discharge] ON [case1]
(
[discharge_entered_dt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
CREATE TABLE [Employer](
[Emp_Id] [varchar](15) NULL,
[Segment] [varchar](10) NULL,
[name] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IX_PK] Script Date: 03/28/2013 14:00:35 ******/
CREATE CLUSTERED INDEX [IX_PK] ON [Employer]
(
[Emp_Id] ASC,
[Segment] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
March 28, 2013 at 3:57 pm
CREATE TABLE [case1](
[CaseID] [varchar](15) NULL,
[Emp_ID] [varchar](15) NULL,
[Segment] [varchar](10) NULL,
[dischargedt] [date] NULL,
[discharge_entered_dt] [date] NULL
) ON [PRIMARY]
GO
/****** Object: Index [IX_PK] Script Date: 03/28/2013 13:58:55 ******/
CREATE UNIQUE CLUSTERED INDEX [IX_PK] ON [case1]
(
[CaseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
/****** Object: Index [IX_EmpID] Script Date: 03/28/2013 13:58:47 ******/
CREATE NONCLUSTERED INDEX [IX_EmpID] ON [case1]
(
[Emp_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
/****** Object: Index [IX_Discharge] Script Date: 03/28/2013 13:58:34 ******/
CREATE NONCLUSTERED INDEX [IX_Discharge] ON [case1]
(
[discharge_entered_dt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
CREATE TABLE [Employer](
[Emp_Id] [varchar](15) NULL,
[Segment] [varchar](10) NULL,
[name] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IX_PK] Script Date: 03/28/2013 14:00:35 ******/
CREATE CLUSTERED INDEX [IX_PK] ON [Employer]
(
[Emp_Id] ASC,
[Segment] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
March 28, 2013 at 6:51 pm
using this query instead of using the view directly improved the performance times
SELECT
distinct ID,(Display+'--'+ID) as Display
FROM MyView WHERE
region in ('CT','NE','SE','WE','OT')
and
display in (select [name] from Employer where [Name] LIKE '%aarp%')
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply