SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Time for Queries
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mikebird
Aged Yak Warrior

United Kingdom
529 Posts

Posted - 09/09/2009 :  06:12:56  Show Profile  Reply with Quote
Never spent so long waiting for queries to complete. 90% of my working hours. Can't proceed with investigations / refinements until results come back.

I'm used to 0 seconds... at a stretch, 2, 3 seconds, for millions of rows.

Now it's 30 minutes minimum, or 3 hrs, or overnight to get a few thousand rows. Sure I could improve performance, but need to find out about it ---> the queries! Just my 1.7Gb database on this 4 proc server. Other DBs are tiny, and pubs & Northwind are on there. It's medical.
No DBA rights - just select. I did create SearchAllTables.

What would you do?

I'm wasting my days waiting for queries to finish. Then I want to add one column and run again!!

khtan
In (Som, Ni, Yak)

Singapore
17430 Posts

Posted - 09/09/2009 :  06:44:12  Show Profile  Reply with Quote
maybe you can post your query here and somebody will help to optimize it


KH
Time is always against us

Go to Top of Page

mikebird
Aged Yak Warrior

United Kingdom
529 Posts

Posted - 09/09/2009 :  06:51:49  Show Profile  Reply with Quote
Nice idea! My whole point is - how can getting a few columns back take so long? The views are SIMPLE. Is there someone sat on a bench writing the results on paper and mailing it back to me? Use a courier

- PART A --
select distinct -- long-running! -- 11:58
AttendanceDate,
AttendanceType,
DiagnosisCode,
Sex,
DG.PatientNumber,
Diagnosis
from vrDemographic DG
inner join vrAttendance A on A.PatientNumber = DG.PatientNumber
inner join vrDiagnosis D on D.PatientNumber = DG.PatientNumber
where DiagnosisCode not like '%.%'
and AttendanceDate between '05/01/2009' and '04/09/2009'
order by 2
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17430 Posts

Posted - 09/09/2009 :  07:19:32  Show Profile  Reply with Quote
vrDemographic, vrAttendance, vrDiagnosis are tables or views ? If it is view, we will also need to see the view

what are the indexes available in the tables ?


KH
Time is always against us

Go to Top of Page

mikebird
Aged Yak Warrior

United Kingdom
529 Posts

Posted - 09/09/2009 :  07:37:18  Show Profile  Reply with Quote
vrDemographic simple:
SELECT PatientNumber, NHSNumber, NINumber, Forename, Initials, Surname, Soundex, MaidenName, SalutationCode, Salutation, BirthDate, CurrentAge, Sex,
Address, PostCode, NFA, TelephoneHome, TelephoneWork, TelephoneMobile, EMailAddress, PatientStatusCode, PatientStatus, MaritalStatusCode,
MaritalStatus, EthnicGroupCode, EthnicGroup, CountryOfBirthCode, CountryOfBirth, NationalityCode, Nationality, ReligionCode, Religion,
ResidencyStatusCode, ResidencyStatus, Deceased, CauseOfDeathCode, CauseOfDeath, DateOfDeath, PlaceOfDeathCode, PlaceOfDeath, NKSalutation,
NKSurname, NKForename, NKRelationshipCode, NKRelationship, NKFamilyMember, NKContactMethod, NKAddress, NKPostCode, NKTelephoneHome,
NKTelephoneWork, NKTelephoneMobile, NKEMailAddress, Nextofkin_Consent, Occupation, AreaCode, Area, PASNumber, ConsultantCode, Consultant,
BoroughCode, Borough, OverseasVisitorCode, OverseasVisitor, SexualOrientationCode, SexualOrientation, ContactNumber, CreatedUser, CreatedDate,
AmendedUser, AmendedDate, KnownAs, AlternateAddress, AlternatePostcode, AlternateTelephone, AlternateAreaCode, Agency, FirstLanguage,
DATofResidence, PCTCode, ResidencePCTName, HACode, ResidenceSHAName, GPName, PracticeName, PracticeAddressln1, PracticeAddressln2,
PracticeAddressln3, PracticeAddressln4, PracticeAddressln5, PracticePostcode, PracticeContactTel, KeyTag, ID
FROM dbo.vrExtendedDemographic

