Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Samir H.
  Where is Samir H.?
 Yogyakarta
 Indonesia
 Samir H.
 To: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 Tags
Subject: RE: Composed Index as primary key?
Thread ID: 268913 Message ID: 268954 # Views: 25 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Thursday, July 15, 2010 4:01:37 PM         
   


> >
> > Thank you for taking time to explain.
> > Why should I use all possible combinations? I always have those 3 values, which I then would (did already) put in a expression:
> >
> > nSubj=cursor1.subj_id 
> > nStud=cursor2.stud_id
> > nSemester=combo.value
> > cIndexExpr=bintoc(nSubj)+ bintoc(nStud)+ bintoc(nSemester)  && I do define the order here, isn't it?
> > =seek(cIndexExpr)

> > Without this composite index, I'll have to get back to my SCAN & LOCATE...
> >
> > Regards
> > Samir
>
> I said all possible combinations because today there is to search by Subject, Student and Semester in your mind. That may not be the case as your necessities increase (you know applications grow faster than kids and plants:) In your sample you have all the values (subject, student, semester) and that hides the picture. We will return to this case but first what ifs.
> -What if I know the semester and student and want all subjects participated. Calls for another index?
> -What if I know the subject only. Code then would look like (assuming you are like me and not touching to VFP's defaults on set exact, set ansi ... unless really necessary and temporarily even if you do):
>
>
>  nSubj=cursor1.subj_id 
>  nStud=cursor2.stud_id
> * nSemester=combo.value
>  cIndexExpr=bintoc(m.nSubj)+ bintoc(m.nStud)  && I do define the order here, isn't it? - yes you do:)
> if seek(m.cIndexExpr,'myTable', 'myCompositeTag')
> ...
> 

>
> I can't deny that you would gain speed in doing so. But you wouldn't go back to locate, scan ... at least in the sense you think (as if I am reading your mind:). As per locate I wouldn't suggest anyway because despite all those rushmore banners locate is slow to my experience. But Scan...enscan is not something you should fear and put aside. IMHO Seek() connected with scan ... endscan often beats SQL where applicable. For example think you do not have composite index but individual:
>
>
>  nSubj=cursor1.subj_id 
>  nStud=cursor2.stud_id
>  nSemester=combo.value && BTW this wouldn't be numeric unless you directly use rowsource.column but assume correct now
> * Typically I expect a student have less subject+semester (student rows)
> * than say a subject's rows (student+semester)
> * so I would use student's index
> select myTable
> set order to tag studentID
> if seek(m.nStud)
>    scan ;
>         FOR subjectID = m.nSubj and semesterID = m.nSemesterID ;
>         WHILE studentID = m.nStud
> 
>      * do something
> 
>    endscan
> endif
> 
This is not as fast as single index seek but the difference is not measurable IMHO unless a student is taking thousands of subject and semesters. WHILE ensures that you are only scanning within rows that belong to that student (as a student if I have an average of 10 subjects per semester and 10 semesters so far then for me this would mean to do a "scan for" in 100 rows at most which should take a millisecond or two). It is also easier than to remember and write an expression matching composite index each time. This is flexible you can seek( subject ) and scan while for subject based work etc.
>
> Cetin Basoz
>
> .Net has got better.Think about moving - check my blog:
> Blog (main)
> Blog (mirror)

Ok, thank you for all information. I thought already about such situations as you described, where I don't have all values for the composite index, but thought that it will be then SELECT's job. I imagined the composite index for the frequent and common operations, and SELECT for all other operations. I will go thru this thread & my code again, and see what I will do.

Regards
Samir

ENTIRE THREAD

Composed Index as primary key? Posted by Samir H. @ 7/15/2010 11:26:26 AM
RE: Composed Index as primary key? Posted by Cetin Basoz @ 7/15/2010 12:34:34 PM
RE: Composed Index as primary key? Posted by Samir H. @ 7/15/2010 1:21:52 PM
RE: Composed Index as primary key? Posted by Cetin Basoz @ 7/15/2010 2:19:09 PM
RE: Composed Index as primary key? Posted by Samir H. @ 7/15/2010 2:40:10 PM
RE: Composed Index as primary key? Posted by Samir H. @ 7/15/2010 2:03:14 PM
RE: Composed Index as primary key? Posted by tushar @ 7/15/2010 2:16:13 PM
RE: Composed Index as primary key? Posted by Samir H. @ 7/15/2010 2:38:39 PM
RE: Composed Index as primary key? Posted by tushar @ 7/15/2010 3:44:03 PM
RE: Composed Index as primary key? Posted by Cetin Basoz @ 7/15/2010 2:35:00 PM
RE: Composed Index as primary key? Posted by Samir H. @ 7/15/2010 2:49:40 PM
RE: Composed Index as primary key? Posted by Cetin Basoz @ 7/15/2010 3:45:23 PM
RE: Composed Index as primary key? Posted by Samir H. @ 7/15/2010 4:01:37 PM
RE: Composed Index as primary key? Posted by Mike Yearwood @ 7/15/2010 8:21:47 PM
RE: Composed Index as primary key? Posted by Samir H. @ 7/16/2010 4:48:41 AM
RE: Composed Index as primary key? Posted by Mike Yearwood @ 7/16/2010 6:07:24 AM
RE: Composed Index as primary key? Posted by Samir H. @ 7/16/2010 6:16:14 AM
RE: Composed Index as primary key? Posted by Cetin Basoz @ 7/16/2010 1:12:42 PM
RE: Composed Index as primary key? Posted by Samir H. @ 7/16/2010 1:23:51 PM
RE: Composed Index as primary key? Posted by Samir H. @ 7/16/2010 1:46:54 PM
RE: Composed Index as primary key? Posted by tushar @ 7/15/2010 4:02:29 PM
RE: Composed Index as primary key? Posted by Samir H. @ 7/15/2010 4:12:41 PM
RE: Composed Index as primary key? Posted by Anders Altberg @ 7/16/2010 1:20:32 PM
RE: Composed Index as primary key? Posted by Samir H. @ 7/16/2010 1:31:56 PM