optimization x crash query
-
hi! me again!
As some of you have seen, i've been trying to make some functions to split a delimited value ( ids delimited by ; ) into their description, from a join at another table.
Now that the functions are all working ( split(), id2description(), I tried to put that function inside the main query which makes four or five joins. Fine.
The query works, returns 132 rows in some seconds.
But when I put the function id2description( fielddelimited ) to the main query, it simply keeps runing forever and never ends. I waited for 11 minutes and It was still running
Then I limited main query to 5 rows, 10 rows, 15 rows. Worked with the function included. But when I put more, like 20 rows ( top 20 ), It start to run forever.
How can I find where it is starting to get lost in the query?
Do I have to open close the database conection in the function or something?
I have no clue. Who's fault? The left joins'? the function's join for each row? ( its like 3 or 4 ids mostly for each row )
any idea?
tables structures:
maintable {id, fields, delimitedfield,fk,fk,fk,fk and left joins for those fks} >> results 132 rows
roles (id, description) >> results around 10 rows
delimitedfield = id;id;id;
( the function just split this value, get each id description and return a descr-descr-descr string )
[]s
-
First things first:
1. Functions force row-by-row processing (use them sparingly)
2. Make sure you have indexes in place that are being used by your where clauses, joins, group bys and order bys. (Check Execution plan)
3. We need to see the query or it will be very difficult to guess
Cheers,
* Noel -
these are the functions:
/*
Split() quebrar uma string com valores delimitados por 1 caractere
@param input string delimitada
@param delimiter delimitador com 1 caractere qualquer
@return output table output{valor}
@usage SELECT valor FROM Split( 'aa;bb;ff;xx;', '|' )
*/
ALTER FUNCTION Split( @input VARCHAR(1000), @delimiter CHAR(1) = '|' )
RETURNS @output TABLE( valor VARCHAR(1000) )
AS
BEGIN
DECLARE @join VARCHAR(1000)
DECLARE @result VARCHAR(1000)
DECLARE @valor VARCHAR(1000)
WHILE( 1 = 1 )
BEGIN
--ver se o delimitador existe na string
IF charindex( @delimiter, @input ) = 0
BEGIN
--se não existir o delimitador, insere somente a string na tabela de saída
IF( @input '' )
BEGIN
INSERT INTO @output ( valor ) VALUES ( @input )
END
BREAK
END
ELSE
BEGIN
if charindex( ';', @input ) = 1
begin
SET @input = substring( @input, 2, len(@input) )
end
SET @valor = substring( @input, 1, charindex( @delimiter, @input ) -1 )
IF( @valor '' )
BEGIN
INSERT INTO @output ( valor ) VALUES( @valor )
SET @input = SUBSTRING( @input, CHARINDEX( @delimiter, @input )+ 1, LEN( @input ) )
END
END
END
RETURN
END
-- SELECT TOP 10 valor FROM dbo.Split( ';14;0;-1;-2;', ';' )
-- SELECT TOP 10 valor FROM dbo.Split( ';0;', ';' )
/*
ids2Rolename() converte uma tabela de ids (roles.roleid) em suas descrições delimitada por '-'
@param idlist string delimitada
@param delimiter delimitador com 1 caractere qualquer
@return result string
@usage SELECT Split( 'aa;bb;ff;xx;', '|' )
*/
drop function ids2Rolenames
create FUNCTION ids2Rolenames( @idlist VARCHAR(1000), @delimiter CHAR(1) )
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @tmp_ids TABLE( valor VARCHAR(1000) )
DECLARE @tmp_distinct TABLE( valor VARCHAR(1000), description VARCHAR(1000) )
DECLARE @tmp_description TABLE( description VARCHAR(1000) )
DECLARE @result VARCHAR(1000)
/* get a table with splited data - each id in a row */
INSERT INTO @tmp_ids SELECT valor FROM Split(@idlist, @delimiter )
INSERT INTO @tmp_distinct
SELECT
s.valor ,
CASE s.valor
WHEN '0' THEN 'Administrators'
WHEN '-1' THEN 'All Users'
WHEN '-2' THEN 'Host'
WHEN '-3' THEN 'Unauthenticated Users'
WHEN '' THEN ''
WHEN NULL THEN ''
ELSE r.rolename
END AS description
FROM @tmp_ids s
LEFT OUTER JOIN roles r ON s.valor = r.roleid
INSERT INTO @tmp_description SELECT DISTINCT(description) FROM @tmp_distinct
SELECT @result = ISNULL( @result + ' - ', '' ) + description FROM @tmp_description
RETURN @result
END
-- select valor from Split( ';0;-1;-2;', ';' )
-- SELECT dbo.ids2Rolenames( '14;0;-1;-2;', ';' ) as rolenames
this is the main query that crashes with more than 10 results:
SELECT TOP 11
p.PortalName AS NomedoPortal,
tp.TabName AS PáginaPai,
t.TabName AS Página,
m.ModuleTitle AS TitulodoModulo,
md.FriendlyName AS NomedoModulo,
m.PaneName AS PanedoMódulo,
m.Alignment AS Alinhamento,
m.AuthorizedEditRoles AS PodemEditar,
m.AuthorizedViewRoles AS PodemVer
--,( SELECT dbo.ids2Rolenames( m.AuthorizedViewRoles, ';' ) ) AS PodemVer_descr
FROM
tabs t
LEFT OUTER JOIN tabs tp ON t.parentid = tp.parentid
LEFT OUTER JOIN portals p ON t.portalid = p.portalid
LEFT OUTER JOIN modules m ON t.tabid = m.tabid
LEFT OUTER JOIN moduledefinitions md ON m.moduledefid = md.moduledefid
LEFT OUTER JOIN desktopmodules dm ON md.desktopmoduleid = dm.desktopmoduleid
WHERE 0=0
AND t.isdeleted = 0
AND m.isdeleted = 0
AND dm.isadmin = 0 --AND m.moduleid = 367
AND p.PortalName IS NOT NULL --modulos internos de banners
ORDER BY m.ModuleTitle
-
If you're not going to need to filter the results of the function, I would apply it in an outer step.
That is: do your primary select statement in an embedded query and apply the function in the outer query.
Select w.*, functioncall( w.listcolumn, ';') as desc_list
FROM ( SELECT....outer join everthingunderthesun...etc.
) w
Then optimize the internal query by itself, and see how it performs stand alone and then with the outer query with the function call.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply