tregheter i VIEW

[HDSoftware]

Heisan folkens

Jeg har en datastruktur som er slik:

Country ->> PostCode ->> Addresses <<- Contact

 

Det betyr at en kontakt kan ha mange adresser, besøk, post, faktura etc.

 

Jeg har laget et view som henter adresser

SELECT	 dbo.Conaddress.GUID, dbo.Conaddress.RecType, dbo.Conaddress.RecProp,
	 dbo.Conaddress.CON_GUID, dbo.Conaddress.AType, dbo.Conaddress.Address1,
	 dbo.Conaddress.Address2, dbo.Conaddress.POS_GUID, dbo.Conaddress.City,
	 dbo.Conaddress.Province, dbo.Conaddress.Status, dbo.Postcode.Zip,
	 dbo.Postcode.Name AS PostofficeName, dbo.Country.ID AS CountryID,
	 dbo.Country.ID2 AS CountryID2, dbo.Country.Name AS CountryName
FROM	 dbo.Conaddress 
LEFT OUTER JOIN dbo.Postcode ON dbo.Postcode.GUID = dbo.Conaddress.POS_GUID 
LEFT OUTER JOIN dbo.Country ON dbo.Country.GUID = dbo.Postcode.COU_GUID

 

Videre bruker jeg dette VIEW'et inne i V_Contacts for å finne adressene4 til kontakten.

 

Problemet er at dette tar utrolig lang tid å kjøre. Det er totalt 13000 adresse i databasen og 9000 kontakter. For meg ser det ut til at SQL serveren må genererer V_Address like mange ganger som den representeres i V_COntact, og det er jo i og for seg forståelig.

 

Finnes det en enkel måte å få dette til å gå fort på? Med dette view'et jeg har så tar det opp mot 10 sekunder før jeg få\r respons. Må jo være noe fundamentalt feil med spørringene

Endret 18. februar 2009 av HDSoftware

[kaffenils]

For meg ser det ut til at SQL serveren må genererer V_Address like mange ganger som den representeres i V_COntact, og det er jo i og for seg forståelig

SQL Server gjør nok ikke det.

 

Hvis du SELECTer hele V_Contact så bygger SQL Server sannsynligvis en hash tabell "Hash Match"er de to tabellene/viewene.

 

Kjør

set showplan_xml on

og deretter kjører du spørringen som tar så lang tid. I resultatet vil du nå se xml-data. Kopier disse og post dem her så skal jeg se om jeg finner ut hva som foregår.

[HDSoftware]

Vet ikek om jeg forstod dette riktig. Prøvde å kjøre dette i en ny query og fikk som resultat en post med en URL til et XML dokument inneholdene følgende:

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.3042.00">
 <BatchSequence>
<Batch>
  <Statements>
	<StmtSimple StatementText="set showplan_xml on" StatementId="1" StatementCompId="1" StatementType="SET ON/OFF" />
  </Statements>
</Batch>
 </BatchSequence>
</ShowPlanXML>

Dernest kjørte jeg mitt SELECT script igjen, som ser slik ut:

SELECT	 dbo.Contact.FullName, dbo.Contact.FirstName, dbo.Contact.MiddleName, dbo.Contact.LastName, dbo.Contact.Initials, dbo.Contact.NickName, 
				  dbo.Contact.CompanyName, dbo.Contact.MarketingName, dbo.Contact.Attention, dbo.Contact.SortName, dbo.Contact.Title, dbo.Contact.Suffix, 
				  dbo.Conaddress.Address1, dbo.Conaddress.Address2, dbo.Postcode.Zip, dbo.Postcode.Name, Country_1.Name AS Expr1, Conphone_2.AreaCode, 
				  Conphone_2.LocalNumber, Conphone_2.Extension, Conaddress_1.Address1 AS Expr2, Conaddress_1.Address2 AS Expr3, Postcode_1.Zip AS Expr4, 
				  Postcode_1.Name AS Expr5, dbo.Country.Name AS Expr6, Conphone_1.AreaCode AS Expr7, Conphone_1.LocalNumber AS Expr8, Conphone_1.Extension AS Expr9, 
				  Conphone_3.AreaCode AS Expr10, Conphone_3.LocalNumber AS Expr11, Conphone_3.Extension AS Expr12, dbo.Conphone.AreaCode AS Expr13, 
				  dbo.Conphone.LocalNumber AS Expr14, dbo.Conphone.Extension AS Expr15, dbo.Contact.Gender, dbo.Contact.Status, dbo.Contact.Lng, dbo.Contact.DateOfBirth, 
				  dbo.Contact.DeathDay, dbo.Contact.CreatedBy_GUID, Contact_1.SortName AS Expr16
FROM		 dbo.Conaddress AS Conaddress_1 INNER JOIN
				  dbo.Country INNER JOIN
				  dbo.Postcode AS Postcode_1 ON dbo.Country.GUID = Postcode_1.COU_GUID ON Conaddress_1.POS_GUID = Postcode_1.GUID RIGHT OUTER JOIN
				  dbo.Contact ON Conaddress_1.GUID = dbo.Contact.MailingAddress_GUID LEFT OUTER JOIN
				  dbo.Conemail ON dbo.Contact.Email_GUID = dbo.Conemail.GUID LEFT OUTER JOIN
				  dbo.Confile ON dbo.Contact.Url_GUID = dbo.Confile.GUID LEFT OUTER JOIN
				  dbo.Contact AS Contact_1 ON dbo.Contact.CreatedBy_GUID = Contact_1.GUID LEFT OUTER JOIN
				  dbo.Conphone ON dbo.Contact.Fax_GUID = dbo.Conphone.GUID LEFT OUTER JOIN
				  dbo.Conphone AS Conphone_1 ON dbo.Contact.HomePhone_GUID = Conphone_1.GUID LEFT OUTER JOIN
				  dbo.Conphone AS Conphone_2 ON dbo.Contact.WorkPhone_GUID = Conphone_2.GUID LEFT OUTER JOIN
				  dbo.Conphone AS Conphone_3 ON dbo.Contact.MobilePhone_GUID = Conphone_3.GUID LEFT OUTER JOIN
				  dbo.Postcode INNER JOIN
				  dbo.Conaddress ON dbo.Postcode.GUID = dbo.Conaddress.POS_GUID INNER JOIN
				  dbo.Country AS Country_1 ON dbo.Postcode.COU_GUID = Country_1.GUID ON dbo.Contact.Address_GUID = dbo.Conaddress.GUID

som resulterte i følgende:

"Microsoft SQL Server 2005 XML Showplan"

og så viste den kun en record med en tom post. Gjorde jeg dette riktig tro?

 

edit: Den spørringen jeg har limt inn tar 20 sekunder å åpne!!

Endret 3. mars 2009 av HDSoftware

[kaffenils]

Merkelig. Raden skulle innehold et xml dokument med informasjon om hvordan spørringen eksekveres. Prøv å velge "Query"->"Result to"->"Result to text" fra menyen.

[kaffenils]

Jeg ser at spørringen er generert av SSMS sin query editor, og den har klart å velge en RIGHT OUTER JOIN. Slikt blir det ofte problemer av. Jeg har pr. dags dato aldri hatt behov for RIGHT JOIN. Prøv å skrive om spørringen (uten å bruke den grafiske query editoren) til kun å bruke LEFT JOIN.

 

Hvorfor lagrer du forresten telefonnummer i en egen tabell? Hva oppnår dere med det?

Hvorfor ikke bare lagre telefonnummeret rett i tabellen så slipper du alle LEFT JOIN spørringene som er med på å gjøre spørringen tregere enn nødvendig.

Av og til kan overdreven nomalisering ta livet av en database. Telefonnummergreia deres er et godt eksempel på dette.

[HDSoftware]

Vet ikke om dette hjalp noe som helst...

Microsoft SQL Server 2005 XML Showplan
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.3042.00"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT	 dbo.Contact.FullName, dbo.Contact.FirstName, dbo.Contact.MiddleName, dbo

(1 row(s) affected)

Virker veldig upresist og lite fortellende spør du meg

[kaffenils]

I text mode får du bare tilbake 2000 tegn (sånn ca). Jeg ville bare sjekke om du fikk ut noe i det hele tall. Bytt tilbake til Output To Grid. Når raden kommer opp så vil jeg at du prøver å klikke i kolonnen. Dorhåentligvis åpner en xml-viewer seg.

[HDSoftware]

Jeg ser at spørringen er generert av SSMS sin query editor, og den har klart å velge en RIGHT OUTER JOIN. Slikt blir det ofte problemer av. Jeg har pr. dags dato aldri hatt behov for RIGHT JOIN. Prøv å skrive om spørringen (uten å bruke den grafiske query editoren) til kun å bruke LEFT JOIN.

 

Hvorfor lagrer du forresten telefonnummer i en egen tabell? Hva oppnår dere med det?

Hvorfor ikke bare lagre telefonnummeret rett i tabellen så slipper du alle LEFT JOIN spørringene som er med på å gjøre spørringen tregere enn nødvendig.

Av og til kan overdreven nomalisering ta livet av en database. Telefonnummergreia deres er et godt eksempel på dette.

Ok. Ser den, men se for deg en listboks som har kolonne sortering. Se også for deg en listboks som er basert på dokumenter. Du vil gjerne se mottakerens navn i denne listeboksen. Mottagers navn er normalt ikke lagret i dokument recorden. Hvordan ville du løst den? Eneste jeg ser for meg her er noe slik:

select dok.navn, kun.navn
from dok
join kun on dok.kundeID = kun.KundeID
order by kun.navn

 

Dette er jo et typpisk, men mikroskopisk, eksempel på lesing av en tabell som sorteres på en annen. Og dette er jo akkurat den situasjonen vi sliter med. Og jeg må jo si at en kunde tabell på 9000 poster bør kunne annses som liten. Må da være en måte å speede opp dette på??

Endret 11. mars 2009 av HDSoftware

[kaffenils]

I en normalisert database må en selvfølgelig joine inn tabeller slik du sier. Problemet er at jo flere tabeller du må joine i tabellen jo lenger tid tar det å utføre spørringen.

 

HAr du klart å produsere xml-dokumentet jeg etterspurte?

[HDSoftware]

Har ikke klart å lage noe XML. Får det ikek til. Det blir bare det "tomme" dokumentet av en eller annen grunn. Skjønner ikke helt dette, men nå har jeg heller aldri prøvd det før.

 

Jeg skjønner at jo flere join's jo tregere. Faktisk har vi vel kommet frem til at vårt view er "for" avansert til at dette kan gå fort, selv om jeg kansje er litt skuffa over hvor "lite" som skal til for at en SQL server går i sirup. Men rett skal være rett. Dette er en VirtualServer instans med kunn 1GB minne. Samtidig går det to andre VM'er p åden maskinen som begge kjører IIS, men når det er sagt, kundenes enkle maskinpark er heller ikke noe serlig bedre.

[kaffenils]

1GB RAM er veldig lite, og jeg fryter nok at det er mye disk I/O på serveren som forårsaker at ting går tregt.

 

Siden du ikke får ut xml-planen så gjør vi det på en annen måte. I SSMS velger du 'Show actual execution plan' fra 'Query' menyen. Kjør spørringen og gå til 'Exection plan' fanen, høyreklikk og velg 'Save execution plan as...'. Innholdet i denne filen er det samme xml-dokumentet som du får veld set showplan_xml on. Du kan enten laste opp filen her, eller post xml dokumentet.

[HDSoftware]

Jeg finner ikke disse alternativene i SSMS. Kan det være fordi jeg kjører SSMS Express? Skal se om jeg finner en CD med SQL server på så jeg kan installere en full klient

[blackbrrd]

1GB RAM er veldig lite, og jeg fryter nok at det er mye disk I/O på serveren som forårsaker at ting går tregt.

 

Siden du ikke får ut xml-planen så gjør vi det på en annen måte. I SSMS velger du 'Show actual execution plan' fra 'Query' menyen. Kjør spørringen og gå til 'Exection plan' fanen, høyreklikk og velg 'Save execution plan as...'. Innholdet i denne filen er det samme xml-dokumentet som du får veld set showplan_xml on. Du kan enten laste opp filen her, eller post xml dokumentet.

1gb ram er først veldig lite hvis du har en database på mer enn 1gb minus ram os't bruker.

[kaffenils]

1gb ram er først veldig lite hvis du har en database på mer enn 1gb minus ram os't bruker.

Joda, og det er derfor jeg har bedt om å få se execution planen så jeg kan se hva som skjer, og hvilke deler av spørringen som går tregt.

 

I regnestykket ditt må du i tillegg ta hensyn til at SQL Server trenger minne til å lagre temporære tabeller til bl.a. sortering og hashing ifm. spørringer.