T-SQL

Re: Probleme limita referinte la o tabela; The query processor ran out of stack space during query optimization. Please simplify the query

Diana — Tue, 09 Oct 2007 11:45:59 GMT

rsocol:
Diana,
Cascading DRI nu ajută în acest caz. Chiar sunt neindicate: dacă ştergi user-ul, să ştergi toate înregistrările modificate de el ?! În nici un caz. Poate te refereai la Cascading DRI între tabela Users şi tabelele u1, u2, etc. Atunci da, ai dreptate, însă menţinerea acestor tabele presupune şi adăugarea datelor în ele (nu doar ştergerea), aşa că oricum îţi trebuie un trigger.

...este o cale de explorat, nu neaparat de urmat...

Re: Probleme limita referinte la o tabela; The query processor ran out of stack space during query optimization. Please simplify the query

rsocol — Tue, 09 Oct 2007 11:14:58 GMT

După câte înţeleg eu, e vorba de [2], pentru că Ela a scris "[...] facem fk din orice tabela catre cea de useri ca sa logam id-ul userului care a creat fiecare inregistrare si a celui care a modificat-o ultima data."

Răzvan

Re: Probleme limita referinte la o tabela; The query processor ran out of stack space during query optimization. Please simplify the query

B_gd_n[ ]Sahlean — Tue, 09 Oct 2007 10:30:05 GMT

elenivarsavia > Am o intrebare: rolul acestei solutii de proiectare a bazei de date folosind o tabela cu utilizatori si cu legaturi de la celelante tabele la tabela utilizator este acela:

[1] de a restrictiona accesul utilizatorilor doar la inregistrarile "lor"

[2] de a permite inregistrarea tuturor modificarilor realizate de un utilizator (prin executia de comenzi INSERT / UPDATE / DELETE)

Re: Probleme limita referinte la o tabela; The query processor ran out of stack space during query optimization. Please simplify the query

rsocol — Tue, 09 Oct 2007 08:46:40 GMT

După cum spuneam, întreţinerea FK-urilor (spre mai multe tabele alternative de utilizatori) poate fi automatizată.

Să presupunem că ai 350 de tabele cu FK-uri spre tabela Users:

USE tempdb

CREATE TABLE Users (
UserID int IDENTITY PRIMARY KEY,
UserName nvarchar(50) NOT NULL UNIQUE
)

CREATE TABLE T1 (a int primary key, UserID int references Users(UserID))
CREATE TABLE T2 (a int primary key, UserID int references Users(UserID))
[...]
CREATE TABLE T350 (a int primary key, UserID int references Users(UserID))

Pentru cei care vor să testeze ideea, CREATE TABLE-urile de mai sus pot fi generate cu următorul query:

SELECT 'CREATE TABLE T'+CAST(n as varchar(10))+' (a int primary key, UserID int references Users(UserID))'
FROM (SELECT TOP 350 ROW_NUMBER() OVER (ORDER BY name) n FROM syscolumns) x

Ei, în acest caz, dacă vrem să ştergem pur şi simplu din tabela Users cu instrucţiunea "DELETE Users WHERE UserID=1", obţinem minunata eroare:

Msg 8621, Level 17, State 2, Line 1
The query processor ran out of stack space during query optimization. Please simplify the query.

Să presupunem că facem 5 tabele auxiliare cu useri (n-avem nevoie decât de UserID, nu şi de celelalte coloane din tabela Users):

CREATE TABLE Users1 (UserID int PRIMARY KEY REFERENCES Users)
CREATE TABLE Users2 (UserID int PRIMARY KEY REFERENCES Users)
CREATE TABLE Users3 (UserID int PRIMARY KEY REFERENCES Users)
CREATE TABLE Users4 (UserID int PRIMARY KEY REFERENCES Users)
CREATE TABLE Users5 (UserID int PRIMARY KEY REFERENCES Users)

GO
CREATE TRIGGER CopiazaUserID ON Users FOR INSERT
AS
IF @@ROWCOUNT>0 BEGIN
    SET NOCOUNT ON
    INSERT INTO Users1 (UserID) SELECT UserID FROM inserted
    INSERT INTO Users2 (UserID) SELECT UserID FROM inserted
    INSERT INTO Users3 (UserID) SELECT UserID FROM inserted
    INSERT INTO Users4 (UserID) SELECT UserID FROM inserted
    INSERT INTO Users5 (UserID) SELECT UserID FROM inserted
END

