Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Mike Yearwood
  Where is Mike Yearwood?
 Toronto
 Canada
 Mike Yearwood
 To: Samir H.
  Where is Samir H.?
 Yogyakarta
 Indonesia
 Samir H.
 Tags
Subject: RE: Composed Index as primary key?
Thread ID: 268913 Message ID: 269022 # Views: 24 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Friday, July 16, 2010 6:07:24 AM         
   


> >
> > 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)
>
> Hi Cetin,
> It looks like Tushar & Mike also think better use single indexes. So I changed my code to seek()+scan.
>
>
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 nothing!
>      	EXIT 
>    endscan
> endif
> thisform.edtComments.value=myTable.comments		&& memo-field
> RETURN
> * If I don't want to give any feedback to the user, it seems that there's no need
> * to check for eof(). -> myTable.comments would just be an empty string if eof()=.t.
> * or do you see the need to do following?
> IF !EOF()
> 	thisform.edtComments.value=myTable.comments		&& memo-field
> ELSE 
> 	thisform.edtComments.value=''
> ENDIF

>
> Last question(?) in this thread :)
> Would you still recommend a candidate composite "BINTOC(subj)+BINTOC(stud)+BINTOC(semester)" just to enforce uniqueness of records? The application should actually take care of that. But your applications do too, do you then still make a given index candidate?
>
>
> Regards
> Samir

I add data integrity rules (like that one) to the database, rather than in the business rules or UI.

Mike Yearwood
Microsoft MVP Visual FoxPro 2008, 2009
We have enough youth. We need a fountain of smart!
There may be many ways to skin a cat, but there are very few right ways to do it.

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