Reg: Query Performence

Below query output results time taking very much.

SELECT NON EMPTY

{[Measures].[Score]} ON COLUMNS,

NON EMPTY {

(DESCENDANTS({[Organisation].[Organisation].&[36]},0),

[Question].[Type Id].[Category Id],

[Question].[Type Description].[Category Description],

[Question].[Short Description].[Short Description]

)}

HAVING [Measures].[Points]>0 ON ROWS

FROM (SELECT [Assignment].[Id].&[1] ON COLUMNS

FROM [Sample])

Total 7 dimensions 2 measure groups

Fact Table : 18,000,00 Records

Dimensions: 2000 Records

[Organisation].[Organisation] having 9 levels

like

Level1

Level2

LEVEl3

Is there any solution to improve the query performence.Any steps to inreasing performence of cube. Please help me. this is urgent for me

Could you please rewrite your query in terms of AdventureWorks?

Reg: Duplicates Removing Problem

I have Data like below

TableA

Col1 Col2 Col3

1 'Test One' 4/7/2007

1 'Test One-1' 4/7/2007

2 'Test' 4/7/2007

3 'Test Three' 4/7/2007

3 'Test Three-1' 4/7/2007

3 'Test Three-2' 4/7/2007

I need to delete the duplicate records[Latest record has to be remaining like that] from above table. i need the out put like below

Col1 Col2 Col3

1 'Test One-1' 4/7/2007

2 'Test' 4/7/2007

3 'Test Three-2' 4/7/2007

If any body know the solution please help me.

Regards

Hanu

You'll want something along the lines of:

SELECT Col1, MAX(Col2), MAX(Col3)

FROM Table

GROUP BY Col1

However, you will probably come into problems with the Col2 values as on character MAX finds the highest value in the collating sequence which may or may not be accurate.

To do latest record, you'd be better off using something like a datetime field (with time) and then something like this.

SELECT t.Col1, t.Col2, t.Col3

FROM Table t

INNER JOIN (SELECT Col1, MAX(Col3) AS Col3

FROM Table
GROUP BY Col1) AS Mx

ON t.Col1 = Mx.Col1 AND t.Col3 = Mx.Col3

Hope this helps!

|||

Unless you expand Col3 to a full datetime datatype column, including the time, there is no way, as you have presented the data, to determine which row of data is the 'Latest record'.

Or you could add an IDENTITY column to the table. Or some what to determine sequence of rows.

Currently, you have none.

|||I think it's worth mentioning that the results of the query richbrownesq suggested are not guaranteed to be in the original table, as MAX(Col2) and MAX(Col3) could come from different rows of the table.

If you have a column, say dateInserted, from which you can determine which row is "latest," you can do this in SQL Server 2000 [not tested in a repro]

SQL Server 2000

delete from t
where exists (
select * from t as t_copy
where t_copy.Col1 = t.Col2
and t_copy.dateInserted > t.dateInserted
)

In other words, delete all rows that are superseded by a later row for the same Col1 value.

In SQL Server 2005, it may be more efficient to do this:

SQL Server 2005

with t_ranked as (

select
*,
rank() over (
partition by Col1
order by dateInserted desc
) as rk
from T
)
delete from T_ranked
where rk > 1

Both solutions will leave multiple rows for a single Col1 if they are tied for latest dateInserted value.

Steve Kass
Drew University
http://www.stevekass.com
|||

Arnie Rowland wrote:

Unless you expand Col3 to a full datetime datatype column, including the time, there is no way, as you have presented the data, to determine which row of data is the 'Latest record'.

Or you could add an IDENTITY column to the table. Or some what to determine sequence of rows.

Currently, you have none.

On the contrary Column2 shows the chronology.

I'll post the query later. I'm in the middle of something right now.

Adamus

|||

Technically there are not duplicates with the exception of col1 + col3. Col2 makes the record unique

To get the desired resultset, grab the right(max) from Col2 (if one exists) and group Col1

Adamus

|||

While that 'may' be true, when handling the data, whether using MAX() or [ ORDER BY ], there will be significant ambiguity when the count gets up to 'Test Three-10'.

How would you 'easily' determine the sequence of the following (without having to parse)?

'Test Three-1'

'Test Three-10'

'Test Three-11'

'Test Three-2'

'Test Three-3'

'Test Three-4'

'Test Three-5'

'Test Three-6'

'Test Three-7'

'Test Three-8'

'Test Three-9'

|||

Arnie Rowland wrote:

While that 'may' be true, when handling the data, whether using MAX() or [ ORDER BY ], there will be significant ambiguity when the count gets up to 'Test Three-10'.

How would you 'easily' determine the sequence of the following (without having to parse)?

'Test Three-1'

'Test Three-10'

'Test Three-11'

'Test Three-2'

'Test Three-3'

'Test Three-4'

'Test Three-5'

'Test Three-6'

'Test Three-7'

'Test Three-8'

'Test Three-9'

You cannot avoid parsing when dealing with strings nor should you resort to the 'easy' and quick.

Adamus

|||

Arnie Rowland wrote:

...there will be significant ambiguity...

As an FYI there is no ambiguity in uniqueness regardless of datatype. Just because a string scares you, doesn't mean you should coin it ambiguous.

Adamus

|||

Adam

It would better demonstate a desire to helpfully contribute to the forums were you to offer constructive suggestions to solve the OP's problem.

As I indicated earlier, Col2 provides no way to determine the chronology of data added to the database. The nature of the data may lead one to 'assume' a sequence, but, as indicated earlier, as the data is presented, it is not possible to determine if 'Test Three-2' is entered BEFORE or AFTER 'Test Three-2' -therefore impossible to determine which is the 'latest record'.

You are making assumptions on the OP's data that are not justified.

|||

Arnie Rowland wrote:

Adam

It would better demonstate a desire to helpfully contribute to the forums were you to offer constructive suggestions to solve the OP's problem.

As I indicated earlier, Col2 provides no way to determine the chronology of data added to the database. The nature of the data may lead one to 'assume' a sequence, but, as indicated earlier, as the data is presented, it is not possible to determine if 'Test Three-2' is entered BEFORE or AFTER 'Test Three-2' -therefore impossible to determine which is the 'latest record'.

You are making assumptions on the OP's data that are not justified.

Arnie,

While I continue to feel that you resorting the path of least resistance, there still remains a logical progression and association with the OP's table and desired resultset. I would think it is insulting not helpful to the OP to suggest his question has no solution without modifying the table.

Yes, adding an identity field is the obvious choice, but again this might not be an option.

Working with the data "provided," there is a solution. Where is the assumption?

Adamus

|||

Arnie Rowland wrote:

It would better demonstate a desire to helpfully contribute to the forums were you to offer constructive suggestions to solve the OP's problem.

Arnie said = "No Solution"

Adamus said = "Here's the solution"

Please revisit the thread to determine who is being constructive and who is being argumentative.

Thanks,

Adamus

|||

Arnie Rowland wrote:

You are making assumptions on the OP's data that are not justified.

Arnie, you are not making assumptions when working with the data provided.

You, on the other hand, are assuming that the table can be altered.

With the provided information, if you do not see a pattern, I'm not sure how you can help.

Adamus

Referencing an alias in a where clause

I have the following data set - there is more to it than whats below, I just made it easier to read and highlight my problem!

SELECT LEFT(actv.ProjID, 4) AS proj, actv.Activity, actv.TotalExpensesLB, actv.PADM
FROM dbo.xtbl_MERActv actv
WHERE LEFT(actv.projID,4) = @.project OR actv.PADM = @.PADM

What I want to do is have the user enter a 4 digit number (@.project) which will correspond to LEFT(actv.ProjID, 4). The way it is now, if the user enters a 4 digit number, no records are returned. If the user enters a 6 digit number ( the real length of the projID), then it runs correctly and I get the records I want.

