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