caching a view?

I've got a web application that does queries against a very large
product database (SQL Server 2000). The data that needs to be returned
is such that the query is HUGE, and very expensive.
Thus far, I've had great success by caching that big query (returning
all rows) in my web application server, and searching against that to
perform product searches. This works great, but now the data is
changing more rapidly and a cache solution is not as ideal as before
So I'd like to go back to querying the database for each product
search. I've moved the big query into a view in SQL Server, but that
of course doesn't do anything for performance. The query is simply too
large and complex to do these product search queries. I was thinking I
could flatten the data out into one big table for product search
queries only, or something like that - kind of effectively caching a
view in SQL Server, and using triggers to update it when relevant data
has changed.
Is this a realistic endeavor? Is there a canned method of doing this,
or will I have to do it through brute force? Any pointers or
suggestions would be much appreciated.
Thanks,
ErikErik - please take a look in BOL for "Indexed Views" or check out this
article for more info:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/indexvw.mspx
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Erik
"voldengen@.gmail.com" wrote:
> I've got a web application that does queries against a very large
> product database (SQL Server 2000). The data that needs to be returned
> is such that the query is HUGE, and very expensive.
I am not sure why this would be the case, a user may only view a finite
amount of information! Is this information paged or are you selecting
information that is subsequently dropped?
> So I'd like to go back to querying the database for each product
> search. I've moved the big query into a view in SQL Server, but that
> of course doesn't do anything for performance. The query is simply too
> large and complex to do these product search queries. I was thinking I
> could flatten the data out into one big table for product search
> queries only, or something like that - kind of effectively caching a
> view in SQL Server, and using triggers to update it when relevant data
> has changed.
>
Have you considered partitioning the data and using a partitioned view?
Posting DDL and sample data may help understand exactly what you are trying
to do.
> Thanks,
> Erik
>
John|||how many rows do you have?
what type of search do you do?
do you do free text searches? have your try to create a server side full
text catalog?
(full text catalogs are great to index text content and support queries like
google search)
do you use storeprocedure to access your database or ad-hoc queries?
how the table is indexed?
you talk about a web application, do you use ASP.NET? do you use SQL
dependent caching in your web application? (you keep in cache the result of
a search until the database is updated, you don't keep to entire table in
memory)
and also, what is your SQL Server hardware?
<voldengen@.gmail.com> wrote in message
news:1167380092.221925.65670@.a3g2000cwd.googlegroups.com...
> I've got a web application that does queries against a very large
> product database (SQL Server 2000). The data that needs to be returned
> is such that the query is HUGE, and very expensive.
> Thus far, I've had great success by caching that big query (returning
> all rows) in my web application server, and searching against that to
> perform product searches. This works great, but now the data is
> changing more rapidly and a cache solution is not as ideal as before
> So I'd like to go back to querying the database for each product
> search. I've moved the big query into a view in SQL Server, but that
> of course doesn't do anything for performance. The query is simply too
> large and complex to do these product search queries. I was thinking I
> could flatten the data out into one big table for product search
> queries only, or something like that - kind of effectively caching a
> view in SQL Server, and using triggers to update it when relevant data
> has changed.
> Is this a realistic endeavor? Is there a canned method of doing this,
> or will I have to do it through brute force? Any pointers or
> suggestions would be much appreciated.
> Thanks,
> Erik
>|||Thanks everyone for those suggestions. I will research indexed and
partitioned views to see how that works.
The number of rows is about 200K.
An overly simplified recordset would look like:
-id (int)
-supplier (varchar)
-category (int)
-subcategory (int)
-description (varchar)
-notes (text)
-quantity (int)
My product queries return all columns, searching against any of them.
Most queries are based on supplier, category, and subcategory.
I've considered setting up a verity index for doing full text searches,
but that only covers a fraction of the queries I'm doing.
SQL hardware is a beefy P4 with 2gigs of ram, win2k3...Dell Poweredge
server.
Thanks again for the suggestions.
-Erik
On Dec 29, 4:39 am, "Jeje" <willg...@.hotmail.com> wrote:
> how many rows do you have?
> what type of search do you do?
> do you do free text searches? have your try to create a server side full
> text catalog?
> (full text catalogs are great to index text content and support queries like
> google search)
> do you use storeprocedure to access your database or ad-hoc queries?
> how the table is indexed?
> you talk about a web application, do you use ASP.NET? do you use SQL
> dependent caching in your web application? (you keep in cache the result of
> a search until the database is updated, you don't keep to entire table in
> memory)
> and also, what is your SQL Server hardware?
> <volden...@.gmail.com> wrote in messagenews:1167380092.221925.65670@.a3g2000cwd.googlegroups.com...
> > I've got a web application that does queries against a very large
> > product database (SQL Server 2000). The data that needs to be returned
> > is such that the query is HUGE, and very expensive.
> > Thus far, I've had great success by caching that big query (returning
> > all rows) in my web application server, and searching against that to
> > perform product searches. This works great, but now the data is
> > changing more rapidly and a cache solution is not as ideal as before
> > So I'd like to go back to querying the database for each product
> > search. I've moved the big query into a view in SQL Server, but that
> > of course doesn't do anything for performance. The query is simply too
> > large and complex to do these product search queries. I was thinking I
> > could flatten the data out into one big table for product search
> > queries only, or something like that - kind of effectively caching a
> > view in SQL Server, and using triggers to update it when relevant data
> > has changed.
> > Is this a realistic endeavor? Is there a canned method of doing this,
> > or will I have to do it through brute force? Any pointers or
> > suggestions would be much appreciated.
> > Thanks,
> > Erik|||voldengen@.gmail.com wrote:
> Thanks everyone for those suggestions. I will research indexed and
> partitioned views to see how that works.
> The number of rows is about 200K.
> An overly simplified recordset would look like:
> -id (int)
> -supplier (varchar)
> -category (int)
> -subcategory (int)
> -description (varchar)
> -notes (text)
> -quantity (int)
> My product queries return all columns, searching against any of them.
> Most queries are based on supplier, category, and subcategory.
> I've considered setting up a verity index for doing full text searches,
> but that only covers a fraction of the queries I'm doing.
> SQL hardware is a beefy P4 with 2gigs of ram, win2k3...Dell Poweredge
> server.
> Thanks again for the suggestions.
>
How are you facilitating the flexible searching? If you're using
something like
WHERE (@.Category IS NULL OR category = @.Category)
You might want to consider using dynamic SQL instead. The above syntax
will prevent your query from using indexes efficiently.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||My query uses a few outer joins, indexed views probably won't work.
I'll check out partitioned views now...
Thanks again,
Erik
On Dec 29, 2:00 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> Erik - please take a look in BOL for "Indexed Views" or check out this
> article for more info:http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/indexv...
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459541F1.8030507@.realsqlguy.com...
> voldengen@.gmail.com wrote:
>> Thanks everyone for those suggestions. I will research indexed and
>> partitioned views to see how that works.
>> The number of rows is about 200K.
>> An overly simplified recordset would look like:
>> -id (int)
>> -supplier (varchar)
>> -category (int)
>> -subcategory (int)
>> -description (varchar)
>> -notes (text)
>> -quantity (int)
>> My product queries return all columns, searching against any of them.
>> Most queries are based on supplier, category, and subcategory.
>> I've considered setting up a verity index for doing full text searches,
>> but that only covers a fraction of the queries I'm doing.
>> SQL hardware is a beefy P4 with 2gigs of ram, win2k3...Dell Poweredge
>> server.
>> Thanks again for the suggestions.
> How are you facilitating the flexible searching? If you're using
> something like
> WHERE (@.Category IS NULL OR category = @.Category)
> You might want to consider using dynamic SQL instead. The above syntax
> will prevent your query from using indexes efficiently.
>
Indeed. The suggestions you have received assume that your query really is
too expensive to run every time. Before going to indexed views, caching or
any other workaround, you should be sure that you can't optimize your query
so it is cheap enough to run every time.
If you post the query and DDL here someone might suggest an efficient
approach.
David|||<voldengen@.gmail.com> wrote in message
news:1167413897.388996.88880@.a3g2000cwd.googlegroups.com...
> My query uses a few outer joins, indexed views probably won't work.
> I'll check out partitioned views now...
> Thanks again,
> Erik
> On Dec 29, 2:00 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
>> Erik - please take a look in BOL for "Indexed Views" or check out this
>> article for more
>> info:http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/indexv...
>> Cheers,
>> Paul Ibison SQL Server MVP,www.replicationanswers.com.
>
I can't imagine how partitioned views would help.
David|||On 29 Dec 2006 00:14:52 -0800, voldengen@.gmail.com wrote:
>I've got a web application that does queries against a very large
>product database (SQL Server 2000). The data that needs to be returned
>is such that the query is HUGE, and very expensive.
>Thus far, I've had great success by caching that big query (returning
>all rows) in my web application server, and searching against that to
>perform product searches. This works great, but now the data is
>changing more rapidly and a cache solution is not as ideal as before
>So I'd like to go back to querying the database for each product
>search. I've moved the big query into a view in SQL Server, but that
>of course doesn't do anything for performance. The query is simply too
>large and complex to do these product search queries. I was thinking I
>could flatten the data out into one big table for product search
>queries only, or something like that - kind of effectively caching a
>view in SQL Server, and using triggers to update it when relevant data
>has changed.
>Is this a realistic endeavor? Is there a canned method of doing this,
>or will I have to do it through brute force? Any pointers or
>suggestions would be much appreciated.
>Thanks,
>Erik
Hi Erik,
You have already gotten a few good answers, including the very sound
advise to try tuning "that big query" first.
Failing that, indexed views would be my second choice, but I understand
that the use of outer joins precludes that option. That leaves you with
one other option - to build your own indexed view: create a table to
store the results from the big query, AND create triggers on all tables
used in the big query to change the results in the "cached view" as the
base data is changed. (Note that this is essentially what happpens under
the covers when you create an indexed view - but because of the outer
join, deducing the correct updates to the "cached view" from the updates
to the base tables becomes too hard for SQL Server).
--
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:nkfdp2ll6cjckrl0h997dvttjt1ov5ppe2@.4ax.com...
> On 29 Dec 2006 00:14:52 -0800, voldengen@.gmail.com wrote:
>>I've got a web application that does queries against a very large
>>product database (SQL Server 2000). The data that needs to be returned
>>is such that the query is HUGE, and very expensive.
>>Thus far, I've had great success by caching that big query (returning
>>all rows) in my web application server, and searching against that to
>>perform product searches. This works great, but now the data is
>>changing more rapidly and a cache solution is not as ideal as before
>>So I'd like to go back to querying the database for each product
>>search. I've moved the big query into a view in SQL Server, but that
>>of course doesn't do anything for performance. The query is simply too
>>large and complex to do these product search queries. I was thinking I
>>could flatten the data out into one big table for product search
>>queries only, or something like that - kind of effectively caching a
>>view in SQL Server, and using triggers to update it when relevant data
>>has changed.
>>Is this a realistic endeavor? Is there a canned method of doing this,
>>or will I have to do it through brute force? Any pointers or
>>suggestions would be much appreciated.
>>Thanks,
>>Erik
> Hi Erik,
> You have already gotten a few good answers, including the very sound
> advise to try tuning "that big query" first.
> Failing that, indexed views would be my second choice, but I understand
> that the use of outer joins precludes that option.
> ...
Not entirely. If the query has, say, four inner joins and three outer joins
you can create an indexed view on the inner joins and add the outer joins in
a non-indexed view.
David|||Great, thanks very much for the suggestions. I like these last two
quite a bit - flatten the nasty stuff with a table, updated by
triggers, and join to an indexed view. Wonderful!