0

I use SQL Server 2012.

I have a table with 1 500 000 individuals. In this table, I have a non-clustered indexed field UR_ID. The application I work with selects data from my individual table. It uses the following query:

declare @p5 int
exec sp_prepexec @p5 output,
N'@P1 nvarchar(4000),@P2 numeric(38,0),@P3 numeric(38,0)',
N'select a.INDIVIDUAL_ID as ID_OBJEC_1 
from dbo.INDIVIDUAL a 
where lower(a.UR_ID) like @P1 
and coalesce (a.FLD_DELETE, @P2 )= @P3 
order by a.LST_UPD_DT desc, a.INDIVIDUAL_ID asc',
N'1000004708691',0,0
select @p5

Basically, the query just translates the query from the indexed field to the primary key of the record. In this example, '1000004708691' is the searched indexed field UR_ID. FLD_DELETE and LST_UPD_DT are other fields in the INDIVIDUAL tables.

The query takes 04.76 seconds to execute. Because I was diagnosing performance problem, I used the following query to check what was wrong:

select a.INDIVIDUAL_ID as ID_OBJEC_1 
from dbo.INDIVIDUAL a 
where lower(a.UR_ID) like '1000004708691' 
and coalesce (a.FLD_DELETE, 0)= 0
order by a.LST_UPD_DT desc, a.INDIVIDUAL_ID asc

The query takes 1.18 seconds to execute.

I don't understand why using sp_prepexec would make the query 5x slower. I also don't understand why a non-clustered indexed field search can take as much as 1 second to execute.

How can I improve the performances here? What am I missing?

Edit: Execution Plan of the Query

MMacphail
  • 541
  • 3
  • 19
  • Why just not wrapped the code in real stored procedure? Could you test `sp_executesql`, too? – gotqn Oct 10 '18 at 06:05
  • I have no control over the application's code, but I believe it's because the schema has to be generic. I'll try sp_executesql and keep you posted. – MMacphail Oct 10 '18 at 07:16
  • You need to show us the execution plan. Are you sure the index is being used? I think it is not, you are using a function on your where clause and that makes your query [non-sargable](https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable). Also, the fact that `sp_prepexec` is slower, might as well be just because you are running that first and the data is cached when you subsequently run directly the query (which looks faster then). From my tests, both executions perform the same number of logical reads. – Rigerta Oct 10 '18 at 08:04
  • Stop taking care of **case sensivity of numbers** and comparing equality with `like`. If `UR_ID` is a number - store it in numeric/int/bigint field, not in varchar. – Ivan Starostin Oct 10 '18 at 08:04
  • Here is some research about sp_prepexec vs sp_executesql: https://www.brentozar.com/archive/2018/03/sp_prepare-isnt-good-sp_executesql-performance/ – Ivan Starostin Oct 10 '18 at 08:19

0 Answers0