Newsgroup: it.comp.software.database
Titolo: [MSSQL] SP con cursori annidati :-(

salve
ho il seguente problema:
-tabella dei movimenti di vendita, con codice articolo e data movimento. 
(400k record)
-tabella dei listini di vendita con articolo e 2 campi per la data di 
validità del listino (data inizio e data fine) (200k record)
necessito di ottenere una terza tabella che mi aggiunge, ad ogni mov di 
vendita, il prezzo di listino in vigore, prendendo quello con date di 
validità a cavallo della data di vendita e, se non ne trova prende 
quello che ha NULL negli estremi di date di listino.
a causa di questa 'priorità' non basta una semplice query, devo usare 
una stored procedure che, come si puo' intuire, è incredibilmente lenta :-(
in pratica scorro un cursore sulla tabella dei movimenti e, per ogni 
movimento, devo cercare con un altro cursore il prezzo di listino, 
dovendo fare quindi 1 o 2 sottoquery per ogni movimento.
ok, con gli indici è ovviamente migliorato, con qualche ottimizzazione 
alle tabelle e alla SP anche, ma devo cambiare completamente il metodo 
di calcolo xche ho bisogno di ordini di grandezza di miglioramento. 
quindi bisogna cambiare completamente sistema.
per dare un esempio... dal query analizer 100 movimenti me li fa in 90 
secondi!!!
che fo?
dite che non ci sono storie e devo cercare di ottimizzare al massimo le 
2 tabelle di partenza sia in campi che in record? ma non mi garantisce 
interi ordini di grandezza di miglioramento.
forse con 2 passaggi di normali query... uno che considera solo i 
listini con data e uno che considera solo gli altri, e poi unire i 
risultati...
ringrazio chi ha un consiglio e prometto che passero' ogni tanto a dare 
qualche risposta ai niubbi :-)
ciao
Nicola

 quello che ha NULL negli estremi di date di listino.
Cosa prende se non trova il prezzo nell'intervallo di date?
Quello con entrambe data inizio e data fine validita' a NULL
oppure solo uno dei due?
Mi serve capire meglio la semantica dei NULL, ad esempio: data di
fine validita' non NULL,  ma data di inizio validita' inizio valorizzata,
puo' voler dire dalla data di inizio validita' fino ad oggi.
In maniera analoga data di inizio validita' a NULL, ma data di fine
validita' non a NULL, puo' voler dire da qualunque data precedente
quella di fine validita' fino ad essa.
Se mi dai un esempio ti posso rispondere.
Ciao,
-- 
Gianluca Hotz - SQL Server MVP
http://italy.mvps.org
 - 
http://www.solidqualitylearning.com
http://www.ugiss.org
  - 
http://www.ugidotnet.org
http://www.ghotz.com
  - 
http://blogs.ugidotnet.org/ghotz

 oppure solo uno dei due?
o ho entrambe le date con valore oppure sono entrambe a null.
se non trova il primo prende il secondo (se c'è). inoltre mi 
garantiscono che i periodi dei listini di uno stesso articolo non si 
sovrappongono (almenoquello :-) )
esempio:
LISTINI:
ART1 - 01/01/2004 - 30/06/2004 - prezzo1
ART1 - NULL       - NULL       - prezzo2
ART2 - 01/01/2004 - 31/12/2004 - prezzo3
MOVIMENTI:
14/04/2004 - ART1 <------ prende prezzo1
18/08/2004 - ART1 <------ prende prezzo2
10/01/2005 - ART2 <------ 0 o NULL, non ho preferenze
domani intendo provare le seguente strada:
1) poto al massimo la tabella dei listini eliminando tutti gli articoli 
non movimentati.
2) vedo di fare le 2 query in serie sfruttando il join con i listini 
invece che usare i cursori e/o funzioni. a occhio non dovrebbero esserci 
grossi problemi.
grazie, se risolvo scrivero' come ho fatto :-)
-- 
Denetor

 prende prezzo2 10/01/2005 - ART2 <------ 0 o NULL, non ho preferenze
Questa potrebbe essere una soluzione, con alcune riserve
a posteriori:
--<CUT HERE>--
/*
** Esempio 1
*/
USE tempdb
GO
IF EXISTS(
        SELECT  *
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE   TABLE_TYPE = 'BASE TABLE'
          AND   TABLE_SCHEMA = 'dbo'
          AND   TABLE_NAME = 'Movimento'
)
        DROP TABLE dbo.Movimento
GO
IF EXISTS(
        SELECT  *
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE   TABLE_TYPE = 'BASE TABLE'
          AND   TABLE_SCHEMA = 'dbo'
          AND   TABLE_NAME = 'Listino'
)
        DROP TABLE dbo.Listino
GO
CREATE TABLE dbo.Listino (
        IDProdotto              int             NOT NULL
,       DataInizioValidita      datetime        NULL
,       DataFineValidita        datetime        NULL
,       Prezzo                  money           NOT NULL
)
GO
SET NOCOUNT ON
INSERT  dbo.Listino VALUES ( 1, '20040101', '20040630', 120)
INSERT  dbo.Listino VALUES ( 1,       NULL,       NULL, 150)
INSERT  dbo.Listino VALUES ( 2, '20040101', '20041231', 220)
SET NOCOUNT OFF
GO
CREATE TABLE dbo.Movimento (
        DataMovimento   datetime        NOT NULL
,       IDProdotto      int             NOT NULL
,       CONSTRAINT      pkMovimento
        PRIMARY KEY     (
                        DataMovimento
        ,               IDProdotto
                        )
)
GO
SET NOCOUNT ON
INSERT  dbo.Movimento VALUES ('20040414', 1)
INSERT  dbo.Movimento VALUES ('20040818', 1)
INSERT  dbo.Movimento VALUES ('20050110', 2)
SET NOCOUNT OFF
GO
-- movimenti per i quali esiste un prezzo, associato al prodotto,
-- in corso di validita' alla data movimento
SELECT
        M1.DataMovimento
,       M1.IDProdotto
,       L1.Prezzo
FROM    dbo.Movimento AS M1
JOIN    dbo.Listino   AS L1
  ON    M1.IDProdotto = L1.IDProdotto
 AND    M1.DataMovimento BETWEEN L1.DataInizioValidita
                         AND L1.DataFineValidita
UNION
-- movimenti per i quali non esiste un prezzo, associato al prodotto,
-- in corso di validita' alla data movimento, ma esiste un prezzo, sempre
-- associato al prodotto, senza periodo di validita' specificato
SELECT
        M1.DataMovimento
,       M1.IDProdotto
,       L1.Prezzo
FROM    dbo.Movimento AS M1
JOIN    dbo.Listino   AS L1
  ON    M1.IDProdotto = L1.IDProdotto
WHERE   L1.DataInizioValidita IS NULL
  AND   L1.DataFineValidita   IS NULL
  AND   NOT EXISTS(
        SELECT  *
        FROM    dbo.Listino   AS L2
        WHERE   M1.IDProdotto = L2.IDProdotto
          AND   M1.DataMovimento BETWEEN L2.DataInizioValidita
                                 AND L2.DataFineValidita
        )
UNION
-- movimenti per i quali non esiste un prezzo associato al prodotto
SELECT
        M1.DataMovimento
,       M1.IDProdotto
,       CAST(0 AS money) AS Prezzo
FROM    dbo.Movimento AS M1
WHERE   NOT EXISTS(
        SELECT  *
        FROM    dbo.Listino   AS L2
        WHERE   M1.IDProdotto = L2.IDProdotto
          AND   M1.DataMovimento BETWEEN L2.DataInizioValidita
                                 AND L2.DataFineValidita
        )
  AND   NOT EXISTS(
        SELECT  *
        FROM    dbo.Listino   AS L3
        WHERE   M1.IDProdotto = L3.IDProdotto
          AND   L3.DataInizioValidita IS NULL
          AND   L3.DataFineValidita   IS NULL
        )
--<CUT HERE>--
Un paio di considerazioni.
Prima di tutto, nonostante i volumi non siano particolarmente
preoccupanti, visto il numero di correlazioni ti conviene veriricare
il piano di esecuzione e vedere se ci sono margini di miglioramento
aggiungendo un indice.
Per evitare query cosi' onerose, a mio parere, faresti meglio a
rivedere la tabella dei listini eliminando completamente l'attuale
semantica dei NULL, anzi eliminandoli completamente.
L'idea e' quella di utilizzare due valori predefiniti come data
di inizio del tempo e data di fine del tempo (rispettivamente
la data minima e la data massima ammissibile dal tipo dato datetime).
A questo punto, prendendo spunto dai dati di esempio che hai fornito,
potresti esprimere la stessa cosa in questo modo:
ART1 - 01/01/2004 - 30/06/2004 - prezzo1
ART1 - 01/07/2004 - 31/12/9999 - prezzo2
ART2 - 01/01/2004 - 31/12/2004 - prezzo3
Questo ti permette di semplificare radicalmente le query
e di non avere mai a che fare con la logica a 3 valori
introdotta dalla presenza dei NULL
Se ti interessa approfondire come gestire al meglio la
temporalita' nei database, ti consiglio il libro di
Snodgrass che da qualche tempo puoi trovare gratuitamente
a questo indirizzo:
http://www.cs.arizona.edu/people/rts/
http://www.cs.arizona.edu/people/rts/tdbbook.pdf


 
http://www.cs.arizona.edu/people/rts/
me lo leggo volentieri visto che le date sono un po' ostiche da gestire.
mille grazie per l'aiuto, è stato preziosissimo :-)
-- 
Denetor