|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Triggers - need suggestionI have around 50 tables for which i wanted to write simple triggers(trigger
to insert data in some other normalized table) as we dont have database level triggers in SQL Server i am forced to do table level for all these 50 tables.there are around 500 users will be accessing the table on the rate of 50 times per day, please suggest is it a good idea to have triggers in this scenario?or any other alternatives for this? my requirement is the data goes to 50 different tables and when display report to user we have get data from all these table , now we are looping through all the tables and inserting in temp table and displaying to user , but we found this process is taking enormous time delay in showing up the report (3 to 5 mins),we have to greatly reduce this time any suggestions? vsr (v**@discussions.microsoft.com) writes:
> I have around 50 tables for which i wanted to write simple I'm sorry, but I don't enough information to answer any question. What> triggers(trigger to insert data in some other normalized table) as we > dont have database level triggers in SQL Server i am forced to do table > level for all these 50 tables.there are around 500 users will be > accessing the table on the rate of 50 times per day, please suggest is > it a good idea to have triggers in this scenario?or any other > alternatives for this? my requirement is the data goes to 50 different > tables and when display report to user we have get data from all these > table , now we are looping through all the tables and inserting in temp > table and displaying to user , but we found this process is taking > enormous time delay in showing up the report (3 to 5 mins),we have to > greatly reduce this time any suggestions? sort of data do you want to write into this "other normalized table"? Could you give a concrete example? -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx its just putting the data from all the tables to one table...it goes like
this... Table1 Columns Name , Addess1,Address2,City,Stata,Zip Table2 Columns Name , Addess1,Address2,City,Stata,Zip Table3 Columns Name , Addess1,Address2,City,Stata,Zip Table4 Columns Name , Addess1,Address2,City,Stata,Zip the normalized table columns are similar to above...Name , Addess1,Address2,City,Stata,Zip whenever any insert , update, delete happens on any of the above tables i need to insert / update/delete the data in normalized table , whenever user requests report i can directly query this normalized table by having simple join with one another table to display (instead of looping through all the tables) if needed more information i can send code snippets with table structure. Show quote "Erland Sommarskog" wrote: > vsr (v**@discussions.microsoft.com) writes: > > I have around 50 tables for which i wanted to write simple > > triggers(trigger to insert data in some other normalized table) as we > > dont have database level triggers in SQL Server i am forced to do table > > level for all these 50 tables.there are around 500 users will be > > accessing the table on the rate of 50 times per day, please suggest is > > it a good idea to have triggers in this scenario?or any other > > alternatives for this? my requirement is the data goes to 50 different > > tables and when display report to user we have get data from all these > > table , now we are looping through all the tables and inserting in temp > > table and displaying to user , but we found this process is taking > > enormous time delay in showing up the report (3 to 5 mins),we have to > > greatly reduce this time any suggestions? > > I'm sorry, but I don't enough information to answer any question. What > sort of data do you want to write into this "other normalized table"? > Could you give a concrete example? > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > Hi,
Well just wondering if it is possible to do the same thing with say sproc or something ? maybe you might wanna try it out and see if the performance is increased or so. (and remember to place transaction properly) Also a trigger would act like one big transaction holding locks and stuff. you might wanna evaluate your scenario and check if you have to use trigger ? abhishek Show quote "vsr" wrote: > its just putting the data from all the tables to one table...it goes like > this... > > Table1 Columns Name , Addess1,Address2,City,Stata,Zip > Table2 Columns Name , Addess1,Address2,City,Stata,Zip > Table3 Columns Name , Addess1,Address2,City,Stata,Zip > Table4 Columns Name , Addess1,Address2,City,Stata,Zip > > the normalized table columns are similar to above...Name , > Addess1,Address2,City,Stata,Zip > > whenever any insert , update, delete happens on any of the above tables i > need to insert / update/delete the data in normalized table , whenever user > requests report i can directly query this normalized table by having simple > join with one another table to display (instead of looping through all the > tables) > > if needed more information i can send code snippets with table structure. > > "Erland Sommarskog" wrote: > > > vsr (v**@discussions.microsoft.com) writes: > > > I have around 50 tables for which i wanted to write simple > > > triggers(trigger to insert data in some other normalized table) as we > > > dont have database level triggers in SQL Server i am forced to do table > > > level for all these 50 tables.there are around 500 users will be > > > accessing the table on the rate of 50 times per day, please suggest is > > > it a good idea to have triggers in this scenario?or any other > > > alternatives for this? my requirement is the data goes to 50 different > > > tables and when display report to user we have get data from all these > > > table , now we are looping through all the tables and inserting in temp > > > table and displaying to user , but we found this process is taking > > > enormous time delay in showing up the report (3 to 5 mins),we have to > > > greatly reduce this time any suggestions? > > > > I'm sorry, but I don't enough information to answer any question. What > > sort of data do you want to write into this "other normalized table"? > > Could you give a concrete example? > > > > > > -- > > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > > > Books Online for SQL Server 2005 at > > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > > Books Online for SQL Server 2000 at > > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > how can we replace triggers with stored procedures? we cant change the
existing structure of the schema and that is the big limitation, and at the same time we need to bring up the reporting performance. Show quote "Abhishek Pandey" wrote: > Hi, > Well just wondering if it is possible to do the same thing with say sproc or > something ? maybe you might wanna try it out and see if the performance is > increased or so. (and remember to place transaction properly) > > Also a trigger would act like one big transaction holding locks and stuff. > you might wanna evaluate your scenario and check if you have to use trigger ? > > abhishek > > > "vsr" wrote: > > > its just putting the data from all the tables to one table...it goes like > > this... > > > > Table1 Columns Name , Addess1,Address2,City,Stata,Zip > > Table2 Columns Name , Addess1,Address2,City,Stata,Zip > > Table3 Columns Name , Addess1,Address2,City,Stata,Zip > > Table4 Columns Name , Addess1,Address2,City,Stata,Zip > > > > the normalized table columns are similar to above...Name , > > Addess1,Address2,City,Stata,Zip > > > > whenever any insert , update, delete happens on any of the above tables i > > need to insert / update/delete the data in normalized table , whenever user > > requests report i can directly query this normalized table by having simple > > join with one another table to display (instead of looping through all the > > tables) > > > > if needed more information i can send code snippets with table structure. > > > > "Erland Sommarskog" wrote: > > > > > vsr (v**@discussions.microsoft.com) writes: > > > > I have around 50 tables for which i wanted to write simple > > > > triggers(trigger to insert data in some other normalized table) as we > > > > dont have database level triggers in SQL Server i am forced to do table > > > > level for all these 50 tables.there are around 500 users will be > > > > accessing the table on the rate of 50 times per day, please suggest is > > > > it a good idea to have triggers in this scenario?or any other > > > > alternatives for this? my requirement is the data goes to 50 different > > > > tables and when display report to user we have get data from all these > > > > table , now we are looping through all the tables and inserting in temp > > > > table and displaying to user , but we found this process is taking > > > > enormous time delay in showing up the report (3 to 5 mins),we have to > > > > greatly reduce this time any suggestions? > > > > > > I'm sorry, but I don't enough information to answer any question. What > > > sort of data do you want to write into this "other normalized table"? > > > Could you give a concrete example? > > > > > > > > > -- > > > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > > > > > Books Online for SQL Server 2005 at > > > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > > > Books Online for SQL Server 2000 at > > > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > > You might want to try creating a materialized view. You accomplish this by
creating a view WITH SCHEMABINDING and then creating a UNIQUE CLUSTERED INDEX on the view. There are additional requirements: SET options that must be assigned correctly when creating the view and also when manipulating the underlying tables. Look up CREATE VIEW and CREATE INDEX in BOL. This solution has advantages in that the view reflects the contents of the underlying tables without the query performance penalty, and you don't have to maintain a bunch of triggers. The disadvantages are similar to the disadvantages of having a separate table that must be maintained with triggers--that is, increased probability of deadlocks and reduced insert and update performance. Another disadvantage is that the SET options must be assigned correctly for operations that manipulate the underlying tables, which may require modification of the procedures or client code that execute the updates. Show quote "vsr" <v**@discussions.microsoft.com> wrote in message news:55082E0A-063F-4774-91FD-B7C10D90E2A2@microsoft.com... > how can we replace triggers with stored procedures? we cant change the > existing structure of the schema and that is the big limitation, and at > the > same time we need to bring up the reporting performance. > > "Abhishek Pandey" wrote: > >> Hi, >> Well just wondering if it is possible to do the same thing with say sproc >> or >> something ? maybe you might wanna try it out and see if the performance >> is >> increased or so. (and remember to place transaction properly) >> >> Also a trigger would act like one big transaction holding locks and >> stuff. >> you might wanna evaluate your scenario and check if you have to use >> trigger ? >> >> abhishek >> >> >> "vsr" wrote: >> >> > its just putting the data from all the tables to one table...it goes >> > like >> > this... >> > >> > Table1 Columns Name , Addess1,Address2,City,Stata,Zip >> > Table2 Columns Name , Addess1,Address2,City,Stata,Zip >> > Table3 Columns Name , Addess1,Address2,City,Stata,Zip >> > Table4 Columns Name , Addess1,Address2,City,Stata,Zip >> > >> > the normalized table columns are similar to above...Name , >> > Addess1,Address2,City,Stata,Zip >> > >> > whenever any insert , update, delete happens on any of the above tables >> > i >> > need to insert / update/delete the data in normalized table , whenever >> > user >> > requests report i can directly query this normalized table by having >> > simple >> > join with one another table to display (instead of looping through all >> > the >> > tables) >> > >> > if needed more information i can send code snippets with table >> > structure. >> > >> > "Erland Sommarskog" wrote: >> > >> > > vsr (v**@discussions.microsoft.com) writes: >> > > > I have around 50 tables for which i wanted to write simple >> > > > triggers(trigger to insert data in some other normalized table) as >> > > > we >> > > > dont have database level triggers in SQL Server i am forced to do >> > > > table >> > > > level for all these 50 tables.there are around 500 users will be >> > > > accessing the table on the rate of 50 times per day, please suggest >> > > > is >> > > > it a good idea to have triggers in this scenario?or any other >> > > > alternatives for this? my requirement is the data goes to 50 >> > > > different >> > > > tables and when display report to user we have get data from all >> > > > these >> > > > table , now we are looping through all the tables and inserting in >> > > > temp >> > > > table and displaying to user , but we found this process is taking >> > > > enormous time delay in showing up the report (3 to 5 mins),we have >> > > > to >> > > > greatly reduce this time any suggestions? >> > > >> > > I'm sorry, but I don't enough information to answer any question. >> > > What >> > > sort of data do you want to write into this "other normalized table"? >> > > Could you give a concrete example? >> > > >> > > >> > > -- >> > > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >> > > >> > > Books Online for SQL Server 2005 at >> > > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >> > > Books Online for SQL Server 2000 at >> > > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >> > > I will start trying with view option but i may need to use union to cobine
the results set of all the tables while creating this view , i heard union will again hit the performance,is it true? Show quote "Brian Selzer" wrote: > You might want to try creating a materialized view. You accomplish this by > creating a view WITH SCHEMABINDING and then creating a UNIQUE CLUSTERED > INDEX on the view. There are additional requirements: SET options that must > be assigned correctly when creating the view and also when manipulating the > underlying tables. Look up CREATE VIEW and CREATE INDEX in BOL. > > This solution has advantages in that the view reflects the contents of the > underlying tables without the query performance penalty, and you don't have > to maintain a bunch of triggers. The disadvantages are similar to the > disadvantages of having a separate table that must be maintained with > triggers--that is, increased probability of deadlocks and reduced insert and > update performance. Another disadvantage is that the SET options must be > assigned correctly for operations that manipulate the underlying tables, > which may require modification of the procedures or client code that execute > the updates. > > > "vsr" <v**@discussions.microsoft.com> wrote in message > news:55082E0A-063F-4774-91FD-B7C10D90E2A2@microsoft.com... > > how can we replace triggers with stored procedures? we cant change the > > existing structure of the schema and that is the big limitation, and at > > the > > same time we need to bring up the reporting performance. > > > > "Abhishek Pandey" wrote: > > > >> Hi, > >> Well just wondering if it is possible to do the same thing with say sproc > >> or > >> something ? maybe you might wanna try it out and see if the performance > >> is > >> increased or so. (and remember to place transaction properly) > >> > >> Also a trigger would act like one big transaction holding locks and > >> stuff. > >> you might wanna evaluate your scenario and check if you have to use > >> trigger ? > >> > >> abhishek > >> > >> > >> "vsr" wrote: > >> > >> > its just putting the data from all the tables to one table...it goes > >> > like > >> > this... > >> > > >> > Table1 Columns Name , Addess1,Address2,City,Stata,Zip > >> > Table2 Columns Name , Addess1,Address2,City,Stata,Zip > >> > Table3 Columns Name , Addess1,Address2,City,Stata,Zip > >> > Table4 Columns Name , Addess1,Address2,City,Stata,Zip > >> > > >> > the normalized table columns are similar to above...Name , > >> > Addess1,Address2,City,Stata,Zip > >> > > >> > whenever any insert , update, delete happens on any of the above tables > >> > i > >> > need to insert / update/delete the data in normalized table , whenever > >> > user > >> > requests report i can directly query this normalized table by having > >> > simple > >> > join with one another table to display (instead of looping through all > >> > the > >> > tables) > >> > > >> > if needed more information i can send code snippets with table > >> > structure. > >> > > >> > "Erland Sommarskog" wrote: > >> > > >> > > vsr (v**@discussions.microsoft.com) writes: > >> > > > I have around 50 tables for which i wanted to write simple > >> > > > triggers(trigger to insert data in some other normalized table) as > >> > > > we > >> > > > dont have database level triggers in SQL Server i am forced to do > >> > > > table > >> > > > level for all these 50 tables.there are around 500 users will be > >> > > > accessing the table on the rate of 50 times per day, please suggest > >> > > > is > >> > > > it a good idea to have triggers in this scenario?or any other > >> > > > alternatives for this? my requirement is the data goes to 50 > >> > > > different > >> > > > tables and when display report to user we have get data from all > >> > > > these > >> > > > table , now we are looping through all the tables and inserting in > >> > > > temp > >> > > > table and displaying to user , but we found this process is taking > >> > > > enormous time delay in showing up the report (3 to 5 mins),we have > >> > > > to > >> > > > greatly reduce this time any suggestions? > >> > > > >> > > I'm sorry, but I don't enough information to answer any question. > >> > > What > >> > > sort of data do you want to write into this "other normalized table"? > >> > > Could you give a concrete example? > >> > > > >> > > > >> > > -- > >> > > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > >> > > > >> > > Books Online for SQL Server 2005 at > >> > > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > >> > > Books Online for SQL Server 2000 at > >> > > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > >> > > > > > can we use UNION in views? i read VIEW definition can not contain UNION.
Show quote "Brian Selzer" wrote: > You might want to try creating a materialized view. You accomplish this by > creating a view WITH SCHEMABINDING and then creating a UNIQUE CLUSTERED > INDEX on the view. There are additional requirements: SET options that must > be assigned correctly when creating the view and also when manipulating the > underlying tables. Look up CREATE VIEW and CREATE INDEX in BOL. > > This solution has advantages in that the view reflects the contents of the > underlying tables without the query performance penalty, and you don't have > to maintain a bunch of triggers. The disadvantages are similar to the > disadvantages of having a separate table that must be maintained with > triggers--that is, increased probability of deadlocks and reduced insert and > update performance. Another disadvantage is that the SET options must be > assigned correctly for operations that manipulate the underlying tables, > which may require modification of the procedures or client code that execute > the updates. > > > "vsr" <v**@discussions.microsoft.com> wrote in message > news:55082E0A-063F-4774-91FD-B7C10D90E2A2@microsoft.com... > > how can we replace triggers with stored procedures? we cant change the > > existing structure of the schema and that is the big limitation, and at > > the > > same time we need to bring up the reporting performance. > > > > "Abhishek Pandey" wrote: > > > >> Hi, > >> Well just wondering if it is possible to do the same thing with say sproc > >> or > >> something ? maybe you might wanna try it out and see if the performance > >> is > >> increased or so. (and remember to place transaction properly) > >> > >> Also a trigger would act like one big transaction holding locks and > >> stuff. > >> you might wanna evaluate your scenario and check if you have to use > >> trigger ? > >> > >> abhishek > >> > >> > >> "vsr" wrote: > >> > >> > its just putting the data from all the tables to one table...it goes > >> > like > >> > this... > >> > > >> > Table1 Columns Name , Addess1,Address2,City,Stata,Zip > >> > Table2 Columns Name , Addess1,Address2,City,Stata,Zip > >> > Table3 Columns Name , Addess1,Address2,City,Stata,Zip > >> > Table4 Columns Name , Addess1,Address2,City,Stata,Zip > >> > > >> > the normalized table columns are similar to above...Name , > >> > Addess1,Address2,City,Stata,Zip > >> > > >> > whenever any insert , update, delete happens on any of the above tables > >> > i > >> > need to insert / update/delete the data in normalized table , whenever > >> > user > >> > requests report i can directly query this normalized table by having > >> > simple > >> > join with one another table to display (instead of looping through all > >> > the > >> > tables) > >> > > >> > if needed more information i can send code snippets with table > >> > structure. > >> > > >> > "Erland Sommarskog" wrote: > >> > > >> > > vsr (v**@discussions.microsoft.com) writes: > >> > > > I have around 50 tables for which i wanted to write simple > >> > > > triggers(trigger to insert data in some other normalized table) as > >> > > > we > >> > > > dont have database level triggers in SQL Server i am forced to do > >> > > > table > >> > > > level for all these 50 tables.there are around 500 users will be > >> > > > accessing the table on the rate of 50 times per day, please suggest > >> > > > is > >> > > > it a good idea to have triggers in this scenario?or any other > >> > > > alternatives for this? my requirement is the data goes to 50 > >> > > > different > >> > > > tables and when display report to user we have get data from all > >> > > > these > >> > > > table , now we are looping through all the tables and inserting in > >> > > > temp > >> > > > table and displaying to user , but we found this process is taking > >> > > > enormous time delay in showing up the report (3 to 5 mins),we have > >> > > > to > >> > > > greatly reduce this time any suggestions? > >> > > > >> > > I'm sorry, but I don't enough information to answer any question. > >> > > What > >> > > sort of data do you want to write into this "other normalized table"? > >> > > Could you give a concrete example? > >> > > > >> > > > >> > > -- > >> > > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > >> > > > >> > > Books Online for SQL Server 2005 at > >> > > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > >> > > Books Online for SQL Server 2000 at > >> > > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > >> > > > > > Sorry, you can't create an indexed view if you use UNION or UNION ALL.
Why do you have so many tables with the same structure? Why not switch things around so that you have a single underlying table and a bunch of views? You could name the views table1, table2, etc., assuming those are the names of the original tables. These views could possibly be updatable, too. Read up on them. Show quote "vsr" <v**@discussions.microsoft.com> wrote in message news:E4D195FF-D5F2-40D3-B8CD-14D697F0ACFA@microsoft.com... > can we use UNION in views? i read VIEW definition can not contain UNION. > > "Brian Selzer" wrote: > >> You might want to try creating a materialized view. You accomplish this >> by >> creating a view WITH SCHEMABINDING and then creating a UNIQUE CLUSTERED >> INDEX on the view. There are additional requirements: SET options that >> must >> be assigned correctly when creating the view and also when manipulating >> the >> underlying tables. Look up CREATE VIEW and CREATE INDEX in BOL. >> >> This solution has advantages in that the view reflects the contents of >> the >> underlying tables without the query performance penalty, and you don't >> have >> to maintain a bunch of triggers. The disadvantages are similar to the >> disadvantages of having a separate table that must be maintained with >> triggers--that is, increased probability of deadlocks and reduced insert >> and >> update performance. Another disadvantage is that the SET options must be >> assigned correctly for operations that manipulate the underlying tables, >> which may require modification of the procedures or client code that >> execute >> the updates. >> >> >> "vsr" <v**@discussions.microsoft.com> wrote in message >> news:55082E0A-063F-4774-91FD-B7C10D90E2A2@microsoft.com... >> > how can we replace triggers with stored procedures? we cant change the >> > existing structure of the schema and that is the big limitation, and at >> > the >> > same time we need to bring up the reporting performance. >> > >> > "Abhishek Pandey" wrote: >> > >> >> Hi, >> >> Well just wondering if it is possible to do the same thing with say >> >> sproc >> >> or >> >> something ? maybe you might wanna try it out and see if the >> >> performance >> >> is >> >> increased or so. (and remember to place transaction properly) >> >> >> >> Also a trigger would act like one big transaction holding locks and >> >> stuff. >> >> you might wanna evaluate your scenario and check if you have to use >> >> trigger ? >> >> >> >> abhishek >> >> >> >> >> >> "vsr" wrote: >> >> >> >> > its just putting the data from all the tables to one table...it goes >> >> > like >> >> > this... >> >> > >> >> > Table1 Columns Name , Addess1,Address2,City,Stata,Zip >> >> > Table2 Columns Name , Addess1,Address2,City,Stata,Zip >> >> > Table3 Columns Name , Addess1,Address2,City,Stata,Zip >> >> > Table4 Columns Name , Addess1,Address2,City,Stata,Zip >> >> > >> >> > the normalized table columns are similar to above...Name , >> >> > Addess1,Address2,City,Stata,Zip >> >> > >> >> > whenever any insert , update, delete happens on any of the above >> >> > tables >> >> > i >> >> > need to insert / update/delete the data in normalized table , >> >> > whenever >> >> > user >> >> > requests report i can directly query this normalized table by having >> >> > simple >> >> > join with one another table to display (instead of looping through >> >> > all >> >> > the >> >> > tables) >> >> > >> >> > if needed more information i can send code snippets with table >> >> > structure. >> >> > >> >> > "Erland Sommarskog" wrote: >> >> > >> >> > > vsr (v**@discussions.microsoft.com) writes: >> >> > > > I have around 50 tables for which i wanted to write simple >> >> > > > triggers(trigger to insert data in some other normalized table) >> >> > > > as >> >> > > > we >> >> > > > dont have database level triggers in SQL Server i am forced to >> >> > > > do >> >> > > > table >> >> > > > level for all these 50 tables.there are around 500 users will be >> >> > > > accessing the table on the rate of 50 times per day, please >> >> > > > suggest >> >> > > > is >> >> > > > it a good idea to have triggers in this scenario?or any other >> >> > > > alternatives for this? my requirement is the data goes to 50 >> >> > > > different >> >> > > > tables and when display report to user we have get data from all >> >> > > > these >> >> > > > table , now we are looping through all the tables and inserting >> >> > > > in >> >> > > > temp >> >> > > > table and displaying to user , but we found this process is >> >> > > > taking >> >> > > > enormous time delay in showing up the report (3 to 5 mins),we >> >> > > > have >> >> > > > to >> >> > > > greatly reduce this time any suggestions? >> >> > > >> >> > > I'm sorry, but I don't enough information to answer any question. >> >> > > What >> >> > > sort of data do you want to write into this "other normalized >> >> > > table"? >> >> > > Could you give a concrete example? >> >> > > >> >> > > >> >> > > -- >> >> > > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >> >> > > >> >> > > Books Online for SQL Server 2005 at >> >> > > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >> >> > > Books Online for SQL Server 2000 at >> >> > > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >> >> > > >> >> >> The unfotunate thing is we are using some third part tool which collects the
info and stroes in these table they designed such a way that they create different tables for different workflows (eventhough all the information is same in these workflows except the name) so we are left out with very few options, how about creating a regular view instead of the indexed one (all the tables are properly indexed), will it help? Show quote "Brian Selzer" wrote: > Sorry, you can't create an indexed view if you use UNION or UNION ALL. > > Why do you have so many tables with the same structure? Why not switch > things around so that you have a single underlying table and a bunch of > views? You could name the views table1, table2, etc., assuming those are > the names of the original tables. These views could possibly be updatable, > too. Read up on them. > > "vsr" <v**@discussions.microsoft.com> wrote in message > news:E4D195FF-D5F2-40D3-B8CD-14D697F0ACFA@microsoft.com... > > can we use UNION in views? i read VIEW definition can not contain UNION. > > > > "Brian Selzer" wrote: > > > >> You might want to try creating a materialized view. You accomplish this > >> by > >> creating a view WITH SCHEMABINDING and then creating a UNIQUE CLUSTERED > >> INDEX on the view. There are additional requirements: SET options that > >> must > >> be assigned correctly when creating the view and also when manipulating > >> the > >> underlying tables. Look up CREATE VIEW and CREATE INDEX in BOL. > >> > >> This solution has advantages in that the view reflects the contents of > >> the > >> underlying tables without the query performance penalty, and you don't > >> have > >> to maintain a bunch of triggers. The disadvantages are similar to the > >> disadvantages of having a separate table that must be maintained with > >> triggers--that is, increased probability of deadlocks and reduced insert > >> and > >> update performance. Another disadvantage is that the SET options must be > >> assigned correctly for operations that manipulate the underlying tables, > >> which may require modification of the procedures or client code that > >> execute > >> the updates. > >> > >> > >> "vsr" <v**@discussions.microsoft.com> wrote in message > >> news:55082E0A-063F-4774-91FD-B7C10D90E2A2@microsoft.com... > >> > how can we replace triggers with stored procedures? we cant change the > >> > existing structure of the schema and that is the big limitation, and at > >> > the > >> > same time we need to bring up the reporting performance. > >> > > >> > "Abhishek Pandey" wrote: > >> > > >> >> Hi, > >> >> Well just wondering if it is possible to do the same thing with say > >> >> sproc > >> >> or > >> >> something ? maybe you might wanna try it out and see if the > >> >> performance > >> >> is > >> >> increased or so. (and remember to place transaction properly) > >> >> > >> >> Also a trigger would act like one big transaction holding locks and > >> >> stuff. > >> >> you might wanna evaluate your scenario and check if you have to use > >> >> trigger ? > >> >> > >> >> abhishek > >> >> > >> >> > >> >> "vsr" wrote: > >> >> > >> >> > its just putting the data from all the tables to one table...it goes > >> >> > like > >> >> > this... > >> >> > > >> >> > Table1 Columns Name , Addess1,Address2,City,Stata,Zip > >> >> > Table2 Columns Name , Addess1,Address2,City,Stata,Zip > >> >> > Table3 Columns Name , Addess1,Address2,City,Stata,Zip > >> >> > Table4 Columns Name , Addess1,Address2,City,Stata,Zip > >> >> > > >> >> > the normalized table columns are similar to above...Name , > >> >> > Addess1,Address2,City,Stata,Zip > >> >> > > >> >> > whenever any insert , update, delete happens on any of the above > >> >> > tables > >> >> > i > >> >> > need to insert / update/delete the data in normalized table , > >> >> > whenever > >> >> > user > >> >> > requests report i can directly query this normalized table by having > >> >> > simple > >> >> > join with one another table to display (instead of looping through > >> >> > all > >> >> > the > >> >> > tables) > >> >> > > >> >> > if needed more information i can send code snippets with table > >> >> > structure. > >> >> > > >> >> > "Erland Sommarskog" wrote: > >> >> > > >> >> > > vsr (v**@discussions.microsoft.com) writes: > >> >> > > > I have around 50 tables for which i wanted to write simple > >> >> > > > triggers(trigger to insert data in some other normalized table) > >> >> > > > as > >> >> > > > we > >> >> > > > dont have database level triggers in SQL Server i am forced to > >> >> > > > do > >> >> > > > table > >> >> > > > level for all these 50 tables.there are around 500 users will be > >> >> > > > accessing the table on the rate of 50 times per day, please > >> >> > > > suggest > >> >> > > > is > >> >> > > > it a good idea to have triggers in this scenario?or any other > >> >> > > > alternatives for this? my requirement is the data goes to 50 > >> >> > > > different > >> >> > > > tables and when display report to user we have get data from all > >> >> > > > these > >> >> > > > table , now we are looping through all the tables and inserting > >> >> > > > in > >> >> > > > temp > >> >> > > > table and displaying to user , but we found this process is > >> >> > > > taking > >> >> > > > enormous time delay in showing up the report (3 to 5 mins),we > >> >> > > > have > >> >> > > > to > >> >> > > > greatly reduce this time any suggestions? > >> >> > > > >> >> > > I'm sorry, but I don't enough information to answer any question. > >> >> > > What > >> >> > > sort of data do you want to write into this "other normalized > >> >> > > table"? > >> >> > > Could you give a concrete example? > >> >> > > > >> >> > > > >> >> > > -- > >> >> > > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > >> >> > > > >> >> > > Books Online for SQL Server 2005 at > >> >> > > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > >> >> > > Books Online for SQL Server 2000 at > >> >> > > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > >> >> > > > >> > >> > >> > > > The problem I see is that the view definition will need to be updated every
time a new table is created. Even with triggers and an extra table, you'd have to create triggers at the same time that the new tables are created. It's too bad you can't change the code from the third-party tool so that it creates a view instead of a table. In my opinion, that would be the best option. The only other option I can think of is dynamic SQL, and that comes with its own set of problems. With SQL Server 2005, you might be able to use DDL triggers, though I'm not yet expert on it or them. Show quote "vsr" <v**@discussions.microsoft.com> wrote in message news:2A8074E9-C998-4BF9-93AC-D7CC8CA69F3A@microsoft.com... > The unfotunate thing is we are using some third part tool which collects > the > info and stroes in these table they designed such a way that they create > different tables for different workflows (eventhough all the information > is > same in these workflows except the name) so we are left out with very few > options, how about creating a regular view instead of the indexed one (all > the tables are properly indexed), will it help? > > "Brian Selzer" wrote: > >> Sorry, you can't create an indexed view if you use UNION or UNION ALL. >> >> Why do you have so many tables with the same structure? Why not switch >> things around so that you have a single underlying table and a bunch of >> views? You could name the views table1, table2, etc., assuming those are >> the names of the original tables. These views could possibly be >> updatable, >> too. Read up on them. >> >> "vsr" <v**@discussions.microsoft.com> wrote in message >> news:E4D195FF-D5F2-40D3-B8CD-14D697F0ACFA@microsoft.com... >> > can we use UNION in views? i read VIEW definition can not contain >> > UNION. >> > >> > "Brian Selzer" wrote: >> > >> >> You might want to try creating a materialized view. You accomplish >> >> this >> >> by >> >> creating a view WITH SCHEMABINDING and then creating a UNIQUE >> >> CLUSTERED >> >> INDEX on the view. There are additional requirements: SET options >> >> that >> >> must >> >> be assigned correctly when creating the view and also when >> >> manipulating >> >> the >> >> underlying tables. Look up CREATE VIEW and CREATE INDEX in BOL. >> >> >> >> This solution has advantages in that the view reflects the contents of >> >> the >> >> underlying tables without the query performance penalty, and you don't >> >> have >> >> to maintain a bunch of triggers. The disadvantages are similar to the >> >> disadvantages of having a separate table that must be maintained with >> >> triggers--that is, increased probability of deadlocks and reduced >> >> insert >> >> and >> >> update performance. Another disadvantage is that the SET options must >> >> be >> >> assigned correctly for operations that manipulate the underlying >> >> tables, >> >> which may require modification of the procedures or client code that >> >> execute >> >> the updates. >> >> >> >> >> >> "vsr" <v**@discussions.microsoft.com> wrote in message >> >> news:55082E0A-063F-4774-91FD-B7C10D90E2A2@microsoft.com... >> >> > how can we replace triggers with stored procedures? we cant change >> >> > the >> >> > existing structure of the schema and that is the big limitation, and >> >> > at >> >> > the >> >> > same time we need to bring up the reporting performance. >> >> > >> >> > "Abhishek Pandey" wrote: >> >> > >> >> >> Hi, >> >> >> Well just wondering if it is possible to do the same thing with say >> >> >> sproc >> >> >> or >> >> >> something ? maybe you might wanna try it out and see if the >> >> >> performance >> >> >> is >> >> >> increased or so. (and remember to place transaction properly) >> >> >> >> >> >> Also a trigger would act like one big transaction holding locks and >> >> >> stuff. >> >> >> you might wanna evaluate your scenario and check if you have to use >> >> >> trigger ? >> >> >> >> >> >> abhishek >> >> >> >> >> >> >> >> >> "vsr" wrote: >> >> >> >> >> >> > its just putting the data from all the tables to one table...it >> >> >> > goes >> >> >> > like >> >> >> > this... >> >> >> > >> >> >> > Table1 Columns Name , Addess1,Address2,City,Stata,Zip >> >> >> > Table2 Columns Name , Addess1,Address2,City,Stata,Zip >> >> >> > Table3 Columns Name , Addess1,Address2,City,Stata,Zip >> >> >> > Table4 Columns Name , Addess1,Address2,City,Stata,Zip >> >> >> > >> >> >> > the normalized table columns are similar to above...Name , >> >> >> > Addess1,Address2,City,Stata,Zip >> >> >> > >> >> >> > whenever any insert , update, delete happens on any of the above >> >> >> > tables >> >> >> > i >> >> >> > need to insert / update/delete the data in normalized table , >> >> >> > whenever >> >> >> > user >> >> >> > requests report i can directly query this normalized table by >> >> >> > having >> >> >> > simple >> >> >> > join with one another table to display (instead of looping >> >> >> > through >> >> >> > all >> >> >> > the >> >> >> > tables) >> >> >> > >> >> >> > if needed more information i can send code snippets with table >> >> >> > structure. >> >> >> > >> >> >> > "Erland Sommarskog" wrote: >> >> >> > >> >> >> > > vsr (v**@discussions.microsoft.com) writes: >> >> >> > > > I have around 50 tables for which i wanted to write simple >> >> >> > > > triggers(trigger to insert data in some other normalized >> >> >> > > > table) >> >> >> > > > as >> >> >> > > > we >> >> >> > > > dont have database level triggers in SQL Server i am forced >> >> >> > > > to >> >> >> > > > do >> >> >> > > > table >> >> >> > > > level for all these 50 tables.there are around 500 users will >> >> >> > > > be >> >> >> > > > accessing the table on the rate of 50 times per day, please >> >> >> > > > suggest >> >> >> > > > is >> >> >> > > > it a good idea to have triggers in this scenario?or any other >> >> >> > > > alternatives for this? my requirement is the data goes to 50 >> >> >> > > > different >> >> >> > > > tables and when display report to user we have get data from >> >> >> > > > all >> >> >> > > > these >> >> >> > > > table , now we are looping through all the tables and >> >> >> > > > inserting >> >> >> > > > in >> >> >> > > > temp >> >> >> > > > table and displaying to user , but we found this process is >> >> >> > > > taking >> >> >> > > > enormous time delay in showing up the report (3 to 5 mins),we >> >> >> > > > have >> >> >> > > > to >> >> >> > > > greatly reduce this time any suggestions? >> >> >> > > >> >> >> > > I'm sorry, but I don't enough information to answer any >> >> >> > > question. >> >> >> > > What >> >> >> > > sort of data do you want to write into this "other normalized >> >> >> > > table"? >> >> >> > > Could you give a concrete example? >> >> >> > > >> >> >> > > >> >> >> > > -- >> >> >> > > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >> >> >> > > >> >> >> > > Books Online for SQL Server 2005 at >> >> >> > > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >> >> >> > > Books Online for SQL Server 2000 at >> >> >> > > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >> >> >> > > >> >> >> >> >> >> >> >> >> Thank you all in expressing different views to find solution for my problem.
I started with Views and then Proceed with Triggers approach but when tested for performance niether of these approaches are yeilding the better results,now i decided to change the Schema for this application (eventhough its pain and time consuming) where i can replace all the tables with single table. once again thank you all. Show quote "Brian Selzer" wrote: > The problem I see is that the view definition will need to be updated every > time a new table is created. Even with triggers and an extra table, you'd > have to create triggers at the same time that the new tables are created. > It's too bad you can't change the code from the third-party tool so that it > creates a view instead of a table. In my opinion, that would be the best > option. The only other option I can think of is dynamic SQL, and that comes > with its own set of problems. With SQL Server 2005, you might be able to > use DDL triggers, though I'm not yet expert on it or them. > > > "vsr" <v**@discussions.microsoft.com> wrote in message > news:2A8074E9-C998-4BF9-93AC-D7CC8CA69F3A@microsoft.com... > > The unfotunate thing is we are using some third part tool which collects > > the > > info and stroes in these table they designed such a way that they create > > different tables for different workflows (eventhough all the information > > is > > same in these workflows except the name) so we are left out with very few > > options, how about creating a regular view instead of the indexed one (all > > the tables are properly indexed), will it help? > > > > "Brian Selzer" wrote: > > > >> Sorry, you can't create an indexed view if you use UNION or UNION ALL. > >> > >> Why do you have so many tables with the same structure? Why not switch > >> things around so that you have a single underlying table and a bunch of > >> views? You could name the views table1, table2, etc., assuming those are > >> the names of the original tables. These views could possibly be > >> updatable, > >> too. Read up on them. > >> > >> "vsr" <v**@discussions.microsoft.com> wrote in message > >> news:E4D195FF-D5F2-40D3-B8CD-14D697F0ACFA@microsoft.com... > >> > can we use UNION in views? i read VIEW definition can not contain > >> > UNION. > >> > > >> > "Brian Selzer" wrote: > >> > > >> >> You might want to try creating a materialized view. You accomplish > >> >> this > >> >> by > >> >> creating a view WITH SCHEMABINDING and then creating a UNIQUE > >> >> CLUSTERED > >> >> INDEX on the view. There are additional requirements: SET options > >> >> that > >> >> must > >> >> be assigned correctly when creating the view and also when > >> >> manipulating > >> >> the > >> >> underlying tables. Look up CREATE VIEW and CREATE INDEX in BOL. > >> >> > >> >> This solution has advantages in that the view reflects the contents of > >> >> the > >> >> underlying tables without the query performance penalty, and you don't > >> >> have > >> >> to maintain a bunch of triggers. The disadvantages are similar to the > >> >> disadvantages of having a separate table that must be maintained with > >> >> triggers--that is, increased probability of deadlocks and reduced > >> >> insert > >> >> and > >> >> update performance. Another disadvantage is that the SET options must > >> >> be > >> >> assigned correctly for operations that manipulate the underlying > >> >> tables, > >> >> which may require modification of the procedures or client code that > >> >> execute > >> >> the updates. > >> >> > >> >> > >> >> "vsr" <v**@discussions.microsoft.com> wrote in message > >> >> news:55082E0A-063F-4774-91FD-B7C10D90E2A2@microsoft.com... > >> >> > how can we replace triggers with stored procedures? we cant change > >> >> > the > >> >> > existing structure of the schema and that is the big limitation, and > >> >> > at > >> >> > the > >> >> > same time we need to bring up the reporting performance. > >> >> > > >> >> > "Abhishek Pandey" wrote: > >> >> > > >> >> >> Hi, > >> >> >> Well just wondering if it is possible to do the same thing with say > >> >> >> sproc > >> >> >> or > >> >> >> something ? maybe you might wanna try it out and see if the > >> >> >> performance > >> >> >> is > >> >> >> increased or so. (and remember to place transaction properly) > >> >> >> > >> >> >> Also a trigger would act like one big transaction holding locks and > >> >> >> stuff. > >> >> >> you might wanna evaluate your scenario and check if you have to use > >> >> >> trigger ? > >> >> >> > >> >> >> abhishek > >> >> >> > >> >> >> > >> >> >> "vsr" wrote: > >> >> >> > >> >> >> > its just putting the data from all the tables to one table...it > >> >> >> > goes > >> >> >> > like > >> >> >> > this... > >> >> >> > > >> >> >> > Table1 Columns Name , Addess1,Address2,City,Stata,Zip > >> >> >> > Table2 Columns Name , Addess1,Address2,City,Stata,Zip > >> >> >> > Table3 Columns Name , Addess1,Address2,City,Stata,Zip > >> >> >> > Table4 Columns Name , Addess1,Address2,City,Stata,Zip > >> >> >> > > >> >> >> > the normalized table columns are similar to above...Name , > >> >> >> > Addess1,Address2,City,Stata,Zip > >> >> >> > > >> >> >> > whenever any insert , update, delete happens on any of the above > >> >> >> > tables > >> >> >> > i > >> >> >> > need to insert / update/delete the data in normalized table , > >> >> >> > whenever > >> >> >> > user > >> >> >> > requests report i can directly query this normalized table by > >> >> >> > having > >> >> >> > simple > >> >> >> > join with one another table to display (instead of looping > >> >> >> > through > >> >> >> > all > >> >> >> > the > >> >> >> > tables) > >> >> >> > > >> >> >> > if needed more information i can send code snippets with table > >> >> >> > structure. > >> >> >> > > >> >> >> > "Erland Sommarskog" wrote: > >> >> >> > > >> >> >> > > vsr (v**@discussions.microsoft.com) writes: > >> >> >> > > > I have around 50 tables for which i wanted to write simple > >> >> >> > > > triggers(trigger to insert data in some other normalized > >> >> >> > > > table) > >> >> >> > > > as > >> >> >> > > > we > >> >> >> > > > dont have database level triggers in SQL Server i am forced > >> >> >> > > > to > >> >> >> > > > do > >> >> >> > > > table > >> >> >> > > > level for all these 50 tables.there are around 500 users will > >> >> >> > > > be > >> >> >> > > > accessing the table on the rate of 50 times per day, please > >> >> >> > > > suggest > >> >> >> > > > is > >> >> >> > > > it a good idea to have triggers in this scenario?or any other > >> >> >> > > > alternatives for this? my requirement is the data goes to 50 > >> >> >> > > > different > >> >> >> > > > tables and when display report to user we have get data from > >> >> >> > > > all > >> >> >> > > > these > >> >> >> > > > table , now we are looping through all the tables and > >> >> >> > > > inserting > >> >> >> > > > in > >> >> >> > > > temp > >> >> >> > > > table and displaying to user , but we found this process is > >> >> >> > > > taking > >> >> >> > > > enormous time delay in showing up the report (3 to 5 mins),we > >> >> >> > > > have > >> >> >> > > > to > >> >> >> > > > greatly reduce this time any suggestions? > >> >> >> > > > >> >> >> > > I'm sorry, but I don't enough information to answer any > >> >> >> > > question. > >> >> >> > > What > >> >> >> > > sort of data do you want to write into this "other normalized > >> >> >> > > table"? > >> >> >> > > Could you give a concrete example? > >> >> >> > > > >> >> >> > > > >> >> >> > > -- > >> >> >> > > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > >> >> >> > > > >> >> >> > > Books Online for SQL Server 2005 at > >> >> >> > > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > >> >> >> > > Books Online for SQL Server 2000 at > >> >> >> > > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > >> >> >> > > > >> >> > >> >> > >> >> > >> > >> > >> > > > vsr (v**@discussions.microsoft.com) writes:
Show quote > its just putting the data from all the tables to one table...it goes like So someone created many tables, when they should have created one and> this... > > Table1 Columns Name , Addess1,Address2,City,Stata,Zip > Table2 Columns Name , Addess1,Address2,City,Stata,Zip > Table3 Columns Name , Addess1,Address2,City,Stata,Zip > Table4 Columns Name , Addess1,Address2,City,Stata,Zip > > the normalized table columns are similar to above...Name , > Addess1,Address2,City,Stata,Zip > > whenever any insert , update, delete happens on any of the above tables > i need to insert / update/delete the data in normalized table , whenever > user requests report i can directly query this normalized table by > having simple join with one another table to display (instead of looping > through all the tables) only, and you are trying to rectify this? There are a couple of ways to skin this cat: 1) Have trigger on all these tables, that inserts into the normalised table. 2) Don't create a table, but create a view over all these tables. This has the advantage of not affecting the insert operations, but the report could take a somewhat larger toll. 3) Consolidate all these tables, into one single normalized, and replace the current small tables with views, that you add INSTEAD OF triggers, so that the INSERT operations are unaffected. 4) Like 3, but fix the INSERT operations, so there is no need for views. If you are only doing this for reporting, I would favour 2, as this has least impact on the system. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thanks for all different suggestions , creating procedure may not work in
this scenario as Erland said there are two options one is creating triggers on all these tables and insert into normalized table and query against that normalized table and one more another option creating view but again this view goes against all tables and may take time rite? and if i go for creating triggers on all these table i not sure how it affects performance , are there any tools where i can mimic the production environment specially load on these tables to check the performance, i am really in confusion to select the approach, i wanted to go for the triggers approach as tried all other options (like views etc.,) but not able to guaze the perporfmance of the SQL Server. Show quote "Erland Sommarskog" wrote: > vsr (v**@discussions.microsoft.com) writes: > > its just putting the data from all the tables to one table...it goes like > > this... > > > > Table1 Columns Name , Addess1,Address2,City,Stata,Zip > > Table2 Columns Name , Addess1,Address2,City,Stata,Zip > > Table3 Columns Name , Addess1,Address2,City,Stata,Zip > > Table4 Columns Name , Addess1,Address2,City,Stata,Zip > > > > the normalized table columns are similar to above...Name , > > Addess1,Address2,City,Stata,Zip > > > > whenever any insert , update, delete happens on any of the above tables > > i need to insert / update/delete the data in normalized table , whenever > > user requests report i can directly query this normalized table by > > having simple join with one another table to display (instead of looping > > through all the tables) > > So someone created many tables, when they should have created one and > only, and you are trying to rectify this? > > There are a couple of ways to skin this cat: > > 1) Have trigger on all these tables, that inserts into the normalised > table. > 2) Don't create a table, but create a view over all these tables. This > has the advantage of not affecting the insert operations, but the > report could take a somewhat larger toll. > 3) Consolidate all these tables, into one single normalized, and replace > the current small tables with views, that you add INSTEAD OF triggers, > so that the INSERT operations are unaffected. > 4) Like 3, but fix the INSERT operations, so there is no need for views. > > If you are only doing this for reporting, I would favour 2, as this has > least impact on the system. > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > vsr (v**@discussions.microsoft.com) writes:
> Thanks for all different suggestions , creating procedure may not work It will be less work to implement the view, than to write 50 triggers.> in this scenario as Erland said there are two options one is creating > triggers on all these tables and insert into normalized table and query > against that normalized table and one more another option creating view > but again this view goes against all tables and may take time rite? Both ways are basically cut-and-paste exercises. but the view is still only one object to manage. > and if i go for creating triggers on all these table i not sure how it If you are worried about performance, I say that you should stick to> affects performance , are there any tools where i can mimic the > production environment specially load on these tables to check the > performance, the view. One alternative that I did not mention is an indexed view. In this case the view is materialized, so here you get your normalised table. Just like triggers, an indexed view incurs some overhead on updates, but probably less than the triggers. Full support for indexed views requires Enterprise Edition. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx i can start with creating views , but the problem is i may neew to use UNION
to combine the results of all tables , i read that we can not use UNION in VIEW definitons, any suggestions on this? Show quote "Erland Sommarskog" wrote: > vsr (v**@discussions.microsoft.com) writes: > > Thanks for all different suggestions , creating procedure may not work > > in this scenario as Erland said there are two options one is creating > > triggers on all these tables and insert into normalized table and query > > against that normalized table and one more another option creating view > > but again this view goes against all tables and may take time rite? > > It will be less work to implement the view, than to write 50 triggers. > Both ways are basically cut-and-paste exercises. but the view is still > only one object to manage. > > > and if i go for creating triggers on all these table i not sure how it > > affects performance , are there any tools where i can mimic the > > production environment specially load on these tables to check the > > performance, > > If you are worried about performance, I say that you should stick to > the view. > > One alternative that I did not mention is an indexed view. In this > case the view is materialized, so here you get your normalised table. > Just like triggers, an indexed view incurs some overhead on updates, > but probably less than the triggers. > > Full support for indexed views requires Enterprise Edition. > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > vsr (v**@discussions.microsoft.com) writes:
> i can start with creating views , but the problem is i may neew to use You should not learn everything you read in the press. Or on the Internet.> UNION to combine the results of all tables , i read that we can not use > UNION in VIEW definitons, any suggestions on this? You can use UNION in views just fine. But in this case you should use UNION ALL, as UNION implies a DISTINCT operation you don't want (I suppose). -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thank you very much , i will try with this VIEW approach tomorrow and let you
all know the results , please stay tuned with this thread/topic. Show quote "Erland Sommarskog" wrote: > vsr (v**@discussions.microsoft.com) writes: > > i can start with creating views , but the problem is i may neew to use > > UNION to combine the results of all tables , i read that we can not use > > UNION in VIEW definitons, any suggestions on this? > > You should not learn everything you read in the press. Or on the Internet. > > You can use UNION in views just fine. But in this case you should use > UNION ALL, as UNION implies a DISTINCT operation you don't want (I > suppose). > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > |
|||||||||||||||||||||||
Other interesting topics