how to improve this query, thanks

I have a pretty good db server with four CPUs, it has not any other loads on it, but the following query takes 4ms to return. I use the syscolumns this way quite often, I am not sure why it takes it that long to return, any idea?

select 'master',id,colid,name,xtype,length,xprec,xscale,status from [ablestatic].[dbo].syscolumns
where id=(select id from [ablestatic].[dbo].sysobjects where name='link_data_ezregs')

You might want to run profiler to see whats happening. The query by itself doesnt seem to be doing much. See if this makes any diff (although it shoudnt)

SELECT 'master',id,colid,name,xtype,length,xprec,xscale,status

FROM [ablestatic].[dbo].syscolumns sc

JOIN sysobjects so on sc.id = so.id
WHERE so.name='link_data_ezregs' and so.xtype = 'U'

|||

thanks.

I got the 2 - 4 ms when I run the query in one of my SP and watch it with profiler.

If I run it in query analyzer and in profile I got 15 ms.

|||15ms = milli secs or minutes?|||

millisecond for sure

|||I doubt if theres much you can do. you could use the stored proc as you suggested but even T-SQL should be as efficient, if its a single T-SQL statement.|||Check the execution plan when the query is executed inside/outside a sp, it may be caused by parameter sniffing, here are some related articles:

http://forums.asp.net/2/1377161/ShowThread.aspx

Understanding Plan Guides

How to I retrieve the first value from a table in T-SQL?

I want to do something similar to ExecuteScalar in ADO.net but instead in T-SQL.

Basically I want to do a query that will return the first value in the table queried and put it into a variable. How do I do this?

Thanks in advance.

SELECT

top 1 yourCol

FROM

T1

ORDER

BYyourCol|||Well that returns a table with the first record BUT I wanted the thing returned as a value.

Say I declared a integer variable with

DECLARE @.x, I wanted the integer variable to be equal to the first record which would be an "age" column in the recordset

SELECT age FROM PEOPLE

I can't do @.x=SELECT age FROM people|||

CREATE

PROCEDURE [dbo].[sp__top1Name]

@.myAge

intOUTPUTAS

BEGIN

SET @.myAge=(SELECTtop 1 Agefrom T1ORDERBY Age)

END

In your code, you need to grab this value from stored procedure OUTPUT parameter which is the age you are looking for.