vrExtendedDemographic:
SELECT dbo.Demographic.PatientNumber, dbo.Demographic.NHSNumber, dbo.Demographic.NINumber, dbo.Demographic.Forename, dbo.Demographic.Initials,
dbo.Demographic.Surname, dbo.Demographic.Soundex, dbo.Demographic.MaidenName, dbo.GeneralLookups.LocalCode AS SalutationCode,
dbo.GeneralLookups.Description AS Salutation, dbo.Demographic.BirthDate, dbo.fnCOCalcAge(dbo.Demographic.BirthDate, GETDATE()) AS CurrentAge,
dbo.Demographic.Sex, dbo.Demographic.Address, dbo.Demographic.PostCode, GL7.Description AS NFA, dbo.Demographic.TelephoneHome,
dbo.Demographic.TelephoneWork, dbo.Demographic.TelephoneMobile, dbo.Demographic.EMailAddress, GL13.LocalCode AS PatientStatusCode,
GL13.Description AS PatientStatus, GL1.LocalCode AS MaritalStatusCode, GL1.Description AS MaritalStatus, GL2.Coded AS EthnicGroupID,
GL2.LocalCode AS EthnicGroupCode, GL2.Description AS EthnicGroup, GL14.LocalCode AS CountryOfBirthCode, GL14.Description AS CountryOfBirth,
GL3.LocalCode AS NationalityCode, GL3.Description AS Nationality, GL4.LocalCode AS ReligionCode, GL4.Description AS Religion,
GL15.LocalCode AS ResidencyStatusCode, GL15.Description AS ResidencyStatus, dbo.Demographic.Deceased,
GL16.LocalCode AS CauseOfDeathCode, GL16.Description AS CauseOfDeath, dbo.Demographic.DateOfDeath, GL17.LocalCode AS PlaceOfDeathCode,
GL17.Description AS PlaceOfDeath, GL5.Description AS NKSalutation, dbo.Demographic.NKSurname, dbo.Demographic.NKForename,
GL6.LocalCode AS NKRelationshipCode, GL6.Description AS NKRelationship,
CASE NKFamilyMember WHEN 1 THEN 'Yes' ELSE 'No' END AS NKFamilyMember, GL9.Description AS NKContactMethod,
dbo.Demographic.NKAddress, dbo.Demographic.NKPostCode, dbo.Demographic.NKTelephoneHome, dbo.Demographic.NKTelephoneWork,
dbo.Demographic.NKTelephoneMobile, dbo.Demographic.NKEMailAddress,
CASE NKConsent WHEN 1 THEN 'Yes' ELSE 'No' END AS Nextofkin_Consent, dbo.Demographic.Occupation, GL18.LocalCode AS AreaCode,
GL18.Description AS Area, dbo.Demographic.PASNumber, GL19.LocalCode AS ConsultantCode, GL19.Description AS Consultant,
GL20.LocalCode AS BoroughCode, GL20.Description AS Borough, GL21.LocalCode AS OverseasVisitorCode, GL21.Description AS OverseasVisitor,
GL22.LocalCode AS SexualOrientationCode, GL22.Description AS SexualOrientation, dbo.Demographic.ContactNumber, dbo.Demographic.CreatedUser,
dbo.Demographic.CreatedDate, dbo.Demographic.AmendedUser, dbo.Demographic.AmendedDate, dbo.Demographic.KnownAs,
dbo.Demographic.AlternateAddress, dbo.Demographic.AlternatePostcode, dbo.Demographic.AlternateTelephone, dbo.Demographic.AlternateAreaCode,
dbo.Locations.Description AS Agency, dbo.Locations.Coded AS AgencyCode, GL11.Description AS FirstLanguage,
GL12.Description AS DATofResidence, pct1.PCO AS PCTCode, pct2.Name AS ResidencePCTName, pct2.HACode, pct3.Name AS ResidenceSHAName,
OCSCodes.dbo.Practitioners.Name AS GPName, Main_2.Name AS PracticeName, Main_2.AddressLine1 AS PracticeAddressln1,
Main_2.AddressLine2 AS PracticeAddressln2, Main_2.AddressLine3 AS PracticeAddressln3, Main_2.AddressLine4 AS PracticeAddressln4,
Main_2.AddressLine5 AS PracticeAddressln5, Main_2.Postcode AS PracticePostcode, Main_2.Telephone AS PracticeContactTel,
'lvDemographic' AS KeyTag, dbo.Demographic.Counter AS ID
FROM dbo.Demographic LEFT OUTER JOIN
OCSCodes.dbo.Main AS Main_2 ON dbo.Demographic.GPPracticeCode COLLATE Latin1_General_CI_AS = Main_2.Code LEFT OUTER JOIN
OCSCodes.dbo.Practitioners ON dbo.Demographic.GPCode COLLATE Latin1_General_CI_AS = OCSCodes.dbo.Practitioners.Code LEFT OUTER JOIN
dbo.GeneralLookups ON dbo.Demographic.Salutation = dbo.GeneralLookups.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS GL1 ON dbo.Demographic.MaritalStatus = GL1.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS GL2 ON dbo.Demographic.EthnicGroup = GL2.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS GL3 ON dbo.Demographic.Nationality = GL3.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS GL4 ON dbo.Demographic.Religion = GL4.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS GL5 ON dbo.Demographic.NKSalutation = GL5.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS GL6 ON dbo.Demographic.NKRelationship = GL6.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS GL7 ON dbo.Demographic.NoFixedAbode = GL7.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS GL9 ON dbo.Demographic.NKContactMethod = GL9.Coded LEFT OUTER JOIN
dbo.Locations ON dbo.Demographic.Agency = dbo.Locations.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS GL11 ON dbo.Demographic.FirstLanguage = GL11.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS GL12 ON dbo.Demographic.DATofResidence = GL12.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS GL13 ON dbo.Demographic.PatientStatus = GL13.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS GL14 ON dbo.Demographic.CountryOfBirth = GL14.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS GL15 ON dbo.Demographic.ResidencyStatus = GL15.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS GL16 ON dbo.Demographic.CauseOfDeath = GL16.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS GL17 ON dbo.Demographic.PlaceOfDeath = GL17.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS GL18 ON dbo.Demographic.AreaCode = GL18.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS GL19 ON dbo.Demographic.Consultant = GL19.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS GL20 ON dbo.Demographic.Borough = GL20.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS GL21 ON dbo.Demographic.OverseasVisitor = GL21.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS GL22 ON dbo.Demographic.SexualOrientation = GL22.Coded LEFT OUTER JOIN
OCSCodes.dbo.DORs AS pct1 ON dbo.Demographic.PostCode COLLATE Latin1_General_CI_AS = pct1.Postcode LEFT OUTER JOIN
OCSCodes.dbo.Main AS pct2 ON pct1.PCO = pct2.Code LEFT OUTER JOIN
OCSCodes.dbo.Main AS pct3 ON pct2.HACode = pct3.Code
WHERE (dbo.Demographic.RecordDeleted = 0)


