Hi,
I do not know if the section is the right one , but because that the difference in performance is linked to VB6 I think is the right one.
I wrote a stored procedure in sql server 2008 which has completely different results when ADODB use the ODBC connection or provider SQLNCLI .
With ODBC , the result is correct and verified , with SQLNCLI results in an empty dataset.
This is the stored
CREATE Procedure [dbo].[TolReadPostiSettoreMan] @P1 numeric(18,0), @P2 numeric(18,0), @P3 numeric(18,0), @P4 datetime, @P5 int=1, @Turno int=0, @SngData int=0, @RealMap int=0 AS
declare @MSG varchar(7900)
Declare @RS int, @cnt int, @IFila int
Declare @CFila int, @Posto varchar(10), @Iposto int, @IsAbb int, @DDay char(10), @DPlan char(10)
Declare @IDPostoMan numeric(18,0), @IDposto numeric(18,0), @IDMan numeric(18,0), @DataVendita datetime, @IDPP numeric(18,0)
Declare @Stato smallint, @Priorita smallint, @Blocco smallInt, @Fila varchar(20), @IDTurno int, @BiglABB int, @Colore smallint
declare @IDD int, @IDM int, @GG smalldatetime
Create Table #Settore (IDMan numeric(18,0), DataVendita char(10), Fila varchar(10), Posti text, iFila int)
Create table #Posti (IDPostoMan int, IDPosto int, IDMan int, IDPP int, IDTurno int, BiglABB smallint, DataVendita smalldatetime,
Stato smallint, Priorita smallint, Blocco smallint, Fila varchar(20), Posto varchar(10), IPosto int, IFila int, Colore smallint, IsAbb int)
declare @tblanag table (idanagrafica int, cognome varchar(30))
set nocount on
insert into @tblanag
select a.IDanagrafica, Cognome from TolPostiImpegnati A
inner join TolAnagraficaPrenotazione b
on a.IDAnagrafica=B.IDAnagrafica
where a.stato=0
and lower(replace(rtrim(cognome)+rtrim(nome),'''','')) in ('cerimonialentfi','stampantfi','compagniantfi','serviziontfi','strutturantfi','tecnicantfi','chiusurentfi')
group by a.idanagrafica,cognome
select @DDay=convert(char(10), getdate(),120)
if @turno=0 SET @Realmap=1
print @realmap
if @Turno>0 and @SngData=1 begin
if exists(Select * from TolEventiTurni where IDTurno=@Turno)
select top 1 @IDD=A.IDREC,@GG=min(giorno),@IDM=A.IDMan
from tolmanifestazionedate a inner join tolmanabbturnidate b
on a.idrec=b.idrecdate
where convert(char(10),giorno,120)>=convert(char(10),getdate(),120)
and A.idman in (select idman from TolEventiTurni
where IDTurno=@Turno and Fisso=1)
and b.idturno=@Turno
group by A.idrec,a.idman
else
select top 1 @IDD=A.IDREC,@GG=min(giorno),@IDM=A.IDMan
from tolmanifestazionedate a inner join tolmanabbturnidate b
on a.idrec=b.idrecdate
where convert(char(10),giorno,120)>=convert(char(10),getdate(),120)
and A.idman in (select idman from tolstagionemanifestazione
where idstagione in (select idstagione
from tolstagionemanifestazione where idman=@P2))
and b.idturno=@Turno
group by A.idrec,a.idman
set @IDD=isnull(@IDD,1)
if @IDD>0 begin
set @P2=@IDM
set @P5=@IDD
set @P4=@GG
end
end
Select DISTINCT Colore=case
when s.cognome='cerimoniale' then 1
when s.cognome='stampa' then 10
when s.cognome='compagnia' then 13
when s.cognome='servizio' then 6
when s.cognome='struttura' then 12
when s.cognome='chiusure' then 14
when s.cognome='tecnica' then 14
else F.Colore
end , D.IDPostoMan, F.BiglABB into #colori
from
TolManifestazioneTB F inner join TolMovimentazioneVenditaDett E with (UPDLOCK)
on E.IDMan=F.IDMan and E.IDTipBig=F.IDTipBig
inner join TolPostoVendita D
on D.IDMovDett=E.IDREC
inner join TolPostoSettoreMan G with (UPDLOCK)
on D.IDPostoMan=G.IDPostoMan
left join TolPostiImpegnati H with (UPDLOCK)
on G.IDpostoMan=H.IDPostoMan and H.Stato=0
left join (select IDPostoMan, Cognome=rtrim(b.Cognome) from TolPostiImpegnati A
inner join TolAnagraficaPrenotazione b
on a.IDAnagrafica=B.IDAnagrafica
inner join @tblanag c
on b.idanagrafica=c.idanagrafica
where a.stato=0
) as s
on g.idpostoman=s.idpostoman
where F.IDMan=@p2
and F.IDSettore=@p1
and E.IDUtenteAnn=0
and G.IDRECDATE=@p5
and G.IDTurno=@turno
union Select DISTINCT Colore=case
when s.cognome='cerimoniale' then 1
when s.cognome='stampa' then 10
when s.cognome='compagnia' then 13
when s.cognome='servizio' then 6
when s.cognome='struttura' then 12
when s.cognome='chiusure' then 14
when s.cognome='tecnica' then 14
else 1
end , D.IDPostoMan, 0
from TolVPostoSettoreMan D with (UPDLOCK)
inner join (select IDPostoMan, Cognome=rtrim(b.Cognome) from TolPostiImpegnati A
inner join TolAnagraficaPrenotazione b
on a.IDAnagrafica=B.IDAnagrafica
inner join @tblanag c
on b.idanagrafica=c.idanagrafica
where a.stato=0
) as s
on d.idpostoman=s.idpostoman
where D.IDMan=@p2
and D.IDSettore=@p1
and D.IDRECDATE=@p5
and D.IDTurno=@turno
if @RealMap=0
Insert Into #Posti
Select distinct A.IDPostoMan, A.IDPosto, A.IDMan, A.IDPP, A.IDTurno, BiglAbb=isnull(S.BiglAbb,0), DataVendita=MD.Giorno,
case
when LB.Conferma in(1,1) and LB.Emesso=0 then 7
when LB.Conferma =0 then 7
when LB.Conferma=1 and LB.Emesso=0 then 7
when isnull(TPI.idpostoman,0)>0 and TPI.Stato=0 then 7
when A.Stato=11 then 1
when isnull(s.colore,1)>0 then 1
else A.Stato
end Stato,
A.Priorita,
A.Blocco, Fila=rtrim(B.Fila), B.Posto, B.IPosto, B.IFila,
Colore=isnull(S.Colore,1), IsABB=1 from TolPostoSettoreMan A with (UPDLOCK) inner join TolPostoSettore B
on A.IDPosto=B.IDPosto
inner join TolManifestazioneDate MD
on A.IDrecDate=MD.IDRec
left join #Colori S
on A.IDPostoMan=S.IDPostoMan
left join TolTurniTeatraliRiporto TR
on A.IDTurno=TR.IDTurnoC
left join TolCmpAbbListaAbbonati LB
on TR.IDTurnoP=LB.IDTurno and B.IDPosto=LB.IDPosto and LB.conferma in(1,0,1)
left join TolPostiImpegnati TPI
on A.IDPostoMan=TPI.IDPostoMan and tpi.stato=0
where A.Idman=@P2
and A.IDTurno=@Turno
and A.Idrecdate=@P5
and B.idsettore=@P1
else
Insert Into #Posti
Select distinct A.IDPostoMan, A.IDPosto, A.IDMan, A.IDPP, A.IDTurno, BiglAbb=isnull(S.BiglAbb,0), DataVendita=MD.Giorno,
case
when isnull(s.colore,1)>0 then 1
else A.Stato
end Stato,
A.Priorita, A.Blocco, Fila=rtrim(B.Fila), B.Posto, B.IPosto, B.IFila,
Colore=isnull(S.Colore,1), IsABB=1
from TolPostoSettoreMan A with (UPDLOCK) inner join TolPostoSettore B
on A.IDPosto=B.IDPosto
inner join TolManifestazioneDate MD
on A.IDrecDate=MD.IDRec
left join #Colori S
on A.IDPostoMan=S.IDPostoMan
where A.Idman=@P2
and A.IDTurno=@Turno
and A.Idrecdate=@P5
and B.idsettore=@P1
Update #Posti set IsAbb=K.IDAnagrafica
from TolPostoVendita H inner join #Posti
on #Posti.IdpostoMan=H.IDPostoMan
inner join TolAnagraficaPrenotazione K
on H.IDAnagrafica=K.IDAnagrafica
drop table #Colori
declare @postoID int, @IDPV int, @turnoid int, @idrecd int, @Vend int
declare @id_pp int, @id_posto int, @id_rec int, @st int, @NMan int
if @SngData=1 begin
select @NMAn=count(distinct Descri)
FROM TolElencoManTeatro A inner join TolManifestazioneTBTurni B
on A.IDMAN=B.IDMAN
where A.idman in (select distinct idman from tolpostosettoreman where idturno=@turno)
and Chiusura=0
set @NMan=isnull(@NMan,0)
if @NMAN>0 begin
declare cStato cursor for
select A.idposto, A.idpp, A.idturno, A.idrecdate, S.Venduti
from TolVPostoSettoreMan A inner join TolManABBTurniDate B
on A.IDMAN=B.IDMAN and A.IDRECDATE=B.IDRECDATE and A.IDTurno=@Turno
inner join TolManifestazioneCV C
on B.IDMAN=C.IDMAN and B.IDRecDate=C.IDRecDate
inner join (Select Venduti=count(*), PM.idposto, IDSettore
from tolpostosettoreman PM with (UPDLOCK) inner join TolPostoSettore PS
on PM.IDPosto=PS.IDPosto
where idturno=@Idturno
and PM.stato>0 and idsettore=@P1
group by PM.idposto, IDsettore) as S
on A.Idposto=S.IDPosto and A.IDsettore=S.IDSettore
where (A.stato=4 or A.stato>0)
and A.IDMAN=@P2
and A.IDrecDate=@P5
and A.IDSettore=@P1
and Venduti<@NMan
group by A.idposto, A.idpp, A.idturno, A.idrecdate, S.Venduti
open cstato
fetch next from cstato into @postoID, @idpv, @turnoid, @idrecd, @Vend
if @@fetch_status=0 set @rs=0 else set @rs=1
while (@rs=0) begin
select @id_pp=idpp, @st=stato from #posti
where IDposto=@postoid
if @IDPV<>@ID_PP and @st in (1,4)
update #posti set BiglABB=1, stato=4
where idposto=@postoid
fetch next from cstato into @postoid, @idpv, @turnoid, @idrecd, @Vend
if @@fetch_status=0 set @rs=0 else set @rs=1
end
close cstato
deallocate cstato
end
end
Declare Settore Cursor for
Select IDPostoMan, IDPosto, IDMan, IDPP, IDTurno, BiglABB, DataVendita, Stato, Priorita,
Blocco, Fila, Posto, IPosto, IFila, Colore, IsAbb
from #Posti order by iposto
set @cnt=0
set @MSG=''
Open Settore
Fetch next from Settore into @IDPostoMan, @IDPosto, @IDMan, @IDPP, @IDTurno, @BiglABB, @DataVendita,
@Stato, @Priorita, @Blocco, @Fila, @Posto, @IPosto, @IFila, @Colore, @IsAbb
if @@Fetch_Status=0 set @Rs=0 else set @RS=1
set @CFila=@IFila
set @msg=''
While @RS=0 begin
if @Cfila<>@IFila begin
insert into #Settore values(@P2, Convert(char(10), @DataVendita,103), @Cfila, @Msg, @CFila)
set @cnt=0
set @CFila=@IFila
set @MSG=''
end
set @Msg = @Msg + convert(varchar(18),@IDPostoMan) + ',' + convert(varchar(18),@IDPosto) + ',' +
convert(varchar(18),@IDPP) + ',' + convert(varchar(18),@IDTurno) + ',' +
convert(varchar(4),@BiglABB) + ',' + convert(varchar(4),@Stato) + ',' +
convert(varchar(4),@Priorita) + ',' + convert(varchar(4),@Blocco) + ',' +
convert(varchar(18),@Iposto) + ',' + @Posto + ',' + convert(varchar(3),@Colore) + ';'
set @cnt=@cnt+1
Fetch next from Settore into @IDPostoMan, @IDPosto, @IDMan, @IDPP, @IDTurno, @BiglABB, @DataVendita,
@Stato, @Priorita, @Blocco, @Fila, @Posto, @IPosto, @IFila, @Colore, @IsAbb
if @@Fetch_Status=0 set @Rs=0
else begin
insert into #Settore values(@P2, Convert(char(10),@DataVendita,103), @Cfila, @Msg, @CFila)
set @RS=1
end
end
close Settore
Deallocate Settore
set nocount off
select * from #Settore order by ifila
drop table #Settore
drop table #Posti
I know that is quite complex, but not the only one in the database that has a similar complexity , yet it seems to be the only one that has this strange behavior.
Thanks in advance
