Time for Queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Time for Queries

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-09-09 : 06:12:56

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)

17689 Posts

Posted - 2009-09-09 : 06:44:12

maybe you can post your query here and somebody will help to optimize it

KH
[spoiler]Time is always against us[/spoiler]

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-09-09 : 06:51:49

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-09 : 07:19:32

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
[spoiler]Time is always against us[/spoiler]

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-09-09 : 07:37:18

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

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-09-09 : 07:48:45

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?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-09 : 08:26:42

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
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources