Different execution plans for same query

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

Transact-SQL (2000)

Different execution plans for same query

Author

Topic

thenearfuture
Starting Member

35 Posts

Posted - 2007-08-21 : 07:38:15

A coworker sent me two of his queries to try to find out why one takes 4 seconds and the other takes more than 4 minutes. Both queries are exactly the same in the SELECT and FROM clauses. The only difference is the right side of items in the WHERE clause.

The actual queries are each 26 lines long and have inner joins on 13 tables and one view. Instead of putting them here, I've written just a sample representation of the problem to make it easier for people to see what I'm talking about. Please let me know if I need to post the actual queries, table structures, index information, execution plans, etc.

Query 1 (4 seconds):


SELECT	x.a, x.b, x.c, y.b, y.c, z.b
FROM		x INNER JOIN y ON x.a = y.a INNER JOIN
		z ON z.c = x.c
WHERE	x.b = "one" AND
		y.c = "two" AND
		x.date > CONVERT(DATETIME, '2007-5-20 00:00:00', 102) AND
		x.date < CONVERT(DATETIME, '2007-5-20 12:00:00', 102)

Query 2 (4 minutes):


SELECT	x.a, x.b, x.c, y.b, y.c, z.b
FROM		x INNER JOIN y ON x.a = y.a INNER JOIN
		z ON z.c = x.c
WHERE	x.b = "three" AND
		y.c = "four" AND
		x.date > CONVERT(DATETIME, '2007-8-20 00:00:00', 102) AND	
		x.date < CONVERT(DATETIME, '2007-8-20 12:00:00', 102)

(NOTE: The queries were written in Enterprise Manager, which automatically does the "CONVERT(DATETIME...)" thing - we are in Europe).

I've looked at the actual execution plans for both queries, and they are significantly different.

Does anyone have any idea why the slight change in the WHERE clause would make the query optimizer choose a much slower execution plan?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2007-08-21 : 07:52:47

Both queries are almost same except the value for condition..

Did you noted the time difference for the above two select statements or the said time difference is for the complete select statement ( 26 lines statement).

Post the complete query so we can analyze it.

thenearfuture
Starting Member

35 Posts

Posted - 2007-08-21 : 08:11:35

Here are the two actual queries.

The queries are basically the same. They select the same columns from the same tables, except with different values. As you can see, only the right side of the "WHERE" clause is different.

Since the queries are the same in everything except the actual values of the columns, I'm very confused as to why SQL Server 2000 chooses to execute them differently.

Query 1 (4 seconds):


SELECT DISTINCT 
                     	tbl_ACTCourt.vch_Name AS Fogedret, 
		tbl_ACTCourt.vch_Address AS Adresse, 
		tbl_ACTZipCity.vch_ZipCode AS Postnr, 
		tbl_ACTZipCity.vch_City AS [By], 
                      	tbl_CSEContribution.big_CSECase_FK AS Sag, 
		tbl_ACTActor.vch_LegalName AS Kreditor, 
		'mod' AS Mod, 
		vw_NameAndAddress.vch_Name AS Debitor, 
                      	tbl_CSEContributionEntry_New.mny_Amount AS Beløb, 
		'kr.' AS kr

FROM         tbl_CSEContributionEntry_New INNER JOIN
                      tbl_CSEContribution INNER JOIN
                      vw_NameAndAddress ON tbl_CSEContribution.big_CSECase_FK = vw_NameAndAddress.big_CSECaseId ON 
                      tbl_CSEContributionEntry_New.int_CSEContribution_FK = tbl_CSEContribution.int_CSEContributionId INNER JOIN
                      tbl_CSECase ON tbl_CSEContribution.big_CSECase_FK = tbl_CSECase.big_CSECaseId INNER JOIN
                      tbl_ACTMunicipality INNER JOIN
                      tbl_CSEAddress ON tbl_ACTMunicipality.int_ACTMunicipalityId = tbl_CSEAddress.int_ACTMunicipality_FK INNER JOIN
                      tbl_ACTJurisdiction ON tbl_ACTMunicipality.int_ACTJurisdiction_FK = tbl_ACTJurisdiction.int_ACTJurisdictionId INNER JOIN
                      tbl_ACTCourt ON tbl_ACTJurisdiction.int_ACTJurisdictionId = tbl_ACTCourt.int_ACTJurisdiction_FK INNER JOIN
                      tbl_ACTZipCity ON tbl_ACTCourt.int_ACTZipCity_FK = tbl_ACTZipCity.int_ACTZipCityId ON 
                      tbl_CSECase.int_CSEAddress_FK = tbl_CSEAddress.int_CSEAddressId INNER JOIN
                      tbl_ACTProduct ON tbl_CSECase.int_ACTProduct_FK = tbl_ACTProduct.int_ACTProductId INNER JOIN
                      tbl_ACTCaseType ON tbl_ACTProduct.int_ACTCaseType_FK = tbl_ACTCaseType.int_ACTCaseTypeId INNER JOIN
                      tbl_ACTCreditor ON tbl_ACTCaseType.int_ACTCreditor_FK = tbl_ACTCreditor.int_ACTCreditorId INNER JOIN
                      tbl_ACTActor ON tbl_ACTCreditor.int_ACTCreditorId = tbl_ACTActor.int_ACTActorId

