How to optimize SQL Server code?

Question

I have a table with the columns: Id, time, value.

First step: Given input parameters as signal id, start time and end time, I want to first extract rows with the the signal id and time is between start time and end time.

Second: Assume I have selected 100 rows in the first step. Given another input parameter which is max_num, I want to further select max_num samples out of 100 rows but in a uniform manner. For example, if max_num is set to 10, then I will select 1, 11, 21, .. 91 rows out of 100 rows.

I am not sure if the stored procedure below is optimal, if you find any inefficiencies of the code, please point that out to me and give some suggestion.

create procedure data_selection
@sig_id bigint,
@start_time datetime2, 
@end_time datetime2, 
@max_num float
AS

    BEGIN
    declare @tot float
    declare @step int
    declare @selected table (id int primary key identity not null, Date datetime2, Value real)

    // first step
    insert into @selected (Date, Value) select Date, Value from Table
    where Id = @sig_id
    and Date > = @start_time and Date < = @end_time
    order by Date

    // second step
    select @tot = count(1) from @selected
    set @step = ceiling(@tot / @max_num)
    select * from @selected
    where id % @step = 1

    END

I am new to stackoverflow so please let me know if the question is inappropriate instead of giving downvote. — Matthew
– Matthew, Commented May 4, 2016 at 18:17
I did not down vote, but did you consider a scenario where @tot < @Max_Num? Try it out — Adish
– Adish, Commented May 4, 2016 at 18:19
@Adish, yes it is considered in my actual application. Here just to make the case simple. Thanks for the suggestion. — Matthew
– Matthew, Commented May 4, 2016 at 18:21
I didn't down vote you but some people don't like it when you just post code and say, "hey take a look for me and optimize my code" without giving any specifics as to where you think it should be optimized. — Dresden
– Dresden, Commented May 4, 2016 at 18:21

shawnt00 · Accepted Answer · 2016-05-04 19:01:16Z

2

EDITED to calculate step on the fly. I had first thought this was an argument.

;with data as (
    select row_number() over (order by [Date]) as rn, *
    from Table
    where Id = @sig_id and Date between @start_time and @end_time
), calc as (
    select cast(ceiling(max(rn) / @max_num) as int) as step from data
)
select * from data cross apply calc as c
where (rn - 1) % step = 0 --and rn <= (@max_num - 1) * step + 1

Or I guess you can just order/filter by your identity value as you already had it:

;with calc as (select cast(ceiling(max(rn) / @max_num) as int) as step from @selected)
select * from @selected cross apply calc as c
where (id - 1) % step = 0 --and id <= (@max_num - 1) * step + 1

I think that because you're rounding step up with ceiling you'll easily find scenarios where you get fewer rows than @max_num. You might want to round down instead: case when floor(max(rn) / @max_num) = 0 then 1 else floor(max(rn) / @max_num) end as step?

edited May 4, 2016 at 19:01

answered May 4, 2016 at 18:18

shawnt00

18.1k3 gold badges23 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Matthew Over a year ago

thanks for shawnt00, is this code applicable to the microsoft server studio? It seems give me an error: Incorrect syntax near the keyword 'with'.

Kidiskidvogingogin Over a year ago

@Matthew you have to put a semicolon before the with

Matthew Over a year ago

Msg 319, Level 15, State 1, Procedure SelectDataTest, Line 12 [Batch Start Line 230] Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Msg 102, Level 15, State 1, Procedure SelectDataTest, Line 16 [Batch Start Line 230] Incorrect syntax near ','. Msg 102, Level 15, State 1, Procedure SelectDataTest, Line 17 [Batch Start Line 230] Incorrect syntax near '@step'.

Matthew Over a year ago

Yes, that works. But I still got this error: The data types bigint and float are incompatible in the modulo operator. What should I change in this case? I declared the step as int but not sure where is the bigint come?

shawnt00 Over a year ago

@Matthew, looks like you're using the row_number() version which uses a bigint return type. A cast should work here. See above.

|

Collectives™ on Stack Overflow

How to optimize SQL Server code?

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related