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.EMPKEY , dbo.HRMABZ.CONNUMB
, dbo.HRM_CALENDER.Datum, dbo.HRMABZ.ABZTXT

FROM dbo.HRM_CALENDER INNER JOIN dbo.HRMABZ
ON dbo.HRM_CALENDER.Datum >= dbo.HRMABZ.ABZDATF
AND dbo.HRM_CALENDER.Datum <= dbo.HRMABZ.ABZDATT

WHERE (dbo.HRMABZ.ABZTECH = 'N')
AND (dbo.HRMABZ.ABZLDLT = 'N')
AND (dbo.HRM_CALENDER.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]

Thomi Baechler (th***************@azul.ch) writes:

I run the following query against to identical databases. Execution
time on the first DB is 0 seconds, on the other 6 seconds!
So what you mean with identical? Do they have exactly the same data? Or
do they have the same schemas but different data?
SELECT dbo.HRMABZ.EMPKEY , dbo.HRMABZ.CONNUMB
, dbo.HRM_CALENDER.Datum, dbo.HRMABZ.ABZTXT

FROM dbo.HRM_CALENDER INNER JOIN dbo.HRMABZ
ON dbo.HRM_CALENDER.Datum >= dbo.HRMABZ.ABZDATF
AND dbo.HRM_CALENDER.Datum <= dbo.HRMABZ.ABZDATT

WHERE (dbo.HRMABZ.ABZTECH = 'N')
AND (dbo.HRMABZ.ABZLDLT = 'N')
AND (dbo.HRM_CALENDER.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])'

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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Thomi Baechler (th***************@azul.ch) writes:

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])'

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

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Thomi Bächler]

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

So what you mean with identical? Do they have exactly the same data? Or
do they have the same schemas but different data?

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]

Thomi Bächler (th***************@azul.ch) writes:

Identical means: Full backup of the database on our clients server and
restore on our testserver -> exact the same data in both databases
Hm, I wonder if the statistics are the same? Run an UPDATE STATISTICS
WITH FULLSCAN on bnth tables on both servers.
Complete query plans: sure, I can post the complete plan. I am an absolute
newbie. Can your tell me how to do that?
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_PROFILE ON. In this case the query will
be executed.
Number of rows: HRMABZ has about 4000 rows and HRM_CALENDER about 3800
rows
Ah, that appears it would make it a toss up which plan to use.
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?

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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Thomi Bächler]

Hello

I run the UPDATE...STATISTICS 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__52442E1F]),
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(Gather 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__52442E1F]),
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:

Thomi Bächler (th***************@azul.ch) writes:

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

Hm, I wonder if the statistics are the same? Run an UPDATE STATISTICS
WITH FULLSCAN on bnth tables on both servers.

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

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_PROFILE ON. In this case the query will
be executed.

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

Ah, that appears it would make it a toss up which plan to use.

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?

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]

Thomi Bächler (th***************@azul.ch) writes:

Execution plan is as follows:

Fast Server (german, SQL2000 SP3a, single Pentium, 768MB RAM)
-------------------------------------------------------------

Slow Server (english, SQL2000 SP3a, dual Pentium, 1G RAM)
---------------------------------------------------------
|--Parallelism(Gather Streams)

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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Thomi Bächler]

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

Thomi Bächler (th***************@azul.ch) writes:

Execution plan is as follows:

Fast Server (german, SQL2000 SP3a, single Pentium, 768MB RAM)
-------------------------------------------------------------

Slow Server (english, SQL2000 SP3a, dual Pentium, 1G RAM)
---------------------------------------------------------
|--Parallelism(Gather Streams)

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.