Slow Query with Date

[Thomi Baechler]

Hello Everybody

I run the following query against to identical databases. Execution
time on the first DB is 0 seconds, on the other 6 seconds!

SELECT dbo.HRMABZ.EMPK EY , dbo.HRMABZ.CONN UMB
, dbo.HRM_CALENDE R.Datum, dbo.HRMABZ.ABZT XT

FROM dbo.HRM_CALENDE R INNER JOIN dbo.HRMABZ
ON dbo.HRM_CALENDE R.Datum >= dbo.HRMABZ.ABZD ATF
AND dbo.HRM_CALENDE R.Datum <= dbo.HRMABZ.ABZD ATT

WHERE (dbo.HRMABZ.ABZ TECH = 'N')
AND (dbo.HRMABZ.ABZ LDLT = 'N')
AND (dbo.HRM_CALEND ER.Valid = 1)

I tried to analyze to query execution in the execution plan and found
two different arguments in a 'nested loop / inner join' which I dont
understand. The nested loop does consume most of the time:

On the fast Server the argument says:
'Outer References:([HRMABZ].[ABZDATT],[HRMABZ].[ABZDATF])'

On the slow server the argument says:
'WHERE: ([HRM_Calender].[Datum]>=[HRMABZ].[ABZDATF] AND
[HRM_Calender].[]<=[HRMABZ].[ABZDATT])'

Additional information for the two databases:
- Slow database runs on a SQL2000 SP3a English, W2K SP3
- Fast database runs on a SQL2000 SP3a German, W2k SP3

Can somebody explain me the difference between the two execution plans
and how I could force the slow database acting the same way as the
fast one does?

Thank you
Thomi

[Erland Sommarskog]

Re: Slow Query with Date

Thomi Baechler (thomi.ns.baech ler@azul.ch) writes:[color=blue]
> I run the following query against to identical databases. Execution
> time on the first DB is 0 seconds, on the other 6 seconds![/color]

So what you mean with identical? Do they have exactly the same data? Or
do they have the same schemas but different data?
[color=blue]
> SELECT dbo.HRMABZ.EMPK EY , dbo.HRMABZ.CONN UMB
> , dbo.HRM_CALENDE R.Datum, dbo.HRMABZ.ABZT XT
>
> FROM dbo.HRM_CALENDE R INNER JOIN dbo.HRMABZ
> ON dbo.HRM_CALENDE R.Datum >= dbo.HRMABZ.ABZD ATF
> AND dbo.HRM_CALENDE R.Datum <= dbo.HRMABZ.ABZD ATT
>
> WHERE (dbo.HRMABZ.ABZ TECH = 'N')
> AND (dbo.HRMABZ.ABZ LDLT = 'N')
> AND (dbo.HRM_CALEND ER.Valid = 1)
>
> I tried to analyze to query execution in the execution plan and found
> two different arguments in a 'nested loop / inner join' which I dont
> understand. The nested loop does consume most of the time:
>
> On the fast Server the argument says:
> 'Outer References:([HRMABZ].[ABZDATT],[HRMABZ].[ABZDATF])'
>
> On the slow server the argument says:
> 'WHERE: ([HRM_Calender].[Datum]>=[HRMABZ].[ABZDATF] AND
> [HRM_Calender].[]<=[HRMABZ].[ABZDATT])'[/color]

Well, the quey is a join between two tables, and SQL Server could
start with any of the two using the filters in the WHERE clause
for an initial filter, and then look up a row in the other table.
In the two different query plans, is the same table being subject
to a clustered index scan?

Could you post the complete query plans?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: Slow Query with Date

Thomi Baechler (thomi.ns.baech ler@azul.ch) writes:[color=blue]
> I tried to analyze to query execution in the execution plan and found
> two different arguments in a 'nested loop / inner join' which I dont
> understand. The nested loop does consume most of the time:
>
> On the fast Server the argument says:
> 'Outer References:([HRMABZ].[ABZDATT],[HRMABZ].[ABZDATF])'
>
> On the slow server the argument says:
> 'WHERE: ([HRM_Calender].[Datum]>=[HRMABZ].[ABZDATF] AND
> [HRM_Calender].[]<=[HRMABZ].[ABZDATT])'[/color]

Also, information about the number of rows in each table would help.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Thomi Bächler]

Re: Slow Query with Date

Hi Erland

Thank you for your fast answer!

Identical means: Full backup of the database on our clients server and
restore on our testserver -> exact the same data in both databases

Complete query plans: sure, I can post the complete plan. I am an absolute
newbie. Can your tell me how to do that?

Number of rows: HRMABZ has about 4000 rows and HRM_CALENDER about 3800 rows

As far as I know is the only differnce between the two DBs/Servers the
language. I have to join dates (datetime) and my guess would be that the
english server has to do a language conversion of the date which leads to .
Could that be possible?

Thank you very much!
Thomi

[color=blue]
>
> So what you mean with identical? Do they have exactly the same data? Or
> do they have the same schemas but different data?
>[/color]
[color=blue]
>
> Well, the quey is a join between two tables, and SQL Server could
> start with any of the two using the filters in the WHERE clause
> for an initial filter, and then look up a row in the other table.
> In the two different query plans, is the same table being subject
> to a clustered index scan?
>
> Could you post the complete query plans?[/color]

[Erland Sommarskog]

Re: Slow Query with Date

Thomi Bächler (thomi.ns.baech ler@azul.ch) writes:[color=blue]
> Identical means: Full backup of the database on our clients server and
> restore on our testserver -> exact the same data in both databases[/color]

Hm, I wonder if the statistics are the same? Run an UPDATE STATISTICS
WITH FULLSCAN on bnth tables on both servers.
[color=blue]
> Complete query plans: sure, I can post the complete plan. I am an absolute
> newbie. Can your tell me how to do that?[/color]

For a simple query, you issue this command before you issue the query:

SET SHOWPLAN_TEXT ON

This command is also an implicit SET NOEXEC ON, so the query will not be
executed.

You can also use SET STATISTICS_PROF ILE ON. In this case the query will
be executed.
[color=blue]
> Number of rows: HRMABZ has about 4000 rows and HRM_CALENDER about 3800
> rows[/color]

Ah, that appears it would make it a toss up which plan to use.
[color=blue]
> As far as I know is the only differnce between the two DBs/Servers the
> language. I have to join dates (datetime) and my guess would be that the
> english server has to do a language conversion of the date which leads
> to . Could that be possible?[/color]

Not very likely. If all date columns are datetime, there is no conversion
to make, since datetime is a binary value. If some column is varchar, there
is always a conversion, no matter the language of the server. And what
matters more than the server language is the collations, but they are
obviously the same, as you copied the database with BACKUP/RESTORE.

But the hardware configuration of the machines could matter, for instance
the number of available processors.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Thomi Bächler]

Re: Slow Query with Date

Hello

I run the UPDATE...STATIS TICS on both tables, on both servers. Nothing
changed!

Execution plan is as follows:

Fast Server (german, SQL2000 SP3a, single Pentium, 768MB RAM)
-------------------------------------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES:([HRMABZ].[ABZDATT],
[HRMABZ].[ABZDATF]))
|--Sort(ORDER BY:([HRMABZ].[ABZDATT] ASC, [HRMABZ].[ABZDATF] ASC))
| |--Clustered Index
Scan(OBJECT:([XHRMDTA036].[dbo].[HRMABZ].[PK__HRMABZ__524 42E1F]),
WHERE:([HRMABZ].[ABZTECH]='N' AND [HRMABZ].[ABZLDLT]='N'))
|--Table Spool
|--Index Spool(SEEK:([HRM_CALENDER].[Datum] >=
[HRMABZ].[ABZDATF] AND [HRM_CALENDER].[Datum] <= [HRMABZ].[ABZDATT]))
|--Table Scan(OBJECT:([XHRMDTA036].[dbo].[HRM_CALENDER]),
WHERE:([HRM_CALENDER].[Valid]=1))


Slow Server (english, SQL2000 SP3a, dual Pentium, 1G RAM)
---------------------------------------------------------
|--Parallelism(Gat her Streams)
|--Nested Loops(Inner Join,
WHERE:([HRM_CALENDER].[Datum]>=[HRMABZ].[ABZDATF] AND
[HRM_CALENDER].[Datum]<=[HRMABZ].[ABZDATT]))
|--Clustered Index
Scan(OBJECT:([XHRMDTA036].[dbo].[HRMABZ].[PK__HRMABZ__524 42E1F]),
WHERE:([HRMABZ].[ABZTECH]='N' AND [HRMABZ].[ABZLDLT]='N'))
|--Table Spool
|--Table Scan(OBJECT:([XHRMDTA036].[dbo].[HRM_CALENDER]),
WHERE:([HRM_CALENDER].[Valid]=1))

To be honest, I dont understand the details in the execution plan. I would
appreciate any help!

Any ideas?
Thank you
Thomi


Am Wed, 8 Dec 2004 20:44:16 +0000 (UTC) schrieb Erland Sommarskog:
[color=blue]
> Thomi Bächler (thomi.ns.baech ler@azul.ch) writes:[color=green]
>> Identical means: Full backup of the database on our clients server and
>> restore on our testserver -> exact the same data in both databases[/color]
>
> Hm, I wonder if the statistics are the same? Run an UPDATE STATISTICS
> WITH FULLSCAN on bnth tables on both servers.
>[color=green]
>> Complete query plans: sure, I can post the complete plan. I am an absolute
>> newbie. Can your tell me how to do that?[/color]
>
> For a simple query, you issue this command before you issue the query:
>
> SET SHOWPLAN_TEXT ON
>
> This command is also an implicit SET NOEXEC ON, so the query will not be
> executed.
>
> You can also use SET STATISTICS_PROF ILE ON. In this case the query will
> be executed.
>[color=green]
>> Number of rows: HRMABZ has about 4000 rows and HRM_CALENDER about 3800
>> rows[/color]
>
> Ah, that appears it would make it a toss up which plan to use.
>[color=green]
>> As far as I know is the only differnce between the two DBs/Servers the
>> language. I have to join dates (datetime) and my guess would be that the
>> english server has to do a language conversion of the date which leads
>> to . Could that be possible?[/color]
>
> Not very likely. If all date columns are datetime, there is no conversion
> to make, since datetime is a binary value. If some column is varchar, there
> is always a conversion, no matter the language of the server. And what
> matters more than the server language is the collations, but they are
> obviously the same, as you copied the database with BACKUP/RESTORE.
>
> But the hardware configuration of the machines could matter, for instance
> the number of available processors.[/color]

[Erland Sommarskog]

Re: Slow Query with Date

Thomi Bächler (thomi.ns.baech ler@azul.ch) writes:[color=blue]
> Execution plan is as follows:
>
> Fast Server (german, SQL2000 SP3a, single Pentium, 768MB RAM)
> -------------------------------------------------------------
>
> Slow Server (english, SQL2000 SP3a, dual Pentium, 1G RAM)
> ---------------------------------------------------------
> |--Parallelism(Gat her Streams)[/color]

So there one was one more differences between the machines you didn't
tell us about! To wit, the number of CPUs.

Parallelism can be a great thing for a really wild query. But unfortunately,
the optimizer appears to be overly optimistic about the wonders of
parallelism, and use parallel plans when it shouldn't.

Add this at the end of the query:

OPTION (MAXDOP 1)

MAXDOP = Max Degree of Parallelism

This option forces a non-parallel plan. Sometimes a non-parallel plan
may be slower than the parallel plan, but this option may still be useful.
If you have an 8-way box, you may not want that wild query to monopolize
the entire server, but if 2-3 CPUs are working with it that may be OK.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Thomi Bächler]

Re: Slow Query with Date

Hi Erland

That did the trick! The query runs now with the same speed on both DBs. We
changed to: Properties -> Processor -> Parallelism -> Use 1 processor. We
think in our environment that might be best anyway.

Thank you for your help!
Thomi

[color=blue]
> Thomi Bächler (thomi.ns.baech ler@azul.ch) writes:[color=green]
>> Execution plan is as follows:
>>
>> Fast Server (german, SQL2000 SP3a, single Pentium, 768MB RAM)
>> -------------------------------------------------------------
>>
>> Slow Server (english, SQL2000 SP3a, dual Pentium, 1G RAM)
>> ---------------------------------------------------------
>> |--Parallelism(Gat her Streams)[/color]
>
> So there one was one more differences between the machines you didn't
> tell us about! To wit, the number of CPUs.
>
> Parallelism can be a great thing for a really wild query. But unfortunately,
> the optimizer appears to be overly optimistic about the wonders of
> parallelism, and use parallel plans when it shouldn't.
>
> Add this at the end of the query:
>
> OPTION (MAXDOP 1)
>
> MAXDOP = Max Degree of Parallelism
>
> This option forces a non-parallel plan. Sometimes a non-parallel plan
> may be slower than the parallel plan, but this option may still be useful.
> If you have an 8-way box, you may not want that wild query to monopolize
> the entire server, but if 2-3 CPUs are working with it that may be OK.[/color]