Showing posts with label bit. Show all posts
Showing posts with label bit. Show all posts

Wednesday, March 21, 2012

Question on SQL experission for complicated query

Hello All,

I have a problem on how to retrieve records from a database using SQL query. It is a little bit complicated query. I do not know what SQL query experission is correct.

In the database table, there are four fields "ClassID", "Personal Nmae", "Age", "Score". The following is an example for illustration.

Class Name Age Score
0 Bob 20 78 <====
0 John 26 66 <****
2 Wilson 28 88
1 John 26 77 <****
3 Alice 25 56
1 Bob 20 89 <====

In the above example table, I would like to know the scores of students who share the same name, at the same age, BUT in different class (ClassID is specified by the user). Actually, I need a SQL SELECT experission which will lead to a query result (a recordset) like the following

Bob 78 89
John 66 77select T.name
, T.score
from daTable as T
inner
join (
select name
, age
from daTable
group
by name
, age
having count(*) > 1
) as D
on D.name = T.name
and D.age = T.age|||r937,

Thank you for your reply, i will try your code and report latter.|||yes, it is correct. However, what is the SQL experission like if we use 'self join' to implement the same thing.|||Curiously is there anything wrong with doing the following?

SELECT s1.Name,s2.Score
FROM student_results s1
JOIN student_results s2
ON s1.Name=s2.Name
AND s1.Age=s2.Age
AND s1.Class<>s2.Class|||not "wrong" per se, but what if a given name has three scores -- what does your query return? what does mine?|||Thanks aschk abd r937,

Actually, in my application there are only two scores for a given name. One more further question,

If we have another table 'ClassTotalScore' consisting of two fields "Class" , "TotalScore"

Can I add some clause to the above suggested SQL experissions to get the sum of total scores of Class=0 and Class=2. Or I have to use another SQL statement to get the sum of total scores of two classes.

Moreover, if either way is possible, I would like to know whether ONE SQL statement is necessarilyfaster than TWO SQL statements.

Actually, those data in the two tables originally are stored in varibles in my VC++ 6.0 program. However, as more and more data are produced, the PC memory is almost exhaused. Hence, i have to resort to the database technology. I notice that the running speed of the databased based program is 10 times slower than the original program. I wonder if this is normal.|||Sounds like your database abstraction layer might be doing something odd (either that or your database system is setup badly/running slowly).
I would still recommend rudy's method of SQL Query by the way. I haven't tested mine with more than two results (because i deleted the tables) but from experience I know that rudy tends to be right ;)|||Unless your programme is using some really nasty inefficient way of storing all your SQL results?|||aschk,

thanks.

I think i did not express my problem clearly in the previous posts. Actually, the speed of storing data (write) is ok for me. I am compliant with the speed of retrieving data (read data)-- more than 10 times slower than the memory based method.

aschk, are you sure this is not normal.

BY the way, there are 1200 plus records in the database.

10 times slower than the original program|||Is the database on the same machine as the program? Is your database cache limit really low? 1200 is nothing really. If you said 12 Million records then I would question it. If your database is on a seperate machine and the records are big then transferring all that data across a network might be your slowdown point. Where does your program take the longest?|||aschk,

thanks.

Yes, I did the tests (both reading--retrieving data and writing--storing data) in the same program on a DELL Optiplex GX620 (3Ghz CPU), 2G memory.

I am new to SQL server 2000, where is the setting for database cache?|||What else is your server doing? Because for it to write quicker than it reads is definitely odd. If you run the query directly in Query analyser how long does it take?|||write = 1 row

read = all 1200 rows

of course it will be slower :cool:

cy, what indexes have you crated?

Tuesday, March 20, 2012

Question on relationship between Cube and Fact tables?

Hi, all,

Sorry for this title which may be a bit confused. But what I am trying to ask is: Any best practices on fact tables to be included in a cube? Is it better to create a separate cube for each fact table and its related dimension tables or any other ideas to put several fact tables and their related dimension tables into one cube?

Thanks in advance for your kind advices.

With best regards,

Yours sincerely,

Helen,

First Analysis your requirement, is there any clubed information you desired from multiple Fact Tables, How may shared dimensions are among them.

How frequently U require that aggregated [from both Fact Table] information etc.

Simply analysis what can have U benifit when make a cube having multiple fact tables.

Or other way when there is no share facts between to fact table donot make Cube having multiple Fact Tables.

Reference is below:

Cube Design Best Practices

Well-designed cubes will better match the needs of your end-users. The following best practices can improve usability and also performance of the cube.

Avoid including unrelated measure groups in the same cube

Having many measure groups in a cube can adversely affect the query performance of the cube, even for queries that only use a single measure group. If your DSV contains many potential fact tables and they will not usually be used together, consider creating multiple smaller cubes containing topic areas that are used together. Then, you can enable occasional cross-topic querying by creating linked measure groups that join together all measure groups in a special cross-topic cube.

