Stored procedure.

venerdì 06 marzo 2009 - 14.51

Elenco Threads

Stanze Forum

Aggiungi ai Preferiti

Cerca nel forum

iif Profilo | Expert

713 messaggi | Data Invio: ven 6 mar 2009 - 14:51

Ciao, ho una sp che esegue delle insert dove all'interno c'è una select che a sua volta ha delle select nidificate nella where.
Dal cliente per eseguirla ci vogliono svariate ore, mentre in ufficio impiega 1,08 minuti.
Vorrei alleggerirla.
Ho dei vantaggi se le select nidificate le tramuto in tabelle temporanee e poi eseguo la select principale attingendo dalle tabelle appena create?
Spero di essere stato chiaro.

Grazie.

alx_81 Profilo | Guru

8.814 messaggi | Data Invio: ven 6 mar 2009 - 14:59

>Ciao,
Ciao!

>ho una sp che esegue delle insert dove all'interno c'è
>una select che a sua volta ha delle select nidificate nella where.
>Dal cliente per eseguirla ci vogliono svariate ore, mentre in
>ufficio impiega 1,08 minuti.
>Vorrei alleggerirla.
>Ho dei vantaggi se le select nidificate le tramuto in tabelle
>temporanee e poi eseguo la select principale attingendo dalle
>tabelle appena create?
Sinceramente la SELECT nella where non mi piace molto..
Prova a postarci la tua sp che vediamo di alleggerirla per bene, e magari proviamo a pensare a tabelle temporanee o quant'altro..

>Grazie.
di nulla!

--

Alessandro Alpi | SQL Server MVP

http://www.alessandroalpi.net
http://blogs.dotnethell.it/suxstellino
http://mvp.support.microsoft.com/profile/Alessandro.Alpi
http://italy.mvps.org

iif Profilo | Expert

713 messaggi | Data Invio: ven 6 mar 2009 - 15:05

Eccola:

USE [db]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_MntReserve] AS

truncate table ContrattiUltimi
insert into ContrattiUltimi select * from contract as c where
contratto = (select max(contratto) from contract as c1 where c1.fleet = c.fleet)
and [sequenza contratto] = (select max([sequenza contratto]) from contract as c2
where c2.fleet = c.fleet and c2.contratto = c.contratto)

truncate table MntReserve
insert into MntReserve

SELECT contract.[avis cmpy],contract.Fleet, contract.Duration ,
contract.[Distance km] AS Distance,
contract.[Data Inizio Contratto] AS DataInizio,
contract.[Data Fine Contratto] AS DataFine,
contract.[Data Termination] as DataTermination,
contract.Prodotto, veicolo.[Make Name],
veicolo.[Model Name], veicolo.[Vehicle Desc],
veicolo.[Mntc Profile], veicolo.[Body Style],
veicolo.[4 Whl driver], veicolo.[Used Tyre Ind],
contract.status, contract.categoria,
contract.[quantità gomme] as Quantity,
contract.[contratto used tyre] as UsedTyre,
quotation.[tyre cost group] as TyreCostGroup,
tyrerule.tyre_r TyreRule,
(select max(odometer) from wa where fleet=contract.fleet and [avis cmpy]=contract.[avis cmpy]) as Odometer

, cast( CASE ISNULL(isnull([tyre amount], 0) + isnull([tyf amount], 0) + isnull([tym amount], 0) + isnull([tyw amount], 0), 0)
WHEN 0 THEN isnull(EstRevenue.[Tyre Rev], 0)
ELSE ISNULL(isnull([tyre amount], 0) + isnull([tyf amount], 0) + isnull([tym amount], 0) + isnull([tyw amount], 0), 0)
END as money) AS TyreRev
, cast(CASE ISNULL(isnull([mnt amount], 0) + isnull([am4 amount], 0) + isnull([vmm amount], 0) + isnull([mtb amount], 0), 0)
WHEN 0 THEN isnull(EstRevenue.[Mntc Rev], 0)
ELSE ISNULL(isnull([mnt amount], 0) + isnull([am4 amount], 0) + isnull([vmm amount], 0) + isnull([mtb amount], 0), 0)
END as money) AS MntRev

