none
Qual a Performance da variável do tipo @Table ?

    Question

  • Galera estou com a seguinte dúvida.

    Tenho um processo de carga e faço um insert de 1000 registros e estou com problema de timeout no meu banco de dados porque eu envio linha a linha do List<string> para a procedure de insert e assim vai inserindo linha a linha,o problema é que o timeout da conexão com o banco atinge e para na 200 linha.

    Para resolver isso eu converti o List<string> em Datable e para fazer o processo via BulkCopy ou seja a minha procedure manda um DataTable para procedure de insert eu criei uma variavel do tipo @Table e funcionou perfeito rápido. 

    Minha pergunta é em termos de performance do banco de dados o que compromete ? Usar a Variável do tipo @Table não é performático ? Popular usa variável com 1000 linhas com 5 colunas é destruir a performance do banco de dados e os DBA não iram gostar nada disso ? Sendo que este processo será executa 1 vez ao dia ?

    Aguardo comentários de todos.

    Saturday, January 19, 2013 12:22 AM

Answers

  • Boa Tarde a Todos

    Segue abaixo solução adotada, insert via Bulkcopy.

    public static void bulkCopyIntoTable(DataTable dtData,string sDatabaseTableName,string sConnectionString)
    02.{
    03. 
    04.using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sConnectionString))
    05.{
    06.bulkCopy.DestinationTableName = sDatabaseTableName;
    07.try
    08.{
    09.// Write from the source to the destination.
    10.bulkCopy.WriteToServer(dtData);
    11.}
    12.catch (Exception ex)
    13.{
    14.Console.WriteLine(ex.Message);
    15.}
    16.finally
    17.{
    18. 
    19.}
    20.}
    21.}

    • Marked as answer by Thomas More Sunday, February 03, 2013 3:15 PM
    Sunday, February 03, 2013 3:15 PM

