Question to index (SQL 2000)

I have got a question concerning multiple indices on one table.
We have an table where two colums combined build a unique index. Does this
index also help to speed up links where only one of the columns is involved,
or does it make senses to define a separate index for that column?
Example:
Fields a and b build an unique index.
Field b is involved in links to other tables.
Index on field b feasible?
TIA,
NorbertNorbert Meiss wrote:
> I have got a question concerning multiple indices on one table.
> We have an table where two colums combined build a unique index. Does this
> index also help to speed up links where only one of the columns is involve
d,
> or does it make senses to define a separate index for that column?
> Example:
> Fields a and b build an unique index.
> Field b is involved in links to other tables.
> Index on field b feasible?
> TIA,
> Norbert
The combined index on a+b will speed up lookups on field a, but not
necessarily on b. I say "not necessarily" because any lookup on b will
perform a scan, but that scan can now be done on the index instead of
the table.
It is certainly feasible to create a second index that uses b+a.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Norbert Meiss" <NorbertMeiss@.discussions.microsoft.com> wrote in message
news:99ACEA45-2216-4B07-B5FF-0C4C82A028A4@.microsoft.com...
> I have got a question concerning multiple indices on one table.
> We have an table where two colums combined build a unique index. Does this
> index also help to speed up links where only one of the columns is
involved,
> or does it make senses to define a separate index for that column?
> Example:
> Fields a and b build an unique index.
> Field b is involved in links to other tables.
> Index on field b feasible?
Yes and no.
If you can do an index on B+A that should cover both B and A.
(though if A has a higher exclusivity than B, A+B and then a separate index
for B MIGHT be better.)

> TIA,
> Norbert

Question to index (SQL 2000)

I have got a question concerning multiple indices on one table.
We have an table where two colums combined build a unique index. Does this
index also help to speed up links where only one of the columns is involved,
or does it make senses to define a separate index for that column?
Example:
Fields a and b build an unique index.
Field b is involved in links to other tables.
Index on field b feasible?
TIA,
NorbertNorbert Meiss wrote:
> I have got a question concerning multiple indices on one table.
> We have an table where two colums combined build a unique index. Does this
> index also help to speed up links where only one of the columns is involved,
> or does it make senses to define a separate index for that column?
> Example:
> Fields a and b build an unique index.
> Field b is involved in links to other tables.
> Index on field b feasible?
> TIA,
> Norbert
The combined index on a+b will speed up lookups on field a, but not
necessarily on b. I say "not necessarily" because any lookup on b will
perform a scan, but that scan can now be done on the index instead of
the table.
It is certainly feasible to create a second index that uses b+a.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Norbert Meiss" <NorbertMeiss@.discussions.microsoft.com> wrote in message
news:99ACEA45-2216-4B07-B5FF-0C4C82A028A4@.microsoft.com...
> I have got a question concerning multiple indices on one table.
> We have an table where two colums combined build a unique index. Does this
> index also help to speed up links where only one of the columns is
involved,
> or does it make senses to define a separate index for that column?
> Example:
> Fields a and b build an unique index.
> Field b is involved in links to other tables.
> Index on field b feasible?
Yes and no.
If you can do an index on B+A that should cover both B and A.
(though if A has a higher exclusivity than B, A+B and then a separate index
for B MIGHT be better.)
> TIA,
> Norbert

Question on Microsoft Association Rules

I build a data mining model to predict what are the best studying methods for the student to pass the examinaton.

Create Mining Model StudentAssociation (
Student_No long key,
Gender text discrete predict,
PassOrFail text discrete predict,
StudyMethod table predict ( MethodName text key )
) Using Microsoft_Association_Rules ( Minimum_Support=0.02, Minimum_Probability=0.03 )

The mining table will contain all the methods that the students use, no matter their examination is passed or failed.
The value of PassOrFail will have either 'Pass' or 'Fail'.

According to the above model, can I query the best studying methods?
Or I should only train the model with the student who pass the examination, and ignore all the failed.

Thanks.
Joe.

I wouldn't use AR, try Decision Trees, Naive Bayes, or Neural Nets. Also, if you're trying to predict what study methods indicate pass or fail, you only need to make PassOrFail predictable, not the nested table.

You definately need positive and negative examples to predict the result. You could create a clustering model on only the passing students to see which types of study methods group together for passing students.

|||

As your suggestion to create a clustering model on only the passing students, then I suppose to find that student uses both "method A" and "method B" will have a highest chance to pass the examination.
However, "Method A" and "Method B" may also have the highest probability to fail if I study the failed students. Am I right?
Do I need to create two clustering models, one for passing and one for fail, in order to have a complete picture?
Or, is there any methods to accomplish this task?