, (SELECT cast(SUM(isnull(cost, 0)) AS money)
FROM Cost AS C
WHERE FLEET = contract.fleet and [avis cmpy]=contract.[avis cmpy] and anno in (1998, 1999,2000,2001,2002,2003, 2004, 2005, 2006) AND EXISTS
(SELECT idcomponent
FROM tyrecomponent AS T
WHERE t .idcomponent = c.[component id]))
AS TyreCost_min2006

, (SELECT cast(SUM(isnull(cost, 0)) AS money)
FROM Cost AS C
WHERE FLEET = contract.fleet and [avis cmpy]=contract.[avis cmpy] and anno = 2007 AND EXISTS
(SELECT idcomponent
FROM tyrecomponent AS T
WHERE t .idcomponent = c.[component id]))
AS TyreCost_2007

, (SELECT cast(SUM(isnull(cost, 0)) AS money)
FROM Cost AS C
WHERE FLEET = contract.fleet and [avis cmpy]=contract.[avis cmpy] and anno = 2008 AND EXISTS
(SELECT idcomponent
FROM tyrecomponent AS T
WHERE t .idcomponent = c.[component id]))
AS TyreCost_2008

, (SELECT cast(SUM(isnull(cost, 0)) AS money)
FROM Cost AS C
WHERE FLEET = contract.fleet and [avis cmpy]=contract.[avis cmpy] and anno = 2009 AND EXISTS
(SELECT idcomponent
FROM tyrecomponent AS T
WHERE t .idcomponent = c.[component id]))
AS TyreCost_2009

, (SELECT cast(SUM(isnull(rebilling, 0)) AS money)
FROM Cost AS C
WHERE FLEET = contract.fleet and [avis cmpy]=contract.[avis cmpy] and anno in (1998, 1999,2000,2001,2002,2003, 2004, 2005, 2006) AND EXISTS
(SELECT idcomponent
FROM tyrecomponent AS T
WHERE t .idcomponent = c.[component id]))
AS TyreRebilling_min2006
, (SELECT cast(SUM(isnull(rebilling, 0)) AS money)
FROM Cost AS C
WHERE FLEET = contract.fleet and [avis cmpy]=contract.[avis cmpy] and anno = 2007 AND EXISTS
(SELECT idcomponent
FROM tyrecomponent AS T
WHERE t .idcomponent = c.[component id]))
AS TyreRebilling_2007

, (SELECT cast(SUM(isnull(rebilling, 0)) AS money)
FROM Cost AS C
WHERE FLEET = contract.fleet and [avis cmpy]=contract.[avis cmpy] and anno = 2008 AND EXISTS
(SELECT idcomponent
FROM tyrecomponent AS T
WHERE t .idcomponent = c.[component id]))
AS TyreRebilling_2008
, (SELECT cast(SUM(isnull(rebilling, 0)) AS money)
FROM Cost AS C
WHERE FLEET = contract.fleet and [avis cmpy]=contract.[avis cmpy] and anno = 2009 AND EXISTS
(SELECT idcomponent
FROM tyrecomponent AS T
WHERE t .idcomponent = c.[component id]))
AS TyreRebilling_2009
, (SELECT cast(SUM(isnull(cost, 0)) AS money)
FROM Cost AS C
WHERE FLEET = contract.fleet and [avis cmpy]=contract.[avis cmpy] and anno in (1998, 1999,2000,2001,2002,2003, 2004, 2005, 2006) AND NOT EXISTS
(SELECT idcomponent
FROM tyrecomponent AS T
WHERE t .idcomponent = c.[component id]))
AS MntCost_min2006
, (SELECT cast(SUM(isnull(cost, 0)) AS money)
FROM Cost AS C
WHERE FLEET = contract.fleet and [avis cmpy]=contract.[avis cmpy] and anno = 2007 AND NOT EXISTS
(SELECT idcomponent
FROM tyrecomponent AS T
WHERE t .idcomponent = c.[component id]))
AS MntCost_2007

, (SELECT cast(SUM(isnull(cost, 0)) AS money)
FROM Cost AS C
WHERE FLEET = contract.fleet and [avis cmpy]=contract.[avis cmpy] and anno = 2008 AND NOT EXISTS
(SELECT idcomponent
FROM tyrecomponent AS T
WHERE t .idcomponent = c.[component id]))
AS MntCost_2008
, (SELECT cast(SUM(isnull(cost, 0)) AS money)
FROM Cost AS C
WHERE FLEET = contract.fleet and [avis cmpy]=contract.[avis cmpy] and anno = 2009 AND NOT EXISTS
(SELECT idcomponent
FROM tyrecomponent AS T
WHERE t .idcomponent = c.[component id]))
AS MntCost_2009
, (SELECT cast(SUM(isnull(Rebilling, 0)) AS money)
FROM Cost AS C
WHERE FLEET = contract.fleet and [avis cmpy]=contract.[avis cmpy] and anno in (1998, 1999,2000,2001,2002,2003, 2004, 2005, 2006) AND NOT EXISTS
(SELECT idcomponent
FROM tyrecomponent AS T
WHERE t .idcomponent = c.[component id]))
AS MntRebilling_min2006
, (SELECT cast(SUM(isnull(Rebilling, 0)) AS money)
FROM Cost AS C
WHERE FLEET = contract.fleet and [avis cmpy]=contract.[avis cmpy] and anno = 2007 AND NOT EXISTS
(SELECT idcomponent
FROM tyrecomponent AS T
WHERE t .idcomponent = c.[component id]))
AS MntRebilling_2007

, (SELECT cast(SUM(isnull(Rebilling, 0)) AS money)
FROM Cost AS C
WHERE FLEET = contract.fleet and [avis cmpy]=contract.[avis cmpy] and anno = 2008 AND NOT EXISTS
(SELECT idcomponent
FROM tyrecomponent AS T
WHERE t .idcomponent = c.[component id]))
AS MntRebilling_2008
, (SELECT cast(SUM(isnull(Rebilling, 0)) AS money)
FROM Cost AS C
WHERE FLEET = contract.fleet and [avis cmpy]=contract.[avis cmpy] and anno = 2009 AND NOT EXISTS
(SELECT idcomponent
FROM tyrecomponent AS T
WHERE t .idcomponent = c.[component id]))
AS MntRebilling_2009

FROM veicolo
INNER JOIN ContrattiUltimi as contract ON veicolo.[Vhc Type Vrsn ID] = contract.[Vhc Type Vrsn Id]
LEFT JOIN quotation on quotation.proposal = contract.proposal
and quotation.[sls quotation] = contract.[sls quotation]
and quotation.[sls quotation seq] = contract.[sls quotation seq]
and quotation.[avis cmpy] = contract.[avis cmpy]
and quotation.[codice cliente] = contract.[codice cliente]
LEFT OUTER JOIN EstRevenue ON contract.Fleet = EstRevenue.Fleet
and EstRevenue.[avis cmpy] = contract.[avis cmpy]
LEFT JOIN tyrerule ON tyrerule.vhc_cntr_id=contract.contratto
and tyrerule.col3=contract.[sequenza contratto]
and tyrerule.avis_c = contract.[avis cmpy]
WHERE ((contract.[Data Inizio Contratto] >= 19980101 AND
contract.[Data Termination] >= 20010101 AND
NOT (contract.status = 7 AND contract.categoria = 4) AND
NOT (contract.status = 6 AND contract.categoria = 3)) OR
(contract.Status = 6 AND contract.Categoria = 3))
AND (contract.[codice cliente]<>10001 and
contract.Prodotto not in ('STK','PL','PC','I14','FM2','IT8','I11','FM1'))

alx_81 Profilo | Guru

8.814 messaggi | Data Invio: ven 6 mar 2009 - 15:23

scusami, ma proprio così mi è impossibile aiutarti.
Fai una cosa, passami le CREATE delle tabelle, le INSERT di alcuni dati, un OUTPUT di esempio che vorresti ottenere e le logiche spiegate un pochino, sennò è veramente dura..
A prima vista tutti quei campi ricavati da select nidificate, possono essere ottimizzati con join, ma prima mi serve capire un po' le tue logiche.
Cerca di venirmi in contro un po' che ci saltiamo fuori.
--

Alessandro Alpi | SQL Server MVP

http://www.alessandroalpi.net
http://blogs.dotnethell.it/suxstellino
http://mvp.support.microsoft.com/profile/Alessandro.Alpi
http://italy.mvps.org

Torna su

Stanze Forum

Elenco Threads

Partecipa anche tu! Registrati!

Hai bisogno di aiuto ?
Perchè non ti registri subito?

Dopo esserti registrato potrai chiedere
aiuto sul nostro Forum oppure aiutare gli altri

Consulta le Stanze disponibili.

Registrati ora !