All replies

  • Thomas,

    Não tem nenhum problema em usar o @Table.

    A questão é que o @table só visualiza quem criou e em tempo de execução, pois é armazenado na RAM (consome recurso da máquina) e a tabela temporária #table ela ficará armazenada na sysobjects e estará disponível a todos.


    Mas, para casos como este de importação, eu costumo usar pacotes SSIS.

    Abraços.

    • Proposed as answer by Antonio Malheiros Sunday, January 20, 2013 3:25 AM
    • Unproposed as answer by Thomas More Sunday, January 20, 2013 3:26 PM
    Sunday, January 20, 2013 3:25 AM
  •  

    Mas eu me refiro ao seguinte ,estou montando um tabela de 1000 linhas em tempo de execução e envio esta tabela da aplicação para o banco de dados via bulkcopy e na minha stored procedure de insert estou usando no novo recurso do SQL Server 2008 ( Table - Value Parameters ) conforme script abaixo. Minha pergunta é este procedimento vai onerar o banco de dados SQL SERVER 2008 , pensou que está nova funcionalidade de receber um parametro do tipo @TableVariable é perfeitamente possivel e  não ira onerar a mémoria RAM do SQL SERVER 2008 pois pelo que estudei ele foi dimensionado para suportar este tipo de parametro porque o insert é feito em bloco e a mémoria RAM do banco será usada e liberada em segundos e não ira atrapalhar em nada a performance o que pode me dizer a respeito ?

    Estou levantando está questão porque utilizei este procedimento e funciona perfeitamente , mas alguns desenvolvedores da empresa dúvidaram que este procedimento fosse correto ? Principalmente porque os DBA não iram aceitar.

    CREATE TYPE SalesHistoryTableType AS TABLE
      (                     
            [Product] [varchar](10) NULL,                
            [SaleDate] [datetime] NULL,                
            [SalePrice] [money] NULL
      )
      GO

    CREATE PROCEDURE usp_InsertBigScreenProducts (       @TableVariable SalesHistoryTableType READONLY ) AS BEGIN       INSERT INTO SalesHistory       (             Product, SaleDate, SalePrice       )       SELECT             Product, SaleDate, SalePrice       FROM             @TableVariable       WHERE             Product = 'BigScreen' END GO


    Sunday, January 20, 2013 3:22 PM
  • Bom dia Thomas,

    Sobre o overhead que a variavel table possa vir a causar, não se preocupe, pq o engine do sql é capaz de saber quando isso estiver ocorrendo e ele então passará a utilizar o tempDB.
    Agora, uma coisa que vc deve lembrar, é que as variaveis do tipo table nao geram estatisticas, e dependo da sua operação envolver where/join, voce poderá ter baixa performance pq nao é gerado um plano de execução otimizado. Li, que alguns experts em sql recomendam usar as table variables com um numero reduzido de linhas, tipo umas 100.

    At.
    Rafael

    Monday, January 21, 2013 10:55 AM
  • Rafael,

    Toda variável de tabela vai para tempDB, não existe restrição quanto a isso. Leia esse post que escrevi sobre o assunto: http://marcosfreccia.wordpress.com/2011/05/27/variavel-de-tabela-adquire-ou-nao-locks/

    Variavel de tabela realmente não possui índices, e então não possui estatísticas, logo para grandes conjuntos de dados  não é uma boa alternativa, porem para pequenos e médios conjuntos paga-se o custo para utilizar, uma vez que você pode diminuir a utilização de CPU devido a menor recompilação existente em variáveis de tabela.

    Não existe uma regra, que depois de tantos registros não utilize Table Variable. O que você provavelmente leu no livro do Grant Fritchey não está totalmente certo e existem coisas sobre esse assunto no livro que deverão ser ajustadas.

    Thomas,

    Mil registros? Isso é muito pouco e se fosse para utilizar dentro de uma procedure por exemplo, eu optaria por utilizar variavel de tabela ao invés de tabela temporária.

    Se a resposta foi útil, classifique-a


    Att,
    Marcos Freccia [MTA|MCTS|MCITP|MCT SQL Server 2008]
    Blog|Twitter
    Assine também os feeds clicando aqui

    Monday, January 21, 2013 11:52 AM
  • Rafael,

    Toda variável de tabela vai para tempDB, não existe restrição quanto a isso. Leia esse post que escrevi sobre o assunto: http://marcosfreccia.wordpress.com/2011/05/27/variavel-de-tabela-adquire-ou-nao-locks/

    Variavel de tabela realmente não possui índices, e então não possui estatísticas, logo para grandes conjuntos de dados  não é uma boa alternativa, porem para pequenos e médios conjuntos paga-se o custo para utilizar, uma vez que você pode diminuir a utilização de CPU devido a menor recompilação existente em variáveis de tabela.

    Não existe uma regra, que depois de tantos registros não utilize Table Variable. O que você provavelmente leu no livro do Grant Fritchey não está totalmente certo e existem coisas sobre esse assunto no livro que deverão ser ajustadas.

    Thomas,

    Mil registros? Isso é muito pouco e se fosse para utilizar dentro de uma procedure por exemplo, eu optaria por utilizar variavel de tabela ao invés de tabela temporária.

    Se a resposta foi útil, classifique-a


    Att,
    Marcos Freccia [MTA|MCTS|MCITP|MCT SQL Server 2008]
    Blog|Twitter
    Assine também os feeds clicando aqui

    Bom dia Marcos,

    Talvez eu não tenha deixado muito claro, mas, não disse que havia restrição quanto ao ir para o tempDB ou não, mesmo pq elas sao criadas dentro do tempDB. Referia-me quanto a utilizaçao de recurso, que pelo que li sobre, inicialmente ele utilizada a ram disponivel, porém poderá ser movida para o disco a qualquer momento. Quanto a quantidade de linhas, referia-me a questão de performance, que pode ser realmente ruim pela falta de indices/estatisticas.
    As informações, obtive no site da SQLCentral  e blog do Pinal Dave.

    At.
    Rafael

    Monday, January 21, 2013 12:31 PM
  • Rafael,

    O único tipo de tabela virtual que não vai para tempdb é a CTE. 

    A tabela pode até ser processada em memoria, mas o objeto é sim criado primeiramente em tempdb, porem recursos realizados com essa tabela podem ser realizados em disco caso a memoria disponível não for suficiente como: Sort, Hash Joins entre outras operações.


    Att,
    Marcos Freccia [MTA|MCTS|MCITP|MCT SQL Server 2008]
    Blog|Twitter
    Assine também os feeds clicando aqui

    Monday, January 21, 2013 12:36 PM
  • Thomas,

    Você precisa enviar 1000 linhas para o SQL. Quais são suas possíbilidades?

    1. insert uma a uma

    2. usar o tradicional dataset

    3. criar um modelo EF

    4. um LinqToSQL

    ... (faltou aguma?) ...

    e sua opção do TABLE datatype

    Agora é só uma questão de qual recurso você vai querer estressar mais, porque custo em todas elas vai incorrer.

    Entendo que o um a um é o mais lento e vai pessar mais na rede e exigir um pouco mais do cliente, facilitando a vida do SQL e minimizando contenção

    O dataset pesa em todo lado, especialmente se for fazer os controles de concorrência

    O EF é mais legal, mas é um overkill para um processo que só quer inserir 1000 linhas

    O LinqToSQL até que pesa menos do que o EF e o dataset, e se compara ao um a um

    A sua opção, BulkInsert com TABLE datatype é com certeza a melhor pois utiliza menos rede (menos roundtrips), é mais leve no cliente e o volume e frequência no servidor são leves.

    Na minha opinião, você fez uma boa opção  e está utilizando a tecnologia da melhor forma possível. Só não sei se seus DBAs vão concordar. :)

    De acordo com a Microsoft, a melhor forma de levantar um volume maior de dados (e no seu caso o volume não é tão grande) para o SQL a partir do código .Net é usar a classe SqlBulkCopy. Mais detalhes aqui: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

    []s


    Armando Lacerda

    Tuesday, January 22, 2013 10:56 PM
  • Boa Tarde a Todos

    Segue abaixo solução adotada, insert via Bulkcopy.

    public static void bulkCopyIntoTable(DataTable dtData,string sDatabaseTableName,string sConnectionString)
    02.{
    03. 
    04.using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sConnectionString))
    05.{
    06.bulkCopy.DestinationTableName = sDatabaseTableName;
    07.try
    08.{
    09.// Write from the source to the destination.
    10.bulkCopy.WriteToServer(dtData);
    11.}
    12.catch (Exception ex)
    13.{
    14.Console.WriteLine(ex.Message);
    15.}
    16.finally
    17.{
    18. 
    19.}
    20.}
    21.}

    • Marked as answer by Thomas More Sunday, February 03, 2013 3:15 PM
    Sunday, February 03, 2013 3:15 PM