[Solved] Taking lot of time to read from SQL Server using c#

by


Something like this might work. I’m not sure on the efficiency yet – it may depend on the amount of hits that you’re looking for:

create type HitBlocks as table (
    HitIndex int not null
)
go
create procedure FindMaxCover
    @Hits HitBlocks readonly
as
    ;With DecomposedBlocks as (
        select (HitIndex/8)+1 as ByteIndex,POWER(2,(HitIndex%8)) as BitMask
        from @Hits
    ), Coverage as (
        select
            t.TraceName,SUM(CASE WHEN SUBSTRING(t.BlockVector,db.ByteIndex,1) & BitMask != 0 THEN 1 ELSE 0 END) as Coverage
        from
            Trace t
                cross join
            DecomposedBlocks db
        group by
            t.TraceName
    ), Ranked as (
        select *,RANK() OVER (ORDER BY Coverage desc) as rk
        from Coverage
    )
    select
        t.TraceName,
        t.BlockVector,
        r.Coverage
    from
        Ranked r
            inner join
        Trace t
            on
                r.TraceName = t.TraceName
    where rk = 1

At the moment, this will return multiple rows if there are multiple results with the same coverage level. You may also have to adjust for a) Some off-by-one errors between my expectations and yours, and b) There may be some endianness issues in computing the correct BitMask values.

From your code, you would populate a DataTable with the values you’re currently storing in hit_block_index and pass that across as the @Hits parameter.

solved Taking lot of time to read from SQL Server using c#