Modify the question
The question was, I have an SQL that takes more than 60s to return an answer. Something unthinkable for a search engine!
I have found the reason:
CharIndex
Why is it slow?
I have about 300 results to show (in a subquery) that I then pass through a select filter with a charindex. The problem is that it collects a string with almost thirty values separated by commas ... and has to compare those 300 results with each of the values to verify that it meets the condition!
Overall, is there any way to simplify or accelerate that condition? I have thought about treating it with loops or temporary tables, but I think it will take longer still ...
Here the conditioner:
SET @holons = 'CC01,CC02,CC03,CC04,CC05,CC06,CE01,CE02,CL01,CL02,CL03,CL04,CN01,CN02,CN03,CN04,CN05,CN06,CN07,CO01,CP01,CP02,CP03,CP04,CS01,CS02,CS03,CS04'
Here the condition:
CHARINDEX(',' + FULL_SEARCH.Holon + ',', ',' + @HOLONS + ',') > 0
EXECUTION TIME
00:01:01
QUERY
SELECT
FULL_SEARCH.*
FROM (
SELECT DISTINCT
Contactos.IdContacto,
Clientes.NombreCliente,
Clientes.CodigoCliente,
Clientes.Holon,
Contactos.NombreContacto,
Departamentos.Titulo AS Departamento,
Cargos.Titulo AS Cargo,
Contactos.Telefono,
Contactos.Email,
Contactos.Movil,
Clientes.CodigoPostal,
Clientes.Poblacion,
Clientes.CodigoPostalPoblacion,
Relevancias.Titulo AS Relevancia,
Clientes.Proyecto,
GruposClientes.IdGrupo,
Clientes.Responsable,
Clientes.Direccion,
Contactos.Descripcion,
Contactos.Observaciones,
Clientes.Segmento,
Clientes.Subsegmento
FROM
Contactos
INNER JOIN Clientes ON Clientes.CodigoCliente = Contactos.CodigoCliente
INNER JOIN Departamentos ON Departamentos.IdDepartamento = Contactos.IdDepartamento
LEFT JOIN Cargos ON Cargos.IdCargo = Contactos.IdCargo
LEFT JOIN Relevancias ON Relevancias.IdRelevancia = Contactos.IdRelevancia
LEFT JOIN GruposClientes ON GruposClientes.CodigoCliente = Contactos.CodigoCliente
LEFT JOIN ContactosAcciones ON ContactosAcciones.IdContacto = Contactos.IdContacto
WHERE
contactos.Activo = 1
AND (@CodigoCliente IS NULL OR @CodigoCliente = '' OR Clientes.CodigoCliente like '%' + @CodigoCliente + '%')
AND (@NombreCliente IS NULL OR @NombreCliente = '' OR Clientes.NombreCliente like '%' + @NombreCliente + '%')
AND (@NombreContacto IS NULL OR @NombreContacto = '' OR Contactos.NombreContacto like '%' + @NombreContacto + '%')
AND (@Departamento IS NULL OR @Departamento = '' OR Departamentos.Titulo like '%' + @Departamento + '%')
AND (@IdGrupo IS NULL OR @IdGrupo = '' OR GruposClientes.IdGrupo = @IdGrupo)
AND (@Responsable IS NULL OR @Responsable = '' OR Clientes.Responsable like '%' + @Responsable + '%')
AND (@AccionesMarketing IS NULL OR @AccionesMarketing = '' OR ContactosAcciones.IdAccionMarketingContacto like '%' + @AccionesMarketing + '%')
AND (@Proyecto IS NULL OR @Proyecto = '' OR Clientes.Proyecto like '%' + @Proyecto + '%')
) FULL_SEARCH
WHERE CHARINDEX(FULL_SEARCH.Holon,@HOLONS) > 0
PARAMETERS
@CodigoCliente = NULL,
@NombreCliente = NULL,
@NombreContacto = NULL,
@Departamento = NULL,
@Holons = N'''CC01,CC02,CC03,CC04,CC05,CC06,CE01,CE02,CL01,CL02,CL03,CL04,CN01,CN02,CN03,CN04,CN05,CN06,CN07,CO01,CP01,CP02,CP03,CP04,CS01,CS02,CS03,CS04''',
@GruposClientes = NULL,
@Holon = NULL,
@IdGrupo = 5,
@Responsable = NULL,
@AccionesMarketing = NULL,
@Proyecto = NULL
RESULTS
IdContacto Holon
1 36901 CN04
2 36902 CN04
3 36903 CN04
4 36904 CN04
5 36905 CN04
6 36906 CN04
7 36907 CN04
8 36908 CN04
9 36909 CN04
10 36910 CN04
11 36911 CN04
12 36912 CN04
13 36921 CN02
14 36922 CN02
15 36923 CN02
16 36924 CN02
17 36925 CN02
18 36926 CN02
19 36927 CN02
20 36928 CN02
21 36929 CN02
22 36930 CN02
23 36931 CN02
24 36932 CN02
25 36933 CN02
26 36934 CN02
27 36935 CN02
28 36936 CN02
29 36937 CN02
30 36938 CN02
31 36939 CN02
32 36940 CN02
33 36941 CN02
34 36942 CN02
35 36943 CN02
36 42743 CN06
37 43154 CN06
38 45341 CN01
39 45342 CN01
40 45343 CN01
41 45344 CN01
42 45345 CN01
43 45346 CN01
44 45347 CN01
45 45348 CN01
46 45349 CN01
47 45350 CN01
48 45770 CN06
49 45771 CN06
50 45772 CN06
51 45773 CN06
52 45774 CN06
53 45775 CN06
54 45776 CN06
55 45777 CN06
56 45778 CN06
57 45779 CN06
58 45780 CN06
59 45781 CN06
60 45782 CN06
61 45783 CN06
62 45784 CN06
63 45785 CN06
64 46694 CN06
65 46695 CN06
66 46696 CN06
67 46697 CN06
68 46698 CN06
69 46699 CN06
70 46700 CN06
71 46701 CN06
72 46702 CN06
73 46703 CN06
74 46704 CN06
75 46705 CN06
76 46706 CN06
77 46715 CN05
78 46716 CN05
79 46717 CN05
80 46718 CN05
81 46719 CN06
82 46720 CN06
83 48928 CN06
84 48929 CN06
85 48930 CN06
86 48931 CN06
87 48932 CN06
88 48934 CN06
89 48935 CN06
90 48936 CN06
91 48937 CN06
92 48938 CN06
93 48972 CN05
94 48973 CN05
95 60565 CC02
96 60566 CC02
97 60567 CC02
98 60568 CC02
99 60569 CC02
100 60570 CC02
101 60571 CC02
102 60572 CC02
103 60573 CC02
104 60574 CC02
105 60575 CC02
106 60576 CC02
107 60577 CC02
108 60578 CC02
109 60579 CC02
110 60580 CC02
111 60581 CC02
112 60582 CC02
113 60583 CC02
114 60584 CC02
115 60585 CC02
116 60586 CC02
117 60587 CC02
118 60588 CC02
119 60589 CC02
120 60590 CC02
121 60591 CC02
122 60592 CC02
123 60593 CC02
124 60594 CC02
125 60595 CC02
126 60596 CC02
127 60597 CC02
128 60598 CC02
129 60599 CC02
130 60600 CC02
131 60601 CC02
132 60613 CC01
133 60614 CC01
134 60615 CC01
135 60616 CC01
136 60617 CC01
137 60619 CC01
138 60620 CC01
139 60621 CC01
140 60622 CC01
141 60623 CC01
142 60625 CC01
143 60626 CC01
144 60627 CC01
145 60628 CC01
146 60629 CC01
147 60630 CC01
148 60631 CC01
149 60632 CC01
150 61801 CC01
151 61802 CC01
152 61803 CC01
153 61806 CC02
154 61807 CC02
155 61808 CC02
156 61809 CC02
157 61810 CC02
158 61811 CC02
159 61812 CC02
160 66392 CC04
161 66393 CC04
162 66394 CC04
163 66395 CC04
164 66396 CC04
165 66397 CC04
166 66398 CC04
167 66399 CC04
168 70920 CC02
169 70921 CC02
170 70922 CC02
171 70923 CC02
172 70924 CC02
173 77011 CL04
174 77013 CL04
175 77014 CL04
176 77015 CL04
177 77018 CL04
178 77019 CL04
179 87543 CS03
180 87544 CS03
181 87545 CS03
182 87546 CS03
183 87547 CS03
184 87548 CS03
185 87549 CS03
186 87550 CS03
187 87551 CS02
188 87552 CS02
189 87553 CS02
190 87555 CS02
191 87556 CS02
192 87557 CS02
193 87558 CS02
194 87559 CS02
195 87560 CS02
196 87561 CS02
197 87562 CS02
198 87563 CS02
199 87564 CS02
200 87565 CS02
201 87566 CS02
202 87567 CS02
203 87568 CS02
204 87569 CS02
205 87570 CS02
206 87571 CS02
207 87572 CS02
208 87573 CS02
209 87574 CS02
210 87575 CS02
211 87576 CS02
212 87577 CS02
213 87578 CS02
214 87579 CS02
215 87580 CS02
216 87581 CS04
217 87582 CS04
218 87583 CS04
219 87584 CS04
220 87585 CS04
221 87586 CS04
222 87587 CS04
223 87588 CS04
224 87589 CS04
225 87590 CS04
226 87591 CS04
227 87592 CS04
228 87593 CS04
229 87594 CS04
230 96352 CN05
231 96543 CN02
232 96544 CN02
233 100390 CP04
234 100391 CP04
235 100392 CP04
236 100393 CP04
237 100394 CP04
238 100395 CP04
239 100396 CP04
240 100397 CP04
241 100398 CP04
242 100399 CP04
243 100400 CP04
244 100401 CP04
245 100402 CP04
246 100403 CP04
247 100404 CP04
248 100405 CP01
249 100406 CP01
250 100407 CP01
251 100408 CP01
252 100409 CP01
253 100410 CP01
254 100411 CP01
255 100412 CP01
256 100413 CP01
257 100414 CP01
258 100415 CP01
259 100416 CP01
260 100417 CP01
261 100418 CP01
262 100419 CP01
263 100420 CP02
264 100421 CP02
265 100422 CP02
266 100423 CP02
267 100424 CP02
268 100425 CP02
269 100426 CP02
270 100427 CP02
271 100428 CP02
272 100429 CP02
273 100430 CP02
274 100431 CP02
275 106692 CN02
276 106693 CN02
277 106918 CN04
278 106919 CN04
279 108058 CC03
280 108140 CS03
281 108435 CP01
282 108682 CC03
283 108883 CC03
284 109801 CC03
285 110669 CN06
286 111200 CN06
287 111222 CN06
288 111352 CC02
289 111443 CN02
Obviously I have removed sensitive data from the results