I have tried to use the alias 'proj' in the where statement, but I get an error message that it is an invalid column name.

Where am I going wrong?

Thanks in advance!

You cannot use a column alias in the where clause, you just have to duplicate the calculation.

I cannot see anything wrong with what you are trying to do. My only guess is that perhaps there is a datatype problem. Are both @.project and actv.projID the same datatype?

Also, what version of SQL server is this?

|||

Its SQL 2000, and I haven't actually set the @.project as any data type - could that be the problem? The actv.projID is char.

Actually, I may not have been clear in the problem. When running the data set, it will work fine, but when I try to preview the report, and enter a 4 digit @.project, I get a blank report with no error.

Thanks for the info on not using column alias!

|||

Ah, that's a different problem.

I'm not really sure what could be causing a blank report when your dataset returns data.

What is the datatype of the parameter that you are using to hold the 4-digit number?

Also, when you said that entering a six digit number worked, was that without any modification to the dataset?

|||

Sorry for that confusion about the problem.

The parameter is string, as is the projId.

It seems that using either 4 or 6 digit for @.project will give me results in the viewer, but still blank preview

Using the following statement

WHERE (LEFT(actv.ProjID, 4) = @.project)

|||

I cannot think of a case where the left 4 characters of a string* would be equal to a six character string...

Actually, I take that back. If you have ANSI_NULLS set to OFF (not recommended) and both of the sides were NULL, then it could, but then you should be getting the same results for both entries, which is to say all rows. I guess I'm stumped.

*string meaning varchar, char, nvarchar, or nchar

|||

It doesn't make sense to me at all - I figured there was something really obvious that I had forgotten...I'll take a new look at it on Monday, with fresh eyes, and maybe see my error.

Thanks for your help!

Referencing a composite foreign key

I can't find any examples on how to do the below. Can anyone advise?
I have an 'Address' table which has a primary key constructed from
'postcode' and 'house no'.
I have numerous other tables that reference this table with a foreign
key - how do I reference a composite foreign key such as this - It
isn't a matter of just entering an integer as I won't know what the
composite value for a postcode and house no is.
Am I right in thinking that I need to make the 'postcode' and 'house
no' a composite UNIQUE to ensure these are unique and add an additional
integer primary key to the address entity which the other tables can
then reference?
Thanks,
DJWYou can reference a composite key via:
alter table MyTable
add constraint FK1_MyTable foreign key (postcode, houseno)
references OtherTable (postcode, houseno)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"dwj" <danielwatkinslearn@.hotmail.com> wrote in message
news:1133295056.924542.76350@.g43g2000cwa.googlegroups.com...
>I can't find any examples on how to do the below. Can anyone advise?
> I have an 'Address' table which has a primary key constructed from
> 'postcode' and 'house no'.
> I have numerous other tables that reference this table with a foreign
> key - how do I reference a composite foreign key such as this - It
> isn't a matter of just entering an integer as I won't know what the
> composite value for a postcode and house no is.
> Am I right in thinking that I need to make the 'postcode' and 'house
> no' a composite UNIQUE to ensure these are unique and add an additional
> integer primary key to the address entity which the other tables can
> then reference?
> Thanks,
> DJW
>|||How do you insert data into this table with the foreign key (say it just
contains the foreign key and a varchar name). As follows...
INSERT INTO MyTable VALUES ('QW1 4BP', 90, 'A Name')
DJW
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#Bpu9IS9FHA.2264@.tk2msftngp13.phx.gbl...
> You can reference a composite key via:
> alter table MyTable
> add constraint FK1_MyTable foreign key (postcode, houseno)
> references OtherTable (postcode, houseno)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "dwj" <danielwatkinslearn@.hotmail.com> wrote in message
> news:1133295056.924542.76350@.g43g2000cwa.googlegroups.com...
>|||Precisely. As long as ('QW1 4BP', 90) exists in OtherTable, then the INSERT
will succeed.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Daniel J Watkins" <danielwatkinslearn@.hotmail.com> wrote in message
news:438d4ecc$1_1@.glkas0286.greenlnk.net...
How do you insert data into this table with the foreign key (say it just
contains the foreign key and a varchar name). As follows...
INSERT INTO MyTable VALUES ('QW1 4BP', 90, 'A Name')
DJW
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#Bpu9IS9FHA.2264@.tk2msftngp13.phx.gbl...
> You can reference a composite key via:
> alter table MyTable
> add constraint FK1_MyTable foreign key (postcode, houseno)
> references OtherTable (postcode, houseno)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "dwj" <danielwatkinslearn@.hotmail.com> wrote in message
> news:1133295056.924542.76350@.g43g2000cwa.googlegroups.com...
>

Reducing many-to-many to one-to-one

Hi,

I have the following three tables below containing Resources,
Categories and a link table so each Resource can belong to one or more
Categories. I would like to create a view (ResourceID, ResourceName,
CategoryID, CategoryName) that includes one row for each Resource with
just one of the Categories that it belongs to.

Resource table
- ResourceID
- ResourceName
- etc..

Category table
- CategoryID
- CategoryName
- etc..

ResourceCategory table
- ResourceID
- CategoryID

Can anyone help? Thanks.On Fri, 14 Sep 2007 14:43:33 -0000, nick@.nova5.net wrote:

Quote:

Originally Posted by

>Hi,
>
>I have the following three tables below containing Resources,
>Categories and a link table so each Resource can belong to one or more
>Categories. I would like to create a view (ResourceID, ResourceName,
>CategoryID, CategoryName) that includes one row for each Resource with
>just one of the Categories that it belongs to.

Hi Nick,

That's only possible if you somehow specify WHICH of the categories you
want. Lowest CategoryID? Longest CategoryName? CategoryID closest to a
multiple of 42?

I'm sure that you don't care, but since SQL Server has no "just give me
any I don't care which" operator, you'll have to specify something.

Assuming you want the lowest CategoryID, you can use the following on
SQL Server 7.0 and up (on SQL 2005, you might find a better solution
with CTEs and CROSS APPLY, but since you failed to specify the version,
I'll play it safe):

CREATE VIEW YourShinyNewView
AS
SELECT r.ResourceID, r.ResourceName,
c.CategoryID, c.CategoryName
FROM Resource AS r
INNER JOIN (SELECT Resource, MIN(CategoryID) AS MinCategoryID
FROM ResourceCategory
GROUP BY Resource) AS rc
ON rc.ResourceID = r.ResourceID
INNER JOIN Category AS c
ON c.CategoryID = rc.MinCategoryID;

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Hi Hugo,

Thanks for help, just what I needed. I was trying to put a 'top 1' in
the nested select which obviously (now!) didn't work..

Nick.

Quote:

Originally Posted by

Hi Nick,
>
That's only possible if you somehow specify WHICH of the categories you
want. Lowest CategoryID? Longest CategoryName? CategoryID closest to a
multiple of 42?
>
I'm sure that you don't care, but since SQL Server has no "just give me
any I don't care which" operator, you'll have to specify something.
>
Assuming you want the lowest CategoryID, you can use the following on
SQL Server 7.0 and up (on SQL 2005, you might find a better solution
with CTEs and CROSS APPLY, but since you failed to specify the version,
I'll play it safe):
>
CREATE VIEW YourShinyNewView
AS
SELECT r.ResourceID, r.ResourceName,
c.CategoryID, c.CategoryName
FROM Resource AS r
INNER JOIN (SELECT Resource, MIN(CategoryID) AS MinCategoryID
FROM ResourceCategory
GROUP BY Resource) AS rc
ON rc.ResourceID = r.ResourceID
INNER JOIN Category AS c
ON c.CategoryID = rc.MinCategoryID;
>
(Untested - seewww.aspfaq.com/5006if you prefer a tested reply)
>
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis