top cqu sql how to tune
-
there is a function in legacy code view below and the ind query shows up as top sql.
Any idea how to remove the function and replace it with adhoc sql.
Individual t-sql
SELECT @Type = coalesce(@Type + ', ', '') + type_desc from _dataDictionary_type
where type_id IN (select type from _TYPE_MAPPING WITH (NOLOCK) where Id = @_id)
-- this function is a part of a view and I want to remove it from the view
CREATE FUNCTION [dbo].[Stringmultitypes]
(@id int)
RETURNS VARCHAR(600)
AS
BEGIN
DECLARE @Type varchar(500)
SELECT @Type = coalesce(@Type + ', ', '') + type_desc from dataDictionary_type where type_id IN (select type from TYPE_MAPPING WITH (NOLOCK) where Id = @id)
RETURN @Type
END
- This topic was modified 4 years, 8 months ago by sqlguy80.
-
Can you show the code where the function is used? That will allow people to understand the context in which they have to work. Otherwise, the answer is, pull the function code as is and make it a derived table to join against the existing query (which, I know, is vague, but there you go).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
You could keep the function, but change it to an inline TVF, like this
CREATE FUNCTION dbo.Stringmultitypes ( @id int )
RETURNS table
AS
RETURN SELECT Types = CAST(STUFF((
SELECT ', ' + type_desc
FROM dataDictionary_type AS dt
INNER JOIN TYPE_MAPPING AS tm WITH ( NOLOCK )
ON dt.TYPE_ID = tm.TYPE
WHERE tm.Id = @id
GROUP BY type_desc
FOR XML PATH( '' )
), 1, 2, ''
) AS varchar(600));
GOThen you need to change the way that you call the function. Show us how you currently call it, and we can show you how to make the change.
-
Thank you, The code of the view which uses this function is below:
CREATE VIEW [dbo].
AS
SELECT DISTINCT
dbo.protocol_id,
--dbo.sitecode,
cs.sitecode,
dbo.PI_name as pi,
'SW' + RIGHT('00000000' + CAST(dbo._id AS VARCHAR(8)), 8) AS _id,
dbo.subject,
CASE
WHEN state = 0 THEN 'Submit'
WHEN state = 1 THEN 'Entry'
WHEN state = 2 THEN 'QC'
WHEN state = 3 THEN 'Complet'
WHEN state = 4 THEN 'Cancel'
END AS state,
date_state
, date_created,
dbo.ReasonToCreate,
CASE
WHEN type = 0 THEN 'New'
WHEN type = 1 THEN 'Demographic'
WHEN type = 2 THEN 'Subject Status'
WHEN type = 3 THEN 'Paper Entry'
WHEN type = 4 THEN 'Invalidation'
WHEN type = 5 THEN 'Visit Data'
WHEN type = 6 THEN 'Technical Revision'
WHEN type = 7 THEN 'Recon'
WHEN type = 8 THEN 'Subject-Entered Data'
WHEN type = 9 THEN 'Site-Entered Data'
END AS type,
CASE WHEN dbo.billable = 1 THEN 'Yes'
ELSE 'No'
END AS billable,
dbo._id as i_id,
dbo.login_id,
dbo.study_id,
dbo.site_id,
dbo.author_login_id,
dbo.author_name as author,
CASE
WHEN dbo.study_protocol IS NULL THEN 'Unspecified Protocol'
WHEN dbo.study_protocol = 'NULL' THEN 'Unspecified Protocol'
WHEN dbo.study_protocol = '' THEN 'Unspecified Protocol'
ELSE dbo.study_protocol
END AS study_protocol,
CASE
WHEN dbo.isQueryActive = 1 THEN 'Yes'
ELSE 'No'
END AS isQueryActive,
CASE
WHEN dbo.specialCase = 1 THEN 'Yes'
ELSE 'No'
END AS SpecialCase,
CASE
WHEN dbo.onHold = 1 THEN 'Yes'
ELSE 'No'
END AS onHold,
dbo.[Stringmultitypes](id) as multiType
FROM dbo., dbo.ca_site cs, ca_dataDictionary_Countries, ca_address, ca_site_address WITH (NOLOCK)
where dbo.site_id = cs.site_id
AND countryCode = ca_address.country
AND ca_address.address_id = ca_site_address.address_id
AND ca_site_address.site_id = site_id
-
The structure of yor SQL does not make sense to me. Is this for SQL Server, or some other flavour of SQL?
-
T-SQL , SQL Server , As I said its a database view and legacy code.. That's how code was written in the SQL Server 2000, 2005 days !
-
So, what is the name of the view?
Do you really have a table or view name dbo?
-
Help us out by providing the source table name for each field in the WHERE clause
-
view name is dbo.display_list and the code has the slow function which is showing up as top CPU..the function name and code is in the above code snippet.
stringmultitypes
-
Please see the code below.
Which tables contain the fields countryCode and site_id ?
FROM dbo.
, dbo.ca_site AS cs
, ca_dataDictionary_Countries
, ca_address
, ca_site_address WITH ( NOLOCK )
WHERE dbo.site_id = cs.site_id
AND countryCode = ca_address.country -- Which table contains the field countryCode?
AND ca_address.address_id = ca_site_address.address_id
AND ca_site_address.site_id = site_id -- Which table contains the field site_id? -
countrycode- table ca_dataDictionary_Countries
site_id- table ca_site_Study
-
You are going to have to check the joins. Without knowing your tables and data, I have attempted to change the joins, so that I can add the OUTER APPLY for the iTVF
-- ...,
onHold = CASE WHEN dbo.onHold = 1 THEN 'Yes' ELSE 'No' END,
--multiType = dbo.Stringmultitypes( id ), -- Replace this line with the one below
multiType = smt.Types
FROM dbo
INNER JOIN dbo.ca_site AS cs
ON dbo.site_id = cs.site_id
CROSS JOIN ca_dataDictionary_Countries
INNER JOIN ca_address
ON ca_dataDictionary_Countries.countryCode = ca_address.country
INNER JOIN ca_site_address WITH ( NOLOCK )
ON ca_address.address_id = ca_site_address.address_id
AND ca_site_address.site_id = cs.site_id
OUTER APPLY dbo.Stringmultitypes( id ) AS smt; -
Sorry, had to step away for a presentation. I agree with the suggestions so far.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
We're spinning our wheels because we don't know enough about your code or the underlying tables. You even have an error in the FROM clause of the code in your original post (dbo.,).
Please see the second link in my signature line below for what we need to help you better.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
When you are entering code in a post there is a little button you can press so it shows as code, this makes it easier for people to read your question:
Both @Type and @_id are not declared.
The first line of the view creation has no name:
CREATE VIEW [dbo]. AS
The first table in the comma separate list of table has no name:
FROM dbo., dbo.ca_site cs, ca_dataDictionary_Countries, ca_address, ca_site_address WITH (NOLOCK)
So you get the following errors:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'AS'.
Msg 102, Level 15, State 1, Line 119
Incorrect syntax near ','.
Msg 319, Level 15, State 1, Line 119
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.You will get better responses if you format your question and submit it in a state that will at least compile.
Even worse you must of actually had to go to the effort of editing your code to make it so it doesn't compile!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply