Website with lots of lookup queries
in [ASP .NET]
|
From: Temoc on 12 May 2010 14:10 I am developing a search web interface to query our database. My concern is that the project will have around 100 lookup queries whose values will have to be loaded into Multi-select checkbox components. I would appreciate any ideas on techniques that would reduce page load time as well as the re-opening queries and reloading the values every time the page gets refresh or reloaded. One key point is that the lookup values may change on average around once a month. I am working with SQL Server 2008, VS2010 and DevExpress.
From: Mr. Arnold on 12 May 2010 15:56 Temoc wrote: > I am developing a search web interface to query our database. My concern is > that the project will have around 100 lookup queries whose values will have > to be loaded into Multi-select checkbox components. You can use WCF Data Service using the ADO.NET Entity Framework and Ling to query the database. > > I would appreciate any ideas on techniques that would reduce page load > time as well as the re-opening queries and reloading the values every time > the page gets refresh or reloaded. By using Linq complied queries against the database, the complied queries stay in static memory, and they don't have to be complied again, making querying faster. Also you can use ADO.Entity Framework compiled views to help in Linq query speed. You may also want to look at WCF RIA with Silverlight solution using ADO.NET Entity Framework and Linq, which can be implemented and used by ASP.NET solutions. About the only thing you can do to not go back to the database on page reload is hold the data in cache, or you use a session variable to hold the data in memory. > > One key point is that the lookup values may change on average around once > a month. I am working with SQL Server 2008, VS2010 and DevExpress. > What are you talking about? The data in the database changes?
From: Temoc on 13 May 2010 14:58 Thanks for your response. I hope you won't mind a few more questions. >Also you can use ADO.Entity Framework compiled views to help in Linq query speed. Are you talking about creating actual views on the database? The Lookups come from querying a single table. Will views be beneficial in such a case? Technique 1 > By using Linq complied queries against the database, the complied queries stay in >static memory. I am not too familiar with Linq. I will have to read about it. By chance do you have any links to videos or documentations on this topic? Technique 2 >you use a session variable to hold the data in memory Are you talking about creating tables and loading them in and out session variable? This sounds like a good idea. Would it be possible and desirable to create static tables on a Lookuptables.cs unit and load the data into those tables? Is the implementation of Technique 2 a substitution or an additional to Technique 1? Technique 3 >What are you talking about? The data in the database changes? As an example: Our orders table is constantly changing, on the other hand a lookup query would be like the list of service types that we offer; which would seldom or never change. What I was considering here was the possibility to load and hardcode these lookup values at the time the website is publish or having some type of encrypted re-buildable ini file with such values instead of loading them every time a user logs in. Thanks
From: Mr. Arnold on 13 May 2010 16:59 Temoc wrote: > Thanks for your response. I hope you won't mind a few more questions. > >> Also you can use ADO.Entity Framework compiled views to help in Linq query speed. > Are you talking about creating actual views on the database? The > Lookups come from querying a single table. Will views be beneficial in such a > case? It won't help you if you are using one table, but if you have several tables that were on the designer pane, then it won't waste time in creating the views each time the database is opened, because they are pre-compiled views when running the application. http://msdn.microsoft.com/en-us/library/bb896240.aspx > > Technique 1 >> By using Linq complied queries against the database, the complied queries stay in >static memory. > I am not too familiar with Linq. I will have to read about it. By > chance do you have any links to videos or documentations on this topic? It's about Linq-2-objects, because the ADO.NET Entity Framework returns a database table row as a single object or rows as a collection of objects Linq. What is Language Integrated Query? LINQ is a Microsoft .NET Framework component that adds native data querying capabilities to .NET languages. Microsoft LINQ defines a set of query operators that can be used to query, project and filter data in arrays, enumerable classes, XML, relational database, and third party data sources. While it allows any data source to be queried, it requires that the data be encapsulated as objects. So, if the data source does not natively store data as objects, the data must be mapped to the object domain. Queries written using the query operators are executed either by the LINQ query processing engine or, via an extension mechanism, handed over to LINQ providers which either implement a separate query processing engine or translate to a different format to be executed on a separate data store (such as on a database server as SQL queries). The results of a query are returned as a collection of in-memory objects that can be enumerated using a standard iterator function such as C#'s foreach. Many of the concepts that LINQ has introduced were originally tested in Microsoft's Cω research project. LINQ was released as a part of .NET Framework 3.5 on November 19, 2007. <http://en.wikipedia.org/wiki/Language_Integrated_Query> <http://www.google.com/#hl=en&q=linq+in+action+ebook&aq=0&aqi=g1&aql=&oq=linq+in+action+e&gs_rfai=&fp=f611870ea85734e2> > > Technique 2 >> you use a session variable to hold the data in memory > Are you talking about creating tables and loading them in and out > session variable? This sounds like a good idea. > Would it be possible and desirable to create static tables on a > Lookuptables.cs unit and load the data into those tables? I would just use Linq, ADO.Net Entity Framework and load the data as an object or collection of objects. The object or objects if they have static data in them would be loaded into 'cache' so that all users running the application can query the cache of objects by you using Linq in code. What is ADO.NET Entities framework? ADO.NET Entity Framework is an object-relational mapping (ORM) framework for the .NET Framework. This framework is Microsoft's first ORM offering for the .NET Framework. While Microsoft provided objects to manage the Object-relational impedance mismatch (such as a DataSet). ADO.NET Entity Framework is included with .NET Framework 3.5 Service Pack 1 and Visual Studio 2008 Service Pack 1, released on 11 Aug 2008. It also includes the capability of executing LINQ against ADO.NET Entity Framework entities <http://en.wikipedia.org/wiki/ADO.NET_Entity_Framework> <http://www.codeguru.com/csharp/csharp/cs_linq/article.php/c15489> <http://dotnetslackers.com/ASP_NET/re-167160_Free_Entity_Framework_eBook.aspx> > > Is the implementation of Technique 2 a substitution or an additional to > Technique 1? > > Technique 3 >> What are you talking about? The data in the database changes? > As an example: Our orders table is constantly changing, on the other > hand a lookup query would be like the list of service types that we offer; > which would seldom or never change. What I was considering here was the > possibility to load and hardcode these lookup values at the time the website > is publish or having some type of encrypted re-buildable ini file with such > values instead of loading them every time a user logs in. Just use Linq, Entity Framework, create a SQL table with the data in it, and load the collection of objects from the table into a List<T> in cache/memory and load the control from Web server cache/memory. http://en.wikipedia.org/wiki/Cache > > Thanks
From: Mr. Arnold on 13 May 2010 17:01
Temoc wrote: > Thanks for your response. I hope you won't mind a few more questions. > >> Also you can use ADO.Entity Framework compiled views to help in Linq query speed. > Are you talking about creating actual views on the database? The > Lookups come from querying a single table. Will views be beneficial in such a > case? It won't help you if you are using one table, but if you have several tables that were on the designer pane, then it won't waste time in creating the views each time the database is opened, because they are pre-compiled views when running the application. http://msdn.microsoft.com/en-us/library/bb896240.aspx > > Technique 1 >> By using Linq complied queries against the database, the complied queries stay in >static memory. > I am not too familiar with Linq. I will have to read about it. By > chance do you have any links to videos or documentations on this topic? It's about Linq-2-objects, because the ADO.NET Entity Framework returns a database table row as a single object or rows as a collection of objects Linq. What is Language Integrated Query? LINQ is a Microsoft .NET Framework component that adds native data querying capabilities to .NET languages. Microsoft LINQ defines a set of query operators that can be used to query, project and filter data in arrays, enumerable classes, XML, relational database, and third party data sources. While it allows any data source to be queried, it requires that the data be encapsulated as objects. So, if the data source does not natively store data as objects, the data must be mapped to the object domain. Queries written using the query operators are executed either by the LINQ query processing engine or, via an extension mechanism, handed over to LINQ providers which either implement a separate query processing engine or translate to a different format to be executed on a separate data store (such as on a database server as SQL queries). The results of a query are returned as a collection of in-memory objects that can be enumerated using a standard iterator function such as C#'s foreach. Many of the concepts that LINQ has introduced were originally tested in Microsoft's Cω research project. LINQ was released as a part of .NET Framework 3.5 on November 19, 2007. <http://en.wikipedia.org/wiki/Language_Integrated_Query> <http://www.google.com/#hl=en&q=linq+in+action+ebook&aq=0&aqi=g1&aql=&oq=linq+in+action+e&gs_rfai=&fp=f611870ea85734e2> > > Technique 2 >> you use a session variable to hold the data in memory > Are you talking about creating tables and loading them in and out > session variable? This sounds like a good idea. > Would it be possible and desirable to create static tables on a > Lookuptables.cs unit and load the data into those tables? I would just use Linq, ADO.Net Entity Framework and load the data as an object or collection of objects. The object or objects if they have static data in them would be loaded into 'cache' so that all users running the application can query the cache of objects by you using Linq in code. What is ADO.NET Entities framework? ADO.NET Entity Framework is an object-relational mapping (ORM) framework for the .NET Framework. This framework is Microsoft's first ORM offering for the .NET Framework. While Microsoft provided objects to manage the Object-relational impedance mismatch (such as a DataSet). ADO.NET Entity Framework is included with .NET Framework 3.5 Service Pack 1 and Visual Studio 2008 Service Pack 1, released on 11 Aug 2008. It also includes the capability of executing LINQ against ADO.NET Entity Framework entities <http://en.wikipedia.org/wiki/ADO.NET_Entity_Framework> <http://www.codeguru.com/csharp/csharp/cs_linq/article.php/c15489> <http://dotnetslackers.com/ASP_NET/re-167160_Free_Entity_Framework_eBook.aspx> > > Is the implementation of Technique 2 a substitution or an additional to > Technique 1? > > Technique 3 >> What are you talking about? The data in the database changes? > As an example: Our orders table is constantly changing, on the other > hand a lookup query would be like the list of service types that we offer; > which would seldom or never change. What I was considering here was the > possibility to load and hardcode these lookup values at the time the website > is publish or having some type of encrypted re-buildable ini file with such > values instead of loading them every time a user logs in. Just use Linq, Entity Framework, create a SQL table with the data in it, and load the collection of objects from the table into a List<T> in cache/memory and load the control from Web server cache/memory. http://en.wikipedia.org/wiki/Cache > > Thanks |