Thanks Jamie.

|||

Actually, if you only use passing students, then you won't see that Method A and Method B have higher chances than any other method, since all students passed. What you will see is what methods are used together by passing students. For example, there may be a group of passing students who use methods A and B, another using methods C and D, and yet another using A and D.

You are right in that these methods could just as easily be used by failing students. You should also create a similar model for failing students. You may find that the clusters are the same, they may be different, they may be similar with different proportions. For example, you may have an A and B cluster in both models, but in the passing model you find 40% of the students in A and B and only 5% in the failing model.

Another option if you want to use clustering is to make the Pass/Fail column "Predict Only" - in this case the algorithm will cluster based on all other attributes ignoring pass/fail, and then apply statistics for pass/fail across the clusters that were created. This will show if pass/fail is independent of method groupings or not (you can use the cluster diagram for this). However, if you want specifically to predict pass/fail based on study methods, you are better off using Trees or Neural Nets.

|||

Your answer is very useful for me.

Thanks Jamie.

Question on Custom code assembly

Hi,
I have to build a report whose content is very complex and comes from
multiple tables. I think the best possible way is to write custom
code(assembly). Is it possible to return a data set using custom code
assembly and assign it to report ?
Please help.
-- Thanks
RKOn Oct 19, 11:19 am, "S V Ramakrishna"
<ramakrishna.seeth...@.translogicsys.com> wrote:
> Hi,
> I have to build a report whose content is very complex and comes from
> multiple tables. I think the best possible way is to write custom
> code(assembly). Is it possible to return a data set using custom code
> assembly and assign it to report ?
> Please help.
> -- Thanks
> RK
Hi,
What you could do is write a storeproc for your dataset that handles
all (or most of)
your complex table handling.
V.|||Hi,
Thanks for the reply. Stored Procedure is a very good idea. I have a small
doubt. Is it possible at all to have a return type other than primitive
type(for example an array of integers ) for a method in a custom code
assembly in SSRS ?
--
RK
"Vinnie" <vsempoux@.gmail.com> wrote in message
news:1192786497.261274.131220@.e34g2000pro.googlegroups.com...
> On Oct 19, 11:19 am, "S V Ramakrishna"
> <ramakrishna.seeth...@.translogicsys.com> wrote:
>> Hi,
>> I have to build a report whose content is very complex and comes from
>> multiple tables. I think the best possible way is to write custom
>> code(assembly). Is it possible to return a data set using custom code
>> assembly and assign it to report ?
>> Please help.
>> -- Thanks
>> RK
> Hi,
> What you could do is write a storeproc for your dataset that handles
> all (or most of)
> your complex table handling.
> V.
>

Question on building Time Zone Tables, Daylight Savings Time Table

I wanted to ask the community if my approach to building tables for Time
Zones and Daylight Savings Time is correct. I would like to build with the
following thoughts in mind: 1) time zones are an offset (in hours) to GMT, 2
)
adjustments to time zones can be made during Daylight Savings Time, 3)
different regions of the world have different rules (or no rules) regarding
the start date and end date of Daylight Savings Time. 4) The start date and
end date of Daylight Savings Time can change from year-to-year. 5) the
year-to-year adjustment to Time Zones can change (i.e. during Daylight
Savings Time, the Time Zone is adjusted by 1 hour, the next year the time
zone is adjusted by 2 hours).
I know that my design below is flawed for situation #5. I am welcome to as
many suggestions as possible for a viable solution for all 5 thoughts above.
tblTimeZone:
TimeZoneID (PK)
TimeZoneRegionID (FK)
TimeZoneName
RegularTime_GMT_HourOffset
SavingsTime_GMT_HourOffset
tblTimeZoneRegion:
TimeZoneRegionID (PK)
TimeZoneRegion
tblDST
TimeZoneRegionID (PK)
inYear (PK)
SpringForward_date
FallBack_date
I am using the following pseudocode to adjust the local Time Zones into a
common GMT Time:
IF[date-time in question] BETWEEN tblDST.SpringForward AND tblDST.FallBack
THEN adjust [date-time in question] by tblTimeZone.SavingsTime_GMT_Offset
ELSE adjust [date-time in question] by tblTimeZone.RegularTime_GMT_Offset.Your first assumption is a bit flawed; New Delhi in India is currently
at UTC + 5:30. Yes, it is true that India spans two time zones, and
technically one time zone is UTC + 5 and the other is UTC +6, the
country has an official time that splits the difference.
Not sure about the others; just thought I'd point the first one out.
Stu