Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Location
    The Netherlands
    Posts
    29

    Exclamation Unanswered: How to simplify this slow Stored Procedure

    Dear Reader(s),

    Is there anyway to write the following stored procedure without the loop so that it goes much faster?

    ----------------------------------------------------------------------------
    use MJ_ReportBase
    go
    if exists(select 1 from sysobjects where type='P' and name='sp_Periode')
    begin
    drop procedure sp_Periode
    end
    go
    create procedure sp_Periode
    @start int
    , @stop int
    as
    declare @x int

    set @x = 0
    set @x=@start

    delete from tbl_periode

    while (@x>=@stop)
    begin

    -- ---
    -- ---
    -- Create table tbl_inout
    if exists(select 1 from sysobjects where type='U' and name='tbl_inout')
    begin
    drop table tbl_inout
    end

    select datetimestamp,accname,badgeid,personname,inoutreg into tbl_inout from WinXS..x18 where convert(varchar,datetimestamp,120)+' '+ltrim(str(id))+' '+ltrim(str(badgeid)) in
    (select convert(varchar,max(datetimestamp),120)+' '+ltrim(str(max(id)))+' '+ltrim(str(badgeid)) as datetimestamp from WinXS..x18 where (accname='Kelder -1' or accname='Tnk Entree') and convert(varchar,datetimestamp,105)=convert(varchar ,getdate()-abs(@x),105) group by badgeid)
    and badgeid>0
    order by personname

    -- ---
    -- ---
    -- Create table tbl_result

    if exists(select 1 from sysobjects where type='U' and name='tbl_result')
    begin
    drop table tbl_result
    end

    -- ---
    -- ---

    select
    convert(varchar,datetimestamp,105) 'DATUM'
    , badgeid 'PAS'
    , initials 'VOORNAAM'
    , personname 'NAAM'
    , convert(varchar,min(datetimestamp),108) 'MIN'
    , convert(varchar,max(datetimestamp),108) 'MAX'
    into
    tbl_result
    from
    WinXS..x18
    where
    convert(varchar,datetimestamp,105)=convert(varchar ,getdate()-abs(@x),105)
    and
    accname in ('Kelder -1','Tnk Entree')
    and badgeid>0
    group by
    convert(varchar,WinXS..x18.datetimestamp,105)
    , badgeid
    , initials
    , personname
    order by
    initials
    , personname asc
    , convert(varchar,datetimestamp,105) asc

    -- ---
    -- ---
    -- Rapportage tabel

    insert into
    tbl_periode
    select
    tbl_result.datum as DATUM
    , ltrim(ltrim(rtrim(tbl_result.naam))+' '+ltrim(rtrim(isnull(tbl_result.voornaam,' ')))) as NAAM
    , tbl_result.min as MIN
    , tbl_result.max as MAX
    , case tbl_inout.inoutreg when 1 then 'in' when 2 then 'out' else 'err' end as [IN/OUT]
    , substring('00000',1,5-len(tbl_result.pas))+ltrim(str(tbl_result.pas)) as PAS
    from
    tbl_inout,tbl_result
    where
    tbl_result.datum+' '+tbl_result.max+' '+ltrim(str(tbl_result.pas))
    = convert(varchar,tbl_inout.datetimestamp,105)+' '+convert(varchar,tbl_inout.datetimestamp,108)+' '+ltrim(str(badgeid))
    order by
    tbl_result.naam asc

    -- ---
    -- ---
    --

    set @x=@x-1
    end
    go

    print 'Klaar!'
    --------------------------------------------------------------------------

    What it does is determining the minimum entry time and the maximum exiting time per day of people going true the main entrance of a building.

    Many thanks in advance.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    First...collapse the queries in to 1.

    Second, what's the first select for?

    Third

    Loose the loop and do

    Code:
    WHERE datetimestamp >    GetDate()-@Start
      AND datetimestamp < =  GetDate()-@Stop
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't even want to try to figure out your code. Don't use permanent tables for temporary storage (your tbl_inout table). That will get you into trouble in a multi-user environment.

    Post the relevent DDL for your tables any somebody here can probably show you a more efficient set-based algorithm.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2004
    Location
    The Netherlands
    Posts
    29

    I have found the solution for this problem ...

    Hi to all,

    I have found the solution after a long time puzzeling.

    Please check the sql below:
    --------------------------------------------------------------------------
    select
    tbl_INOUT.*
    , CASE B.inoutreg WHEN 1 THEN 'IN' WHEN 2 THEN 'UIT' ELSE 'Fout' END 'STATUS'
    from
    (
    select
    convert(char(10),datetimestamp,120) 'DATUM'
    , badgeid 'PAS'
    , initials 'VOORNAAM'
    , personname 'NAAM'
    , convert(varchar,min(datetimestamp),108) 'MIN'
    , convert(varchar,max(datetimestamp),108) 'MAX'
    from
    WinXS..x18
    where
    (convert(varchar(10),datetimestamp,120)>=convert(v archar(10),'2005-01-01',120)
    and
    convert(varchar(10),datetimestamp,120)<=convert(va rchar(10),'2005-02-28',120))
    and
    accname in ('Kelder -1','Tnk Entree')
    and
    badgeid>0
    group by
    convert(char(10),datetimestamp,120)
    , badgeid
    , initials
    , personname
    ) tbl_INOUT, WinXS..x18 B
    where
    tbl_INOUT.pas=B.badgeid
    and
    convert(datetime,tbl_INOUT.datum+' '+tbl_INOUT.max,120)=B.datetimestamp
    and
    badgeid=81
    order by
    tbl_INOUT.naam asc
    , tbl_INOUT.datum DESC

    Greetz,
    DePrins

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •