Today I have an optimization question, my boss wants me to save 60,000 records in the shortest time possible. For this I have made a query that takes information from a .csv file and put it in a temporary base, from which I make a small filter and fill my destination table , but processing the 60 takes me about 8 min. I would like to know if someone can help me optimize my query or suggest a faster way of storage. I am using sql management 2017.
IF OBJECT_ID('tempdb..#stats_ddl') is not null DROP TABLE# stats_ddl
GO
CREATE TABLE# stats_ddl(
[Cuenta] varchar(max), [Fecha] varchar(50), --[Hora] varchar(50), [Suc] varchar(50), [Descripcio] varchar(max), [Cargo / Abono] varchar(50), [Importe] varchar(50), [Saldo] varchar(50), [Referencia] varchar(50), [Concepto / Referencia Interbancaria] varchar(max) --
);
Go
declare @cargo varchar(max) = '',
@count int = 0,
@abono varchar(max) = ''
bulk insert erpsamfinanzas.dbo.#stats_ddl
from 'C:\mipc\some\where\myfile \thisismyfile.csv'
with(firstrow = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n')
--select * from# stats_ddl
declare
@countb int = 1
set @count = (select count( * ) from# stats_ddl)
while @countb <= @count
begin
DECLARE @fechaMov VARCHAR(50) = (SELECT TOP(1) fecha FROM# stats_ddl),
@Importe VARCHAR(50) = (SELECT TOP(1)[Importe] FROM# stats_ddl),
@simb VARCHAR(50) = (SELECT TOP(1)[Cargo / Abono] FROM# stats_ddl),
@saldo varchar(50) = (SELECT TOP(1) saldo FROM# stats_ddl),
@conceptoBanco varchar(max) = (SELECT TOP(1) saldo FROM# stats_ddl), @hh varchar(50) = (SELECT TOP(1) Hora from# stats_ddl), @hhx varchar(50) = (SELECT TOP(1) substring(Hora, 1, 2) as hhx from# stats_ddl), @hhy varchar(50) = (SELECT TOP(1) substring(Hora, 3, 4) as hhy from# stats_ddl);
Set @fechaMov = REPLACE(CONVERT(varchar(50), @fechaMov, 103), ':', '');
IF @simb LIKE '%+%'
BEGIN
SET @abono = @Importe
END
IF @simb LIKE '%-%'
BEGIN
SET @cargo = @Importe
END
INSERT INTO[dbo].[conciliacionesExcel]
([cuenta], [fechaMov], [cargo], [abono], [saldo], [conceptoBanco])
VALUES
('3', @fechaMov, @cargo, @abono, @saldo, @conceptoBanco)
--PRINT 'fecha' + @fechaMov + ' ' + CONVERT(VARCHAR, @count) + ' importe: ' + @Importe;
--print 'importes -- cargo: ' + @cargo + ' ' + 'abono ' + @abono;
--print 'saldo: ' + @saldo + ' ';
DELETE TOP(1) FROM# stats_ddl
--SELECT @count = COUNT( * ) FROM# stats_ddl;
end
--select * from stats_ddl
I do not have a way to test but I think what you are looking to do is this, try and tell me if it worked for you, simply perform an INSERT with a SELECT, where the charge and credit filter is being performed:
Try changing all that while for a:
Where the @field you have to replace them with the names of the fields of the temporary table that you want to send.
What this is going to do is copy your entire temporary table to your Excel reconciliations table. Note, that you could also do the bulk insert directly on this table. and if later you need to make changes, make them on it....