Long running query...no good key available

[patrussell]

I have the following RPC showing in my SQL trace and am looking for help on how to optimize the query shown. I have several queries that are similar to this on and no control over the structure of the query as it is run from an external data trending software.

declare @P1 int
set @P1=1
exec sp_prepexec @P1 output, NULL, N'SELECT &quot;FURBURNERS_ARC&quot;.&quot;timestamp&quot; AS &quot;timestamp&quot; , &quot;FURBURNERS_ARC&quot;.&quot;FCATBURNER5_VAL0&quot; AS &quot;FCATBURNER5_VAL0&quot; FROM &quot;FURBURNERS_ARC&quot; WHERE &quot;FURBURNERS_ARC&quot;.&quot;timestamp&quot;>=(select min(&quot;tb&quot;.&quot;timestamp&quot;) as &quot;timestamp&quot; from (select max(&quot;FURBURNERS_ARC&quot;.&quot;timestamp&quot;) as &quot;timestamp&quot; from &quot;FURBURNERS_ARC&quot; where &quot;timestamp&quot;<{ts ''2003-06-16 11:11:01''} UNION select min(&quot;timestamp&quot;) FROM &quot;FURBURNERS_ARC&quot; WHERE &quot;timestamp&quot;>={ts ''2003-06-16 11:11:01''} ) &quot;tb&quot; WHERE NOT &quot;timestamp&quot; IS NULL)  and &quot;FURBURNERS_ARC&quot;.&quot;timestamp&quot;<=(select max(&quot;tb&quot;.&quot;timestamp&quot;) as &quot;timestamp&quot; from (select min(&quot;FURBURNERS_ARC&quot;.&quot;timestamp&quot;) as &quot;timestamp&quot; from &quot;FURBURNERS_ARC&quot; where &quot;timestamp&quot;>{ts ''2003-06-16 12:11:01''} UNION select max(&quot;timestamp&quot;) FROM &quot;FURBURNERS_ARC&quot; WHERE &quot;timestamp&quot;<={ts ''2003-06-16 12:11:01''} ) &quot;tb&quot; where not &quot;timestamp&quot; is null) ORDER BY &quot;FURBURNERS_ARC&quot;.&quot;timestamp&quot;'
select @P1

The timestamps in the tables are anything from 1 second up and each table has between several hundred thousand to several million rows.

I've looked at the SQL performance site but I can't see anything that would apply to my situation. Putting a key on a 1 second timestamp doesn't seem like it would help much...

Thanks for any help that can be given.

Pat Russell

 

[TheBugSlayer]

What can you do if you don't have control over the queries?

 

[patrussell]

I was hoping for some ideas on how to setup the database that would help. Right now I'm probably going to split the data into two seperate tables, one for recent data and one for "archive" data. Most of the queries would be run on the recent data so minimizing the amount of rows needing to be searched should do the trick...I think.



Pat Russell