January 17, 2010 at 2:13 am
Hi,
I want to create an index for the following query-
select IMSI_No as imsi from VU_CCAPS where substring(ICCID_No, 1, 18) = 'SIM#18 digit'
the view and corresponding table script is given below-
CREATE VIEW [dbo].[VU_CCAPS]
AS
SELECT IMSI_No, ICCID_No, Mobile_No
FROM dbo.tbl_SIM_Master
GO
CREATE TABLE [dbo].[tbl_SIM_Master](
[IMSI_No] [varchar](16) NOT NULL,
[ICCID_No] [varchar](22) NULL,
[Mobile_No] [varchar](12) NULL,
[IMSI_Type] [varchar](6) NULL,
[Product_Code] [varchar](10) NULL,
[Item_Code] [varchar](10) NULL,
[SKIT_No] [varchar](30) NULL,
[PIN1] [varchar](10) NULL,
[PIN2] [varchar](10) NULL,
[PUK1] [varchar](10) NULL,
[PUK2] [varchar](10) NULL,
[Print_Date] [datetime] NULL,
[Pckg_Date] [datetime] NULL,
[Deliv_Date] [datetime] NULL,
[SIM_Type] [varchar](2) NULL,
[SIM_Status] [varchar](10) NULL,
CONSTRAINT [PK_tbl_SIM_Master] PRIMARY KEY CLUSTERED
(
[IMSI_No] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_ICCID_No] ON [dbo].[tbl_SIM_Master]
(
[ICCID_No] 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) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_Mobile_No] ON [dbo].[tbl_SIM_Master]
(
[Mobile_No] 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) ON [PRIMARY]
GO
January 17, 2010 at 4:34 am
So what problems are you experiencing?
January 17, 2010 at 4:43 am
select query does not use indexes as there is a substring function and is slow.
I want to use index so that it become faster.
please suggest.
thanks
January 17, 2010 at 4:46 am
should I create another index based on the view?
In that case please send me the index creation steps.
Or is there any other way to make the query faster.
thanks
January 17, 2010 at 9:19 am
You could use either
where ICCID_No = 'SIM#18 digit'
which assumes an exact match or you could use this
where ICCID_No LIKE 'SIM#18 digit%'
that version assume that there are multiple variations after the 18 digits of the search parameter.
Both these version could use an index seek. Both that would also mean that a bookmark lookup would be required to fetch the rest of the columns you need in the select. To circumvent this you could include those 2 extra columns in the index idx_ICCID_No making it a covering index.
Also you could consider not using the view at all since you apparently don't need the 3rd column in the view. A straight select with both columns in the index would do the job.
January 17, 2010 at 10:02 pm
It is not possible to remove substring function because of application behavior.
Also you could consider not using the view at all since you apparently don't need the 3rd column in the view. A straight select with
both columns in the index would do the job.
>>can you please explain detail?
thanks
January 18, 2010 at 3:29 am
Why can't you remove substring? Do you start the search at different places in the column at every run?
Look up for articles on covering indexes on this forum to understand why I suggested that.
January 18, 2010 at 4:00 am
Hi,
thanks a lot.
it will work fine with "like".
January 21, 2010 at 10:55 am
Creating a computed column with the substring on the underlying table and indexing that and using it in the where clause instead of the original column should also work.
Andrew
January 21, 2010 at 11:52 am
Yes that would work, but since the substring starts at character 1, it would make no difference other than adding page writes, reads and CPU.
Excluding the start character and assuming static number of characters, then it would be a great idea.
January 22, 2010 at 10:38 pm
Hi andrew,
Creating a computed column with the substring on the underlying table and indexing that and using it in the where clause instead of the original column should also work.
>>how can do that? can you give me an example with my query?
January 23, 2010 at 3:43 am
You don't need to in this case and that's what you must understand.
Once you get that go in the books online and type computed columns and you'll get all the info you need.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply