none
Query di ricerca "ricorsiva"... RRS feed

  • Domanda

  • Ciao a tutti, vediamo chi mi può dare una mano.

    Ho due tabelle (Documenti e DocumentiAttributi) unite tra loro con una relazione 1-a-Molti. Ho la necessità di ottenere la lista di documenti che hanno alcuni attributi con un determinato valore. Da codice (c#) l'ho fatta in due minuti (ovvero faccio la query sull'intera lista dei documenti cercando il primo attributo, rifaccio la stessa query ma sulla base dati ottenuta dalla query precedente cercando il secondo valore, rifaccio la stessa query ma sulla base dati ottenuta dalla query precedente cercando il terzo valore, e proseguo fino alla fine).

    Funzionare funziona, e finché facevo le prove con pochi elementi nelle tabelle era pure veloce. Adesso però i dati sono aumentati a dismisura e di conseguenza il sistema si è rallentato molto.

    Cosa mi consigliate?

    Ciao e grazie

    Enrico

    venerdì 21 maggio 2010 07:59

Risposte

  • vabbè, i  dati li simulo io, do per scontato che un documento non può avere coppie di attributi duplicati.

    USE TEMPDB
    GO
    CREATE TABLE dbo.DOCUMENTI
    (ID Int Primary Key,
     CODICEDOCUMENTO nvarchar(255) 
    )
    
    CREATE TABLE dbo.DOCUMENTOATTRIBUTO
    (ID INT Identity Primary Key,
     IDATTRIBUTO int,
     IDDOCUMENTO int NOT NULL REFERENCES dbo.Documenti(Id) ,
     ATTRIBUTOVALUE nvarchar(MAX))
    GO
    
    SET NOCOUNT ON
    -- Inserisco i documenti
    INSERT INTO dbo.DOCUMENTI (ID,CODICEDOCUMENTO)
    SELECT 1,'Documento1' UNION ALL
    SELECT 2,'Documento2' UNION ALL
    SELECT 3,'Documento3' 
    
    -- Inserisco gli attributi
    INSERT INTO dbo.DOCUMENTOATTRIBUTO (IDATTRIBUTO,IDDOCUMENTO,ATTRIBUTOVALUE)
    SELECT 534,1,'C' UNION ALL
    SELECT 535,1,'GEN' UNION ALL
    SELECT 536,1,'GEN' UNION ALL
    SELECT 537,1,'LP' UNION ALL
    
    SELECT 534,2,'C' UNION ALL
    SELECT 536,2,'GEN' UNION ALL
    SELECT 537,2,'LP' UNION ALL
    
    SELECT 534,3,'C' UNION ALL
    SELECT 535,3,'GEN' UNION ALL
    SELECT 536,3,'FEB' UNION ALL
    SELECT 537,3,'LP'
    GO

    ora nella sp crea una tabella temporanea e inserisci i valori splittati di "534.C|535.GEN|536.GEN|537.LP" passati alla sp.
    Per semplicità  dell'esempio li inserisco con una normale insert into, provvedi a modificare la insert in modo appropriato.

    /************** Questa parte simula la SP *********/
    
    SET NOCOUNT ON 
    
    -- Tabella temporanea attributi
    CREATE TABLE #Attributi
    (IDATTRIBUTO int,
     ATTRIBUTOVALUE nvarchar(MAX)
    )
    
    -- Inserisco gli attributi passati alla Sp
    INSERT INTO #Attributi
    SELECT 534,'C' UNION ALL
    SELECT 535,'GEN' UNION ALL
    SELECT 536,'GEN' UNION ALL
    SELECT 537,'LP'
    
    -- Rilevo il numero di attributi richiesti
    DECLARE @NumAttributi Int
    SET @NumAttributi=(SELECT COUNT(*) FROM #Attributi)
    
    -- Select Finale
    SELECT D.ID
     ,D.CODICEDOCUMENTO
    FROM dbo.DOCUMENTI AS D
     INNER JOIN
     dbo.DOCUMENTOATTRIBUTO AS DA ON D.ID = DA.IDDOCUMENTO
     INNER JOIN
     #Attributi AS A ON DA.IDATTRIBUTO=A.IDATTRIBUTO AND DA.ATTRIBUTOVALUE=A.ATTRIBUTOVALUE
    GROUP BY D.Id,D.CODICEDOCUMENTO
    HAVING COUNT(*) = @NumAttributi
    /**************** fine sp ***************/
    -- Pulizia
    DROP TABLE #Attributi,dbo.DOCUMENTOATTRIBUTO,dbo.DOCUMENTI
    

    Risultato:
    ID          CODICEDOCUMENTO
    ----------- ----------------------
    1           Documento1

    Ciao
    Giorgio Rancati

    venerdì 21 maggio 2010 11:40
    Moderatore

Tutte le risposte

  • Ciao Enrico,

    puoi mostrare un piccolo esempio di dati e il risultato che vuoi ottenere ?
    meglio ancora se mostri la create table e le insert into per i dati di esempio.

    Ciao
    Giorgio Rancati

    venerdì 21 maggio 2010 09:27
    Moderatore
  • Ciao Giorgio, grazie per l'interessamento!!

    Tabella "Documento":

    ID int (Primary Key con Identity)

    PATH nvarchar(255)

    DESCRIZIONE nvarchar(255)

    ....

     

    Tabella "DocumentoAttributo"

    ID int (Primary Key con Identity)

    IDDocumento (Foreign Key alla tabella Documento)

    IDAttributo (Foreign Key alla tabella Attributo (anagrafica attributi))

    VALORE nvarchar(255)

    ....

     

    La mia query è qualcosa di sto genere:

    SELECT *

    FROM  DOCUMENTO

    INNER JOIN DOCUMENTOATTRIBUTO

    ON DOCUMENTOATTRIBUTO.IDDOCUMENTO=DOCUMENTO.ID

    WHERE DOCUMENTOATTRIBUTO.IDATTRIBUTO = idatt1 AND DOCUMENTOATTRIBUTO.VALORE = valatt1

    questa query mi restituisce "N" righe, a questo punto devo fare una query su queste "N" righe cercando non più idatt1 e valatt1 ma idatt2 e valatt2.

    La lista idattxx e valattxx è variabile in numero e ordine. Ho messo in piedi una Stored Procedure che fa quello che mi serve (a mio avviso in modo bruttissimo), se vuoi te la posto tutta...

    Enrico

     

     

     

     

     

    venerdì 21 maggio 2010 09:42
  • Ciao Enrico,

    ok a grandi linee ho capito, puoi mostrare un piccolo esempio di dati contenuti nella tabella documentoattributo, la lista dei valori di idatt1 e valatt1 e il risultato che vuoi ottenere in base ai dati forniti ?

    in questo modo si può fare un esempio concreto.

    Ciao
    Giorgio Rancati

     

    venerdì 21 maggio 2010 09:55
    Moderatore

  • Struttura tabella: DOCUMENTO
    ID   int Unchecked
    IDDOCUMENTOIDROWORK int Checked
    PATHCOMPLETO  nvarchar(450) Unchecked
    ISCHECKOUT  bit Unchecked
    CODICEDOCUMENTO  nvarchar(255) Checked
    DATACODIFICA  datetime Unchecked
    IDOPERATORE  int Unchecked
    IDFASE   int Unchecked
    IDPARENT  int Unchecked
    REV   int Unchecked

    Struttura tabella: DOCUMENTOATTRIBUTO
    ID  int Unchecked
    IDATTRIBUTO int Unchecked
    IDDOCUMENTO int Unchecked
    ATTRIBUTOVALUE nvarchar(MAX) Checked

    alla mia stored procedure passo una stringa fatta così:
    "534.C|535.GEN|536.GEN|537.LP"

    tramite un function splitto questa stringa in modo da ottenere una tabella con questo formato

    534.C
    535.GEN
    536.GEN
    537.LP

    Con un cursore passo questa tabella, divido la singola riga (cercando il punto) ottenendo così 534 (idatt) e C (valatt).
    quando ho questi due valori faccio la query. alla fine dell'esecuzione, devo restituire le righe della tabella documento
    che hanno rispettato questa ricerca

    Enrico

    venerdì 21 maggio 2010 10:15
  • vabbè, i  dati li simulo io, do per scontato che un documento non può avere coppie di attributi duplicati.

    USE TEMPDB
    GO
    CREATE TABLE dbo.DOCUMENTI
    (ID Int Primary Key,
     CODICEDOCUMENTO nvarchar(255) 
    )
    
    CREATE TABLE dbo.DOCUMENTOATTRIBUTO
    (ID INT Identity Primary Key,
     IDATTRIBUTO int,
     IDDOCUMENTO int NOT NULL REFERENCES dbo.Documenti(Id) ,
     ATTRIBUTOVALUE nvarchar(MAX))
    GO
    
    SET NOCOUNT ON
    -- Inserisco i documenti
    INSERT INTO dbo.DOCUMENTI (ID,CODICEDOCUMENTO)
    SELECT 1,'Documento1' UNION ALL
    SELECT 2,'Documento2' UNION ALL
    SELECT 3,'Documento3' 
    
    -- Inserisco gli attributi
    INSERT INTO dbo.DOCUMENTOATTRIBUTO (IDATTRIBUTO,IDDOCUMENTO,ATTRIBUTOVALUE)
    SELECT 534,1,'C' UNION ALL
    SELECT 535,1,'GEN' UNION ALL
    SELECT 536,1,'GEN' UNION ALL
    SELECT 537,1,'LP' UNION ALL
    
    SELECT 534,2,'C' UNION ALL
    SELECT 536,2,'GEN' UNION ALL
    SELECT 537,2,'LP' UNION ALL
    
    SELECT 534,3,'C' UNION ALL
    SELECT 535,3,'GEN' UNION ALL
    SELECT 536,3,'FEB' UNION ALL
    SELECT 537,3,'LP'
    GO

    ora nella sp crea una tabella temporanea e inserisci i valori splittati di "534.C|535.GEN|536.GEN|537.LP" passati alla sp.
    Per semplicità  dell'esempio li inserisco con una normale insert into, provvedi a modificare la insert in modo appropriato.

    /************** Questa parte simula la SP *********/
    
    SET NOCOUNT ON 
    
    -- Tabella temporanea attributi
    CREATE TABLE #Attributi
    (IDATTRIBUTO int,
     ATTRIBUTOVALUE nvarchar(MAX)
    )
    
    -- Inserisco gli attributi passati alla Sp
    INSERT INTO #Attributi
    SELECT 534,'C' UNION ALL
    SELECT 535,'GEN' UNION ALL
    SELECT 536,'GEN' UNION ALL
    SELECT 537,'LP'
    
    -- Rilevo il numero di attributi richiesti
    DECLARE @NumAttributi Int
    SET @NumAttributi=(SELECT COUNT(*) FROM #Attributi)
    
    -- Select Finale
    SELECT D.ID
     ,D.CODICEDOCUMENTO
    FROM dbo.DOCUMENTI AS D
     INNER JOIN
     dbo.DOCUMENTOATTRIBUTO AS DA ON D.ID = DA.IDDOCUMENTO
     INNER JOIN
     #Attributi AS A ON DA.IDATTRIBUTO=A.IDATTRIBUTO AND DA.ATTRIBUTOVALUE=A.ATTRIBUTOVALUE
    GROUP BY D.Id,D.CODICEDOCUMENTO
    HAVING COUNT(*) = @NumAttributi
    /**************** fine sp ***************/
    -- Pulizia
    DROP TABLE #Attributi,dbo.DOCUMENTOATTRIBUTO,dbo.DOCUMENTI
    

    Risultato:
    ID          CODICEDOCUMENTO
    ----------- ----------------------
    1           Documento1

    Ciao
    Giorgio Rancati

    venerdì 21 maggio 2010 11:40
    Moderatore
  • Ciao Giorgio... che dire!? da qui capisco quanto poco conosco sql!

    tanto di cappello alla soluzione, corro subito ad implementarla e ti faccio sapere come va!

    Grazie mille

    Enrico

    venerdì 21 maggio 2010 11:59
  • ok, vedi anche se puoi fare qualcosa per quel brutto ATTRIBUTOVALUE nvarchar(MAX)
    Se possibile riducilo a un nvarchar(x) o varchar(x) dove x sia una lunghezza più ridotta, ad esempio 10 o 20, visto anche il fatto che passi gli attributi alla sp in una singola stringa, mi sembra strano che ti servano 2Gbyte per la colonna ATTRIBUTOVALUE.

    Con una dimensione più ridotta potresti valutare se conviene creare un indice sulla tabella dbo.DOCUMENTOATTRIBUTO composto dai campi IDATTRIBUTO e ATTRIBUTOVALUE
    ----
    CREATE INDEX IX_IDATTRIBUTO_ATTRIBUTOVALUE ON dbo.DOCUMENTOATTRIBUTO (IDATTRIBUTO,ATTRIBUTOVALUE)
    ----

    Ciao
    Giorgio Rancati

    venerdì 21 maggio 2010 12:26
    Moderatore