Website with lots of lookup queries

[Temoc]

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.

 

[Mr. Arnold]

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?

 

[Temoc]

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

 

[Mr. Arnold]

Thanks for your response. I hope you won’t mind a few more questions.

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
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>

Technique 2
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>

Is the implementation of Technique 2 a substitution or an additional to
Technique 1?

Technique 3
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

 

[Mr. Arnold]

Thanks for your response. I hope you won’t mind a few more questions.

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
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>

Technique 2
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>

Is the implementation of Technique 2 a substitution or an additional to
Technique 1?

Technique 3
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

 

[Temoc]

You have been very helpful. It will take me a couple days to digest all this
information. This is the starting point that I was looking for.
Thank you

 

[3P]

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.

Load to cache. Use PageAcyncTask.