Melhorar a performance da procedure

Question

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

Answer 1

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.

 

 

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

 

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 )

Answer 6

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