SQL 60,000 records in less than 3 minutes

Question

SQL 60,000 records in less than 3 minutes

Navigation

#1 by (3 votes)
#2 by (2 votes)

5

Today I have an optimization question, my boss wants me to save 60,000 records in the shortest possible time. For this I have made a query that takes information from a .csv file and I put it on a temporary basis, from which I make a small filter and fill my target table , but processing the 60 takes me approx 8 min. I would like to know if someone can help me optimize my query or propose a faster storage method. I'm using sql managment 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

sql sql-server insert

asked by E.Rawrdríguez.Ophanim 23.08.2018 в 19:38

source

2 answers

2

Probably changing all that while for one:

INSERT INTO [dbo].[conciliacionesExcel]
SELECT '3', @fechaMov, @cargo, @abono, @saldo, @conceptoBanco
FROM erpsamfinanzas.dbo.#stats_ddl

Where the @field you have to replace them by the names of the fields of the temporary table that you want to send.

This is what you do is copy all your temporary table to your reconciliation tableExcel. Bear in mind, that you could also do the bulk insert directly in this table. and if later you need to make changes, make them on her ....

answered by 23.08.2018 в 20:53

Create a "hidden" class only accessible from another Use regex to replace all occurrences with CASE_INSENSITIVE (regardless of uppercase and lowercase)

score 3 · Accepted Answer

I do not have a way to prove it but I think what you are looking for is this, try and tell me if it works, you simply perform an INSERT with a SELECT, where the debit and credit filter is being made:

bulk insert erpsamfinanzas.dbo.#stats_ddl
from 'C:\mipc\some\where\myfile \thisismyfile.csv'
with(firstrow = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n')

INSERT INTO [dbo].[conciliacionesExcel] ([cuenta], [fechaMov], [abono], [saldo], [conceptoBanco])
SELECT '3', [Fecha], [Importe], [Saldo], 'CONCEPTO' FROM erpsamfinanzas.dbo.#stats_ddl 
WHERE [Cargo / Abono] LIKE '%+%';

INSERT INTO [dbo].[conciliacionesExcel] ([cuenta], [fechaMov], [cargo], [saldo], [conceptoBanco])
SELECT '3', [Fecha], [Importe], [Saldo], 'CONCEPTO' FROM erpsamfinanzas.dbo.#stats_ddl 
WHERE [Cargo / Abono] LIKE '%-%';