I have a Stored Procedure that takes roughly a second to execute, however, using the same parameters when I deploy it on .NET C#, I reach the part where I use an SqlAdapter and use the Fill function, (using debug mode) I press F11 and then it stays there for like a minute until it finally returns a datatable, which only has one row by the way.
Is there something that I must consider when using C# for an SP instead of SQL?
The query in particular is the following:
ALTER PROCEDURE [dbo].[GAS_consumptions_spFullSearchA]
(
@idClient INT,
@beginDate DATETIME,
@endDate DATETIME,
@description VARCHAR(1000),
@department VARCHAR(50),
@area VARCHAR(50),
@idCardType INT,
@idProduct INT,
@idStation INT,
@cardCode VARCHAR(16),
@idCostCenter INT,
@idSection INT,
@idDivision INT,
@isManual INT,
@idInvoice INT
)
AS
SET @description = ISNULL(@description, '')
SELECT DISTINCT
con.idConsumption,
cli.idClient,
ISNULL(cli.tradename, cli.commercialName) AS tradeName,
con.quantity,
pro.[description],
(con.price + con.iepsPerLiter + (con.price * con.tax) / 100) AS price,
(con.subtotal + con.ieps) AS subtotal,
con.createdDate AS dateCreated,
ISNULL(sta.shortname, 'Corporativo') AS name,
ISNULL(nip.idCard, nip2.[idCard]) AS idCard,
ISNULL(clc.[name], 'CONSUMO ADMIN.') AS contact,
'' AS operationType,
con.gasPump,
con.tax,
con.total,
sta.idStation,
sta.[name] AS stationName,
sta.rfc,
con.stationSaleNumber,
SUBSTRING (ISNULL(car.cardCode, car2.[cardCode]), 9, 16) AS cardCode,
ISNULL (veh.economicNumber, veh2.economicNumber) AS economicNumber,
ISNULL (veh.model, veh2.model) AS model,
ISNULL (veh.licensePlates, veh2.licensePlates) AS licensePlates,
sta.pemexKey,
pro.idPemex,
con.isManual,
mcd.comments,
rmc.reasonName,
CASE
WHEN car.cardCode IS NULL THEN 'Tarjeta'
ELSE 'CHEQUE'
END AS operation,
con.TaxAmount,
'1' AS RowType,
con.CommissionsAmount,
con.notes,
con.CommissionsValue,
con.CommissionsTaxAmount,
cat.[description] AS [type],
CASE ISNULL(ico.idInvoice, '-1')
WHEN -1 THEN '-1'
ELSE cor.series + ' ' + CAST(ico.idInvoice AS NVARCHAR)
END AS idInvoice,
con.mileage,
con.stationSaleNumber AS saleNumber,
con.workSchedule AS Turno,
ISNULL(car.area, car2.area) AS Area,
ISNULL(car.department, car2.department) AS department,
CASE ISNULL(ico.idInvoice, '-1')
WHEN -1 THEN '-1'
ELSE inv.series + RIGHT('000000' + CAST(inv.number AS VARCHAR),6)
END AS seriesNumber,
CASE ISNULL(con.requestedAmount, '')
WHEN '' THEN 0
ELSE con.requestedAmount
END AS RequestAmount,
(
SELECT cup.serial + CAST(cup.folio AS VARCHAR)+ '($' + CAST(cup.amount AS VARCHAR) + ') '
FROM CCARD_coupon AS cup
WHERE cup.IdConsumption = con.idConsumption
FOR XML PATH('')
) AS couponConsumtion,
ISNULL(con.couponConsumption, 0) AS couponConsumption,
ISNULL(coce.name, '') AS costCenter,
ISNULL(sec.name, '') AS section,
ISNULL(div.name, '') AS division,
CASE ISNULL(con.isManual, 0)
WHEN 1 THEN 'MANUAL'
ELSE cat.[description]
END AS [transaction],
CASE con.isManual
WHEN 1 THEN 'SI'
ELSE ''
END AS [Manual],
CASE ISNULL(ico.idInvoice, '-1')
WHEN -1 THEN ''
ELSE CONVERT(VARCHAR, inv.createdDate, 120)
END AS fechaFactura,
cntCls.description AS clasificacion,
cntSeg.description AS segmentacion,
cntEco.description AS giro,
CASE
WHEN cct.idClientType IS NULL THEN 'NA'
ELSE cct.description
END AS tipoCliente,
CONVERT(VARCHAR(10), cli.createdDate, 120) AS clienteCreacion,
repBilling.name AS RepFacturacion,
repSales.name AS RepVentas,
repFinance.name AS RepCobranza,
cor.idCorporative,
con.picture,
con.signature,
CAST
(
CASE
WHEN
(
LTRIM(RTRIM(ISNULL(con.picture, ''))) = ''
AND LTRIM(RTRIM(ISNULL(con.signature, ''))) = ''
) THEN 0
ELSE 1
END AS BIT
) AS imagesAvailable,
CAST
(
CASE LTRIM(RTRIM(ISNULL(con.picture, '')))
WHEN '' THEN 0
ELSE 1
END AS BIT
) AS pictureAvailable,
CAST
(
CASE LTRIM(RTRIM(ISNULL(con.signature, '')))
WHEN '' THEN 0
ELSE 1
END AS BIT
) AS signatureAvailable
FROM
GAS_consumptions con WITH(NOLOCK)
INNER JOIN GAS_products pro WITH(NOLOCK) ON pro.IdProduct = con.IdProduct
LEFT OUTER JOIN GAS_oneTimeNips otn WITH(NOLOCK) ON otn.idOneTimeNip = con.idOneTimeNip
LEFT OUTER JOIN [GAS_cardNips] nip2 WITH(NOLOCK) ON nip2.[idNip] = otn.[idNip]
LEFT OUTER JOIN GAS_CardNips nip WITH(NOLOCK) ON nip.idNip = con.idNip
LEFT OUTER JOIN GAS_ClientContacts clc WITH(NOLOCK) ON clc.idClientContact = nip.idClientContact
LEFT OUTER JOIN GAS_Cards car WITH(NOLOCK) ON car.idCard = nip.idCard
LEFT OUTER JOIN [GAS_cards] car2 WITH(NOLOCK) ON car2.[idCard] = nip2.[idCard]
LEFT OUTER JOIN [GAS_cardTypes] cat WITH(NOLOCK) ON cat.[idCardType] = car.[idCardType]
LEFT OUTER JOIN CCARD_stations sta WITH(NOLOCK) ON sta.idStation = con.idStation
LEFT OUTER JOIN GAS_Vehicles veh2 WITH(NOLOCK) ON veh2.idVehicle = car2.idVehicle
LEFT OUTER JOIN GAS_Vehicles veh WITH(NOLOCK) ON veh.idVehicle = car.idVehicle
LEFT OUTER JOIN CCARD_clients cli WITH(NOLOCK) ON cli.idClient = con.idClient
LEFT OUTER JOIN CCARD_clientCreditTypes cct WITH(NOLOCK) ON cct.idClientType = dbo.GetClientType(cli.idAccountType, cli.remissionType, cli.isPayToCard, cli.isCoupon)
INNER JOIN CCARD_corporatives cor WITH(NOLOCK) ON cor.idCorporative = cli.idCorporative
LEFT OUTER JOIN GAS_invoiceConsumptions ico WITH(NOLOCK) ON ico.idConsumption = con.idConsumption
LEFT OUTER JOIN GAS_invoices inv WITH(NOLOCK) ON inv.idInvoice = ico.idInvoice
LEFT OUTER JOIN GAS_manualConsumptionDetail mcd WITH(NOLOCK) ON mcd.idConsumption = con.idConsumption
LEFT OUTER JOIN GAS_reasonsManualConsumption rmc WITH(NOLOCK) ON rmc.idReason = mcd.idReasonManualConsumption
LEFT OUTER JOIN CCARD_divisions div WITH(NOLOCK) ON div.idDivision = con.idDivision
LEFT OUTER JOIN CCARD_sections sec WITH(NOLOCK) ON sec.idSection = div.idSection
LEFT OUTER JOIN CCARD_costCenters coce WITH(NOLOCK) ON coce.idCostCenter = sec.idCostCenter
LEFT OUTER JOIN CCARD_ClientsClassifications cntCls WITH(NOLOCK) ON cntCls.idClientClassification = cli.idClientClassification
LEFT OUTER JOIN CCARD_ClientsSegmentations cntSeg WITH(NOLOCK) ON cntSeg.idClientSegmentation = cli.idClientSegmentation
LEFT OUTER JOIN CCARD_clientsEconomicActivities cntEco WITH(NOLOCK) ON cntEco.idEconomicActivity = cli.idEconomicActivity
LEFT OUTER JOIN PORTA_employees repSales WITH(NOLOCK) ON repSales.idEmployee = cli.salesRepresentative
LEFT OUTER JOIN PORTA_employees repBilling WITH(NOLOCK) ON repBilling.idEmployee = cli.billingRepresentative
LEFT OUTER JOIN PORTA_employees repFinance WITH(NOLOCK) ON repFinance.idEmployee = cli.financeRepresentative
WHERE
con.idClient = @idClient
AND
(
(
con.createdDate >= @beginDate
AND con.createdDate < DATEADD(DAY, 1, @endDate)
AND
(
@description IS NULL
OR CAST(con.idConsumption AS VARCHAR(100)) LIKE '%' + @description + '%'
OR pro.description LIKE '%' + @description + '%'
OR sta.[name] LIKE '%' + @description + '%'
OR veh.economicNumber LIKE '%' + @description + '%'
OR veh.licensePlates LIKE '%' + @description + '%'
)
AND
(
@idCardType = -1
OR cat.idCardType = @idCardType
)
AND
(
@idProduct = pro.idProduct
OR @idProduct = -1
)
AND
(
sta.idStation = @idStation
OR @idStation = -1
)
AND
(
@cardCode = '-1'
OR
(
car.cardCode LIKE @cardCode
OR car2.cardCode LIKE @cardCode
)
)
AND
(
@department IS NULL
OR @department = '-1'
OR @department LIKE car.department
)
AND
(
@area IS NULL
OR @area = '-1'
OR @area LIKE car.area
)
AND
(
coce.idCostCenter = @idCostCenter
OR @idCostCenter = -1
)
AND
(
sec.idSection = @idSection
OR @idSection = -1
)
AND
(
div.idDivision = @idDivision
OR @idDivision = -1
)
AND
(
con.isManual = @isManual
OR @isManual = -1
)
AND @idInvoice = -1
)
OR inv.idInvoice = @idInvoice
)
ORDER BY con.idConsumption