vrAttendance:
SELECT dbo.DailyAttendance.PatientNumber, dbo.DailyAttendance.AttendanceDate, dbo.fnCOCalcAge(dbo.Demographic.BirthDate,
dbo.DailyAttendance.AttendanceDate) AS AgeAtAttendance, dbo.fnCOTimeAsString(dbo.DailyAttendance.ArrivalTime) AS ArrivalTime,
dbo.fnCOTimeAsString(dbo.DailyAttendance.SeenTime) AS SeenTime, dbo.fnCOTimeAsString(dbo.DailyAttendance.FinishTime) AS FinishTime,
glAttendanceType.LocalCode AS AttendanceTypeCode, glAttendanceType.Description AS AttendanceType, dbo.DailyAttendance.Planned,
dbo.Locations.Coded AS LocationCode, dbo.Locations.Description AS Location, glArea.LocalCode AS AreaCode, glArea.Description AS Area,
glSeenBy.LocalCode AS SeenByCode, glSeenBy.Description AS SeenBy, glOutcome.LocalCode AS OutcomeCode, glOutcome.Description AS Outcome,
glFurtherActions.LocalCode AS FurtherActionsCode, glFurtherActions.Description AS FurtherActions, dbo.DailyAttendance.CreatedUser,
dbo.DailyAttendance.CreatedDate, dbo.DailyAttendance.AmendedUser, dbo.DailyAttendance.AmendedDate, 'lvAttendance' AS KeyTag,
dbo.DailyAttendance.Site, dbo.DailyAttendance.ID, dbo.DailyAttendance.ParentSite, dbo.DailyAttendance.ParentID, dbo.DailyAttendance.Counter
FROM dbo.DailyAttendance LEFT OUTER JOIN
dbo.GeneralLookups AS glFurtherActions ON dbo.DailyAttendance.FurtherActions = glFurtherActions.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS glOutcome ON dbo.DailyAttendance.Outcome = glOutcome.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS glSeenBy ON dbo.DailyAttendance.SeenBy = glSeenBy.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS glArea ON dbo.DailyAttendance.Area = glArea.Coded LEFT OUTER JOIN
dbo.Locations ON dbo.DailyAttendance.Location = dbo.Locations.Coded LEFT OUTER JOIN
dbo.GeneralLookups AS glAttendanceType ON dbo.DailyAttendance.AttendanceType = glAttendanceType.Coded LEFT OUTER JOIN
dbo.Demographic ON dbo.DailyAttendance.PatientNumber = dbo.Demographic.PatientNumber
WHERE (dbo.DailyAttendance.RecordDeleted = 0) AND (dbo.DailyAttendance.PatientNumber IN
(SELECT PatientNumber
FROM dbo.Demographic)) AND (dbo.DailyAttendance.ParentID IN
(SELECT ID
FROM dbo.Episode) OR
dbo.DailyAttendance.ParentID IS NULL)

