MartyConnelly
martyconnelly at shaw.ca
Fri Apr 2 12:20:31 CST 2004
Jet includes an undocumented Registry setting you can use to turn on the logging of query optimization plan information to a text file. Jet only writes information to the ShowPlan.out file as it creates the query plan. See Access 2002 Desktop Developer's Handbook Paul Litwin, Ken Getz, and Mike Gunderloy Chapter 15 Online here http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacbk02/html/odc_4009c15.asp under Microsoft's Unsupported Jet Optimization Tools I got this out of the Microsoft Jet Database Engine Programmer's Guide you add this registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Debug (everything but the Debug key is likely to be there already) and add this value: JETSHOWPLAN ON The easy way: copy the following into a text file called ShowPlan.reg, and doubleclick it in Explorer: REGEDIT4 [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Debug] "JETSHOWPLAN"="ON" When this switch is set ON, every query optimization will be logged to a Showplan.log file. The location can be kind of tricky -- it may be in your default Access directory, it may be in the location of the database you're running. I was never quite sure if it created itself in the default directory or in the current directory, so you may have to search for it. However, a caveat: having Showplan on can slow down Access a lot, as well as make it prone to crashing. Use it for analysis only. S D wrote: >Hi Francisco, > >you are correct. I have never used them. I never took time to figure it out. >I always wondered if there was a diff. I'll take you're advise and start hacking the statistics. > >SQL Server is just a (very) small portion of my working field. Most of it is MS-Access...and there is no query-plan thingy in Access :-( > >Sander > >Francisco H Tapia <my.lists at verizon.net> wrote: >I personnally stay away from where joins. I've seen them all to often >yield different results.. perhaps the developer didn't know how to use >them, but I've always stuck to INNER/LEFT/RIGHT Joins... and >specifically because you will tend to yield a better query plan. Have >you run the statistics to see how many table scans you get from joining >in the where clause? > > > >S D said the following on 4/1/2004 4:37 AM: > > > >>Hi paul, >> >>nice to see you here also. >> >>Personally I do NOT use the INNER JOIN stuff. I just use plain SQL >>SELECT a.x, b.y >> >> >>FROM table1 a, table2 b > > >>WHERE a.a = b.a >>AND .... >>That would result in: >>UPDATE tblPersonnelJobCategories >>SET tblPersonnelJobCategories.Selected = True >>WHERE tblPersonnelJobCategories.UserID= @UserId >>AND tblPersonnelJobCategories.CategoryID = tblPersonnelCategoriesSelected.CategoryID >>AND tblPersonnelJobCategories.PayrollNo = tblPersonnelCategoriesSelected.PayrollNo >> >> >>A tip for you're query: does it run when you hardcode the UserID? >> >>Did you try to run: >>SELECT tblPersonnelJobCategories.UserID, >>tblPersonnelJobCategories.Selected >>FROM tblPersonnelJobCategories, >>tblPersonnelCategoriesSelected >>WHERE tblPersonnelJobCategories.UserID= @UserId >>AND tblPersonnelJobCategories.CategoryID = tblPersonnelCategoriesSelected.CategoryID >>AND tblPersonnelJobCategories.PayrollNo = tblPersonnelCategoriesSelected.PayrollNo >> >>Success. >> >>Sander >> >> >>paul.hartland at fsmail.net wrote: >>To all, >> >>I have a Visual Basic 6 front-end together with a SQL Server 7.0 back-end, and have tried writing a stored procedure to do the following: >> >>I pass a UserID into the SP, then I want the SP to UPDATE tblPersonnelJobCategories to TRUE where tblPersonnelJobCategories.UserID is equal to the UserID that I passed to the SP but I only want the UPDATE to take place where the PayrollNo & CategoryID in tblPersonnelJobCategories matches the PayrollNo & CategoryID in tblPersonnelCategoriesSelected >> >>I tried using the follwing SQL statement in my SP but it doesn’t like the Inner Join: >> >>UPDATE tblPersonnelJobCategories INNER JOIN tblPersonnelCategoriesSelected ON (tblPersonnelJobCategories.CategoryID = tblPersonnelCategoriesSelected.CategoryID) AND (tblPersonnelJobCategories.PayrollNo = tblPersonnelCategoriesSelected.PayrollNo) SET tblPersonnelJobCategories.Selected = True WHERE tblPersonnelJobCategories.UserID= @UserId >> >>Any help would be greatly appreciated, as I’m fairly new to SP’s and thought an INNER JOIN would be ok. >> >> >> >> > > > > -- Marty Connelly Victoria, B.C. Canada