WHERE     	(tbl_CSEContribution.dtm_Created > CONVERT(DATETIME, '2007-5-7 00:00:00', 102)) AND 
                       	(tbl_CSEContribution.dtm_Created < CONVERT(DATETIME, '2007-5-7 12:00:00', 102)) AND
		(tbl_CSEContributionEntry_New.chr_CSEContributionEntryPart_FK = 'betpakr') AND 
		(tbl_ACTCourt.chr_ACTCourtType_FK = 'fogedret') AND 
		(tbl_CSEContribution.big_CSECase_FK IN
                          (SELECT     big_ObjectId
                            FROM          tbl_PRDOutput
                            WHERE      (int_DOCDocument_FK = 231) AND (dtm_Merge > CONVERT(DATETIME, '2007-5-7 00:00:00', 102)) AND (bit_Cancelled = 0)))

ORDER BY 	tbl_ACTCourt.vch_Name, vw_NameAndAddress.vch_Name

Query 2 (4 minutes):


SELECT DISTINCT 
                      	tbl_ACTCourt.vch_Name AS Fogedret, 
		tbl_ACTCourt.vch_Address AS Adresse, 
		tbl_ACTZipCity.vch_ZipCode AS Postnr, 
		tbl_ACTZipCity.vch_City AS [By], 
                     	tbl_CSEContribution.big_CSECase_FK AS Sag, 
		tbl_ACTActor.vch_LegalName AS Kreditor, 
		'mod' AS Mod,
		vw_NameAndAddress.vch_Name AS Debitor, 
                     	tbl_CSEContributionEntry_New.mny_Amount AS Beløb, 
		'kr.' AS kr

FROM         tbl_CSEContributionEntry_New INNER JOIN
                      tbl_CSEContribution INNER JOIN
                      vw_NameAndAddress ON tbl_CSEContribution.big_CSECase_FK = vw_NameAndAddress.big_CSECaseId ON 
                      tbl_CSEContributionEntry_New.int_CSEContribution_FK = tbl_CSEContribution.int_CSEContributionId INNER JOIN
                      tbl_CSECase ON tbl_CSEContribution.big_CSECase_FK = tbl_CSECase.big_CSECaseId INNER JOIN
                      tbl_ACTMunicipality INNER JOIN
                      tbl_CSEAddress ON tbl_ACTMunicipality.int_ACTMunicipalityId = tbl_CSEAddress.int_ACTMunicipality_FK INNER JOIN
                      tbl_ACTJurisdiction ON tbl_ACTMunicipality.int_ACTJurisdiction_FK = tbl_ACTJurisdiction.int_ACTJurisdictionId INNER JOIN
                      tbl_ACTCourt ON tbl_ACTJurisdiction.int_ACTJurisdictionId = tbl_ACTCourt.int_ACTJurisdiction_FK INNER JOIN
                      tbl_ACTZipCity ON tbl_ACTCourt.int_ACTZipCity_FK = tbl_ACTZipCity.int_ACTZipCityId ON 
                      tbl_CSECase.int_CSEAddress_FK = tbl_CSEAddress.int_CSEAddressId INNER JOIN
                      tbl_ACTProduct ON tbl_CSECase.int_ACTProduct_FK = tbl_ACTProduct.int_ACTProductId INNER JOIN
                      tbl_ACTCaseType ON tbl_ACTProduct.int_ACTCaseType_FK = tbl_ACTCaseType.int_ACTCaseTypeId INNER JOIN
                      tbl_ACTCreditor ON tbl_ACTCaseType.int_ACTCreditor_FK = tbl_ACTCreditor.int_ACTCreditorId INNER JOIN
                      tbl_ACTActor ON tbl_ACTCreditor.int_ACTCreditorId = tbl_ACTActor.int_ACTActorId

WHERE     	(tbl_CSEContribution.dtm_Created > CONVERT(DATETIME, '2007-08-15 00:00:00', 102)) AND 
		(tbl_CSEContribution.dtm_Created < CONVERT(DATETIME, '2007-08-15 14:00:00', 102)) AND 
		(tbl_CSEContributionEntry_New.chr_CSEContributionEntryPart_FK = 'fogedret') AND 
		(tbl_ACTCourt.chr_ACTCourtType_FK = 'fogedret') AND 
		(tbl_CSEContribution.big_CSECase_FK IN
                          (SELECT     big_ObjectId
                            FROM          tbl_PRDOutput
                            WHERE      (int_DOCDocument_FK = 328) AND (dtm_Merge > CONVERT(DATETIME, '2007-8-15 00:00:00', 102)) AND (bit_Cancelled = 0)))

ORDER BY 	tbl_ACTCourt.vch_Name, vw_NameAndAddress.vch_Name

pootle_flump

1064 Posts

Posted - 2007-08-21 : 08:24:33

What are the two plans? How manay rows returned by the two queries referenced in the IN statements? What is the distribution of values for tbl_CSEContributionEntry_New.chr_CSEContributionEntryPart_FK?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 08:38:18

