Exportação para SQL - MUITO RÁPIDA

[emotta]

Pessoal, fiz uma rotina que pode ajudar a muitos aqui. Acredito que muitos passam pelo problema de ter uma tabela em DBF com muitos dados e ter que exporta-la a um banco de dados SQL.Pela tabela ter muitos dados o processo da forma convencional é extremamente lento e demora muito.

Eu consegui desenvolver uma solução para deixar muito rapida, na verdade absurdamente mais rapida. No exemplo que estou disponibilizando uma tabela com 10mil registros demora 34 segundos no modo convencional e na forma rapida ele faz o mesmo trabalho de exportação em apenas 1 segundo.

Para executar este exemplo você precisa usar o SQLRDD e já estar conectado ao banco de dados. Nos meus testes eu fiz com o SQL SERVER porem acredito que dê facilmente para executar em outros banco de dados apenas compatibilizando a linha onde tem "SET NOCOUNT ON" deve ser encontrado o comando equivalente para outros bancos. Para saber o que essa funcao faz basta pesquisar no google que tem ótimas explicações.

A rotina que estou colocando já cria o arquivo DBF, popula ele e exportar ao SQL. Os comandos para conexão ao banco você deve implementar. Lembrando que o exemplo funciona para SQLRDD, se alguem usa outro RDD é só convertar, o fonte está muito simples.

Bom divertimento, abraços

/*
TEMPO DE IMPORTAÇÃO PARA O SQL
MODO CONVENCIONAL (lFast = .f.) : 34 segundos
MODO OTIMIZADO (lFast = .T.) : 1 segundo
*/

Function u_Teste()
Local cFile_Dbf := "TAB_CLI.DBF"
Local cFile_Sql := "TAB_CLI"
Local aStru := {}
Local lFast := .t.  // mude aqui para .T. se quiser testar o modo otimizado (rapido) ou .f para utilizar o modo convencional (Mais lento)
Local nSec

fErase(cFile_Dbf)

aadd(aStru,{"TAB_CODIGO","C",6,0})
aadd(aStru,{"TAB_NOME","C",30,0})

DbCreate(cFile_Dbf,aStru,"DBFCDX")
DbUseArea(.t.,"DBFCDX",cFile_Dbf,"DBF",.T.,.F.)
Popula_Dbf()

nSec := Seconds()
If lFast
	FastExp("DBF",cFile_Sql)  // funcao para exportação rapida para SQL
Else
   SlowExp("DBF",cFile_Sql)  // funcao para exportação convencional (mais lenta)para SQL
EndIf

MsgStop("Tempo de exportação de dados: "+Str(Seconds()-nSec,6))

DbUseArea(.t.,"SQLRDD",cFile_Sql,"SQL",.T.,.F.)
SQL->(Browse())
SQL->(DbCloseArea())
DBF->(DbCloseArea())

Return

// funcao que faz a exportação do modo convencional (mais lento)
Static Function SlowExp(cAlias,cFileSql)
If Sr_File(cFileSql)
	SR_DropTable(cFileSql)
EndIf

COPY TO &cFileSql VIA "SQLRDD"

Return

// funcao que faz a exportação do modo otimizado (mais rapido)
Static Function FastExp(cAlias,cFileSql)
Local aStru := (cAlias)->(DbStruct())
Local aInsert := {}
Local cInsert

If Sr_File(cFileSql)
	SR_DropTable(cFileSql)
EndIf

DbCreate(cFileSql,aStru,"SQLRDD")

DbSelectArea(cAlias)
DbGotop()
While !Eof()
   cInsert := SqlMontaInsert(cFileSql)
   aadd(aInsert,cInsert)
   DbSkip()
EndDo

FlushData(aInsert)

Return

// funcao que monta string para comando insert do banco de dados
Static Function SqlMontaInsert(cTable)
Local aStru  := DbStruct()
Local nI
Local aCampos := {}
Local cRet := ""
Local uVal

For nI := 1 to Len(aStru)
   uVal := FieldGet(nI)
   If Empty(uVal)
      uVal := "null"
   Else
      uVal := Sr_cDbValue(uVal)
   EndIf
	aadd(aCampos,{aStru[nI,1],uVal})
Next

cRet := "INSERT INTO "+cTable+" ("
For nI := 1 to Len(aCampos)
   If nI > 1
      cRet+=","
   EndIf
   cRet+=aCampos[nI,1]
Next
cRet+=") VALUES ("
For nI := 1 to Len(aCampos)
   If nI > 1
      cRet+=","
   EndIf
   cRet+=aCampos[nI,2]
Next
cRet+=")"

Return cRet


// funcao que descarrega os comandos INSERT para o banco de dados fazendo a quebra para que não tenha perda de pacote ao enviar ao sql
Static Function FlushData(aInsert)
Local cSql := ""
Local aSql := {}
Local nPacote
Local nMaxPacote  := 120 * 1024  // Limita o tamanho do pacote para envio ao SQL em 60kb

cSql := "SET NOCOUNT ON;"+Chr(13)+Chr(10)
For nI := 1 to Len(aInsert)
   cSql+=aInsert[nI]+Chr(13)+Chr(10)
	If Len(cSql) > nMaxPacote // o limite é 65,536 * Network Packet Size - coloquei menos pra garantir que não estoure
		SR_GetConnection():Execute( cSql )
		cSql := "SET NOCOUNT ON;"+Chr(13)+Chr(10)
	EndIf