GO
CREATE TRIGGER StergeUserID ON Users INSTEAD OF DELETE
AS
IF @@ROWCOUNT>0 BEGIN
    SET NOCOUNT ON
    DELETE Users1 WHERE UserID IN (SELECT UserID FROM deleted)
    DELETE Users2 WHERE UserID IN (SELECT UserID FROM deleted)
    DELETE Users3 WHERE UserID IN (SELECT UserID FROM deleted)
    DELETE Users4 WHERE UserID IN (SELECT UserID FROM deleted)
    DELETE Users5 WHERE UserID IN (SELECT UserID FROM deleted)
    DELETE Users WHERE UserID IN (SELECT UserID FROM deleted)
END

În loc de trigger-ul "INSTEAD OF DELETE" puteam să facem CASCADE DELETE la cele 5 FK-uri, dar cred că ar funcţiona mai bine aşa.

Cu 5 tabele de user-i, dacă alocăm pe rând FK-urile la cea mai puţin utilizată, putem să ajungem până la vreo 1000 de tabele cu FK-uri fără să dea eroare. Pentru a modifica FK-urile existente, putem executa următoarea procedură:

CREATE PROCEDURE FixUserFKs
AS
SET NOCOUNT ON

BEGIN TRAN

DECLARE ForeignKeysList CURSOR LOCAL READ_ONLY FOR
SELECT OBJECT_NAME(f.object_id) as FKName,
    SCHEMA_NAME(t.schema_id) as SchemaName,
    t.name as TableName, c.name as ColumnName
FROM sys.foreign_keys f INNER JOIN sys.tables t ON f.parent_object_id=t.object_id
INNER JOIN sys.foreign_key_columns fc ON f.object_id=fc.constraint_object_id
INNER JOIN sys.columns c ON c.object_id=t.object_id AND c.column_id=fc.parent_column_id
WHERE f.referenced_object_id=OBJECT_ID('Users')
ORDER BY TableName
IF @@ERROR<>0 BEGIN ROLLBACK RETURN END

DECLARE @FKName sysname, @SchemaName sysname, @TableName sysname, @ColumnName sysname
OPEN ForeignKeysList
IF @@ERROR<>0 BEGIN ROLLBACK RETURN END

WHILE 1=1 BEGIN
    FETCH NEXT FROM ForeignKeysList INTO @FKName, @SchemaName, @TableName, @ColumnName
    IF @@FETCH_STATUS<>0 BREAK

    DECLARE @ReferencedTable sysname
    SELECT @ReferencedTable=name FROM (
        SELECT TOP 1 name, (
            SELECT COUNT(*) FROM sys.foreign_keys f
            WHERE f.referenced_object_id=t.object_id
        ) cnt
        FROM sys.tables t
        WHERE name LIKE 'Users[1-5]'
        ORDER BY cnt, name
    ) X WHERE cnt<255
    IF @@ERROR<>0 BEGIN ROLLBACK RETURN END

    IF @ReferencedTable IS NULL BEGIN
        RAISERROR ('Could not find any alternate Users table !',16,1)
        ROLLBACK
        RETURN
    END

    DECLARE @SQL nvarchar(4000)
    SET @SQL='ALTER TABLE '+QUOTENAME(@SchemaName)+'.'+QUOTENAME(@TableName)
        +' DROP CONSTRAINT '+QUOTENAME(@FKName)
    PRINT @SQL
    EXEC(@SQL)
    IF @@ERROR<>0 BEGIN ROLLBACK RETURN END

    SET @SQL='ALTER TABLE '+QUOTENAME(@SchemaName)+'.'+QUOTENAME(@TableName)
        +' ADD CONSTRAINT '+QUOTENAME(@FKName)+' FOREIGN KEY ('+QUOTENAME(@ColumnName)+')'
        +' REFERENCES '+QUOTENAME(@ReferencedTable)+' (UserID)'
    PRINT @SQL
    EXEC(@SQL)
    IF @@ERROR<>0 BEGIN ROLLBACK RETURN END
END

CLOSE ForeignKeysList
DEALLOCATE ForeignKeysList

COMMIT

Putem executa această procedură şi mai târziu, dacă se creează noi FK-uri care referă tabela Users. De fapt, putem să le spunem dezvoltatorilor: "nu vă bateţi capul să alegeţi voi tabela alternativă potrivită; faceţi FK-urile noi către tabela Users, iar din când în când o să executăm această procedură ca să le mute pe alte tabele mai puţin folosite". Şi astfel putem să trăim fericiţi până la adânci bătrâneţi fără să ne mai doară problema cu numărul prea mare de FK-uri spre aceeaşi tabelă.

Pentru cei care au vrut să testeze soluţia de mai sus, puteţi să scăpaţi de sutele de tabele de test executând rezultatul query-ului:

SELECT 'DROP TABLE T'+CAST(n as varchar(10))
FROM (SELECT TOP 350 ROW_NUMBER() OVER (ORDER BY name) n FROM syscolumns) x

