28. august 2005 - 10:43Der er
24 kommentarer og 1 løsning
Optimering af forespørgsel, skal gøres i et sql-hug
Hej alle
Hvis nu jeg var go til sql, så havde jeg kørt denne sammen i en sætning / et recordsæt, så alt data blev fundet i et sql-hug, men det er jeg ikke, og derfor er min kode noget ineffektiv og indlæsningstiden er for høj. Kan du hjælpe mig med at optimere sætningen med subselects og joins mv, så alt hentes på en gang?
<% 'finder de forumer som har tilknyttet en eller flere moderatorer (undlad forumer som er specielle, låste eller inaktive) strSQL = "SELECT FORUM_FORUM.FORUM_ID FROM FORUM_FORUM INNER JOIN FORUM_MODERATOR ON FORUM_FORUM.FORUM_ID = FORUM_MODERATOR.FORUM_ID INNER JOIN FORUM_CATEGORY ON FORUM_FORUM.CAT_ID = FORUM_CATEGORY.CAT_ID INNER JOIN FORUM_GROUPS ON FORUM_CATEGORY.CAT_ID = FORUM_GROUPS.GROUP_CATID WHERE FORUM_GROUPS.GROUP_ID = 6 AND FORUM_CATEGORY.CAT_ID <> 41 AND FORUM_FORUM.F_STATUS = 1" set rs = dataConn.execute(strSQL) while not rs.eof 'finder de tilknyttede moderatorer (et forum kan godt have flere moderatorer) strSQL = "SELECT MEMBER_ID FROM FORUM_MODERATOR WHERE FORUM_ID = " & RS("FORUM_ID") set rs2 = dataConn.execute(strSQL) while not rs2.eof 'indlæs brugerprofilen for de fundne moderatorer (en brugerprofil pr. moderator) strSQL = "SELECT M_NAME FROM FORUM_MEMBERS WHERE MEMBER_ID = " & RS2("MEMBER_ID") set rs3 = dataConn.execute(strSQL) while not rs3.eof 'udskriv navnet på moderatoreren response.write rs3("M_NAME") & "," rs3.movenext wend rs2.movenext wend rs.movenext wend
In the first select you have an INNER JOIN. If you copyy this select int a new view you should see a thin line between the two tables. If I am righ then you should now be able to add the FORUM_MODERATOR table to the view and then make a JOIN using drag and drop. The join is on FORUM_ID. You can do the same for the FORUM_MEMBERS table and JOIN on MEMBER_ID.
You can actually make a view and select from that instead of making the SQL in code, it will be faster because the SQL you make in code needs to be checked before it gets executed. In a view this is already done!
Now I have saved the view (in Enterprise), but how do I call it from my asp code?
(I know that I can copy the code to my asp-page, but I would like know how to call the view instead... because it is much easier to mantain it from Enterprise.)
This is fun - thank you very much - why haven't I known about views before ;-) I have so many, very complex sql requests that I - over time - can convert to views and the site will load quicker and I can mantain everything much easier :-D :-D :-D
No, its not possible to drag and drop with SP's. In SP's you can make much more complex selects. You can also make code to enable you to do things which you cant do in views. In this case I would use a VIEW and when you find that using VIEWS isnt possible then you can try using SP's. But you can NOT select directly from an SP.
try looking in Books Online (help), there is quite a lot of information on SP's
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
