none
Melhorar a performance da procedure RRS feed

  • Pergunta

  • Tenho esta procedure que le a base de dados, monta a lista e pagina os dados.
    Tem como melhorar a sua performance?

    CREATE PROCEDURE [dbo].[USP_Pec_02_Artigos_Lista]
     
        @QtdePagina As Int,
        @vCategoria As  Varchar(50), 
        @PagAtual As Int,
        @TotalArtigos As Int OUTPUT

        AS
     
        DECLARE @ch_top AS Int
        DECLARE @Titulo As Varchar(200)
        DECLARE @Data As DateTime
        DECLARE @QtdeInicial As Int
        DECLARE @Contador As Int
     
        SET @QtdeInicial = 0
        SET @Contador = 0
     
        SET NOCOUNT ON
     
        CREATE TABLE #ArtigosTemp
        (
            ch_top int,
            Titulo Varchar(200),
            Data DateTime
          )
     
        DECLARE curPaginacaoArtigos  CURSOR FAST_FORWARD FOR 
            SELECT ch_top, Titulo, Data  FROM Tb_Pec_02_Artigos where Categoria=@vCategoria order by Data Desc
     
        OPEN curPaginacaoArtigos
        FETCH NEXT FROM curPaginacaoArtigos
            INTO @CH_TOP,@Titulo, @Data
     
            WHILE @@FETCH_STATUS = 0
            BEGIN
                IF @QtdeInicial >= (@PagAtual * @QtdePagina) - @QtdePagina
                BEGIN
                    INSERT INTO #ArtigosTemp VALUES(@CH_TOP, @Titulo, @Data )
                    SET @Contador = @Contador + 1
     
                    IF @Contador >= @QtdePagina
                        BREAK
                END
                SET @QtdeInicial = @QtdeInicial + 1
     
                FETCH NEXT FROM curPaginacaoArtigos
                   INTO @CH_TOP,  @Titulo, @Data
            END
     
        CLOSE curPaginacaoArtigos
        DEALLOCATE curPaginacaoArtigos
     
        SELECT ch_top, Titulo, Data FROM #ArtigosTemp
     
        DROP TABLE #ArtigosTemp
     
        SET NOCOUNT OFF
     
        SET @TotalArtigos = (SELECT COUNT(ch_top) FROM Tb_Pec_02_Artigos where  Categoria=@vCategoria )

    Dirceu
    quarta-feira, 16 de maio de 2007 12:24

Todas as Respostas

  • Dirceu,

     

    Inicialmente seria necessário analisar o plano de execução desta procedure para procurar identicar possíveis gargalo durante a execução, mas a principio quando se trabalha com cursor o SQL Server tem um algum custo de processamento.

     

     

    quarta-feira, 16 de maio de 2007 13:20
  • Bom dia Dirceu concordo com o Junior, procure substituir seu cursor para uma variável do tipo table ou até mesmo por uma tabela temporária. Boa sorte

     

     

     

     

     

     

    Espero ter ajudado

    quarta-feira, 16 de maio de 2007 15:54
  • Você pode trocar o uso do cursos por uma variavel temp, segue um exemplo de como o código ficaria.

     

     

    CREATE PROCEDURE [dbo].[USP_Pec_02_Artigos_Lista]

    @QtdePagina As Int,

    @vCategoria As Varchar(50),

    @PagAtual As Int,

    @TotalArtigos As Int OUTPUT

    AS

    DECLARE @ID as Int

    DECLARE @ch_top AS Int

    DECLARE @Titulo As Varchar(200)

    DECLARE @Data As DateTime

    DECLARE @QtdeInicial As Int

    DECLARE @Contador As Int

    SET @QtdeInicial = 0

    SET @Contador = 0

    SET NOCOUNT ON

    CREATE TABLE #ArtigosTemp

    (

    ch_top int,

    Titulo Varchar(200),

    Data DateTime

    )

    DECLARE @TabPaginacaoArtigos Table(ID Int IDENTITY(1,1),

    ch_top Int,

    Titulo VarChar(200),

    Data DateTime);

    INSERT INTO @TabPaginacaoArtigos (ch_top, Titulo, Data)

    SELECT ch_top, Titulo, Data

    FROM Tb_Pec_02_Artigos

    WHERE Categoria = @vCategoria

    ORDER BY Data Desc

    SET @ID = 0;

    SELECT TOP 1

    @ID = ID,

    @ch_top = ch_top,

    @Titulos = Titulos,

    @Data = Data

    FROM @TabPaginacaoArtigos

    WHERE ID > @ID

    ORDER BY ID;

    WHILE @@RowCount > 0

    BEGIN

    IF @QtdeInicial >= (@PagAtual * @QtdePagina) - @QtdePagina

    BEGIN

    INSERT INTO #ArtigosTemp VALUES(@CH_TOP, @Titulo, @Data )

    SET @Contador = @Contador + 1

    IF @Contador >= @QtdePagina

    BREAK

    END

    SET @QtdeInicial = @QtdeInicial + 1

    END

    SELECT TOP 1

    @ID = ID,

    @ch_top = ch_top,

    @Titulos = Titulos,

    @Data = Data

    FROM @TabPaginacaoArtigos

    WHERE ID > @ID

    ORDER BY ID;

    SELECT ch_top, Titulo, Data FROM #ArtigosTemp

    DROP TABLE #ArtigosTemp

    SET NOCOUNT OFF

    SET @TotalArtigos = (SELECT COUNT(ch_top) FROM Tb_Pec_02_Artigos where Categoria=@vCategoria )

    END

    quarta-feira, 16 de maio de 2007 18:14
  • Deu esse erro:

    Msg 137, Level 15, State 1, Procedure USP_Pec_02_Artigos_Lista2, Line 69
    Must declare the scalar variable "@Titulos".
    Msg 137, Level 15, State 1, Procedure USP_Pec_02_Artigos_Lista2, Line 107
    Must declare the scalar variable "@Titulos".
    Msg 102, Level 15, State 1, Procedure USP_Pec_02_Artigos_Lista2, Line 125
    Incorrect syntax near 'END'.

    Dirceu
    quarta-feira, 16 de maio de 2007 18:57
  •  

    Tente novamente com este comando:

     

    CREATE PROCEDURE [dbo].[USP_Pec_02_Artigos_Lista]

    @QtdePagina As Int,

    @vCategoria As Varchar(50),

    @PagAtual As Int,

    @TotalArtigos As Int OUTPUT

    AS

    DECLARE @ID as Int

    DECLARE @ch_top AS Int

    DECLARE @Titulo As Varchar(200)

    DECLARE @Data As DateTime

    DECLARE @QtdeInicial As Int

    DECLARE @Contador As Int

    SET @QtdeInicial = 0

    SET @Contador = 0

    SET NOCOUNT ON

    CREATE TABLE #ArtigosTemp

    (

    ch_top int,

    Titulo Varchar(200),

    Data DateTime

    )

    DECLARE @TabPaginacaoArtigos Table(ID Int IDENTITY(1,1),

    ch_top Int,

    Titulo VarChar(200),

    Data DateTime);

    INSERT INTO @TabPaginacaoArtigos (ch_top, Titulo, Data)

    SELECT ch_top, Titulo, Data

    FROM Tb_Pec_02_Artigos

    WHERE Categoria = @vCategoria

    ORDER BY Data Desc

    SET @ID = 0;

    SELECT TOP 1

    @ID = ID,

    @ch_top = ch_top,

    @Titulo = Titulo,

    @Data = Data

    FROM @TabPaginacaoArtigos

    WHERE ID > @ID

    ORDER BY ID;

    WHILE @@RowCount > 0

    BEGIN

    IF @QtdeInicial >= (@PagAtual * @QtdePagina) - @QtdePagina

    BEGIN

    INSERT INTO #ArtigosTemp VALUES(@CH_TOP, @Titulo, @Data )

    SET @Contador = @Contador + 1

    IF @Contador >= @QtdePagina

    BREAK

    END

    SET @QtdeInicial = @QtdeInicial + 1

    END

    SELECT TOP 1

    @ID = ID,

    @ch_top = ch_top,

    @Titulo = Titulo,

    @Data = Data

    FROM @TabPaginacaoArtigos

    WHERE ID > @ID

    ORDER BY ID;

    SELECT ch_top, Titulo, Data FROM #ArtigosTemp

    DROP TABLE #ArtigosTemp

    SET NOCOUNT OFF

    SET @TotalArtigos = (SELECT COUNT(ch_top) FROM Tb_Pec_02_Artigos where Categoria=@vCategoria )

    quarta-feira, 16 de maio de 2007 20:18
  • Olá Fabiano,

    Obrigado pelas dicas.

    Consegui implementar destas forma como me passou, mas a paginação que a procedure executava deixou de funcionar.

     

    Uso este código com esta precedure:

    private int _totalRegistrosPagina = 20;

    int Totalreg;

    string retorno = null;

    protected void Page_Load(object sender, EventArgs e)

    {

    Response.Cache.SetExpires(DateTime.Now.AddSeconds(165));

    retorno = Request.ServerVariables["HTTP_REFERER"];

    if (!(Page.IsPostBack))

    {

    this.CarregaGrid(1);

    this.Navegacao(1);

    }

    }

    private void CarregaGrid(int paginaAtual)

    {

    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["E-CampoConnectionString"].ConnectionString);

    SqlDataReader dr = null;

    SqlCommand cmd = new SqlCommand("USP_Pec_02_Artigos_Lista", conn);

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add(new SqlParameter("@Vcategoria", SqlDbType.NVarChar, 50));

    cmd.Parameters["@Vcategoria"].Value = "Orgƒnicos";

    SqlParameter paramQtdeRegistroPagina;

    paramQtdeRegistroPagina = new SqlParameter("@QtdePagina", SqlDbType.Int);

    paramQtdeRegistroPagina.Value = this._totalRegistrosPagina;

    cmd.Parameters.Add(paramQtdeRegistroPagina);

    SqlParameter paramPaginaAtual;

    paramPaginaAtual = new SqlParameter("@PagAtual", SqlDbType.Int);

    paramPaginaAtual.Value = paginaAtual;

    cmd.Parameters.Add(paramPaginaAtual);

    SqlParameter paramTotalRegistros;

    paramTotalRegistros = new SqlParameter("@TotalArtigos", SqlDbType.Int);

    paramTotalRegistros.Direction = ParameterDirection.Output;

    cmd.Parameters.Add(paramTotalRegistros);

    try

    {

    conn.Open();

    dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

    if (dr.HasRows)

    {

    this.dtlNoticia.DataSource = dr;

    this.dtlNoticia.DataBind();

    this.lblPaginaCorrente.Text = paginaAtual.ToString();

    Totalreg = Convert.ToInt32(cmd.Parameters["@TotalArtigos"].Value);

    this.lblTotalReg.Text = Totalreg.ToString();

    }

    }

    catch (Exception)

    {

    Response.Redirect("http://wwww.ecampo.com.br/Conteudo/Comunicacao/");

    }

    finally

    {

    if (!((dr == null)))

    {

    dr.Close();

    }

    this.ControlePaginacao(Convert.ToInt32(cmd.Parameters["@TotalArtigos"].Value));

    }

    }

    private void ControlePaginacao(int totalArtigo)

    {

    if ((totalArtigo % this._totalRegistrosPagina) == 0)

    {

    this.lblTotal.Text = Convert.ToString((totalArtigo / this._totalRegistrosPagina));

    }

    else

    {

    this.lblTotal.Text = Convert.ToString(Convert.ToInt32((totalArtigo / this._totalRegistrosPagina) + 1));

    }

    }

    private void Navegacao(int paginaAtual)

    {

    if (Totalreg == 0)

    {

    dtlNoticia.Visible = false;

    }

    else

    {

    if (Totalreg <= 20)

    {

    this.lnkPrevious.Enabled = false;

    this.lnkNext.Enabled = false;

    this.lnkFirst.Enabled = false;

    this.lnkLast.Enabled = false;

    }

    else

    {

    if (paginaAtual == 1)

    {

    this.lnkPrevious.Enabled = false;

    this.lnkNext.Enabled = true;

    this.lnkFirst.Enabled = false;

    this.lnkLast.Enabled = true;

    }

    else if (paginaAtual == Convert.ToInt32(this.lblTotal.Text) && paginaAtual > 1)

    {

    this.lnkPrevious.Enabled = true;

    this.lnkNext.Enabled = false;

    this.lnkFirst.Enabled = true;

    this.lnkLast.Enabled = false;

    }

    else

    {

    this.lnkPrevious.Enabled = true;

    this.lnkNext.Enabled = true;

    this.lnkFirst.Enabled = true;

    this.lnkLast.Enabled = true;

    }

    }

    }

    }

     

    Já tentei mas não consegui localizar o erro!

     

    Grato,

    Dirceu

    segunda-feira, 21 de maio de 2007 19:13