is this query optimized? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

is this query optimized?

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-06-13 : 09:32:14

I started with this query:

SELECT procs.name as ProcName,
params.name as ParameterName,
types.name as ParamType,
params.max_length,
params.precision,
params.scale,
params.is_output,
params.has_default_value
FROM sys.procedures procs
LEFT OUTER JOIN sys.all_parameters params
ON procs.object_id = params.object_id
LEFT OUTER JOIN sys.types types
ON params.system_type_id = types.system_type_id
AND params.user_type_id = types.user_type_id
WHERE procs.is_ms_shipped = 0
AND params.name = '@DISPOSAL_AREA_NAME'
AND procs.name = 'webservices_BENEFICIAL_USES_DM_SELECT'
ORDER BY procname,
params.parameter_id

Now, all I need from it is the column params.is_output.

I have modified it down to what I need, but I'm wondering if I can remove some of the joins or anything else for better performance without losing the proper results:

SELECT params.is_output
FROM sys.procedures procs
LEFT OUTER JOIN sys.all_parameters params
ON procs.object_id = params.object_id
LEFT OUTER JOIN sys.types types
ON params.system_type_id = types.system_type_id
AND params.user_type_id = types.user_type_id
WHERE procs.is_ms_shipped = 0
AND params.name = '@DISPOSAL_AREA_NAME'
AND procs.name = 'webservices_BENEFICIAL_USES_DM_SELECT'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-13 : 09:36:44

[code]SELECT params.is_output
FROM sys.procedures AS procs
INNER JOIN sys.all_parameters AS params ON params.object_id = procs.object_id
LEFT JOIN sys.types AS types ON types.system_type_id = params.system_type_id
AND types.user_type_id = params.user_type_id
WHERE procs.is_ms_shipped = 0
AND params.name = '@DISPOSAL_AREA_NAME'
AND procs.name = 'webservices_BENEFICIAL_USES_DM_SELECT'

SELECT PARAMETER_MODE
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_NAME = 'webservices_BENEFICIAL_USES_DM_SELECT'
AND PARAMETER_NAME = '@DISPOSAL_AREA_NAME'[/code]

E 12°55'05.25"
N 56°04'39.16"

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-06-13 : 09:46:41

Thank you for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources