Calculate week from date

Hi,

I want to convert a date to a weeknumber in my view.
How is this possible with SQL?

select datepart(wk,getdate())

hth|||Thanx!

Works fine for me!

Calculate Difference

Sorry, I know that I'm very troublesome
I will like to create a view that stores the difference between 2 Integer
columns from 2 different table. Is there a function to perform this?
And I wrote a SQL statement as below,
Select CustID, ReqNo, 'Total'=Sum(AcceptedQty)
From Delivery
Group By CustID, ReqNo
Will this return me the total accepted quantity of each combination of
custid and reqno? It's working fine currently, but I just need a
confirmation~~
Thank you.Hi
You post is not clear what you are trying to achieve check out
http://www.aspfaq.com/etiquett_e.asp?id=5006 on how to post.
To use two tables you will need to use a JOIN. You can find out more about
JOINs in books online for example the "Join Fundamentals" topic or at
http://msdn.microsoft.com/library/d...r />
_610z.asp
Select O.CustID, O.ReqNo, Sum(O.OrderedQty) AS [TotalOrdered],
ISNULL(Sum(D.AcceptedQty),0) AS [TotalDelivered], Sum(O.OrderedQty) -
ISNULL(SUM(D.AcceptedQty),0) AS [Outstanding]
From Orders O
LEFT JOIN Delivery D O.CustId = D.CustID AND O.ReqNo = D.ReqNo
Group By O.CustID, O.ReqNo
As Orders may not have been delivered the the Delivered tables is joined
using using an outer join. The use of ISNULL makes the sum 0 if there are no
entries in delivery.
John
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:75378B2B-8975-47A0-B069-3A84D8819699@.microsoft.com...
> Sorry, I know that I'm very troublesome
> I will like to create a view that stores the difference between 2 Integer
> columns from 2 different table. Is there a function to perform this?
> And I wrote a SQL statement as below,
> Select CustID, ReqNo, 'Total'=Sum(AcceptedQty)
> From Delivery
> Group By CustID, ReqNo
> Will this return me the total accepted quantity of each combination of
> custid and reqno? It's working fine currently, but I just need a
> confirmation~~
> Thank you.|||Well a View will not "store" anything...
If you simply want to calculate and display the difference between two
columns in two different tables, use a join, but you have to tell SQL which
row(s) in each table to get the column valuesfrom, and, if there are ore tha
n
one row in each table, which one on tableA should be used to subtract from
which one in Table B. i.e., how to "Join" the two tables...
Assuming you know how to Join the 2 tables
Just refer to the two columns with their TableNames...
Select TableA.ColName - TableB.ColumnName
From TableA Join TableB
In TableB.ForeignKeyColumn = TableA.PrimaryKeyColumn
"wrytat" wrote:

> Sorry, I know that I'm very troublesome
> I will like to create a view that stores the difference between 2 Integer
> columns from 2 different table. Is there a function to perform this?
> And I wrote a SQL statement as below,
> Select CustID, ReqNo, 'Total'=Sum(AcceptedQty)
> From Delivery
> Group By CustID, ReqNo
> Will this return me the total accepted quantity of each combination of
> custid and reqno? It's working fine currently, but I just need a
> confirmation~~
> Thank you.|||Thank you for your directions and Sorry... I'll try to be clearer...
Ok, let's say the total quantity of product we delivered (Delivered
Quantity) to our customer is less than or more than the quantity the custome
r
requested (Requested Quantity). If the Total Delivered Quantity (as goods ca
n
be delivered in more than 1 trip) is more than Requested Quantity minus the
Allowance (i.e. the quantity of shortage or excess that is still acceptable
to the customer), the order is considered completed.
I have 3 tables that stores these three information:
The Product table, that stores details of each product, contains the
Allowance of the product (as Allowance). The Primary Keys are CustID and
Comp# (component number).
The DelReq table, that stores the details of each delivery order requested
by customers, keeps the Requested Quantity (as ReqQty). The Primary Keys are
CustID and ReqNo (Request ID).
The DelDetail table, that stores the details of the outcome of each delivery
trip, keeps the Delivered Quantity (as AcceptedQty). The Primary Keys are
CustID, ReqNo, SC# (Sales Confirmation Number), and Item# (different number
for each delivery).
So now, I want to do a processing to find out which delivery requests have
already been completed and then push them into an archive. Firstly, I need t
o
find out the total Delivered Quantity of each request. And I also need to
find out the difference between the Requested Quantity and the Allowance, so
that finally I can compare the total Delivered Quantity with the difference.
Thus, I thought of this:
Create View VW_TotalAccepted
AS
SELECT DelDetail.CustID, DelDetail.ReqNo, Sum(DelDetail.AcceptedQty) AS
[Total], (DelReq.ReqQty-Product.Allowance) AS [AcceptableQty]
FROM DelDetail, DelReq, Product
GROUP BY DelDetail.CustID, DelDetail.ReqNo
WHERE DelDetail.CustID = DelReq.CustID AND
DelDetail.ReqNo = DelReq.ReqNo AND
Product.CustID = DelReq.CustID AND
Product.Comp# = DelReq.Comp#
I know this is a bit complicated and I'm not very good in explaining the
problem. Sorry.
I'm not sure whether this is right, and will there be any problem with the
efficiency and performance of the server. Will the above help me achieve wha
t
I want?
And is using JOIN better than specifying join condition in the WHERE clause?
If so, I have to join 3 tables...
Create View VW_TotalAccepted
AS
SELECT DelDetail.CustID, DelDetail.ReqNo, Sum(DelDetail.AcceptedQty) AS
[Total], (DelReq.ReqQty-Product.Allowance) AS [AcceptableQty]
FROM DelReq JOIN DelDetail ON
(DelDetail.CustID = DelReq.CustID
AND DelDetail.ReqNo = DelReq.ReqNo),
DelReq JOIN Product ON
(Product.CustID = DelReq.CustID
AND Product.Comp# = DelReq.Comp#)
GROUP BY DelDetail.CustID, DelDetail.ReqNo
Is this correct? Looks strange...
>
>

Calculate age?

I have a DOB field in my sql table, does anyone know how to display that field as an actual age in a view or a SP?select CONVERT(INTEGER, getDATE() - DOB)/365 from table|||that's only approximate, and will probably be wrong for some people on some days

;)

here's an age calculation that's correct to the day:select year(getdate())
- year(DOB)
- case when month(getdate())
> month(DOB)
then 0
else
case when month(getdate())
< month(DOB)
then 1
else
case when day(getdate())
< day(DOB)
then 1
else 0
end
end
end as age
from ...|||SELECT DATEDIFF(yy, DOB, GETDATE())

my 2 cents...|||Frettmaestro, what's your birthday?

unless it is within the first four weeks of january, it has not yet happened this year

so do your datediff calculation on your own birthday, and see what answer you get

is that your correct age?

;)|||Hmm, busted! So much for quick solutions, hehe...I would have to go with the same solution as r123456... ->

SELECT CONVERT(int, DATEDIFF(dd, '1976-03-06 13:00:000', GETDATE()))/365

But how is this only approximate...?|||it is only approximate because it totally messes up around the last day of february and/or first day of march, and it gets worse the older the person is|||This means that a 80-year old person would risk to wait 20 days before his age was updated from 79 to 80. If this is the case then I guess I at least could live with that...and I bet the 80-year old guy would be thrilled, hehehe :) Just kidding offcourse...accuracy is *very* important in all aspects of what you do.|||yeah, accuracy, what i said in my first post :cool:

here's how it works: subtract the years, then adjust it by 1 based on whether... oh, never mind, it was real easy to write, it should be real easy to figure out (hey, i should make that my sig, eh)

Calc Measure Immediate Viewing

This is probably obvious, but....

In MSAS 2000, I could immediately view the new calc measure in Analysis Manager without rebuilding the cube. Is this still possible in SSAS 2005? Can you list the steps?

Yes you can using the new MDX debugger.

When in BIDS, in the caluclaiton tab, create your calculated member and then select the Debug menu and start debuggging.

The cube browser will appear just beneath the calculaiton script.

You can not only step by step through your various caluclaiton, but you can also change them and see the impact immediately.

It is a full fledge VS debugger for MDX...

|||

Thierry,

Thanks for the answer. It was very helpful.

I've read that you can develop in Online Mode in the BI Studio to get the same affect as in Analysis Manager (AS 2000). What is the difference between using the debugger in project mode and seeing the change immediately online mode? Are there pros and cons to using online mode versus project mode?

Justin

Caching problem?

If I create a view and then a mining structure run it everything works fine BUT if I then alter the view (e.g change a filter setting in a named query) or the actual contents of the database, the OLD contents persist.

Using the view refresh says there are no changes (which is correct as I don't change any column names etc.). If I remove Keep Training Cases, this fixes it, but for Time Series, it then gives an error, so you have to go back and re-select it... is there a direct option to clear caches?

The dependency settings aren't sensitive to non-destructive changes in the DSV. You can simply do a ProcessFull on the mining structure to reprocess from the data.

There is an option ProcessClearStructureOnly (something like that), that clears the training cases from the cache.

TimeSeries requires the cache or the viewer will not work - you can likely drop the cache using the process method above, but you will get errors when trying to view the time series models (at least the chart part)

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/pr...in/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:[vbcol=seagreen]
> 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 li
ke
> 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 o
f
> 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@.a3g2000
cwd.googlegroups.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.
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/pr...
tain/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:
> 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:
>
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

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!

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,
Erik
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/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.googlegrou ps.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:[vbcol=seagreen]
> 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.goo glegroups.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.
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:
> 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.googlegrou ps.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:
>
I can't imagine how partitioned views would help.
David
|||"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:
>
> 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

Cached reports REQUIRE that SQL Server used SQL Server authentication (or mixed)?

I am trying to setup cached reports so that one of my larger reports doesn't have to be re-run every time someone wants to view it (the data source only updates every 24 hours).

Anyway I made a new data source, set the report to use that, and in that data source I said to use "SQLexampleUserName" as the stored credentials.

Now when I go to run the report it says: Login failed for user 'username'. The user is not associated with a trusted SQL Server connection.

This is referenced in the MSKB here:

http://support.microsoft.com/default.aspx/kb/555332

Which makes sense, but now my question is: If I want to used a cached report do I HAVE to allow SQL Server Credentials?

I was using Windows Authentication only up to this point.

Yes, you can still use windows credentials for cached reports. To use windows user credentials, in the report properties, on the data sources tab, select 'credentials stored securely in the report server' then enter yorur windows creds, check the use as windows credentials checkbox, then click apply.

Hope that helps,

-Lukasz

|||

I'm afraid I'm still having trouble.

I followed your steps from the report manage web based interface and now I have:

An error has occurred during report processing.
Cannot create a connection to data source 'My-Server'.
Login failed for user 'DOMAIN\testuser'.

To make the changes I went to the Data Sources tab as you said, then selecteed "A Customer Data Source" and the Microsoft SQL Server. My Connection string is:

Data Source= My-Server; Initial Catalog=MyDatabase

Then "credentials stored securely in the report server" and "use as windows credentials when connecting to the data source"

I tried the user name as DOMAIN\user and just user.

Now it may just be that I don't have things configured properly somehow, but the report runs just fine if I use Windows Authentication and don't try to cache the report.

Can I make these adjustments from inside the design studio instead of the web based report manager?

|||

That config seems like it should work, it matches the one I'm used. Couple things to check:

1- Did you add the correct permissions for the test account to actually access the database?

2- Create a local user account, make it an SysAdmin (just to troubleshoot!!!!) and try it that way. I don't use a AD account because that account doesn't need to access any network resources, and have more control over the password reset time, etc. Creating a local account to troubleshoot should also rule out any strange AD issues you might be having.

Don't check the 'impersonate' box (you didn't mention it, so I'm guessing you haven't checked it)

Otherwise, once it's running, you'll want to harden the system by ensuring the account that's cached has the lowest possible permissions needed to execute the reports.

Geof

|||

Thank you, I will check on that.

As for security, what permissions does an account need to run a basic report?

If I was reporting off TestDatabase on the server I just need to grant the account a login on the server and then read (public) access to TestDatabase, is that correct?

|||

Depends on what security you've added to the basic database, but yes, a login, and then public access as described.

Based on your error message, I think you aren't getting past the login stage. There is no need to grant any access rights to any reportserver system databases.

|||

Thanks.

I agree that it appears my test account is having issues as I tried it with my personal account and it works just fine.

I'll review my security permissions and work it out from there..

But the SQL Server 2005 optimiser is inconsistent

We are experiencing a problem in SQL Server 2005 Standard Edition (on x86 & x64, RTM & SP1 CTP1). The problem is we have a view which does something like "CREATE VIEW myView;SELECT * FROM MyTable WHERE ISNumeric(MyVal)=1" when you do "SELECT * FROM myView" you see a dataset which only contains numeric values.

However it's clear that if you do "SELECT * FROM myView WHERE MyVal>5" that it is evaluating the >5 before the IsNumeric function (I assume as > is less costly than IsNumeric and thus it is more efficient this way). This didn't happen in Sql Server 2000 & 7.0.

My concern here is that how can you trust views if when you put evaluations on them they're working against a different dataset to that which you view if you do SELECT * ?
I am currently working with a workaround which is to simply put TOP in the sub-queries to force the execution order to that which I've defined. However this is nasty as I can't do TOP 100% as it gets optimised out and so instead I have to do TOP 999999999 or similar.

However my biggest concern by far is that even in "SQL Server 2000 (80)" compatibility mode the behaviour is not consistent wtih SS2000.

CREATE TABLE #Problem (idkey int IDENTITY(1,1), numinastr varchar(25))

INSERT INTO #Problem (numinastr) values ('1')
INSERT INTO #Problem (numinastr) values ('10')
INSERT INTO #Problem (numinastr) values ('25')
INSERT INTO #Problem (numinastr) values ('40')
INSERT INTO #Problem (numinastr) values ('>500')
INSERT INTO #Problem (numinastr) values ('600')
INSERT INTO #Problem (numinastr) values ('1000')
INSERT INTO #Problem (numinastr) values ('error!')

-- Note Lack of any non-numeric rows
SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1

-- This Command executes correctly
SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1 AND numinastr>15

--This one however is parsed incorrectly, with >15 being evalutated before ISNumeric
SELECT * from (
SELECT * FROM #Problem WHERE ISNUMERIC(numinastr)=1
) a where numinastr>15

-- Creating a view of SELECT * FROM #Problem WHERE ISNUMERIC(numinastr)=1 and
-- then querying that also gives the same error

DROP TABLE #Problem

I have been told (by an MVP) that you can't assume a specific execution order for queries. Do any DBA's out there really think this acceptable? I consider this a bug. If I put a query in as a sub-query or view, or if I bracket my where statement in such a way I expect it to respect what I've told it!

A view is nothing more than a representation of some SQL. The optimiser will not treat the view as a single entity but rather merge the SQL into the main SQL.

Secondly, SQL does not guarentee order of execution therefore you have to assume the worst. (as you've been told) This is core to how the opimser works.

You could create an indexed view and use the noexpand clause.

|||

This is not a bug. You can hit the problem in SQL Server 2000 also depending on your query plan and/or data. Some new changes to the query optimizer causes more chances of this happening in SQL Server 2005. See the older threads below for more information:

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

Note that even though the ANSI SQL standards talks about various parts of the SELECT statement getting evaluated in a specific order like ON, WHERE, GROUP BY, HAVING, SELECT, ORDER BY most relational database systems optimize the query as a whole for performance reasons. For example, the engine might reorder certain predicates based on internal processing logic or evaluate certain parts of the query using an indexed or materialized view or employ other join strategies. So you should not assume any order in the evaluation of predicates. The only way to guarantee it is to rewrite the predicate conditions in cases like this using a CASE expression or dump intermediate results into a temporary table and then perform the filters which may raise errors depending on the data. Hope this helps.

|||

I appreciate that you can never guarantee the order in which expressions get evaluated but surely these two commands should be optimised to the the same plan....they don't.

-- This Command executes correctly
SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1 AND numinastr>15

--This one however is parsed incorrectly, with >15 being evalutated before ISNumeric
SELECT * from (
SELECT * FROM #Problem WHERE ISNUMERIC(numinastr)=1
) a where numinastr>15

SQL Server 2000 optimiser was consistent in this regard.

|||

The optimiser is cost based. My understanding is that one of the major things that changes between versions is the costs of different operations due to changes in hardware etc.

I suspect that the different versions may have different costs and so different plans are compiled. I would also suspect to get different plans on different machines due to differences in processor memory etc.