June 29, 2010 at 6:17 am
Hi guys! First time i post there /o/
I would like a better aproach for a procedure i have developed... Actually i have 2 main problems, the dynamic SQL and the multiple value in the conditional.
My actual code:
CREATE PROCEDURE [dbo].[sp_Associacao_Distrito]
@versoes varchar(500) = null,
@modelos varchar(500) = null,
@segmentos varchar(500) = null,
@subsegmentos varchar(500) = null,
@regioesOperacionais varchar(500) = null,
@distritos varchar(500) = null,
@estados varchar(500) = null,
@areasOperacionais varchar(500) = null,
@cidades varchar(500) = null,
@bairros varchar(500) = null,
@concessionarias varchar(500) = null,
@modalidadesVenda varchar(500) = null,
@data_ini varchar(10) = null,
@data_fim varchar(10) = null,
@id_concessionaria varchar(1000) = null,
@classesMercado varchar(500) = null
AS
BEGIN
SET NOCOUNT ON;
DECLARE @str nvarchar(4000),
@paramlist nvarchar(4000)
SET @str = 'SELECT '
SET @str = @str + ' nm_distrito, '
SET @str = @str + ' count(id_importacao_associacao) as qtd '
IF convert(datetime, @data_ini) >= convert(varchar(10), dateadd(month, -1, getdate()),101) AND
convert(datetime, @data_fim) >= convert(varchar(10), dateadd(month, -1, getdate()),101)
BEGIN
SET @str = @str + ' FROM vw_associacao_fast as vw' -- \o\ \o\ \o/ \o/ \o/ /o/ /o/
END
ELSE IF convert(datetime, @data_ini) >= convert(varchar(10), DATEADD(mm,DATEDIFF(mm,0, DATEADD(month, -12, GETDATE()) ),0),101) AND
convert(datetime, @data_fim) >= convert(varchar(10), DATEADD(mm,DATEDIFF(mm,0, DATEADD(month, -12, GETDATE()) ),0),101)
BEGIN
SET @str = @str + ' FROM vw_associacao_ultimos_meses as vw' -- \o\ \o/ /o/
END
ELSE
BEGIN
SET @str = @str + ' FROM vw_associacao as vw'
END
SET @str = @str + ' WHERE 1=1'
IF @versoes is not nullSET @str = @str + ' AND (exists(SELECT * FROM dbo.fnSplit(@versoes,'','') as fnV where fnV.item = vw.id_versao )) '
IF @modelos is not nullSET @str = @str + ' AND (exists(SELECT * FROM dbo.fnSplit(@modelos,'','') as fnV where fnV.item = vw.id_modelo )) '
IF @segmentos is not nullSET @str = @str + ' AND (exists(SELECT * FROM dbo.fnSplit(@segmentos,'','') as fnV where fnV.item = vw.id_segmento )) '
IF @subsegmentos is not nullSET @str = @str + ' AND (exists(SELECT * FROM dbo.fnSplit(@subsegmentos,'','') as fnV where fnV.item = vw.id_sub_segmento )) '
IF @regioesOperacionais is not null SET @str = @str + ' AND (exists(SELECT * FROM dbo.fnSplit(@regioesOperacionais,'','') as fnV where fnV.item = vw.id_regiao_operacional )) '
IF @distritos is not nullSET @str = @str + ' AND (exists(SELECT * FROM dbo.fnSplit(@distritos,'','') as fnV where fnV.item = vw.id_distrito )) '
IF @estados is not nullSET @str = @str + ' AND (exists(SELECT * FROM dbo.fnSplit(@estados,'','') as fnV where fnV.item = vw.id_estado )) '
IF @areasOperacionais is not nullSET @str = @str + ' AND (exists(SELECT * FROM dbo.fnSplit(@areasOperacionais,'','') as fnV where fnV.item = vw.id_area_operacional )) '
IF @cidades is not nullSET @str = @str + ' AND (exists(SELECT * FROM dbo.fnSplit(@cidades,'','') as fnV where fnV.item = vw.id_cidade )) '
IF @concessionarias is not nullSET @str = @str + ' AND (exists(SELECT * FROM dbo.fnSplit(@concessionarias,'','') as fnV where fnV.item = vw.id_concessionaria )) '
IF @modalidadesVenda is not nullSET @str = @str + ' AND (exists(SELECT * FROM dbo.fnSplit(@modalidadesVenda,'','') as fnV where fnV.item = vw.id_modalidade_venda )) '
IF @classesMercado is not nullSET @str = @str + ' AND (exists(SELECT * FROM dbo.fnSplit(@classesMercado,'','') as fnV where fnV.item = vw.id_classe_mercado )) '
IF @data_ini is not nullSET @str = @str + ' AND(dt_emplacamento >= @data_ini) '
IF @data_fim is not nullSET @str = @str + ' AND(dt_emplacamento <= @data_fim) '
IF @id_concessionaria is not NULL SET @str = @str + ' AND (exists(SELECT * FROM dbo.fnSplit(@id_concessionaria,'','') as fnV where fnV.item = vw.id_concessionaria ) ) '
SET @str = @str + ' group by nm_distrito'
SET @str = @str + ' order by qtd desc'
SELECT @paramlist = '@versoesvarchar(500),
@modelosvarchar(500),
@segmentosvarchar(500),
@subsegmentosvarchar(500),
@regioesOperacionaisvarchar(500),
@distritosvarchar(500),
@estadosvarchar(500),
@areasOperacionaisvarchar(500),
@cidadesvarchar(500),
@bairrosvarchar(500),
@concessionariasvarchar(500),
@modalidadesVendavarchar(500),
@data_inivarchar(10),
@data_fimvarchar(10),
@id_concessionariavarchar(500),
@classesMercadovarchar(500)'
Exec sp_executesql @str, @paramlist,
@versoes, @modelos,@segmentos,@subsegmentos,@regioesOperacionais,
@distritos,@estados,@areasOperacionais,@cidades,@bairros,@concessionarias,
@modalidadesVenda,@data_ini,@data_fim,@id_concessionaria,@classesMercado
END
I have all those parameters that actually i pass as a concatenated string separed by ",", so in the conditional i use this function fnsplit that returns a table for this string... looking in the execution plan, when this happens its something like running to the hills...
I have tried change this to User Table as a parameter and doing joins in the select, but this neither resolved performance issues, neither i can have an optional join...
The issue about the dynamic SQL that i would like to ask is that... i read some article about it, a lot of people dont like it, and others like... reading Erland Sommarskog article for example i learned about the sp_executesql command... and one advice he make is that to much of this will increase the database size because sql sabe all execution plan... so, i have around 50 procedures that use this same schema of select... will that be a problem?
The main reason i choose the dynamic sql was to use the IF to determine which table i would use, because this database has something like 3 millions lines in the table... and the table fast, and ultimos_meses i have few data, what make it faster... and because of... if no parameter was sent, dont need to verify it anyway....
So... i am a so bad DBA as it appear, or iam going in the right path? Theres something i could do to improve performance?
Thanks a lot
June 29, 2010 at 6:22 am
Does performance improves if you hardcode what your are fnsplit() today?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 29, 2010 at 6:27 am
You mean, if i make the split in the code instead of calling the function? What about reuse of code?
This is actually this functions.. i have found it in the internet by the way:
CREATE FUNCTION [dbo].[fnSplit](
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
June 29, 2010 at 6:29 am
diego_silva_pires (6/29/2010)
You mean, if i make the split in the code instead of calling the function?
No. I mean skip the function - just a test - to see if performance issue is caused by the function.
If performance remains bad then function is not the problem.
If performance gets - magically - fantastic then function has to be finetuned.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 29, 2010 at 6:42 am
First time i execute the procedure, i dont pass any parameter, so the function arent called... the procedure returns in 5 seconds, second time i execute it took just one second...
Then i pass one of the parameters, a string with 10 values separeted by "," it took 9 seconds, second time i execute, just one second too....
But i think function its not the real problem... i have others procedures there that has the same problem... first time executing take a lots of time, after that.. 1 second of less, probably because of the sp_executesql... but the parameters passed by the end user are not the same, so everytime will be created a new execution plan, is that right?
June 29, 2010 at 6:53 am
There's nothing wrong with a few of these types of queries in almost any system. They do come up, and it's pretty normal. Using sp_executesql and parameters can improve the code reuse and cut down on the amount of compiles and recompiles you experience. All good. But when you're talking about setting up 50 or more of these stored procedures, I'd say you're on a bad path and need to reexamine your architecture. How is it that you need to have 50 different catch-all queries? It's probably worth reading Gail Shaw's post[/url] on this.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 29, 2010 at 6:57 am
Here is what happens when same query is executed twice.
First run has to do physical I/O to move datablocks from disk to buffer cache the logical I/O to serve the query.
Second run - providing it is executed soon after first run - doesn't have to do the physical I/O e.g. datablocks are already in the buffer cache so the most expensive phase of the query is skipped then query is perceived as performing better.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 29, 2010 at 7:25 am
Grant, i have all these proc because all of then return a different thing.... let me try to give you guys a more wide view...
This table vw_associacao, and their deriveted vw_associacao_fast and vw_associacao_ultimos_meses are a condensed table, without joins, just ids and values for i mount all reports i need...
So i have one proc that return the name and quantity, agruped by the field nm_distrito, another one for nm_estado, another one for nm_pais, etc... there are something like 10 name fields, i have at least one proc for each of of then... and others procedures for more complex reports...
And this vw_associacao is one table... i have another one, vw_industria, that works the same, but has some different fields... the creation of this table was already a choise to avoid one normalization by joins that we need to do all the time, something that you saw her that consumed really a lot of time and resources....
Thats why i asked that if the dynamic SQL in this scenario of lots of procedures was a good idea... and the problem of the multiple condition... the article you sent to my, the guy refer to use dynamic sql to avoid the 'or is null'... but in the other hand i have many procs in dynamic that is not a good scenario too...
And Paulb... so... nice, first time do the hard work, after that its all good... but if a change just one parameter all the hard work will need to be done again?
One guy that work with me suggested that i forget about the dynamic sql, make a lots of procs, one for each table to search, instead of my if to verify the data.... this would be better? Some tests i made after all i found the same problem of performance because of the hard use of conditional...
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply