Merging query into one efficiently

Transact-SQL
1

Hi

How do I merge these 2 queries into one efficiently ?

select a.docnum as Document_Number , a.docname as Document_Name ,a.version, DATEADD(HH,10,a.EDITWHEN) as Last_Modified, c.folderid as Folder_ID , c.Foldername as Folder_Name , c.folderpath as Folder_Path, a.AUTHOR as Document_Author,
case when t_alias  = 'Mime' then 'Email' else 'Document' end as 'DocType' , a.C13ALIAS as 'From', a.C14ALIAS as 'To', a.C15ALIAS as 'Copied_in_(CC)'
--into #doc_select
from mhgroup.docmaster a (nolock)   join mhgroup.project_items b (nolock) on a.docnum = b.item_id 
 join #folders_levels c on b.prj_id = c.Folderid 
--where a.ENTRYWHEN >= '2014-09-01 00:00:00.000' and a.ENTRYWHEN <= '2014-12-31 00:00:00.000'
where a.VERSION = (select MAX(VERSION ) from mhgroup.DOCMASTER s where s.DOCNUM = a.DOCNUM )
--group by c.Folderid , c.Foldername, c.FolderPath--, a.T_ALIAS
order by Document_Number

--drop table  #folders_levels

select count(*) ,item_id  , m1.version --, mhgroup.WhereIsDoc(item_id)
from mhgroup.docmaster m1 left outer join mhgroup.project_items m2 on m1.docnum = m2.item_id 
where c2alias = 'IT0055' and itemtype = 'D'
group by item_id , m1.version 
having count(*)> 1
2

The liberal use of NOLOCK in Autonomy (or Worksite, Filesite ... whatever they are calling it this week) DMS terrifies me ... we've rewritten all the 3rd party queries that used NOLOCK as they were garbage - and in the process converted an 18 hours (unworkable of course ...) overnight process to 5 minutes. But that's another story unrelated to your question. But something to be aware of. We've also eradicated incorrect metadata on documents (fed from a Matters records' database) in the process. I don't know whether that was poor quality programming, there was plenty of that in their original code, or whether it was the liberal use of NOLOCK missing rows on index blocks as they were being split during SQL operations - I used to get ALERT emails on ERROR=601 several times a day, we don't get any now :slight_smile: . We regard out DOCs as mission critical, and for us NOLOCK has no place there ...

You might find that your first query is more efficient if use use ROW_NUMBER() OVER() to get the "first" VERSION, sorted Descending, rather than your test against the subquery for MAX(VERSION). Not quite sure how your second query relates, but it might be that you could incorporate the COUNT into the same ROW_NUMBER() OVER() code block, if that's the case its likely to be very efficient.

I'm only guessing what you want to do - perhaps provide a COUNT of "c2alias = 'IT0055' and itemtype = 'D'" for each row in the first query?

Or do you just want to know if MORE than one exists? (might be more efficient ways of doing that than counting them ...)

maybe you could just add something like this into your query:

	CROSS APPLY
	(
		select count(*) AS MyCount
		from mhgroup.docmaster m1 
			left outer join mhgroup.project_items m2 
			on m1.docnum = m2.item_id 
		where 	m1.c2alias = 'IT0055' 
			and m2.itemtype = 'D'
			-- Join to outer query - not sure what you need
			AND m1.docnum = a.docnum
			AND m1.version = a.version 
	) AS X

That bothered me a bit too. Are you 10 hours away from the Server's time? What about daylight saving time? Just worried that hardwired like that it will be in queries all over the place and if it changes for any reason that would be a nightmare to fix Might be worth, at the least, having a VIEW onto DOCMASTER that had all columns, and also a calculated column for the 10-hours adjustment, so that if the formula ever needed to change you would only have to fix the view (and my guess would be that it would change to "add 10 hours for all documents up to dd-Mmm-yyyy and then XX hours for anything after that" when the server was moved to a different timezone!)

3

Hi Kristen
Thanks for your reply. All servers are UTC time and I'm based in Australia.