Bhudev

|||

Dear Helen,

Depends on your system requirements.

But probably you only need one cube, and I think it because in the SQL 2005 you can process parts of the cube separately, in spite of all the cube...

For example, one thing I usually do, is in the datasource view, in spite of use a table from database, I use a view based on this table with only the field I will need in the cube. Only in case we have a lot of fields that will not used...

Other thing is used named calculations, or the compute calculation in database...

Regards!

|||

Hi, Bhudeve,

Thanks for you advices.

Yes, as there are shared dimensions for different measure groups and you never know when and what the users would like to see on their side. Thus, to optimally meet the querying needs, always put them together in a cube? But we can always create partitions for different measure groups if we want?

Does it make any sense? Looking forward to hearing from more of your advices.

With best regards,

Yours sincerely,

|||

Hi, PedroCGD,

Thanks for your suggestions.

Are you saying you put all data together in a single cube? And then partition the cube on different measure groups? Thanks a lot and would like to hear more from you for that.

With best regards,

Yours sincerely,

|||

Yes!

And I will try to explain in my blog, how I'm doing in my project!

Each FactTable will be your measure group... with only the measures you need for your cube!

regards!!

|||

Hi, PedroCGD,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

|||

Helen,

I'm not against to keep separate Cubes when I do not know the actual requirement. But still there is need to consider all Pros and Cons before completing Design Document for your data Warehouse.

OLAP Design Best Practices for Analysis Services 2005

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/olapdbpssas2005.mspx#ELGAC

document states Having many measure groups in a cube can adversely affect the query performance of the cube, even for queries that only use a single measure group. If your DSV contains many potential fact tables and they will not usually be used together, consider creating multiple smaller cubes containing topic areas that are used together. Then, you can enable occasional cross-topic querying by creating linked measure groups that join together all measure groups in a special cross-topic cube.

Bhudev

|||

Hi, Bhudeve,

Thanks for that. Very helpful.

With best regards,

Yours sincerely,

Monday, March 12, 2012

Question on Process killed and still exists with a killed/roll

Sometimes it does take a little bit time to finish off the rollback
(despite it indicates it is 100% completed'), but if the process still
there after let say good 10 mins. Run 'select * from sysprocesses' on
the master database and see if there is any lock problem there. Check
the 'blocked' field of the process.
Also check the SQL logs to see for any exceptions/errors. What SQL
platform you are on? What Service Pack you have installed.
Regards,
Mel
"MSLam" wrote:

> Sometimes it does take a little bit time to finish off the rollback
> (despite it indicates it is 100% completed'), but if the process still
> there after let say good 10 mins. Run 'select * from sysprocesses' on
> the master database and see if there is any lock problem there. Check
> the 'blocked' field of the process.
> Also check the SQL logs to see for any exceptions/errors. What SQL
> platform you are on? What Service Pack you have installed.
> Regards,
> Mel
>

Saturday, February 25, 2012

Question on Cube aggregation percentage?

Hi, all experts here,

As I am a bit confused about the aggregation percentage of a cube, when we partition the cube, we need to set the aggregation percentage. But how does the analysis services really work here with the pre-defined cube aggregation percentage? Does not the analysis services engine only aggregates data on cube hierarchies and user-defined aggregations? (assume we have defined both the natural and user-defined hierarchies and user-defined aggregations), how can we determine how much percentage of aggregations it will be for that?

Hope it is clear for your advices and help and I am looking forward to hearing from you shortly and thanks a lot in advance.

With best regards,

Yours sincerely,

The aggregation percent number is only an approximate representation of the number of aggregations and estimated performance benifit of aggregations designed by the aggregation wizard. Note that it's a bit misleading in that you don't want 100% but something more like 30%. I recommend reading http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SSAS2005PerfGuide.doc for more information on this subject.|||

Hi, Matt,

Thanks a lot for your kind advices and the very helpful imformation.

With best regards,

Yours sincerely,

Question on column mappings between mining structure and case table for lift chart

Hi, all experts here,

I am a bit confused for the model evaluation (lift chart), should we map all the columns for both the mining structure and the case table? I mean for those predictive models, we have a predict column, shouldnt we ignore the mapping of the predictive column between the mining structure and the case table? But it seemes we are not allowed to miss the predictive column mapping between the mining structure and the case table.

Why is that? Could any experts here give me some explanation on that?

Hope my question is clear for your help.

Thanks a lot and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

The mapping of the predicted column is required to compare the prediction with the actual test data. It is not used in the actual prediction|||

Hi, Bogdan,

Thanks a lot for your advices.

With best regards,

Yours sincerely,