JOIN with table valued function very slow

If have a tabled-valued function uCalendar that returns a two-column table
with attributes dayno (number of days past 19000101) and caldate, which is a
formatted date based on dayno.
DDL for uCalendar:
CREATE FUNCTION uCalendar (@.startdate datetime = '19000101', @.enddate
datetime )
RETURNS @.calendar TABLE (dayno bigint, caldate char(20))
AS
BEGIN
DECLARE @.firstday bigint
DECLARE @.lastday bigint
SELECT @.firstday = DATEDIFF(dd, 0, @.startdate)
SELECT @.lastday = DATEDIFF(dd, 0, @.enddate)
WHILE (@.firstday <= @.lastday)
BEGIN
INSERT INTO @.calendar VALUES (@.firstday, CONVERT(char(20), DATEADD(dd,
@.firstday, 0), 107))
SET @.firstday = @.firstday + 1
END
RETURN
END
Calling the function like so
select * from uCalendar('20050101','20051231')
returns the result set very quickly.
I have another query that reports the number of hits against a website per
day:
SELECT DATEDIFF(dd, 0, [time]) AS dayno, COUNT(*) AS "hits" FROM weblog
GROUP BY DATEDIFF(dd, 0, [time])
weblog is a view that references a base table with approximately 500,000
rows. The above query finishes in about 2 seconds.
However, if I try
select t1.dayno from uCalendar('20050101','20051231') AS t1
LEFT JOIN
(SELECT DATEDIFF(dd, 0, [time]) AS dayno, COUNT(*) AS "hits" FROM weblog
GROUP BY DATEDIFF(dd, 0, [time])) t2
ON t1.dayno=t2.dayno
It seems to hang. The longest I let it run was about 3 minutes. If the
result sets from each "side" of the join are produced rapidly, why doesn't
this query produce its results quickly? Is it because it is constantly
re-evaluating the function over and over?
I'm working on a better solution using a stored procedure to get the entire
result. The point of my question is *why* is it slow, not "this will work
instead".
Thanks,
-Mark WilliamsHi Mark
Why are you not using a calendar table for this
http://www.aspfaq.com/show.asp?id=2519?
John
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:F3466971-ED3D-4026-91B1-0EAFD26DBDB1@.microsoft.com...
> If have a tabled-valued function uCalendar that returns a two-column table
> with attributes dayno (number of days past 19000101) and caldate, which is
> a
> formatted date based on dayno.
> DDL for uCalendar:
> CREATE FUNCTION uCalendar (@.startdate datetime = '19000101', @.enddate
> datetime )
> RETURNS @.calendar TABLE (dayno bigint, caldate char(20))
> AS
> BEGIN
> DECLARE @.firstday bigint
> DECLARE @.lastday bigint
> SELECT @.firstday = DATEDIFF(dd, 0, @.startdate)
> SELECT @.lastday = DATEDIFF(dd, 0, @.enddate)
> WHILE (@.firstday <= @.lastday)
> BEGIN
> INSERT INTO @.calendar VALUES (@.firstday, CONVERT(char(20), DATEADD(dd,
> @.firstday, 0), 107))
> SET @.firstday = @.firstday + 1
> END
> RETURN
> END
> Calling the function like so
> select * from uCalendar('20050101','20051231')
> returns the result set very quickly.
> I have another query that reports the number of hits against a website per
> day:
> SELECT DATEDIFF(dd, 0, [time]) AS dayno, COUNT(*) AS "hits" FROM weblog
> GROUP BY DATEDIFF(dd, 0, [time])
> weblog is a view that references a base table with approximately 500,000
> rows. The above query finishes in about 2 seconds.
> However, if I try
> select t1.dayno from uCalendar('20050101','20051231') AS t1
> LEFT JOIN
> (SELECT DATEDIFF(dd, 0, [time]) AS dayno, COUNT(*) AS "hits" FROM weblog
> GROUP BY DATEDIFF(dd, 0, [time])) t2
> ON t1.dayno=t2.dayno
> It seems to hang. The longest I let it run was about 3 minutes. If the
> result sets from each "side" of the join are produced rapidly, why
> doesn't
> this query produce its results quickly? Is it because it is constantly
> re-evaluating the function over and over?
> I'm working on a better solution using a stored procedure to get the
> entire
> result. The point of my question is *why* is it slow, not "this will work
> instead".
> Thanks,
> -Mark Williams|||Mark,
Best I can tell, the query optimizer has no information about the
number of rows or distribution of values in
uCalendar('20050101','20051231'),
and so instead of materializing the grouped table, then joining
it with the UDF, it chooses a query plan that runs a count from
the weblog table for each row of the calendar table. When I run
this against the Northwind Orders table, I see that the optimizer thinks
there are only one or two rows in the UDF result set. There is no
way to tell the optimizer to think agian.
The quickest solution is create a permanent (not a UDF) calendar
table indexed on at least the bigint column, and then join against that.
Here I do so, and put a wide range of dates into the calendar table.
I'm writing this against Northwind..Orders so I can test it, but the
improvement in the query plan should translate to your situation
as well.
CREATE TABLE tCalendar (
dayno bigint primary key,
caldate char(20) unique
)
GO
insert into tCalendar
select dayno, caldate
from uCalendar('20010101','20101231')
go
select t1.dayno
from tCalendar AS t1
left outer join (
SELECT
DATEDIFF(dd, 0, [OrderDate]) AS dayno,
COUNT(*) AS "hits" FROM Northwind..Orders
GROUP BY DATEDIFF(dd, 0, [OrderDate])
) T
on t1.dayno = T.dayno
WHERE t1.dayno between
datediff(day,0,'20050101') and datediff(day,0,'20051231')
It's important here to be sure the WHERE clause is a SARG.
Since you don't use any datetime data types here, you can't
compare anything directly against your two datetime strings,
and you don't want to put t1.dayno into an expression.
Ideally, you would use datetime as the type to store dates
with, not bigint and not a string, but perhaps this will help
you out until you can make other improvements to your design.
A permanent calendar table is always a good idea, and if you
use uCalendar widely, you could rewrite it to select from a
permanent table (using the same WHERE clause I show here
outside the join), so you don't have to rewrite as many queries.
I assume you know that your sample query is not too practical,
since because of the outer join with no where clause, your result
will just be all dayno values in the UDF. But the optimizer doesn't
manage to catch that...
Steve Kass
Drew University
Mark Williams wrote:

