I removed a function which was used in the WHERE clause

say for example

select <columns>

from <tablename>

where <column_name> = dbo.fngetreportfield('Estimated','Final')

i modified the above query as

Declare @reportfield int

select @reportfield = dbo.fngetreportfield('Estimated','Final')

select <columns>

from <tablename>

where...

I used

1) rownumber() over (partition ....order by ....)

2) TOP & CROSS APPLY

methods.

But I didn't see any difference in the execution time.

Actually the table has more than 100000 records. So if i execute query like

select Assetid,min(date)

from t1

group by assetid

having min(date) <> inceptiondate

it is taking 1 minutes.

You are correct. But i would like to know is it possible to exclude the column in the return statement itself. i.e wwithout using the # table.

How much time it took ?

Lynn,

My code is displying 420 rows.

your's is displaying 344 rows.

Let me check the output.

Lynn,

I tried the below code

with final

as( select assetid,

...

I don't want to use DISTINCT or GROUP BY function. Because the table has 150,000 records.

Jeff,

For time being They agreed the current execution time. But i need to look into this for next month release.

Meanwhile i have been asked to fine tune one more procedure....

sample :

112007-10-12 00:00:00.0001214.400000005

112007-10-12 00:00:00.0001214.400000009

112007-10-19 00:00:00.0001224.000000005

112007-10-19 00:00:00.0001224.000000009

112007-10-26 00:00:00.0001235.300000005

112007-10-26 00:00:00.0001235.300000009

112007-10-31 00:00:00.0001241.500000005

112007-10-31 00:00:00.0001241.500000009

112007-10-31 00:00:00.0001339.945960005

112007-10-31 00:00:00.0001341.500000009

112007-10-31 00:00:00.0002339.765820004

122007-10-31 00:00:00.000239.600000005

Expected result:

112007-10-12 00:00:00.0001214.400000005

112007-10-26 00:00:00.0001235.300000005

112007-10-31 00:00:00.0001241.500000005

112007-10-31 00:00:00.0001339.945960005

112007-10-31 00:00:00.0002339.765820004

122007-10-31 00:00:00.000239.600000005

300000 rows(nearly).

Any Inputs?

Network team is looking Latency issue.

Jeff,

Code has been moved to PROD. Execution time is 26 seconds.

Dynamic Sql Part : 13 Seconds

Quirky Update(# table)...

I noticed one thing in the execution time.

Before Fine Tuning:

Running...