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 implement incremental load in fact tables.

Hi all,

i have a fact table which loads through package,when

i m trying to load this table by running the package,i m truncating the

fact table and loading the fresh data,instaed of this without

truncating the fact table i have to implement the incremental logic in

this.

For this i can use SCD or Conditional split,but problem

here is i have many source tables to load this fact table,so its

very difficult to trace the changes in different source tables.

can any one help me out in this?You should stage all of your source tables along with the current load date. That way, when you select from your source staging tables, you can retrieve the data based on that date field, thereby only picking up the new/changed records.