>If have a tabled-valued function uCalendar that returns a two-column table
>with attributes dayno (number of days past 19000101) and caldate, which is
a
>formatted date based on dayno.
>DDL for uCalendar:
>CREATE FUNCTION uCalendar (@.startdate datetime = '19000101', @.enddate
>datetime )
>RETURNS @.calendar TABLE (dayno bigint, caldate char(20))
>AS
>BEGIN
> DECLARE @.firstday bigint
> DECLARE @.lastday bigint
> SELECT @.firstday = DATEDIFF(dd, 0, @.startdate)
> SELECT @.lastday = DATEDIFF(dd, 0, @.enddate)
> WHILE (@.firstday <= @.lastday)
> BEGIN
> INSERT INTO @.calendar VALUES (@.firstday, CONVERT(char(20), DATEADD(dd,
>@.firstday, 0), 107))
> SET @.firstday = @.firstday + 1
> END
> RETURN
>END
>Calling the function like so
>select * from uCalendar('20050101','20051231')
>returns the result set very quickly.
>I have another query that reports the number of hits against a website per
>day:
>SELECT DATEDIFF(dd, 0, [time]) AS dayno, COUNT(*) AS "hits" FROM weblog
>GROUP BY DATEDIFF(dd, 0, [time])
>weblog is a view that references a base table with approximately 500,000
>rows. The above query finishes in about 2 seconds.
>However, if I try
>select t1.dayno from uCalendar('20050101','20051231') AS t1
>LEFT JOIN
>(SELECT DATEDIFF(dd, 0, [time]) AS dayno, COUNT(*) AS "hits" FROM weblog
>GROUP BY DATEDIFF(dd, 0, [time])) t2
>ON t1.dayno=t2.dayno
>It seems to hang. The longest I let it run was about 3 minutes. If the
>result sets from each "side" of the join are produced rapidly, why doesn't
>this query produce its results quickly? Is it because it is constantly
>re-evaluating the function over and over?
>I'm working on a better solution using a stored procedure to get the entire
>result. The point of my question is *why* is it slow, not "this will work
>instead".
>Thanks,
>-Mark Williams
>|||I'm aware of caledar tables, but my question was more oriented toward why it
was slow, and not "what is another solution?" I did come up with another
solution, which produced the results very quickly:
CREATE PROCEDURE hitsByDay
@.startdate datetime = '19000101',
@.enddate datetime
AS
BEGIN
--DECLARE @.calendar TABLE (dayno bigint, caldate char(20))
CREATE TABLE #calendar (dayno bigint, caldate char(20))
DECLARE @.firstday bigint
DECLARE @.lastday bigint
SELECT @.firstday = DATEDIFF(dd, 0, @.startdate)
SELECT @.lastday = DATEDIFF(dd, 0, @.enddate)
WHILE (@.firstday <= @.lastday)
BEGIN
INSERT INTO #calendar VALUES (@.firstday, CONVERT(char(20), DATEADD(dd,
@.firstday, 0), 107))
SET @.firstday = @.firstday + 1
END
SELECT t1.caldate, ISNULL(t2.hits,0) from #calendar AS t1
LEFT JOIN
(SELECT DATEDIFF(dd, 0, [time]) AS dayno, COUNT(*) AS "hits" FROM weblog
GROUP BY DATEDIFF(dd, 0, [time])) t2
ON t1.dayno=t2.dayno
ORDER BY t1.dayno
DROP TABLE #calendar
END
EXEC dbo.hitsByDay '20050101','20051231'
It should be noted that there are no indexes in the base table or the view
that referenced it. There are no natural candidate keys because of the natur
e
of the data (it's a web site log, lots of duplicates). I tried creating an
index the [time] column in the view, but it complained that is was
non-deterministic. (I don't buy that one).
So, the question is, why, specifically, the join with the table-valued
function is so slow. The join with the temporary #calendar table is pretty
quick, even without an index.
--
"John Bell" wrote:

> Hi Mark
> Why are you not using a calendar table for this
> http://www.aspfaq.com/show.asp?id=2519?
> John
> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:F3466971-ED3D-4026-91B1-0EAFD26DBDB1@.microsoft.com...
>
>|||Thank you; very well worded and insightful.
I ended up creating a stored procedure that dynamically creates a temporary
calendar based on the input start and end dates.
CREATE PROCEDURE hitsByDay
@.startdate datetime = '19000101',
@.enddate datetime
AS
BEGIN
--DECLARE @.calendar TABLE (dayno bigint, caldate char(20))
CREATE TABLE #calendar (dayno bigint, caldate char(20))
DECLARE @.firstday bigint
DECLARE @.lastday bigint
SELECT @.firstday = DATEDIFF(dd, 0, @.startdate)
SELECT @.lastday = DATEDIFF(dd, 0, @.enddate)
WHILE (@.firstday <= @.lastday)
BEGIN
INSERT INTO #calendar VALUES (@.firstday, CONVERT(char(20), DATEADD(dd,
@.firstday, 0), 107))
SET @.firstday = @.firstday + 1
END
SELECT t1.caldate, ISNULL(t2.hits,0) from #calendar AS t1
LEFT JOIN
(SELECT DATEDIFF(dd, 0, [time]) AS dayno, COUNT(*) AS "hits" FROM weblog
WHERE [time] BETWEEN @.startdate AND @.enddate
GROUP BY DATEDIFF(dd, 0, [time])) t2
ON t1.dayno=t2.dayno
ORDER BY t1.dayno
DROP TABLE #calendar
END
EXEC dbo.hitsByDay '20051201','20051231'
If you posted to this forum through TechNet, and you found my answers
helpful, please mark them as answers.
"Steve Kass" wrote:

> Mark,
> Best I can tell, the query optimizer has no information about the
> number of rows or distribution of values in
> uCalendar('20050101','20051231'),
> and so instead of materializing the grouped table, then joining
> it with the UDF, it chooses a query plan that runs a count from
> the weblog table for each row of the calendar table. When I run
> this against the Northwind Orders table, I see that the optimizer thinks
> there are only one or two rows in the UDF result set. There is no
> way to tell the optimizer to think agian.
> The quickest solution is create a permanent (not a UDF) calendar
> table indexed on at least the bigint column, and then join against that.
> Here I do so, and put a wide range of dates into the calendar table.
> I'm writing this against Northwind..Orders so I can test it, but the
> improvement in the query plan should translate to your situation
> as well.
> CREATE TABLE tCalendar (
> dayno bigint primary key,
> caldate char(20) unique
> )
> GO
> insert into tCalendar
> select dayno, caldate
> from uCalendar('20010101','20101231')
> go
> select t1.dayno
> from tCalendar AS t1
> left outer join (
> SELECT
> DATEDIFF(dd, 0, [OrderDate]) AS dayno,
> COUNT(*) AS "hits" FROM Northwind..Orders
> GROUP BY DATEDIFF(dd, 0, [OrderDate])
> ) T
> on t1.dayno = T.dayno
> WHERE t1.dayno between
> datediff(day,0,'20050101') and datediff(day,0,'20051231')
>
> It's important here to be sure the WHERE clause is a SARG.
> Since you don't use any datetime data types here, you can't
> compare anything directly against your two datetime strings,
> and you don't want to put t1.dayno into an expression.
> Ideally, you would use datetime as the type to store dates
> with, not bigint and not a string, but perhaps this will help
> you out until you can make other improvements to your design.
> A permanent calendar table is always a good idea, and if you
> use uCalendar widely, you could rewrite it to select from a
> permanent table (using the same WHERE clause I show here
> outside the join), so you don't have to rewrite as many queries.
> I assume you know that your sample query is not too practical,
> since because of the outer join with no where clause, your result
> will just be all dayno values in the UDF. But the optimizer doesn't
> manage to catch that...
> Steve Kass
> Drew University
> Mark Williams wrote:
>
>|||Hi Mark
You may find a more permanent calendar table would be useful elsewhere,
Check where you use data functions and comparisons to see if using one would
be more efficient. Searching Google for "UDF AND SLOW" turns up many hits,
although most of these are related to scalar functions e.g.
http://www.sql-server-performance.c...server_udfs.asp
Your function(s) are not set based solutions which included looping which
would be expected to perform worse with a larger number of iterations, this
is where a calendar table would be significantly faster, therefore I would
make sure that you test it with a full date range.
John
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:D4E2BC61-680E-4B03-9535-4E0015828A5C@.microsoft.com...
> I'm aware of caledar tables, but my question was more oriented toward why
> it
> was slow, and not "what is another solution?" I did come up with another
> solution, which produced the results very quickly:
> CREATE PROCEDURE hitsByDay
> @.startdate datetime = '19000101',
> @.enddate datetime
> AS
> BEGIN
> --DECLARE @.calendar TABLE (dayno bigint, caldate char(20))
> CREATE TABLE #calendar (dayno bigint, caldate char(20))
> DECLARE @.firstday bigint
> DECLARE @.lastday bigint
> SELECT @.firstday = DATEDIFF(dd, 0, @.startdate)
> SELECT @.lastday = DATEDIFF(dd, 0, @.enddate)
> WHILE (@.firstday <= @.lastday)
> BEGIN
> INSERT INTO #calendar VALUES (@.firstday, CONVERT(char(20), DATEADD(dd,
> @.firstday, 0), 107))
> SET @.firstday = @.firstday + 1
> END
> SELECT t1.caldate, ISNULL(t2.hits,0) from #calendar AS t1
> LEFT JOIN
> (SELECT DATEDIFF(dd, 0, [time]) AS dayno, COUNT(*) AS "hits" FROM weblog
> GROUP BY DATEDIFF(dd, 0, [time])) t2
> ON t1.dayno=t2.dayno
> ORDER BY t1.dayno
> DROP TABLE #calendar
> END
> EXEC dbo.hitsByDay '20050101','20051231'
> It should be noted that there are no indexes in the base table or the view
> that referenced it. There are no natural candidate keys because of the
> nature
> of the data (it's a web site log, lots of duplicates). I tried creating an
> index the [time] column in the view, but it complained that is was
> non-deterministic. (I don't buy that one).
> So, the question is, why, specifically, the join with the table-valued
> function is so slow. The join with the temporary #calendar table is pretty
> quick, even without an index.
> --
> "John Bell" wrote:
>

join SmallDateTime column very slow

Hello,
I posted this on programming group, but hasn't got any reply yet.
I have a table and the definition is like:
CarTable(
[RowNumber] [int] IDENTITY(0,1) NOT NULL,
ModelID,
MakeID,
RegisterDate smallDateTime null
PRIMARY KEY CLUSTERED
(
[RowNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ModelID and MakeID are foreign keys from a reference table (call it
refTable here).
I created an idex on MakeID, modelID and RegisterDate as:
CREATE UNIQUE CLUSTERED INDEX
[IX_vwVehicleMain_ReportingAggregate_Aggregate] ON [CarTable]
(
[MakeID] ASC,
[ModelID] ASC,
[RegisterDate] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
= OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON
[INDEX_FG]
The CarTable is quite big (50 million records)
When I do query such as
select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
RegisterDate from CarTable inner join RefTable
on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID= RefTable.ModelID)
It is fairly quick - 2 seonds
However, if I put RegisterDate in the join condition, it becomes very
slow. For example:
select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
RegisterDate from CarTable inner join RefTable
on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID= RefTable.ModelID and
CarTable.RegisterDate > '01/01/1980')
This one is much slower 2 minutes.
My index cover MakeID, ModelID and RegisterDate, so why it is so slow?
Do I need to add an non-clustered index just for RegisterDate?
Many ThanksReplied to in .programming.
Please do not multi-post. If you want to ask the same question in
several newsgroups then the prefered method is to cross-post. This
prevents double answers (and double effort).
--
Gert-Jan
DAXU@.hotmail.com wrote:
> Hello,
> I posted this on programming group, but hasn't got any reply yet.
> I have a table and the definition is like:
> CarTable(
> [RowNumber] [int] IDENTITY(0,1) NOT NULL,
> ModelID,
> MakeID,
> RegisterDate smallDateTime null
> PRIMARY KEY CLUSTERED
> (
> [RowNumber] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
> = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> ModelID and MakeID are foreign keys from a reference table (call it
> refTable here).
> I created an idex on MakeID, modelID and RegisterDate as:
> CREATE UNIQUE CLUSTERED INDEX
> [IX_vwVehicleMain_ReportingAggregate_Aggregate] ON [CarTable]
> (
> [MakeID] ASC,
> [ModelID] ASC,
> [RegisterDate] ASC
> )WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
> = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
> ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON
> [INDEX_FG]
> The CarTable is quite big (50 million records)
> When I do query such as
> select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
> RegisterDate from CarTable inner join RefTable
> on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=> RefTable.ModelID)
> It is fairly quick - 2 seonds
> However, if I put RegisterDate in the join condition, it becomes very
> slow. For example:
> select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
> RegisterDate from CarTable inner join RefTable
> on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=> RefTable.ModelID and
> CarTable.RegisterDate > '01/01/1980')
> This one is much slower 2 minutes.
> My index cover MakeID, ModelID and RegisterDate, so why it is so slow?
> Do I need to add an non-clustered index just for RegisterDate?
> Many Thanks

join SmallDateTime column very slow

Hello,
I posted this on programming group, but hasn't got any reply yet.
I have a table and the definition is like:
CarTable(
[RowNumber] [int] IDENTITY(0,1) NOT NULL,
ModelID,
MakeID,
RegisterDate smallDateTime null
PRIMARY KEY CLUSTERED
(
[RowNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ModelID and MakeID are foreign keys from a reference table (call it
refTable here).
I created an idex on MakeID, modelID and RegisterDate as:
CREATE UNIQUE CLUSTERED INDEX
& #91;IX_vwVehicleMain_ReportingAggregate_
Aggregate] ON [CarTable]
(
[MakeID] ASC,
[ModelID] ASC,
[RegisterDate] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
= OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON
[INDEX_FG]
The CarTable is quite big (50 million records)
When I do query such as
select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
RegisterDate from CarTable inner join RefTable
on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=
RefTable.ModelID)
It is fairly quick - 2 seonds
However, if I put RegisterDate in the join condition, it becomes very
slow. For example:
select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
RegisterDate from CarTable inner join RefTable
on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=
RefTable.ModelID and
CarTable.RegisterDate > '01/01/1980')
This one is much slower 2 minutes.
My index cover MakeID, ModelID and RegisterDate, so why it is so slow?
Do I need to add an non-clustered index just for RegisterDate?
Many ThanksReplied to in .programming.
Please do not multi-post. If you want to ask the same question in
several newsgroups then the prefered method is to cross-post. This
prevents double answers (and double effort).
Gert-Jan
DAXU@.hotmail.com wrote:
> Hello,
> I posted this on programming group, but hasn't got any reply yet.
> I have a table and the definition is like:
> CarTable(
> [RowNumber] [int] IDENTITY(0,1) NOT NULL,
> ModelID,
> MakeID,
> RegisterDate smallDateTime null
> PRIMARY KEY CLUSTERED
> (
> [RowNumber] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
> = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> ModelID and MakeID are foreign keys from a reference table (call it
> refTable here).
> I created an idex on MakeID, modelID and RegisterDate as:
> CREATE UNIQUE CLUSTERED INDEX
> & #91;IX_vwVehicleMain_ReportingAggregate_
Aggregate] ON [CarTable]
> (
> [MakeID] ASC,
> [ModelID] ASC,
> [RegisterDate] ASC
> )WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
> = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
> ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON
> [INDEX_FG]
> The CarTable is quite big (50 million records)
> When I do query such as
> select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
> RegisterDate from CarTable inner join RefTable
> on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=
> RefTable.ModelID)
> It is fairly quick - 2 seonds
> However, if I put RegisterDate in the join condition, it becomes very
> slow. For example:
> select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
> RegisterDate from CarTable inner join RefTable
> on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=
> RefTable.ModelID and
> CarTable.RegisterDate > '01/01/1980')
> This one is much slower 2 minutes.
> My index cover MakeID, ModelID and RegisterDate, so why it is so slow?
> Do I need to add an non-clustered index just for RegisterDate?
> Many Thanks

Join on subquery with distinct?

Can someone give me some reasonable explanation why these two queries are performing dramatically different:

First (slow):

select
tableA.column1,
tableA.column2
from
tableA join (select distinct col1,col2,col3 from myView) vw
on vw.col1=tableA.col1 and vw.col2=tableA.col2 and vw.col3=tableA.col3

Second (fast):

create table #tempTable (col1 varchar(200),col2 int,col3 varchar(200))

insert into #tempTable
select distinct col1,col2,col3 from myView

select

tableA.column1,

tableA.column2

from

tableA join #tempTable vw

on vw.col1=tableA.col1 and vw.col2=tableA.col2 and vw.col3=tableA.col3

SQL Server 2005 Express
Win2K3 Server

Regards,
Marko Simic

Marko:

I can't really mock this without knowing how the view is structured. It is not possible to mock a query plan that involves a view without knowing the components of the view. Can you give details of the makeup of the view?

Dave

|||

Can you post the plan for your queries, such as:

set showplan_text on
--set statistics profile on
go

--your query--

go
--set statistics profile off
set showplan_text off

The showplan one gives you estimated, the profile one gives you actual plan. That will be very helpful in this.

|||

When I executed queries (for generating estimated and actual plans), suddenly problematic one executed much faster.
Most probably, problem was about server's hardware performance, like insufficient RAM, at the moment of query execution.
My assumption is based on the fact that problematic query is using subquery, which by many views, is using more memory then query with temp table.

Anyway, I am sending you a link to XLS file with all statistics (of query with subquery) you asked, in case that something else could cause strange behavior and can be seen from this.

http://139.142.50.130/test/statistics.xls

Thank you for your efforts

|||

Without both to compare it is really impossible to say too confidently, but there are a couple of really large looking table scans (Clustered Index Scan = ordered table scan) followed by a hash match join. This kind of operation can be greatly affected by hardware performance/contention, as the hash match executes faster with more ram.

Comparing the plans might shed some light on it, but if it runs adequately now... :)

|||The optimiser will expand out queries like the first one. Just one of those annoying things about it. You could improve the speed by putting changing it to "select top (999999) distinct col1, col2, col3 from myView", because that will force the server to materialise the table first. Any number will do, so long as it's larger than the number of rows you expect to get out (otherwise you'll lose rows).

Which effectively is the same as populating a temporary table. :)

Rob|||

First of all, thank you all for your replies.
Rob, will you please tell me, what did you mean by "materialise table".
Did you mean that database would move view from memory to hard drive?
And, if that is true, would db engine make that table within tempdb space (as temp tables are) or in original database space?

|||When I say 'materialise', I just mean that the engine will create the table in memory and then join the other tables to it. It won't move it from memory to the hard drive - it will stay in RAM.

Rob|||

ok.Thanks. Then it works as I expected.

|||Ah, great. Glad to help. Can you mark it as an answer please? I know MS are keen for this to happen on all threads.|||With pleasure :) Done|||:) Cheers