Håller på att knåpa ihop en sp som för fritextsökning.

Jag undrar vad som är bäst att använda join eller subquery i mitt fall med koden nedan.

DefItemUserRightSettingsGroupsExtracted innehåller de poster som akutell användare skall ha tillgång till.

I första ex använder jag join och i andra ex använder jag subquery

Båda ger en kostnad på 0% då jag kör execution plan, de tar även lika lång tid att retunera resultat.


select distinct bs.binder_cid, d.cid document_cid ,d.Description1,
(select top 1 DocumentType from view_tbl_DocuSysDocumentRevisions where document_cid = d.cid ) DocumentType,d.ID,Templates.[Description]
from view_tbl_DocuSysBinderStructures bs
inner join view_tbl_DefItemUserRightSettingsGroupsExtracted USGE on bs.binder_cid = USGE.item_cid
inner join view_tbl_DefItemUserRightSettingsGroupsExtracted USGE2 on bs.cid = USGE2.item_cid
inner join view_tbl_DocuSysBinderStructureMembers bsm on bs.cid = bsm.structure_cid
inner join view_tbl_DocuSysDocuments d on bsm.member_cid = d.cid
left join view_tbl_DocuSysDocumentRevisions r on d.cid = r.document_cid
left join view_tbl_DocuSysTemplates Templates on d.DocumentTemplate_cid = Templates.cid
where (USGE.Module_cid=33 and USGE.UserRight_cid=4 and USGE.UserRightLevel = 1) and (USGE.user_cid is NULL OR USGE.user_cid=70)
and (USGE2.Module_cid=34 and USGE2.UserRight_cid=7 and USGE2.UserRightLevel = 1) and (USGE2.user_cid is NULL OR USGE2.user_cid=70)
and d.Description1 like '%mar%'
order by document_cid





select distinct bs.binder_cid, d.cid document_cid ,d.Description1,
(select top 1 DocumentType from view_tbl_DocuSysDocumentRevisions where document_cid = d.cid ) DocumentType,d.ID,Templates.[Description]
from view_tbl_DocuSysBinderStructures bs
inner join view_tbl_DocuSysBinderStructureMembers bsm on bs.cid = bsm.structure_cid
inner join view_tbl_DocuSysDocuments d on bsm.member_cid = d.cid
left join view_tbl_DocuSysDocumentRevisions r on d.cid = r.document_cid
left join view_tbl_DocuSysTemplates Templates on d.DocumentTemplate_cid = Templates.cid
where d.Description1 like '%mar%'
and bs.binder_cid in
(
select item_cid
FROM view_tbl_DefItemUserRightSettingsGroupsExtracted
WHERE (Module_cid=33 and UserRight_cid=4 and UserRightLevel = 1)
AND (user_cid is NULL OR user_cid=70)
)
and bs.cid in
(
select item_cid
FROM view_tbl_DefItemUserRightSettingsGroupsExtracted
WHERE (Module_cid=34 and UserRight_cid=7 and UserRightLevel = 1)
AND (user_cid is NULL OR user_cid=70)
)
order by document_cid

DDL och exempeldata vore trevligt.

Sen rekommenderar jag två inlägg i min blog för att komma tillrätta med de konstiga procentsatserna i Query Analyzer:

Problemet: www.hedgate.net/blog/2005/03/15/qa-percentage-bug/
Lösningen: www.hedgate.net/blog/2005/04/05/workaround-for-query-analyzer-locale-problems/