Next

If !Empty(cSql)
	SR_GetConnection():Execute( cSql )
EndIf
SR_GetConnection():Commit()

Return




// funcao para população inicial do DBF
Static Function Popula_Dbf()
Local nI

For nI := 1 to 10000
   Append Blank
   DBF->TAB_CODIGO := StrZero(nI,6)
   DBF->TAB_NOME   := "NOME "+StrZero(nI,6)
Next
DBF->(DbCommitAll())
Return

 

[Jmsilva]

Vou testar, tenho uma tabela que demora muito....blz

Parabéns!

#boaspraticas

[emotta]

 

Vou testar, tenho uma tabela que demora muito....blz

Parabéns!

#boaspraticas

Faz o teste e coloca aqui os resultados. Também aproveite pra conferir as informações se foram importadas todas corretamente.

[Jmsilva]

 

Faz o teste e coloca aqui os resultados. Também aproveite pra conferir as informações se foram importadas todas corretamente.

Sim, estou meio atarefado no momento, assim que fizer os testes relatarei os resultados

[Jmsilva]

Eduardo, executei seu prg, realmente ficou bem mais rápido. Show!

[emotta]

legal, divirta-se

em meu sistema apliquei este conceito e um procedimento que demorava 4 horas caiu para 20 minutos.

abraços

[alex2002]

O segredo da rotina é que no comando tradicional, vc da insert por insert.

E pela sua rotina vc monta um insert múltiplo.

Isso faz toda diferença.

Em 2006, eu fiz um backup personalizado que gerava mais rápido que o MySQLDUMP e também restaurava mais rápido que o próprio MySQL. E a técnica usada foi justamente esta, de aumentar a quantidade de registro em um insert, também compactei strings e explodia na restauração. Assim o arquivo ficava bem menor que o atual SQL do dump. Além de certa forma criptografar o backup.

Parabéns emotta.

A propósito ele pode ficar ainda mais rápido (e menor) se você usar apenas um insert com várias inserções:

insert into tabela (campoa, campob) values ( valora1, valorb1), (valora2, valorb2), (valora3, valorc3)....

Dê uma olhada.

 

[emotta]

Boa, quando der tempo vou implementar desta forma. Desconhecia essa facilidade para inserir.

Obrigado

[Jmsilva]

Static Procedure FastTurbo(cAlias,cFileSql)
   Local aStru := (cAlias)->(DbStruct())
   Local cSql,nTupla:=2
   Local nMaxTupla  := 1000

   If Sr_File(cFileSql)
      SR_DropTable(cFileSql)
   EndIf
   DbCreate(cFileSql,aStru,"SQLRDD")

   DbSelectArea(cAlias)
   DbGotop()
   cSql := SqlInsert(cFileSql)
   DBSkip()
   While !Eof()
      If nTupla > nMaxTupla // Limita a qtde maxima de linha no sqlserver
         SR_GetConnection():Execute( cSql )
         cSql := SqlInsert(cFileSql) //fields e values
         nTupla:=2
      Else
         cSql += SqlValue()  //values
      Endif
      DbSkip()
      nTupla++
   EndDo
   SR_GetConnection():Execute( cSql )
   SR_GetConnection():Commit()
Return

// funcao que monta string para comando insert do banco de dados
Static Function SqlInsert(cTable)
   Local aStru  := DbStruct()
   Local nI
   Local aCampos := {}
   Local cRet := "SET NOCOUNT ON;"+Chr(13)+Chr(10)
   Local uVal

   For nI := 1 to Len(aStru)
      uVal := FieldGet(nI)
      If Empty(uVal)
         uVal := "null"
      Else
         uVal := Sr_cDbValue(uVal)
      EndIf
      aadd(aCampos,{aStru[nI,1],uVal})
   Next

   cRet := "INSERT INTO "+cTable+" ("
   For nI := 1 to Len(aCampos)
      If nI > 1
         cRet+=","
      EndIf
      cRet+=aCampos[nI,1]
   Next
   cRet+=") VALUES ("
   For nI := 1 to Len(aCampos)
      If nI > 1
         cRet+=","
      EndIf
      cRet+=aCampos[nI,2]
   Next
   cRet+=")"

Return cRet


// funcao que monta string para comando insert do banco de dados
Static Function SqlValue()
   Local aStru  := DbStruct()
   Local nI
   Local aCampos := {}
   Local cRet := "",cSql
   Local uVal

   For nI := 1 to Len(aStru)
      uVal := FieldGet(nI)
      If Empty(uVal)
         uVal := "null"
      Else
         uVal := Sr_cDbValue(uVal)
      EndIf
      aadd(aCampos,{aStru[nI,1],uVal})
   Next

   cRet+=",("
   For nI := 1 to Len(aCampos)
      If nI > 1
         cRet+=","
      EndIf
      cRet+=aCampos[nI,2]
   Next
   cRet+=")"

Return cRet

Eduardo com a tua implementação aplicando a sugestão do Alex, baixou em 2 segundos os testes do seu  exemplo.

Ficou muito boa sua rotina, mais uma vez parabéns!

[emotta]

vlw pelo feedback e é gratificante quando algo é melhorado ! trabalho em parceria é sempre muito bom.

também vou implementar no meu padrão ! 

 

obrigado