vrDiagnosis modify is greyed-out
Go to Top of Page

mikebird
Aged Yak Warrior

United Kingdom
529 Posts

Posted - 09/09/2009 :  07:48:45  Show Profile  Reply with Quote
I may not have permissions to create anything. Although I did create SearchAllTables SP. Is it slow if I use the big, nested views, but select just one column from there? Is it faster if I recreate my own view(s) with a cut down selection of just 5 columns out of the hundreds, if I can?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17430 Posts

Posted - 09/09/2009 :  08:26:42  Show Profile  Reply with Quote
you problem is with vrExtendedDemographic and vrAttendance and maybe also vrDiagnosis. You have multiple outer reference to the same table GeneralLookups and this is going to kill your performance. Not the number of columns that you select in your query.

your best bet is to re-do your query and select directly from the base table and not from these killer view

Based on the information available, i coded only the first 2 columns. Use this as reference and code the rest of columns. It should definitely give you much better performance.

SELECT	distinct
	da.AttendanceDate,
	glAttendanceType.[Description]	AS AttendanceType
FROM	dbo.DailyAttendance da
	LEFT OUTER JOIN dbo.GeneralLookups AS glAttendanceType 	ON da.AttendanceType = glAttendanceType.Coded 
WHERE 	dbo.DailyAttendance.RecordDeleted 	= 0
AND	EXISTS
	(
		SELECT	*
		FROM	dbo.Demographic x
		WHERE	x.PatientNumber	= da.PatientNumber
	)
AND	(
		da.ParentID	IS NULL
	OR	EXISTS
		(
			SELECT	*
			FROM	dbo.Episode x
			WHERE	x.[ID]	= da.ParentID
		)
	)
and 	da.AttendanceDate between '05/01/2009' and '04/09/2009'



KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000