iar apoi:

DROP TABLE Users1, Users2, Users3, Users4, Users5
DROP TABLE Users

dar oricum, dacă le-aţi creat în tempdb, ele vor fi şterse automat la următoarea restartare a server-ului.

Răzvan Socol
SQL Server MVP

Re: Probleme limita referinte la o tabela; The query processor ran out of stack space during query optimization. Please simplify the query

rsocol — Tue, 09 Oct 2007 08:01:33 GMT

Cryogenic1981,

Problema e într-adevăr cauzată de numărul mare de FK-uri spre aceeaşi tabelă (nu de modul în care e scris query-ul). Query-ul poate fi pur şi simplu:

DELETE Users WHERE UserID=101

Dar încearcă aceeaşi instrucţiune dacă ai două tabele care referă UserID sau dacă ai zece tabele care referă UserID. O să observi o diferenţă semnificativă în complexitatea query plan-ului.

Diana,

Cascading DRI nu ajută în acest caz. Chiar sunt neindicate: dacă ştergi user-ul, să ştergi toate înregistrările modificate de el ?! În nici un caz. Poate te refereai la Cascading DRI între tabela Users şi tabelele u1, u2, etc. Atunci da, ai dreptate, însă menţinerea acestor tabele presupune şi adăugarea datelor în ele (nu doar ştergerea), aşa că oricum îţi trebuie un trigger.

Ela,

Întreţinerea FK-urilor poate fi automatizată. Revin astăzi cu detalii.

Răzvan

Re: Probleme limita referinte la o tabela; The query processor ran out of stack space during query optimization. Please simplify the query

Diana — Mon, 08 Oct 2007 20:09:01 GMT

Mai poti:

- sa folosesti "cascading referential integrity constraints" (vezi Books Online pentru detalii).

- sa adaugi in tabela de useri un camp "IsActive" (bit). Nu mai stergi userul, ci, in functie de valoarea campului, modifici logica aplicatiei.

Ce vei face depinde si de "business rules". Intr-adevar, pentru un sfat mai amanuntit ar trebui sa dai amanunte - structura bazei de date, "business rules", query cu probleme - in cazul tau probabil un query prea "stufos"...

Vezi de asemenea si:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=824404&SiteID=1

http://msdn2.microsoft.com/en-us/library/aa337357.aspx

Re: Probleme limita referinte la o tabela; The query processor ran out of stack space during query optimization. Please simplify the query

Cryogenic1981 — Mon, 08 Oct 2007 14:24:49 GMT

Problema nu este nr. de FK-uri ci modul in care e scris query-ul, pesupun ca query-ul in cauza foloseste in clauza where multe conditii de forma WHERE ... ID_xxx IN (1,2,3,4 ...n1,n2,n3).

Daca e asa solutia e rescrierea query-ului, daca poti sa-mi confirmi asta si eventual sa si postezi un query, stiu mai multe solutii la problema. Daca nu e asa oricum tot query-ul e de vina si ar ajuta sa postezi query-ul pt. a vedea cum arata.

Probleme limita referinte la o tabela; The query processor ran out of stack space during query optimization. Please simplify the query

elenivarsavia — Mon, 08 Oct 2007 13:23:00 GMT

Buna,

as avea nevoie de un sfat referitor la o problema de arhitectura.

Folosesc in BD o tabela de useri, sincronizata cu AD, care este referita de majoritatea tabelelor din DB, prin cel putin 2 campuri. Noi ne-am gandit sa facem fk din orice tabela catre cea de useri ca sa logam id-ul userului care a creat fiecare inregistrare si a celui care a modificat-o ultima data.

Problema este ca , in cazul in care un user nu este activ - a fost creat din greseala sau mai stiu eu ce situatie mai apare, am nevoie sa il sterg. Insa, datorita faptului ca deja am peste 300 referinte la tabela de useri, si nr lor va mai creste, sql nu reuseste sa isi construiasca query planul. SI da eroarea : The query processor ran out of stack space during query optimization. Please simplify the query

Deocamdata am gasit 2 solutii:

- sa renuntam la fk si eventual sa facem verificarile pe triggeri la delete, ca userul nu a fost folosit in nici una din cel x...sute de tabele

- sa utilizam o tabela de useri de care sa se lege x tabele care sa contina id user, si care sa fie referite fiecare de o parte din tabelele existente; Ex: tabela u1 - sa fie referita de primele 100 tabele din DB; tabela u2 - referita de urmatoarele 100. Asta ar insemna ca sa stim ce tabela u ii corespunde fiecarei tabele de date din DB. Dar e o solutie greoaie si greu de monitorizat in cazul in care apar modificari.

Are cineva alte solutii? Idei?

Mercic mult

Ela