This might perform better

WHERE		tbl_CSEContribution.dtm_Created >= '2007-05-07'
		AND tbl_CSEContribution.dtm_Created < '2007-05-07 12:00:00'
		AND tbl_CSEContributionEntry_New.chr_CSEContributionEntryPart_FK = 'betpakr'
		AND tbl_ACTCourt.chr_ACTCourtType_FK = 'fogedret'
		AND EXISTS	(
					SELECT		*
					FROM		tbl_PRDOutput AS x
					WHERE		x.int_DOCDocument_FK = 231
							AND x.dtm_Merge >= '2007-05-07'
							AND x.bit_Cancelled = 0
							AND x.big_ObjectId = tbl_CSEContribution.big_CSECase_FK
				)

And since you already is using DISTINCT this may be equal fast

INNER JOIN	tbl_PRDOutput AS x ON x.big_ObjectId = tbl_CSEContribution.big_CSECase_FK
WHERE		tbl_CSEContribution.dtm_Created >= '2007-05-07'
		AND tbl_CSEContribution.dtm_Created < '2007-05-07 12:00:00'
		AND tbl_CSEContributionEntry_New.chr_CSEContributionEntryPart_FK = 'betpakr'
		AND tbl_ACTCourt.chr_ACTCourtType_FK = 'fogedret'
		AND x.int_DOCDocument_FK = 231
		AND x.dtm_Merge >= '2007-05-07'
		AND x.bit_Cancelled = 0

E 12°55'05.25"
N 56°04'39.16"

thenearfuture
Starting Member

35 Posts

Posted - 2007-08-21 : 08:41:50

A.) What are the two plans?

Execution plans (PDF):
Query 1: http://download.yousendit.com/95C9A6E711A369B8
Query 2: http://download.yousendit.com/16172360203493AD

B.) How many rows returned by the two queries referenced in the IN statements?

I ran the two queries just by themselves and got:
Query 1: 581 rows
Query 2: 323 rows

C.) What is the distribution of values for tbl_CSEContributionEntry_New.chr_CSEContributionEntryPart_FK?

There are 6 values:
FOGEDRET
LGLFEE
POLITIFRST
BETPAKR
REGISTER
EXPENSE

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 08:46:55

The reason for the different number of records is

1) Different tbl_CSEContribution.dtm_Created
2) Different int_DOCDocument_FK

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 08:48:39

Seems that execution plans are the same.

E 12°55'05.25"
N 56°04'39.16"

thenearfuture
Starting Member

35 Posts

Posted - 2007-08-21 : 08:55:32

I'm sorry I haven't been clear enough. The queries are supposed to produce different results. My question is why the same basic query (using the same structure, just changing the values of the WHERE clause) produces two different execution plans.

In other words, the queries are the same except for the values of the following fields in the WHERE clause:

1.) tbl_CSEContribution.dtm_Created = (different dates)
2.) tbl_CSEContributionEntry_New.chr_CSEContributionEntryPart_FK = (betpakr/fogedret)
3.) tbl_PRDOutput.int_DOCDocument_FK = (231/328)
4.) tbl_PRDOutput.dtm_Merge = (different dates)

How come those differences change the way the queries are run?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 09:04:33

Also, I found one table that was not necessary for you, tbl_ACTJurisdiction.

SELECT DISTINCT	tbl_ACTCourt.vch_Name AS Fogedret, 
		tbl_ACTCourt.vch_Address AS Adresse, 
		tbl_ACTZipCity.vch_ZipCode AS Postnr, 
		tbl_ACTZipCity.vch_City AS [By], 
		tbl_CSEContribution.big_CSECase_FK AS Sag, 
		tbl_ACTActor.vch_LegalName AS Kreditor, 
		'mod' AS Mod, 
		vw_NameAndAddress.vch_Name AS Debitor, 
		tbl_CSEContributionEntry_New.mny_Amount AS Beløb, 
		'kr.' AS kr
FROM		tbl_CSEContributionEntry_New
INNER JOIN	tbl_CSEContribution ON tbl_CSEContributionEntry_New.int_CSEContribution_FK = tbl_CSEContribution.int_CSEContributionId
INNER JOIN	vw_NameAndAddress ON tbl_CSEContribution.big_CSECase_FK = vw_NameAndAddress.big_CSECaseId 
INNER JOIN	tbl_CSECase ON tbl_CSEContribution.big_CSECase_FK = tbl_CSECase.big_CSECaseId
INNER JOIN	tbl_CSEAddress ON tbl_CSECase.int_CSEAddress_FK = tbl_CSEAddress.int_CSEAddressId 
INNER JOIN	tbl_ACTMunicipality ON tbl_ACTMunicipality.int_ACTMunicipalityId = tbl_CSEAddress.int_ACTMunicipality_FK
INNER JOIN	tbl_ACTCourt ON tbl_ACTMunicipality.int_ACTJurisdiction_FK = tbl_ACTCourt.int_ACTJurisdiction_FK
INNER JOIN	tbl_ACTZipCity ON tbl_ACTCourt.int_ACTZipCity_FK = tbl_ACTZipCity.int_ACTZipCityId
INNER JOIN	tbl_ACTProduct ON tbl_CSECase.int_ACTProduct_FK = tbl_ACTProduct.int_ACTProductId
INNER JOIN	tbl_ACTCaseType ON tbl_ACTProduct.int_ACTCaseType_FK = tbl_ACTCaseType.int_ACTCaseTypeId
INNER JOIN	tbl_ACTCreditor ON tbl_ACTCaseType.int_ACTCreditor_FK = tbl_ACTCreditor.int_ACTCreditorId
INNER JOIN	tbl_ACTActor ON tbl_ACTCreditor.int_ACTCreditorId = tbl_ACTActor.int_ACTActorId
INNER JOIN	tbl_PRDOutput ON tbl_PRDOutput.big_ObjectId = tbl_CSEContribution.big_CSECase_FK
WHERE		tbl_CSEContribution.dtm_Created >= '2007-05-07'
		AND tbl_CSEContribution.dtm_Created < '2007-05-07 12:00:00'
		AND tbl_CSEContributionEntry_New.chr_CSEContributionEntryPart_FK = 'betpakr'
		AND tbl_ACTCourt.chr_ACTCourtType_FK = 'fogedret'
		AND tbl_PRDOutput.int_DOCDocument_FK = 231
		AND tbl_PRDOutput.dtm_Merge >= '2007-05-07'
		AND tbl_PRDOutput.bit_Cancelled = 0
ORDER BY 	tbl_ACTCourt.vch_Name,
		vw_NameAndAddress.vch_Name

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 09:07:04

I suppose you are using Enterprise version of SQL Server?
Then the server is more thourough when executing the query and sometimes (as pootle_flump said) chooses another plan depending on the data.
Standard edition do not do this.

You could try add a query hint at the end of the query!
OPTION (FORCE ORDER)

That can make the difference because you now tell SQL Server to always JOIN in the order as the tables are written in the query.

E 12°55'05.25"
N 56°04'39.16"

thenearfuture
Starting Member

35 Posts

Posted - 2007-08-21 : 09:16:06

Peso - I tried the famous hint about removing the "IN" subquery from the WHERE clause, but it did not improve the performance, unfortunately.

Also, you mentioned that the two execution plans seem to be the same. If you have a couple of minutes, could you please explain that? I'm still too green when it comes to optimizer to figure out why they're the same, even though they look different.

I'm going to try the optimizer hint right now.

UPDATE: The query hint did the trick! Thank you Peso and pootle_flump for your patience, help, and expertise.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 09:29:20

Next time when posting query plans, use this:

set showplan_text on

<your query here>

set showplan_text off

Now copy the StmtText and post here with the CODE tags.

E 12°55'05.25"
N 56°04'39.16"

pootle_flump

1064 Posts

Posted - 2007-08-21 : 09:31:25

quote:
Originally posted by thenearfuture

UPDATE: The query hint did the trick! Thank you Peso and pootle_flump for your patience, help, and expertise.

In that case the plans must not have been the same.

Without being able to Drill down into the pdf, often the results of SET STATISTICS PROFILE ON are more useful as we can see exactly what is going on.

Anyway - glad you got it sorted.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 09:31:42

Now with the Query Hint, do any of the tricks above (EXISTS or extra INNER JOIN) do any difference?
Also, du you get same result when removing the "tbl_ACTJurisdiction" table?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 09:34:23

Pootle and thenearfuture, have a look at this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=87056
for more strange things with query plans.

E 12°55'05.25"
N 56°04'39.16"

thenearfuture
Starting Member

35 Posts

Posted - 2007-08-21 : 09:53:16

Thank you both for the info about how to post execution plans. I will make sure to do that in the future.

Peso - As for your questions:
1.) Yes, the results are the same without tbl_ACTJurisdiction. Nice catch!
2.) Yes, the query hint makes the tricks faster.

As a side note, I really wasn't expecting to have the entire query optimized here. I just wanted to know why the execution plans were so strange, so the new and improved query was a fantastic surprise. You guys are a fantastic resource and a great way to learn.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 10:09:32

Which "trick" was faster? Exists or INNER JOIN?
How is the speed for the fastest of them (combined with query hint force order) and with the table mentioned above removed, versus the original query?

E 12°55'05.25"
N 56°04'39.16"

thenearfuture
Starting Member

35 Posts

Posted - 2007-08-21 : 10:44:37

I will answer these questions in detail tomorrow morning, as I am running out of time today.

Update: These are the results from our development server, taken during off-peak hours.

The results in my original post were from the production server during business hours, which is why the times are different.

Query 2:


		----------------------------------------------------------
		|  Original  |  Exists  |  Inner Join  |  Table removed  |
--------------------------------------------------------------------------
|  No Hint	|    3:09    |   3:10   |    3:31      |      3:29       |
--------------------------------------------------------------------------
|  Hint         |    0:17    |   0:17   |    0:06      |      0:04       |
--------------------------------------------------------------------------

And, if anyone is curious, here is my second attempt at posting the execution plans for the original queries.

Query 1: (fast)

SELECT DISTINCT tbl_ACTCourt.vch_Name AS Fogedret,     tbl_ACTCourt.vch_Address AS Adresse,     tbl_ACTZipCity.vch_ZipCode AS Postnr,     tbl_ACTZipCity.vch_City AS [By],     tbl_CSEContribution.big_CSECase_FK AS Sag,     tbl_ACTActor.vch_LegalName AS Kred
  |--Sort(DISTINCT ORDER BY:([tbl_ACTCourt].[vch_Name] ASC, [tbl_CSECase].[vch_Name] ASC, [tbl_ACTCourt].[vch_Address] ASC, [tbl_ACTZipCity].[vch_ZipCode] ASC, [tbl_ACTZipCity].[vch_City] ASC, [tbl_CSEContribution].[big_CSECase_FK] ASC, [tbl_ACTActor].[vch
       |--Nested Loops(Inner Join, OUTER REFERENCES:([tbl_ACTCreditor].[int_ACTCreditorId]))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([tbl_ACTCaseType].[int_ACTCreditor_FK]))
            |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tbl_ACTProduct].[int_ACTCaseType_FK]))
            |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tbl_ACTCourt].[int_ACTZipCity_FK]) WITH PREFETCH)
            |    |    |    |--Hash Match(Inner Join, HASH:([tbl_ACTCourt].[int_ACTJurisdiction_FK])=([tbl_ACTMunicipality].[int_ACTJurisdiction_FK]))
            |    |    |    |    |--Clustered Index Scan(OBJECT:([EnLight].[dbo].[tbl_ACTCourt].[PK_tbl_ACTCourt]), WHERE:([tbl_ACTCourt].[chr_ACTCourtType_FK]='fogedret'))
            |    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tbl_CSECase].[int_ACTProduct_FK]))
            |    |    |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([tbl_CSEAddress].[int_ACTMunicipality_FK]))
            |    |    |    |         |    |--Hash Match(Inner Join, HASH:([tbl_CSEContribution].[int_CSEContributionId])=([tbl_CSEContributionEntry_New].[int_CSEContribution_FK]))
            |    |    |    |         |    |    |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([tbl_CSEContribution].[big_CSECase_FK])=([tbl_CSECase].[big_CSECaseId]), RESIDUAL:([tbl_CSECase].[big_CSECaseId]=[tbl_CSEContribution].[big_CSECase_FK]))
            |    |    |    |         |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tbl_CSECase].[int_CSEAddress_FK]) WITH PREFETCH)
            |    |    |    |         |    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tbl_CSEContribution].[big_CSECase_FK]) WITH PREFETCH)
            |    |    |    |         |    |    |    |    |    |--Sort(ORDER BY:([tbl_CSEContribution].[big_CSECase_FK] ASC))
            |    |    |    |         |    |    |    |    |    |    |--Hash Match(Inner Join, HASH:([tbl_CSEContribution].[big_CSECase_FK])=([tbl_PRDOutput].[big_ObjectId]), RESIDUAL:([tbl_CSEContribution].[big_CSECase_FK]=[tbl_PRDOutput].[big_ObjectId]))
            |    |    |    |         |    |    |    |    |    |         |--Clustered Index Scan(OBJECT:([EnLight].[dbo].[tbl_CSEContribution].[PK_tbl_CSEContribution]), WHERE:([tbl_CSEContribution].[dtm_Created]>='May  7 2007 12:00AM' AND [tbl_CSEContribut
            |    |    |    |         |    |    |    |    |    |         |--Clustered Index Scan(OBJECT:([EnLight].[dbo].[tbl_PRDOutput].[PK_tbl_OUTQue]), WHERE:(([tbl_PRDOutput].[int_DOCDocument_FK]=231 AND [tbl_PRDOutput].[dtm_Merge]>='May  7 2007 12:00AM
            |    |    |    |         |    |    |    |    |    |--Clustered Index Seek(OBJECT:([EnLight].[dbo].[tbl_CSECase].[PK_CSMCase]), SEEK:([tbl_CSECase].[big_CSECaseId]=[tbl_CSEContribution].[big_CSECase_FK]) ORDERED FORWARD)
            |    |    |    |         |    |    |    |    |--Clustered Index Seek(OBJECT:([EnLight].[dbo].[tbl_CSEAddress].[PK_tbl_CSEAddress]), SEEK:([tbl_CSEAddress].[int_CSEAddressId]=[tbl_CSECase].[int_CSEAddress_FK]) ORDERED FORWARD)
            |    |    |    |         |    |    |    |--Sort(ORDER BY:([tbl_CSECase].[big_CSECaseId] ASC))
            |    |    |    |         |    |    |         |--Hash Match(Inner Join, HASH:([tbl_ACTZipCity].[int_ACTZipCityId])=([tbl_CSEAddress].[int_ACTZipCity_FK]))
            |    |    |    |         |    |    |              |--Clustered Index Scan(OBJECT:([EnLight].[dbo].[tbl_ACTZipCity].[PK_tbl_ACTZipCity]))
            |    |    |    |         |    |    |              |--Hash Match(Inner Join, HASH:([tbl_CSECase].[big_CSECaseId])=([tbl_CSEAddress].[big_CSECase_FK]), RESIDUAL:([tbl_CSECase].[big_CSECaseId]=[tbl_CSEAddress].[big_CSECase_FK]))
            |    |    |    |         |    |    |                   |--Clustered Index Scan(OBJECT:([EnLight].[dbo].[tbl_CSECase].[PK_CSMCase]))
            |    |    |    |         |    |    |                   |--Clustered Index Scan(OBJECT:([EnLight].[dbo].[tbl_CSEAddress].[PK_tbl_CSEAddress]))
            |    |    |    |         |    |    |--Clustered Index Scan(OBJECT:([EnLight].[dbo].[tbl_CSEContributionEntry_New].[PK_tbl_CSEContributionEntry_New_1]), WHERE:([tbl_CSEContributionEntry_New].[chr_CSEContributionEntryPart_FK]='betpakr'))
            |    |    |    |         |    |--Clustered Index Seek(OBJECT:([EnLight].[dbo].[tbl_ACTMunicipality].[PK_tbl_ACTMunicipality]), SEEK:([tbl_ACTMunicipality].[int_ACTMunicipalityId]=[tbl_CSEAddress].[int_ACTMunicipality_FK]) ORDERED FORWARD)
            |    |    |    |         |--Clustered Index Seek(OBJECT:([EnLight].[dbo].[tbl_ACTProduct].[PK_tbl_FileType]), SEEK:([tbl_ACTProduct].[int_ACTProductId]=[tbl_CSECase].[int_ACTProduct_FK]) ORDERED FORWARD)
            |    |    |    |--Clustered Index Seek(OBJECT:([EnLight].[dbo].[tbl_ACTZipCity].[PK_tbl_ACTZipCity]), SEEK:([tbl_ACTZipCity].[int_ACTZipCityId]=[tbl_ACTCourt].[int_ACTZipCity_FK]) ORDERED FORWARD)
            |    |    |--Clustered Index Seek(OBJECT:([EnLight].[dbo].[tbl_ACTCaseType].[PK_tbl_CreditorDeliveryType]), SEEK:([tbl_ACTCaseType].[int_ACTCaseTypeId]=[tbl_ACTProduct].[int_ACTCaseType_FK]) ORDERED FORWARD)
            |    |--Clustered Index Seek(OBJECT:([EnLight].[dbo].[tbl_ACTCreditor].[PK_tbl_Creditor]), SEEK:([tbl_ACTCreditor].[int_ACTCreditorId]=[tbl_ACTCaseType].[int_ACTCreditor_FK]) ORDERED FORWARD)
            |--Clustered Index Seek(OBJECT:([EnLight].[dbo].[tbl_ACTActor].[PK_tbl_Collector]), SEEK:([tbl_ACTActor].[int_ACTActorId]=[tbl_ACTCreditor].[int_ACTCreditorId]) ORDERED FORWARD)

Query 2: (slow)

SELECT DISTINCT                          tbl_ACTCourt.vch_Name AS Fogedret,     tbl_ACTCourt.vch_Address AS Adresse,     tbl_ACTZipCity.vch_ZipCode AS Postnr,     tbl_ACTZipCity.vch_City AS [By],                         tbl_CSEContribution.big_CSECase_FK A
  |--Sort(DISTINCT ORDER BY:([tbl_ACTCourt].[vch_Name] ASC, [tbl_CSECase].[vch_Name] ASC, [tbl_ACTCourt].[vch_Address] ASC, [tbl_ACTZipCity].[vch_ZipCode] ASC, [tbl_ACTZipCity].[vch_City] ASC, [tbl_CSEContribution].[big_CSECase_FK] ASC, [tbl_ACTActor].[vch
       |--Nested Loops(Inner Join, OUTER REFERENCES:([tbl_ACTActor].[int_ACTActorId]))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([tbl_ACTCaseType].[int_ACTCreditor_FK]))
            |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tbl_ACTProduct].[int_ACTCaseType_FK]))
            |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tbl_ACTCourt].[int_ACTZipCity_FK]))
            |    |    |    |--Hash Match(Inner Join, HASH:([tbl_ACTMunicipality].[int_ACTJurisdiction_FK])=([tbl_ACTCourt].[int_ACTJurisdiction_FK]))
            |    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tbl_CSECase].[int_ACTProduct_FK]))
            |    |    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tbl_CSEAddress].[int_ACTMunicipality_FK]))
            |    |    |    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tbl_CSECase].[big_CSECaseId]))
            |    |    |    |    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tbl_CSECase].[int_CSEAddress_FK]))
            |    |    |    |    |    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tbl_CSEContribution].[big_CSECase_FK]))
            |    |    |    |    |    |    |    |    |    |--Hash Match(Inner Join, HASH:([tbl_CSEContribution].[int_CSEContributionId])=([tbl_CSEContributionEntry_New].[int_CSEContribution_FK]))
            |    |    |    |    |    |    |    |    |    |    |--Nested Loops(Left Semi Join, OUTER REFERENCES:([tbl_CSEContribution].[big_CSECase_FK]))
            |    |    |    |    |    |    |    |    |    |    |    |--Clustered Index Scan(OBJECT:([EnLight].[dbo].[tbl_CSEContribution].[PK_tbl_CSEContribution]),  WHERE:([tbl_CSEContribution].[dtm_Created]>'Aug 15 2007 12:00AM' AND [tbl_CSEContribution].
            |    |    |    |    |    |    |    |    |    |    |    |--Row Count Spool
            |    |    |    |    |    |    |    |    |    |    |         |--Filter(WHERE:([tbl_CSEContribution].[big_CSECase_FK]=[tbl_PRDOutput].[big_ObjectId]))
            |    |    |    |    |    |    |    |    |    |    |              |--Sort(DISTINCT ORDER BY:([tbl_PRDOutput].[big_ObjectId] ASC))
            |    |    |    |    |    |    |    |    |    |    |                   |--Clustered Index Scan(OBJECT:([EnLight].[dbo].[tbl_PRDOutput].[PK_tbl_OUTQue]), WHERE:(([tbl_PRDOutput].[int_DOCDocument_FK]=328 AND [tbl_PRDOutput].[dtm_Merge]>'Aug 15 200
            |    |    |    |    |    |    |    |    |    |    |--Clustered Index Scan(OBJECT:([EnLight].[dbo].[tbl_CSEContributionEntry_New].[PK_tbl_CSEContributionEntry_New_1]), WHERE:([tbl_CSEContributionEntry_New].[chr_CSEContributionEntryPart_FK]='foge
            |    |    |    |    |    |    |    |    |    |--Clustered Index Seek(OBJECT:([EnLight].[dbo].[tbl_CSECase].[PK_CSMCase]), SEEK:([tbl_CSECase].[big_CSECaseId]=[tbl_CSEContribution].[big_CSECase_FK]) ORDERED FORWARD)
            |    |    |    |    |    |    |    |    |--Clustered Index Seek(OBJECT:([EnLight].[dbo].[tbl_CSEAddress].[PK_tbl_CSEAddress]), SEEK:([tbl_CSEAddress].[int_CSEAddressId]=[tbl_CSECase].[int_CSEAddress_FK]) ORDERED FORWARD)
            |    |    |    |    |    |    |    |--Table Spool
            |    |    |    |    |    |    |         |--Filter(WHERE:([tbl_CSECase].[big_CSECaseId]=[tbl_CSECase].[big_CSECaseId]))
            |    |    |    |    |    |    |              |--Sort(ORDER BY:([tbl_CSECase].[big_CSECaseId] ASC))
            |    |    |    |    |    |    |                   |--Hash Match(Inner Join, HASH:([tbl_ACTZipCity].[int_ACTZipCityId])=([tbl_CSEAddress].[int_ACTZipCity_FK]))
            |    |    |    |    |    |    |                        |--Clustered Index Scan(OBJECT:([EnLight].[dbo].[tbl_ACTZipCity].[PK_tbl_ACTZipCity]))
            |    |    |    |    |    |    |                        |--Hash Match(Inner Join, HASH:([tbl_CSECase].[big_CSECaseId])=([tbl_CSEAddress].[big_CSECase_FK]), RESIDUAL:([tbl_CSECase].[big_CSECaseId]=[tbl_CSEAddress].[big_CSECase_FK]))
            |    |    |    |    |    |    |                             |--Clustered Index Scan(OBJECT:([EnLight].[dbo].[tbl_CSECase].[PK_CSMCase]))
            |    |    |    |    |    |    |                             |--Clustered Index Scan(OBJECT:([EnLight].[dbo].[tbl_CSEAddress].[PK_tbl_CSEAddress]))
            |    |    |    |    |    |    |--Clustered Index Seek(OBJECT:([EnLight].[dbo].[tbl_ACTMunicipality].[PK_tbl_ACTMunicipality]), SEEK:([tbl_ACTMunicipality].[int_ACTMunicipalityId]=[tbl_CSEAddress].[int_ACTMunicipality_FK]) ORDERED FORWARD)
            |    |    |    |    |    |--Clustered Index Seek(OBJECT:([EnLight].[dbo].[tbl_ACTProduct].[PK_tbl_FileType]), SEEK:([tbl_ACTProduct].[int_ACTProductId]=[tbl_CSECase].[int_ACTProduct_FK]) ORDERED FORWARD)
            |    |    |    |    |--Clustered Index Scan(OBJECT:([EnLight].[dbo].[tbl_ACTCourt].[PK_tbl_ACTCourt]), WHERE:([tbl_ACTCourt].[chr_ACTCourtType_FK]='fogedret'))
            |    |    |    |--Clustered Index Seek(OBJECT:([EnLight].[dbo].[tbl_ACTZipCity].[PK_tbl_ACTZipCity]), SEEK:([tbl_ACTZipCity].[int_ACTZipCityId]=[tbl_ACTCourt].[int_ACTZipCity_FK]) ORDERED FORWARD)
            |    |    |--Clustered Index Seek(OBJECT:([EnLight].[dbo].[tbl_ACTCaseType].[PK_tbl_CreditorDeliveryType]), SEEK:([tbl_ACTCaseType].[int_ACTCaseTypeId]=[tbl_ACTProduct].[int_ACTCaseType_FK]) ORDERED FORWARD)
            |    |--Clustered Index Seek(OBJECT:([EnLight].[dbo].[tbl_ACTActor].[PK_tbl_Collector]), SEEK:([tbl_ACTActor].[int_ACTActorId]=[tbl_ACTCaseType].[int_ACTCreditor_FK]) ORDERED FORWARD)
            |--Clustered Index Seek(OBJECT:([EnLight].[dbo].[tbl_ACTCreditor].[PK_tbl_Creditor]), SEEK:([tbl_ACTCreditor].[int_ACTCreditorId]=[tbl_ACTActor].[int_ACTActorId]) ORDERED FORWARD)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 18:20:18

quote:

Originally posted by thenearfuture


		----------------------------------------
		|  Original  |  Exists  |  Inner Join  |
--------------------------------------------------------
|  No Hint	|    3:09    |   3:10   |    3:31      |
--------------------------------------------------------
|  Hint         |    0:17    |   0:17   |    0:06      |
--------------------------------------------------------

I think we have a winner

Are both queries (ie with different params) equal fast now?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 18:25:21

Is DISTINCT really necessary? If it is, sometimes GROUP BY is faster, since it aggregates at an earlier stage than DISTINCT, which aggregates at the last stage

SELECT		tbl_ACTCourt.vch_Name AS Fogedret, 
		tbl_ACTCourt.vch_Address AS Adresse, 
		tbl_ACTZipCity.vch_ZipCode AS Postnr, 
		tbl_ACTZipCity.vch_City AS [By], 
		tbl_CSEContribution.big_CSECase_FK AS Sag, 
		tbl_ACTActor.vch_LegalName AS Kreditor, 
		'mod' AS Mod, 
		vw_NameAndAddress.vch_Name AS Debitor, 
		tbl_CSEContributionEntry_New.mny_Amount AS Beløb, 
		'kr.' AS kr
FROM		tbl_CSEContributionEntry_New
INNER JOIN	tbl_CSEContribution ON tbl_CSEContributionEntry_New.int_CSEContribution_FK = tbl_CSEContribution.int_CSEContributionId
INNER JOIN	vw_NameAndAddress ON tbl_CSEContribution.big_CSECase_FK = vw_NameAndAddress.big_CSECaseId 
INNER JOIN	tbl_CSECase ON tbl_CSEContribution.big_CSECase_FK = tbl_CSECase.big_CSECaseId
INNER JOIN	tbl_CSEAddress ON tbl_CSECase.int_CSEAddress_FK = tbl_CSEAddress.int_CSEAddressId 
INNER JOIN	tbl_ACTMunicipality ON tbl_ACTMunicipality.int_ACTMunicipalityId = tbl_CSEAddress.int_ACTMunicipality_FK
INNER JOIN	tbl_ACTCourt ON tbl_ACTMunicipality.int_ACTJurisdiction_FK = tbl_ACTCourt.int_ACTJurisdiction_FK
INNER JOIN	tbl_ACTZipCity ON tbl_ACTCourt.int_ACTZipCity_FK = tbl_ACTZipCity.int_ACTZipCityId
INNER JOIN	tbl_ACTProduct ON tbl_CSECase.int_ACTProduct_FK = tbl_ACTProduct.int_ACTProductId
INNER JOIN	tbl_ACTCaseType ON tbl_ACTProduct.int_ACTCaseType_FK = tbl_ACTCaseType.int_ACTCaseTypeId
INNER JOIN	tbl_ACTCreditor ON tbl_ACTCaseType.int_ACTCreditor_FK = tbl_ACTCreditor.int_ACTCreditorId
INNER JOIN	tbl_ACTActor ON tbl_ACTCreditor.int_ACTCreditorId = tbl_ACTActor.int_ACTActorId
INNER JOIN	tbl_PRDOutput ON tbl_PRDOutput.big_ObjectId = tbl_CSEContribution.big_CSECase_FK
WHERE		tbl_CSEContribution.dtm_Created >= '2007-05-07'
		AND tbl_CSEContribution.dtm_Created < '2007-05-07 12:00:00'
		AND tbl_CSEContributionEntry_New.chr_CSEContributionEntryPart_FK = 'betpakr'
		AND tbl_ACTCourt.chr_ACTCourtType_FK = 'fogedret'
		AND tbl_PRDOutput.int_DOCDocument_FK = 231
		AND tbl_PRDOutput.dtm_Merge >= '2007-05-07'
		AND tbl_PRDOutput.bit_Cancelled = 0
GROUP BY	tbl_ACTCourt.vch_Name, 
		tbl_ACTCourt.vch_Address, 
		tbl_ACTZipCity.vch_ZipCode, 
		tbl_ACTZipCity.vch_City, 
		tbl_CSEContribution.big_CSECase_FK, 
		tbl_ACTActor.vch_LegalName, 
		vw_NameAndAddress.vch_Name, 
		tbl_CSEContributionEntry_New.mny_Amount
ORDER BY 	tbl_ACTCourt.vch_Name,
		vw_NameAndAddress.vch_Name
OPTION		(FORCE ORDER)

E 12°55'05.25"
N 56°04'39.16"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources