Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Force Index Query Hints

Author  Topic 

mpolaiah
Starting Member

24 Posts

Posted - 2015-03-03 : 01:25:31
Hi All,

i am using the query
select id From Job where Title like '%java%'
it will take 10 sec time
after that i am using the Force Index Query Hints
like
select id From Job WITH (INDEX(ind_job_Title)) where Title like '%java%'

it is also getting same time there is no improvement

please help me how to use Force Index
OR
how to impove the query performance tips

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-03 : 03:35:53
Because you used '%java%' , it can not use the index .

Try to use 'java%' and this will make the difference.


sabinWeb MCP
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-03 : 05:19:10
One way , that I think on how to improve , is to create a view with schemabinding , add an index on it , and query by it.
An example here:

CREATE VIEW dbo.vTabla_Java
WITH schemaBinding
AS
SELECT
[tj_ID]
,[tj_IsJava] = CAST(CASE WHEN (len([tj_Title])-len(replace([tj_Title],'java',''))) <> 0 THEN 1 ELSE 0 END AS BIT)
,tj_DiffTitleLength = len([tj_Title])-len(replace([tj_Title],'java',''))
FROM
[dbo].[Table_Java]
GO

CREATE UNIQUE CLUSTERED INDEX idx_vTabla_Java_U_C_ID
ON dbo.vTabla_Java (tj_ID);
GO

CREATE NONCLUSTERED INDEX idx_vTabla_Java_NC_DiffTitleLength
ON dbo.vTabla_Java (tj_DiffTitleLength);
GO

CREATE NONCLUSTERED INDEX idx_vTabla_Java_NC_IsJava
ON dbo.vTabla_Java (tj_IsJava);
GO

SELECT tj_ID
FROM dbo.vTabla_Java
WHERE tj_IsJava =1





sabinWeb MCP
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-03-03 : 11:17:45
Make sure the "id" column is in the "ind_job_Title" index. SQL will still have to scan the entire index, although this may help slightly if you have some short/empty titles:

where len(Title) >=4 and Title like '%java%'

since Title can't have 'java' in it if it's not at least 4 bytes.

Go to Top of Page
   

- Advertisement -