Oj! Inte undra på att den tar tid... börjar med att indentera eländet så man har en chans att se vad som händer:
SELECT
*
FROM
(
SELECT
tblCenter.centerId as cId,
tblCenter.centerName,
tblCenter.centerCity as cCity,
tblCenter.centerRegistrationGoal
FROM
tblregistrations
INNER JOIN tblCenter ON
tblCourses.centerId = tblCenter.centerId
LEFT JOIN tblCourses ON
tblCourses.courseId = tblregistrations.regCourseId AND
tblRegistrations.regTransactionStatus <> 'Failed' AND
tblRegistrations.regTransactionStatus <> 'Started'
WHERE
tblCourses.courseExpire >= '" + currentDate + "' AND
tblCenter.siteId = 1 AND
tblCenter.centerDanceCenter = 1
GROUP BY
tblCenter.centerCity
ORDER BY
tblCenter.centerCity
) as t1
LEFT JOIN (
SELECT
COUNT(tblregistrations.regId) as regCount,
tblCenter.centerId as cId
FROM
tblregistrations
INNER JOIN tblCenter ON
tblCourses.centerId = tblCenter.centerId
LEFT JOIN tblCourses ON
tblCourses.courseId = tblregistrations.regCourseId AND
tblRegistrations.regTransactionStatus <> 'Failed' AND
tblRegistrations.regTransactionStatus <> 'Started'
WHERE
tblCourses.courseExpire >= '" + currentDate + "' AND
tblCenter.siteId = 1 AND
tblCenter.centerDanceCenter = 1 AND
tblRegistrations.regPaymentMetohod = 'Faktura' OR
tblRegistrations.regPaymentMetohod = 'Payex'
GROUP BY
tblCenter.centerCity
ORDER BY
tblCenter.centerCity
) as t2 ON
t1.cId = t2.cId
LEFT JOIN (
SELECT
COUNT(tblregistrations.regId) as reserveCount,
tblCenter.centerId as cId
FROM
tblregistrations
INNER JOIN tblCenter ON
tblCourses.centerId = tblCenter.centerId
LEFT JOIN tblCourses ON
tblCourses.courseId = tblregistrations.regCourseId AND
tblRegistrations.regTransactionStatus <> 'Failed' AND
tblRegistrations.regTransactionStatus <> 'Started'
WHERE
tblCourses.courseExpire >= '" + currentDate + "' AND
tblCenter.siteId = 1 AND
tblCenter.centerDanceCenter = 1 AND
tblRegistrations.regPaymentMetohod = 'Reserv'
GROUP BY
tblCenter.centerCity
ORDER BY
tblCenter.centerCity
) as t3 ON
t1.cId = t3.cId
GROUP BY t1.cCity
Här finns åtskilligt att notera och förändra!
Notera först något "livsfarligt"
Du har i mittfrågan:
WHERE
tblCourses.courseExpire >= '" + currentDate + "' AND
tblCenter.siteId = 1 AND
tblCenter.centerDanceCenter = 1 AND
tblRegistrations.regPaymentMetohod = 'Faktura' OR
tblRegistrations.regPaymentMetohod = 'Payex'
Blanda aldrig AND och OR utan parenteser! Vad säger du här? Jo att antingen ska detta gälla:
tblCourses.courseExpire >= '" + currentDate + "' AND
tblCenter.siteId = 1 AND
tblCenter.centerDanceCenter = 1 AND
tblRegistrations.regPaymentMetohod = 'Faktura'
eller detta:
tblRegistrations.regPaymentMetohod = 'Payex'
Men jag gissar att du menar:
WHERE
tblCourses.courseExpire >= '" + currentDate + "' AND
tblCenter.siteId = 1 AND
tblCenter.centerDanceCenter = 1 AND
(
tblRegistrations.regPaymentMetohod = 'Faktura' OR
tblRegistrations.regPaymentMetohod = 'Payex'
)
Du gör väsentligen exakt samma JOINer på exakt samma tre tabeller tre skilda gånger
i stället för att göra JOINen bara en gång och sedan återanvända resultatet genom att spara undan JOIN-resultatet i en temptabell.
Du vill alltså, väsentligen, spara undan resultatet av:
SELECT
tblCenter.centerId,
tblCenter.centerName,
tblCenter.centerCity,
tblCenter.centerRegistrationGoal,
tblCourses.centerId,
tblCourses.courseId,
tblRegistrations.regCourseId,
tblRegistrations.regTransactionStatus,
tblRegistrations.regId,
tblRegistrations.regPaymentMetohod
FROM
tblregistrations
INNER JOIN tblCenter ON
tblCourses.centerId = tblCenter.centerId
LEFT JOIN tblCourses ON
tblCourses.courseId = tblregistrations.regCourseId AND
tblRegistrations.regTransactionStatus <> 'Failed' AND
tblRegistrations.regTransactionStatus <> 'Started'
WHERE -- Följande villkor finns i alla tre fälten
tblCourses.courseExpire >= '" + currentDate + "' AND
tblCenter.siteId = 1 AND
tblCenter.centerDanceCenter = 1
vilket är precis de kolumner du behöver för samtliga tre subfrågor ovan i SELECT, ON och WHERE-delarna.
Men, då kommer vi till ett stort problem för den här koden. Så här bör inte en serie JOINer se ut! Se till att JOIN:a tabeller i den ordning du kan knyta ihop dem i varandra, så att du hela tiden kan matcha "nästa" tabell mot de som står högre upp! Då får du med så få rader som möjligt i JOINen och ju färre rader, ju fortare går det. Detta därför att DB:n direkt ser om det finns matcher till den här raden eller inte och behöver bara titta på (åtminstone) den första tabellen en gång.
Visserligen spelar det roll om tabellerna hämtas ut med INNER JOIN eller LEFT JOIN och generellt bör alla INNER JOINer stå före LEFT JOINerna, men då måste man också kunna binda ihop INNER JOINade tabeller innan man blandar in LEFT JOINade, och det gör inte du. Både tblRegistrations och tblCenter JOINas mot tblCourses som kommer in med LEFT JOIN, vilket gör resultatet tveksamt om du verkligen har en INNER JOIN där du tror? Samtidigt kräver du i WHERE att både tabellen tblCenter och tblCourses ska ha andra värden än NULL på vissa av sina kolumner, varför du ändå måste ha matchning hela vägen. En INNER JOIN är alltid mindre resurskrävande än en LEFT JOIN så vi ersätter med INNER JOIN.
Vad använder du för DBMS? Jag är inte säker på om syntaxen för temptabeller ser likadan ut överallt? Exemplet nedan använder Microsoft SQL Servers T-SQL, men det finns motsvarigheter i andra system.
En första del i lösningen blir därför:
SELECT
R.regId,
R.regTransactionStatus,
R.regPaymentMetohod, -- Borde det vara Method månne?
Co.CourseId,
Ctr.centerId,
Ctr.centerName,
Ctr.centerCity,
Ctr.centerRegistrationGoal,
Ctr.centerId
INTO #CCR
FROM
tblregistrations R
INNER JOIN tblCourses Co ON
Co.courseId = R.regCourseId AND
R.regTransactionStatus <> 'Failed' AND
R.regTransactionStatus <> 'Started'
INNER JOIN tblCenter Ctr ON
Co.centerId = Ctr.centerId
WHERE
Co.courseExpire >= '" + currentDate + "' AND
Ctr.siteId = 1 AND
Ctr.centerDanceCenter = 1
Nu skapar du en temptabell #CCR (namnval efter CoursesCenterRegistrations) där varje kolumn heter samma som i ursprungsfrågan.
Eftersom vi nu bara har INNER JOINer och vi kräver att Co.courseId = R.regCourseId och Co.centerId = Ctr.centerId, så behöver vi inte ta ut alla fyra dessa kolumner, det räcker med en ur varje jämförelse. (Ju färre kolumner, ju snabbare frågor.)
I och med att du skapar en temptabell måste du också komma ihåg att ta bort den när du är färdig med den med:
DROP TABLE #CCR
Nu kan vi, till att börja med, förenkla din ursprungliga fråga till:
SELECT
*
FROM
(
SELECT
centerId as cId,
centerName,
centerCity as cCity,
centerRegistrationGoal
FROM
#CCR
GROUP BY
centerCity
ORDER BY
centerCity
) as t1
LEFT JOIN (
SELECT
COUNT(regId) as regCount,
centerId as cId
FROM
#CCR
WHERE
regPaymentMetohod = 'Faktura' OR
regPaymentMetohod = 'Payex'
GROUP BY
centerCity
ORDER BY
centerCity
) as t2 ON
t1.cId = t2.cId
LEFT JOIN (
SELECT
COUNT(regId) as reserveCount,
centerId as cId
FROM
#CCR
WHERE
regPaymentMetohod = 'Reserv'
GROUP BY
centerCity
ORDER BY
centerCity
) as t3 ON
t1.cId = t3.cId
GROUP BY t1.cCity
Det finns mer att göra, men detta var början.
Sedan ska vi titta lite närmare på alla dessa GROUP BY.
GROUP BY används när man vill räkna ut antal, min, max, summa eller medel av något och gruppera per en eller flera kolumner. Vill man inte det ska man inte använda GROUP BY som är en jobbig operation för SQL Servern. Är man bara ute efter att slippa dubbletter använder man DISTINCT.
I t1 ovan görs inga uträkningar alls, skippa GROUP BY.
I slutresultatet görs inga uträkningar alls, skippa GROUP BY.
I t1, t2 och t3 kvittar det hur raderna är ordnade det är bara i slutresultatet du bryr dig om detta. Ordna därför om resultatet i slutet en gång, inte tre gånger.
Det finns ingen anledning att ha t1 som ett subuttryck, däremot allt skäl att inte ha det. Ju färre subuttryck i FROM, ju fortare går det generellt sätt. (Det finns gånger då det är berättigat och snabbare upp, men man ska veta vad man gör.
Resultat:
SELECT
R.regId,
R.regTransactionStatus,
R.regPaymentMetohod, -- Borde det vara Method månne?
Co.CourseId,
Ctr.centerId,
Ctr.centerName,
Ctr.centerCity,
Ctr.centerRegistrationGoal,
Ctr.centerId
INTO #CCR
FROM
tblregistrations R
INNER JOIN tblCourses Co ON
Co.courseId = R.regCourseId AND
R.regTransactionStatus <> 'Failed' AND
R.regTransactionStatus <> 'Started'
INNER JOIN tblCenter Ctr ON
Co.centerId = Ctr.centerId
WHERE
Co.courseExpire >= '" + currentDate + "' AND
Ctr.siteId = 1 AND
Ctr.centerDanceCenter = 1
SELECT
t1.centerId as cId,
t1.centerName,
t1.centerCity as cCity,
t1.centerRegistrationGoal,
ISNULL(t2.regCount, 0) as regCount,
ISNULL(t3.reserveCount, 0) as reserveCount
FROM
#CCR t1
LEFT JOIN (
SELECT
COUNT(regId) as regCount,
centerId as cId
FROM
#CCR
WHERE
regPaymentMetohod = 'Faktura' OR
regPaymentMetohod = 'Payex'
GROUP BY
centerCity
) as t2 ON
t1.centerId = t2.cId
LEFT JOIN (
SELECT
COUNT(regId) as reserveCount,
centerId as cId
FROM
#CCR
WHERE
regPaymentMetohod = 'Reserv'
GROUP BY
centerCity
) as t3 ON
t1.centerId = t3.cId
ORDER BY
t1.centerCity
-- Städa
DROP TABLE #CCR
Det här bör gå AVSEVÄRT snabbare.
Sedan finns det ju mer avancerade metoder att ta till: index, optimera join-ordningar mm men då måste man veta mer om hur stora tabellerna är och hur de används.
