PERFORMANCE FUNCTION OU STORED PROC
Tenho uma aplicação onde estou com alguns problemas de performance num select. Depois de ler vários artigos aqui e pelo mundo afora, resolvi mudar a rotina rodando esta query no SQL-Server.
Minha dúvida é:
Como preciso retornar um resultset (recordset), qual seria a melhor opção, uma function retornando um 'TABLE' ou uma SP retornando um 'CURSOR' ?
Minha dúvida é:
Como preciso retornar um resultset (recordset), qual seria a melhor opção, uma function retornando um 'TABLE' ou uma SP retornando um 'CURSOR' ?
bem, eu faço sempre uma stored retornando um cursor, é mais rápido.
Se vc precisa de performance vc tem que usar ADO e seu recordset precisa ser aberto adFowardOnly e adReadOnly.
Além disto vc precisa considerar a utilização de indices na tabela em que vc realiza a sua consulta.
Indices é o tipo de coisa que faz rotina que demora horas passar a demorar segundos
Além disto vc precisa considerar a utilização de indices na tabela em que vc realiza a sua consulta.
Indices é o tipo de coisa que faz rotina que demora horas passar a demorar segundos
Citação:MARCOSA escreveu:
bem, eu faço sempre uma stored retornando um cursor, é mais rápido.
Tem como me mandar um exemplo ? de construção e de execução por favor..
Grato desde já,
CLins
Private Sub Command1_Click()
'Reservamos espaço na memória para as variáveis da rotina ( ou Dimencionamos "Dim")
Dim strDB As String
Dim strConn As String
Dim strSQL As String
Dim objCNN As ADODB.Connection
Dim objRS As ADODB.RecordSet
'Aqui vc deve ter uma válida para substituir a que coloquei de exemplo
'Caso não tenha crie um arquivo de nome qualquer .udl para o Windows auxiliá-lo
strConn = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=GERAL_SQL;" & _
"Data Source=desenv\sqlexpress"
'Efetivamente criamos na memória do computador o objeto connection que tinhamos reservado espaço
Set objCNN = New ADODB.Connection
'Usamos o objeto criado para conectar ao banco
objCNN.Open strConn
'Uma string com um comando SQL qualquer
strSQL = "SELECT * FROM tabela"
'Efetivamente criamos na memória do computador o objeto recordset que tinhamos reservado espaço.
Set objRS = New ADODB.Connection
'Aqui usamos o objeto para executar o comando da string através da conexão
'adOpenForwardOnly = Seu Rs somente aceitará movenext e movelast
'adLockReadOnly = Seu Rs não será utilizado para realizar modificações nos dados
objRS.Open strSQL, objCNN, adOpenForwardOnly, adLockReadOnly
'Loop em que percorremos o Rs para carregar algum controle do form
While Not objRS.Eof
.... Faça algo com o registro atual
'Mova ao próximo
objRS.Movenext
Whend
'Fechamos a conexão
objCNN.Close
'Fechamos o RecordSet
objRS.Close
'ExcluÃmos da memória aqueles objetos criado anteriormente
'A linguagem VB tem problemas com estes tipos de variáveis
Set objCNN = Nothing
Set objRS = Nothing
End Sub
'Reservamos espaço na memória para as variáveis da rotina ( ou Dimencionamos "Dim")
Dim strDB As String
Dim strConn As String
Dim strSQL As String
Dim objCNN As ADODB.Connection
Dim objRS As ADODB.RecordSet
'Aqui vc deve ter uma válida para substituir a que coloquei de exemplo
'Caso não tenha crie um arquivo de nome qualquer .udl para o Windows auxiliá-lo
strConn = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=GERAL_SQL;" & _
"Data Source=desenv\sqlexpress"
'Efetivamente criamos na memória do computador o objeto connection que tinhamos reservado espaço
Set objCNN = New ADODB.Connection
'Usamos o objeto criado para conectar ao banco
objCNN.Open strConn
'Uma string com um comando SQL qualquer
strSQL = "SELECT * FROM tabela"
'Efetivamente criamos na memória do computador o objeto recordset que tinhamos reservado espaço.
Set objRS = New ADODB.Connection
'Aqui usamos o objeto para executar o comando da string através da conexão
'adOpenForwardOnly = Seu Rs somente aceitará movenext e movelast
'adLockReadOnly = Seu Rs não será utilizado para realizar modificações nos dados
objRS.Open strSQL, objCNN, adOpenForwardOnly, adLockReadOnly
'Loop em que percorremos o Rs para carregar algum controle do form
While Not objRS.Eof
.... Faça algo com o registro atual
'Mova ao próximo
objRS.Movenext
Whend
'Fechamos a conexão
objCNN.Close
'Fechamos o RecordSet
objRS.Close
'ExcluÃmos da memória aqueles objetos criado anteriormente
'A linguagem VB tem problemas com estes tipos de variáveis
Set objCNN = Nothing
Set objRS = Nothing
End Sub
à Ândices são a grosso modo a ordenação fÃsica da tabela
Vamos supor que tenhamos uma tabela cliente ordenada pelo campo id_cliente, qualquer consulta em que enviarmos id_cliente como referencia irá originar uma busca chamada binária que na prática diminui as vezes milhares de passos numa consulta em tabelas com milhares de registros.
Por outro lado se fizermos uma consulta onde enviamos parametros de busca para o campo nm_cliente ele terá que fazer uma busca linear (percorrer a tabela inteira - "table scan") para garantir o resultado.
Para fazer isto vc precisa entrar no SGBD e criar indices para os campo que vc utiliza na clausula WHERE de sua consulta.
Esta dica só é válida para consultas que estejam verificando dados em tabelas e alguma(s) muito grandes (Digo GRAANDES 100.000 de registros pra mais), senão seu problema pode ser de rede ou lógica de programação.
O inconveniente é que ordenar (indexar) tabela implica em que os dados quando são inseridos ou deletados obrigam uma reorganização imediata o que deixa o processo lento e se isto compromete a aplicação não pode ser feito.
Vamos supor que tenhamos uma tabela cliente ordenada pelo campo id_cliente, qualquer consulta em que enviarmos id_cliente como referencia irá originar uma busca chamada binária que na prática diminui as vezes milhares de passos numa consulta em tabelas com milhares de registros.
Por outro lado se fizermos uma consulta onde enviamos parametros de busca para o campo nm_cliente ele terá que fazer uma busca linear (percorrer a tabela inteira - "table scan") para garantir o resultado.
Para fazer isto vc precisa entrar no SGBD e criar indices para os campo que vc utiliza na clausula WHERE de sua consulta.
Esta dica só é válida para consultas que estejam verificando dados em tabelas e alguma(s) muito grandes (Digo GRAANDES 100.000 de registros pra mais), senão seu problema pode ser de rede ou lógica de programação.
O inconveniente é que ordenar (indexar) tabela implica em que os dados quando são inseridos ou deletados obrigam uma reorganização imediata o que deixa o processo lento e se isto compromete a aplicação não pode ser feito.
Emerson, grato pela ajuda.
Com relação ao ADO eu conheço toda a parte de acesso, atualização,etc...
Minha dúvida principal é com a SP retornando um cursor....
Mas de qualquer forma muito obrigado pela força !
Com relação ao ADO eu conheço toda a parte de acesso, atualização,etc...
Minha dúvida principal é com a SP retornando um cursor....
Mas de qualquer forma muito obrigado pela força !
Nenhum recordset é retornado mais rapidamente do que com esta configuração:
objRS.Open strSQL, objCNN, adOpenForwardOnly, adLockReadOnly
retornar table, cursor ... isto é coisaa de DAO
O restante fica por conta da sua coniguração no server.
objRS.Open strSQL, objCNN, adOpenForwardOnly, adLockReadOnly
retornar table, cursor ... isto é coisaa de DAO
O restante fica por conta da sua coniguração no server.
Clins, faça como o amigo MARCOSA disse...crie uma SP e retorne um select no final dela. Este select será o rowset de retorno para o ADO.Só de vc colocar numa SP, vc já tera um ganho de performance, nme entrando no merito de tuning da mesma.
Algo como :
create proc dbo.nomeprocedure
as
begin
set nocount on
--comandos que vc precisa e no final o select de retorno
select campo from tabela where condição
end
no ado...é como se vc fosse usar para dar um select
dim cn as adodb.connection
set cn = new adodb.connection
dim rs as adodb.recordset
set rs = new adodb.recordset
Se não me engano, por default o ADO já executa o comando usando o que chamamos de firehouse cursor que é a melhor maneira a nivel de performance, que se nao me engano é como foi explanado pelo outro rapaz.
strsql = "EXEC <nomeProcedure>"
rs.open strsql,cn
desta maneira o select retornado pela SP vai para um recordset (RS) no ado.
caso vc precise usar comandos sql ad-hoc ou seja, diretamente na aplicação e nao em SPÂÂÂ's, e estiver usando sql server, utilize a sp_executesql em vez do select direto. Essa procedure do sql consegue montar um plano de execução e colocar em cache para possivel reaproveitamento, pois consultas ad-hocs nao geram plano de execução no sql server. Algo como
Em vez de
strsql = "select nome from clientes where codigo = 1"
use
strsql = "exec sp_executesql 'select nome from clientes where codigo = 1'"
Abraços
Algo como :
create proc dbo.nomeprocedure
as
begin
set nocount on
--comandos que vc precisa e no final o select de retorno
select campo from tabela where condição
end
no ado...é como se vc fosse usar para dar um select
dim cn as adodb.connection
set cn = new adodb.connection
dim rs as adodb.recordset
set rs = new adodb.recordset
Se não me engano, por default o ADO já executa o comando usando o que chamamos de firehouse cursor que é a melhor maneira a nivel de performance, que se nao me engano é como foi explanado pelo outro rapaz.
strsql = "EXEC <nomeProcedure>"
rs.open strsql,cn
desta maneira o select retornado pela SP vai para um recordset (RS) no ado.
caso vc precise usar comandos sql ad-hoc ou seja, diretamente na aplicação e nao em SPÂÂÂ's, e estiver usando sql server, utilize a sp_executesql em vez do select direto. Essa procedure do sql consegue montar um plano de execução e colocar em cache para possivel reaproveitamento, pois consultas ad-hocs nao geram plano de execução no sql server. Algo como
Em vez de
strsql = "select nome from clientes where codigo = 1"
use
strsql = "exec sp_executesql 'select nome from clientes where codigo = 1'"
Abraços
Tópico encerrado , respostas não são mais permitidas