Optimiser problem? Takes ages to retrieve next key in 4-segment

Nikki,
You are absolutely correct in your view of this, but unfortunately,
I don't think there is a way in T-SQL to deal with this. It might
become easier with the analytic functions of SQL Server 2005, but
that may not be true and doesn't help you much now... It may also
be that there is a clever way of doing this with cursors that I'm not
seeing, but it would be a shame to have to do that.
If it weren't for the datetime column, you could create (and perhaps
get away with indexing) a computed table column or view column that
concatenated fixed-length string versions of the index columns. Datetime
conversions are considered non-deterministic or imprecise and can't be
indexed, so you would have to use a decimal column or separate serial
date and time columns stored as integers or strings.
Not fun, but if this is a serious concern for you, it might be worth
the trouble of considering. The concatenation is also the closest you can
come to a "clean" way of expressing this, but it won't be as efficient as
it should be:
select top 1 *
from [Requirement Detail]
WHERE
ORDER_11
+ CONVERT(PRTNUM_11,15)
+ CONVERT(CHAR(30),CURDUE_11,121)
+ TYPE_11
>
@.ORDER_11
+ CONVERT(@.PRTNUM_11,15)
+ CONVERT(CHAR(30),@.CURDUE_11,121)
+ @.TYPE_11
ORDER BY ORDER_11, PRTNUM_11, CURDUE_11, TYPE_11
You might get lucky with this, depending on the
distribution of data you have. Adding this or other
conditions you know can use indexes and are true can
help:
ORDER_11 >= @.ORDER_11
At the risk of increasing your frustration more than highlighting your sense
, I'll
point out that if SQL Server implemented row constructors according
to the ANSI SQL standard, it would be very easy:
-- WARNING: DON'T TRY THIS IN T-SQL. :(
-- USING T-SQL TOP also:
SELECT TOP 1 *
FROM [Requirement Detail]
WHERE (ORDER_11, PRTNUM_11, CURDUE_11, TYPE_11)
> (@.ORDER_11, @.PRTNUM_11, @.CURDUE_11, @.TYPE_11)
ORDER BY ORDER_11, PRTNUM_11, CURDUE_11, TYPE_11
ANSI SQL doesn't have TOP, and I don't have my copy handy
to be sure MAX can be used with row constructors, but the
ANSI version might be one of these:
SELECT *
FROM [Requirement Detail]
WHERE (ORDER_11, PRTNUM_11, CURDUE_11, TYPE_11) = (
SELECT MIN((ORDER_11, PRTNUM_11, CURDUE_11, TYPE_11))
FROM [Requirement Detail]
WHERE (ORDER_11, PRTNUM_11, CURDUE_11, TYPE_11)
> (@.ORDER_11, @.PRTNUM_11, @.CURDUE_11, @.TYPE_11)
)
or,
SELECT *
FROM [Requirement Detail] AS RD1
WHERE NOT EXISTS (
SELECT * FROM [Requirement Detail] AS RD2
WHERE (RD2.ORDER_11, RD2.PRTNUM_11, RD2.CURDUE_11, RD2.TYPE_11)
> (@.ORDER_11, @.PRTNUM_11, @.CURDUE_11, @.TYPE_11)
AND (RD2.ORDER_11, RD2.PRTNUM_11, RD2.CURDUE_11, RD2.TYPE_11)
< (RD1.ORDER_11, RD1.PRTNUM_11, RD1.CURDUE_11, RD1.TYPE_11)
)
SK
Nikki Locke wrote:

> Thanks for your reply.
> Unfortunately clustered indexes are out. If we did have a clustered index,
> it would be on the most frequently used key (which is not this one,
> unfortunately).
> Is there a better way or wording the query? All I want it to do is to make
> a key out of the 4 fields provided, look the key up in the index, and
> return either the found record, or the next record in the index if the
> specified one doesn't exist.
> When stated like that, it is obviously a very cheap operation. If I could
> only find a way of telling SQL server that was what I wanted, all would be
> fine.
>SQL Server does support ALL, but Hugo may have been (and I certainly
was) lamenting the fact that SQL Server doesn't support multicolumn
comparisons of the sort (a1,b1,c1) < (a2,b2,c2). Queries that use
ALL can fairly easily be rewritten without ALL, but queries that use
multicolumn comparisons cannot, and the rewrite, in this case
(
(a1 < a2)
OR
((a1 = a2) and (b1 < b2))
OR
((a1 = a2) and (b1 = b2) and (c1 < c2))
)
is not (that I've seen) optimized to take advantage of an index
on (a,b,c), which ought to help out here.
SK
oj wrote:

> Hugo,
> I haven't been following the entire thread. But sqlserver doe support ALL
> (to some extend).
> http://msdn.microsoft.com/library/e..._qd_11_1sz0.asp
>|||Sounds good! Thanks for the followup.
SK
Nikki Locke wrote:

>Thankyou very much for your detailed and useful reply.
>I now have a much better understanding of the problem, and can start to
>approach it from a different angle.
>I already have a query which executes in 3 msecs (as opposed to 3 secs for
>the original), which is as follows...
>declare @.ORDER_11 nchar(10)
>declare @.PRTNUM_11 nvarchar(15)
>declare @.CURDUE_11 smalldatetime
>declare @.TYPE_11 nchar(2)
>declare @.KEY nchar(35)
>set @.ORDER_11 = '5480184500'
>set @.PRTNUM_11 = '548000000000000'
>set @.CURDUE_11 = '2005-04-20 12:00:00'
>set @.TYPE_11 = 'RQ'
>set @.KEY = @.ORDER_11 + @.PRTNUM_11 + CONVERT(nchar(8), @.CURDUE_11, 112) +
>@.TYPE_11
> SELECT TOP 1
> UNQKEY_11,
> Convert(Money, TIMESTAMP_11),
> ORDER_11,
> PRTNUM_11,
> CURDUE_11,
> TYPE_11
> FROM
> [dbo].[Requirement Detail]
> WHERE
> ((ORDER_11>=@.ORDER_11)) -- the major part of the key
> AND
> ORDER_11 + PRTNUM_11 + CONVERT(nchar(8), CURDUE_11, 112) + TYPE_11
>
> ORDER BY
> ORDER_11,
> PRTNUM_11,
> CURDUE_11,
> TYPE_11
>GO
>As far as I can see, provided there aren't thousands of rows where
>ORDER_11>=@.ORDER_11 but the rest of the condition is not satisfied, this is
>pretty optimal. But I'm still going to fiddle to see if I can improve the
>original query to cut down on the amount of rewriting I have to do on the
>stored procedures (there are hundreds of them!).
>[Aside] I used style 113 and length 8 for the date query because I happen
>to know the time part of the date is not significant in the real data.
>
>

Optimiser issues between SQL Server 7 and SQL Server 2000 Enterprise

Hello All,
Can anybody throw some light on this problem...
In SQL Server version 7, running on NT 4, the following query (generated by
Business Objects, not me!) can be successfully executed in query analyser
and returns correct results without heavily utilising tembdb. A seek is
performed, based on date, on a clustered index only returning the rows from
Sales_Details_Extract (fact table) that are required:
SELECT
sum(Sales_Enquiry.dbo.Sales_Details_Extract.Value),
(CASE WHEN
(
Sales_Enquiry.dbo.Time_v.Month )=( (SELECT Current_Month FROM
BusObj_Control) )
THEN (
sum(Sales_Enquiry.dbo.Sales_Details_Extract.Value)
)
ELSE 0 END),
(CASE WHEN
(
Sales_Enquiry.dbo.Time_v.Month )=( (SELECT Current_Month FROM
BusObj_Control) )
THEN (
sum(Sales_Enquiry.dbo.Sales_Details_Extract.Quantity)
)
ELSE 0 END),
Sales_Enquiry.dbo.Time_v.Year,
Sales_Enquiry.dbo.Time_v.Month,
dbo.SCFlattened.SCSubT2Desc
FROM
Sales_Enquiry.dbo.Sales_Details_Extract,
dbo.Title_Hierarchy,
dbo.Customers,
dbo.SCFlattened,
Sales_Enquiry.dbo.Time_v
WHERE
( Sales_Enquiry.dbo.Sales_Details_Extract.ISBN=dbo.Title_Hierarchy.ISBN and
Sales_Enquiry.dbo.Sales_Details_Extract.Source_System=dbo.Title_Hierarchy.So
urce_System )
AND (
Sales_Enquiry.dbo.Sales_Details_Extract.Customer=dbo.Customers.Customer )
AND ( Sales_Enquiry.dbo.Sales_Details_Extract.Date between
Sales_Enquiry.dbo.Time_v.Period_Start and
Sales_Enquiry.dbo.Time_v.Period_End )
AND ( dbo.Customers.Sales_Channel=dbo.SCFlattened.SCSubT6 )
AND (
( (
Sales_Enquiry.dbo.Time_v.Year ) = ( (select Current_Year from
BusObj_Control) ) )
AND Sales_Enquiry.dbo.Sales_Details_Extract.Sale_Type IN (' ', 'S',
'U')
AND Sales_Enquiry.dbo.Sales_Details_Extract.Sale_Category NOT IN ('V',
'R')
AND
dbo.Customers.Customer NOT IN ('61384 ', '68812 ', '61383 ')
AND
dbo.Title_Hierarchy.ISBN NOT IN ('0141014075')
)
GROUP BY
Sales_Enquiry.dbo.Time_v.Year, Sales_Enquiry.dbo.Time_v.Month,
dbo.SCFlattened.SCSubT2Desc
The table sizes are as follows:
Sales_Details_Extract (view containing ~ 70 million rows)
Title_Hierarchy (Table containing ~180k rows)
Customers, (Table containing ~60k rows)
SCFlattened (Table containing ~30k rows)
Time_v (view containing ~ 80 rows)
Total DB Size ~ 33GB
On new hardware running Windows 2003 Server and SQL Server 2000 Enterprise
Ed. The same query causes tempdb to fill (56GB) before we run out of disk
resource. The database is a direct copy i.e. db detached and attached. The
indexes have recently been rebuilt and the 'auto update stats' option is
selected. The query plan seems to indicate a scan of the entire
Sales_Details (70 million rows) is taking place, and suspect a Cartesian
product is the cause of tempdb growing so large.
We've tried unsuccessfully placing the database including tempdb into 'SQL 7
Compatibility Mode'
Any Suggestions would be gratefully received. I have copies of the
execution plans if interested.
Regards,
Ian (ichinds@.hotmail.com)Have you tried using query hints to influence the plan? "query hints" in
books online has some description.
If you want to try the compatibility mode, then it should be set on your
database, not tempdb.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ian" <ian.hinds@.pearsontc.co.uk> wrote in message
news:O%23r3sSX0EHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hello All,
>
> Can anybody throw some light on this problem...
>
> In SQL Server version 7, running on NT 4, the following query (generated
> by
> Business Objects, not me!) can be successfully executed in query analyser
> and returns correct results without heavily utilising tembdb. A seek is
> performed, based on date, on a clustered index only returning the rows
> from
> Sales_Details_Extract (fact table) that are required:
>
> SELECT
> sum(Sales_Enquiry.dbo.Sales_Details_Extract.Value),
> (CASE WHEN
> (
> Sales_Enquiry.dbo.Time_v.Month )=( (SELECT Current_Month FROM
> BusObj_Control) )
> THEN (
> sum(Sales_Enquiry.dbo.Sales_Details_Extract.Value)
> )
> ELSE 0 END),
> (CASE WHEN
> (
> Sales_Enquiry.dbo.Time_v.Month )=( (SELECT Current_Month FROM
> BusObj_Control) )
> THEN (
> sum(Sales_Enquiry.dbo.Sales_Details_Extract.Quantity)
> )
> ELSE 0 END),
> Sales_Enquiry.dbo.Time_v.Year,
> Sales_Enquiry.dbo.Time_v.Month,
> dbo.SCFlattened.SCSubT2Desc
> FROM
> Sales_Enquiry.dbo.Sales_Details_Extract,
> dbo.Title_Hierarchy,
> dbo.Customers,
> dbo.SCFlattened,
> Sales_Enquiry.dbo.Time_v
> WHERE
> ( Sales_Enquiry.dbo.Sales_Details_Extract.ISBN=dbo.Title_Hierarchy.ISBN
> and
> Sales_Enquiry.dbo.Sales_Details_Extract.Source_System=dbo.Title_Hierarchy.So
> urce_System )
> AND (
> Sales_Enquiry.dbo.Sales_Details_Extract.Customer=dbo.Customers.Customer )
> AND ( Sales_Enquiry.dbo.Sales_Details_Extract.Date between
> Sales_Enquiry.dbo.Time_v.Period_Start and
> Sales_Enquiry.dbo.Time_v.Period_End )
> AND ( dbo.Customers.Sales_Channel=dbo.SCFlattened.SCSubT6 )
> AND (
> ( (
> Sales_Enquiry.dbo.Time_v.Year ) = ( (select Current_Year from
> BusObj_Control) ) )
> AND Sales_Enquiry.dbo.Sales_Details_Extract.Sale_Type IN (' ', 'S',
> 'U')
> AND Sales_Enquiry.dbo.Sales_Details_Extract.Sale_Category NOT IN ('V',
> 'R')
> AND
> dbo.Customers.Customer NOT IN ('61384 ', '68812 ', '61383 ')
> AND
> dbo.Title_Hierarchy.ISBN NOT IN ('0141014075')
> )
> GROUP BY
> Sales_Enquiry.dbo.Time_v.Year, Sales_Enquiry.dbo.Time_v.Month,
> dbo.SCFlattened.SCSubT2Desc
>
>
> The table sizes are as follows:
>
> Sales_Details_Extract (view containing ~ 70 million rows)
> Title_Hierarchy (Table containing ~180k rows)
> Customers, (Table containing ~60k rows)
> SCFlattened (Table containing ~30k rows)
> Time_v (view containing ~ 80 rows)
>
> Total DB Size ~ 33GB
>
>
> On new hardware running Windows 2003 Server and SQL Server 2000 Enterprise
> Ed. The same query causes tempdb to fill (56GB) before we run out of disk
> resource. The database is a direct copy i.e. db detached and attached.
> The
> indexes have recently been rebuilt and the 'auto update stats' option is
> selected. The query plan seems to indicate a scan of the entire
> Sales_Details (70 million rows) is taking place, and suspect a Cartesian
> product is the cause of tempdb growing so large.
>
> We've tried unsuccessfully placing the database including tempdb into 'SQL
> 7
> Compatibility Mode'
>
> Any Suggestions would be gratefully received. I have copies of the
> execution plans if interested.
>
> Regards,
>
> Ian (ichinds@.hotmail.com)
>
>
>
>|||Ian,
I don't know how much influence you have over the query or the schema. I
assume you cannot change the query.
First of all, make sure you statistics are up to date. Run UPDATE
STATISTICS when in doubt.
Next, you could check if all join keys are of the same data type. For
example, column Time_v.Year should have the exact same data type (for
optimal performance) as BusObj_Control.Current_Year.
Also check if the columns in the WHERE clause have the same data type as
the literals of the expressions. For example, column Customers.Customer
should be char or varchar in order to match the data type of the literal
'61384 '.
Hope this helps,
Gert-Jan
Ian wrote:
> Hello All,
> Can anybody throw some light on this problem...
> In SQL Server version 7, running on NT 4, the following query (generated by
> Business Objects, not me!) can be successfully executed in query analyser
> and returns correct results without heavily utilising tembdb. A seek is
> performed, based on date, on a clustered index only returning the rows from
> Sales_Details_Extract (fact table) that are required:
> SELECT
> sum(Sales_Enquiry.dbo.Sales_Details_Extract.Value),
> (CASE WHEN
> (
> Sales_Enquiry.dbo.Time_v.Month )=( (SELECT Current_Month FROM
> BusObj_Control) )
> THEN (
> sum(Sales_Enquiry.dbo.Sales_Details_Extract.Value)
> )
> ELSE 0 END),
> (CASE WHEN
> (
> Sales_Enquiry.dbo.Time_v.Month )=( (SELECT Current_Month FROM
> BusObj_Control) )
> THEN (
> sum(Sales_Enquiry.dbo.Sales_Details_Extract.Quantity)
> )
> ELSE 0 END),
> Sales_Enquiry.dbo.Time_v.Year,
> Sales_Enquiry.dbo.Time_v.Month,
> dbo.SCFlattened.SCSubT2Desc
> FROM
> Sales_Enquiry.dbo.Sales_Details_Extract,
> dbo.Title_Hierarchy,
> dbo.Customers,
> dbo.SCFlattened,
> Sales_Enquiry.dbo.Time_v
> WHERE
> ( Sales_Enquiry.dbo.Sales_Details_Extract.ISBN=dbo.Title_Hierarchy.ISBN and
> Sales_Enquiry.dbo.Sales_Details_Extract.Source_System=dbo.Title_Hierarchy.So
> urce_System )
> AND (
> Sales_Enquiry.dbo.Sales_Details_Extract.Customer=dbo.Customers.Customer )
> AND ( Sales_Enquiry.dbo.Sales_Details_Extract.Date between
> Sales_Enquiry.dbo.Time_v.Period_Start and
> Sales_Enquiry.dbo.Time_v.Period_End )
> AND ( dbo.Customers.Sales_Channel=dbo.SCFlattened.SCSubT6 )
> AND (
> ( (
> Sales_Enquiry.dbo.Time_v.Year ) = ( (select Current_Year from
> BusObj_Control) ) )
> AND Sales_Enquiry.dbo.Sales_Details_Extract.Sale_Type IN (' ', 'S',
> 'U')
> AND Sales_Enquiry.dbo.Sales_Details_Extract.Sale_Category NOT IN ('V',
> 'R')
> AND
> dbo.Customers.Customer NOT IN ('61384 ', '68812 ', '61383 ')
> AND
> dbo.Title_Hierarchy.ISBN NOT IN ('0141014075')
> )
> GROUP BY
> Sales_Enquiry.dbo.Time_v.Year, Sales_Enquiry.dbo.Time_v.Month,
> dbo.SCFlattened.SCSubT2Desc
> The table sizes are as follows:
> Sales_Details_Extract (view containing ~ 70 million rows)
> Title_Hierarchy (Table containing ~180k rows)
> Customers, (Table containing ~60k rows)
> SCFlattened (Table containing ~30k rows)
> Time_v (view containing ~ 80 rows)
> Total DB Size ~ 33GB
> On new hardware running Windows 2003 Server and SQL Server 2000 Enterprise
> Ed. The same query causes tempdb to fill (56GB) before we run out of disk
> resource. The database is a direct copy i.e. db detached and attached. The
> indexes have recently been rebuilt and the 'auto update stats' option is
> selected. The query plan seems to indicate a scan of the entire
> Sales_Details (70 million rows) is taking place, and suspect a Cartesian
> product is the cause of tempdb growing so large.
> We've tried unsuccessfully placing the database including tempdb into 'SQL 7
> Compatibility Mode'
> Any Suggestions would be gratefully received. I have copies of the
> execution plans if interested.
> Regards,
> Ian (ichinds@.hotmail.com)

Optimiser issues between SQL Server 7 and SQL Server 2000 Enterprise

Hello All,
Can anybody throw some light on this problem...
In SQL Server version 7, running on NT 4, the following query (generated by
Business Objects, not me!) can be successfully executed in query analyser
and returns correct results without heavily utilising tembdb. A seek is
performed, based on date, on a clustered index only returning the rows from
Sales_Details_Extract (fact table) that are required:
SELECT
sum(Sales_Enquiry.dbo.Sales_Details_Extract.Value) ,
(CASE WHEN
(
Sales_Enquiry.dbo.Time_v.Month )=( (SELECT Current_Month FROM
BusObj_Control) )
THEN (
sum(Sales_Enquiry.dbo.Sales_Details_Extract.Value)
)
ELSE 0 END),
(CASE WHEN
(
Sales_Enquiry.dbo.Time_v.Month )=( (SELECT Current_Month FROM
BusObj_Control) )
THEN (
sum(Sales_Enquiry.dbo.Sales_Details_Extract.Quanti ty)
)
ELSE 0 END),
Sales_Enquiry.dbo.Time_v.Year,
Sales_Enquiry.dbo.Time_v.Month,
dbo.SCFlattened.SCSubT2Desc
FROM
Sales_Enquiry.dbo.Sales_Details_Extract,
dbo.Title_Hierarchy,
dbo.Customers,
dbo.SCFlattened,
Sales_Enquiry.dbo.Time_v
WHERE
( Sales_Enquiry.dbo.Sales_Details_Extract.ISBN=dbo.T itle_Hierarchy.ISBN and
Sales_Enquiry.dbo.Sales_Details_Extract.Source_Sys tem=dbo.Title_Hierarchy.So
urce_System )
AND (
Sales_Enquiry.dbo.Sales_Details_Extract.Customer=d bo.Customers.Customer )
AND ( Sales_Enquiry.dbo.Sales_Details_Extract.Date between
Sales_Enquiry.dbo.Time_v.Period_Start and
Sales_Enquiry.dbo.Time_v.Period_End )
AND ( dbo.Customers.Sales_Channel=dbo.SCFlattened.SCSubT 6 )
AND (
( (
Sales_Enquiry.dbo.Time_v.Year ) = ( (select Current_Year from
BusObj_Control) ) )
AND Sales_Enquiry.dbo.Sales_Details_Extract.Sale_Type IN (' ', 'S',
'U')
AND Sales_Enquiry.dbo.Sales_Details_Extract.Sale_Categ ory NOT IN ('V',
'R')
AND
dbo.Customers.Customer NOT IN ('61384 ', '68812 ', '61383 ')
AND
dbo.Title_Hierarchy.ISBN NOT IN ('0141014075')
)
GROUP BY
Sales_Enquiry.dbo.Time_v.Year, Sales_Enquiry.dbo.Time_v.Month,
dbo.SCFlattened.SCSubT2Desc
The table sizes are as follows:
Sales_Details_Extract (view containing ~ 70 million rows)
Title_Hierarchy (Table containing ~180k rows)
Customers, (Table containing ~60k rows)
SCFlattened (Table containing ~30k rows)
Time_v (view containing ~ 80 rows)
Total DB Size ~ 33GB
On new hardware running Windows 2003 Server and SQL Server 2000 Enterprise
Ed. The same query causes tempdb to fill (56GB) before we run out of disk
resource. The database is a direct copy i.e. db detached and attached. The
indexes have recently been rebuilt and the 'auto update stats' option is
selected. The query plan seems to indicate a scan of the entire
Sales_Details (70 million rows) is taking place, and suspect a Cartesian
product is the cause of tempdb growing so large.
We've tried unsuccessfully placing the database including tempdb into 'SQL 7
Compatibility Mode'
Any Suggestions would be gratefully received. I have copies of the
execution plans if interested.
Regards,
Ian (ichinds@.hotmail.com)
Have you tried using query hints to influence the plan? "query hints" in
books online has some description.
If you want to try the compatibility mode, then it should be set on your
database, not tempdb.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ian" <ian.hinds@.pearsontc.co.uk> wrote in message
news:O%23r3sSX0EHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hello All,
>
> Can anybody throw some light on this problem...
>
> In SQL Server version 7, running on NT 4, the following query (generated
> by
> Business Objects, not me!) can be successfully executed in query analyser
> and returns correct results without heavily utilising tembdb. A seek is
> performed, based on date, on a clustered index only returning the rows
> from
> Sales_Details_Extract (fact table) that are required:
>
> SELECT
> sum(Sales_Enquiry.dbo.Sales_Details_Extract.Value) ,
> (CASE WHEN
> (
> Sales_Enquiry.dbo.Time_v.Month )=( (SELECT Current_Month FROM
> BusObj_Control) )
> THEN (
> sum(Sales_Enquiry.dbo.Sales_Details_Extract.Value)
> )
> ELSE 0 END),
> (CASE WHEN
> (
> Sales_Enquiry.dbo.Time_v.Month )=( (SELECT Current_Month FROM
> BusObj_Control) )
> THEN (
> sum(Sales_Enquiry.dbo.Sales_Details_Extract.Quanti ty)
> )
> ELSE 0 END),
> Sales_Enquiry.dbo.Time_v.Year,
> Sales_Enquiry.dbo.Time_v.Month,
> dbo.SCFlattened.SCSubT2Desc
> FROM
> Sales_Enquiry.dbo.Sales_Details_Extract,
> dbo.Title_Hierarchy,
> dbo.Customers,
> dbo.SCFlattened,
> Sales_Enquiry.dbo.Time_v
> WHERE
> ( Sales_Enquiry.dbo.Sales_Details_Extract.ISBN=dbo.T itle_Hierarchy.ISBN
> and
> Sales_Enquiry.dbo.Sales_Details_Extract.Source_Sys tem=dbo.Title_Hierarchy.So
> urce_System )
> AND (
> Sales_Enquiry.dbo.Sales_Details_Extract.Customer=d bo.Customers.Customer )
> AND ( Sales_Enquiry.dbo.Sales_Details_Extract.Date between
> Sales_Enquiry.dbo.Time_v.Period_Start and
> Sales_Enquiry.dbo.Time_v.Period_End )
> AND ( dbo.Customers.Sales_Channel=dbo.SCFlattened.SCSubT 6 )
> AND (
> ( (
> Sales_Enquiry.dbo.Time_v.Year ) = ( (select Current_Year from
> BusObj_Control) ) )
> AND Sales_Enquiry.dbo.Sales_Details_Extract.Sale_Type IN (' ', 'S',
> 'U')
> AND Sales_Enquiry.dbo.Sales_Details_Extract.Sale_Categ ory NOT IN ('V',
> 'R')
> AND
> dbo.Customers.Customer NOT IN ('61384 ', '68812 ', '61383 ')
> AND
> dbo.Title_Hierarchy.ISBN NOT IN ('0141014075')
> )
> GROUP BY
> Sales_Enquiry.dbo.Time_v.Year, Sales_Enquiry.dbo.Time_v.Month,
> dbo.SCFlattened.SCSubT2Desc
>
>
> The table sizes are as follows:
>
> Sales_Details_Extract (view containing ~ 70 million rows)
> Title_Hierarchy (Table containing ~180k rows)
> Customers, (Table containing ~60k rows)
> SCFlattened (Table containing ~30k rows)
> Time_v (view containing ~ 80 rows)
>
> Total DB Size ~ 33GB
>
>
> On new hardware running Windows 2003 Server and SQL Server 2000 Enterprise
> Ed. The same query causes tempdb to fill (56GB) before we run out of disk
> resource. The database is a direct copy i.e. db detached and attached.
> The
> indexes have recently been rebuilt and the 'auto update stats' option is
> selected. The query plan seems to indicate a scan of the entire
> Sales_Details (70 million rows) is taking place, and suspect a Cartesian
> product is the cause of tempdb growing so large.
>
> We've tried unsuccessfully placing the database including tempdb into 'SQL
> 7
> Compatibility Mode'
>
> Any Suggestions would be gratefully received. I have copies of the
> execution plans if interested.
>
> Regards,
>
> Ian (ichinds@.hotmail.com)
>
>
>
>
|||Ian,
I don't know how much influence you have over the query or the schema. I
assume you cannot change the query.
First of all, make sure you statistics are up to date. Run UPDATE
STATISTICS when in doubt.
Next, you could check if all join keys are of the same data type. For
example, column Time_v.Year should have the exact same data type (for
optimal performance) as BusObj_Control.Current_Year.
Also check if the columns in the WHERE clause have the same data type as
the literals of the expressions. For example, column Customers.Customer
should be char or varchar in order to match the data type of the literal
'61384 '.
Hope this helps,
Gert-Jan
Ian wrote:
> Hello All,
> Can anybody throw some light on this problem...
> In SQL Server version 7, running on NT 4, the following query (generated by
> Business Objects, not me!) can be successfully executed in query analyser
> and returns correct results without heavily utilising tembdb. A seek is
> performed, based on date, on a clustered index only returning the rows from
> Sales_Details_Extract (fact table) that are required:
> SELECT
> sum(Sales_Enquiry.dbo.Sales_Details_Extract.Value) ,
> (CASE WHEN
> (
> Sales_Enquiry.dbo.Time_v.Month )=( (SELECT Current_Month FROM
> BusObj_Control) )
> THEN (
> sum(Sales_Enquiry.dbo.Sales_Details_Extract.Value)
> )
> ELSE 0 END),
> (CASE WHEN
> (
> Sales_Enquiry.dbo.Time_v.Month )=( (SELECT Current_Month FROM
> BusObj_Control) )
> THEN (
> sum(Sales_Enquiry.dbo.Sales_Details_Extract.Quanti ty)
> )
> ELSE 0 END),
> Sales_Enquiry.dbo.Time_v.Year,
> Sales_Enquiry.dbo.Time_v.Month,
> dbo.SCFlattened.SCSubT2Desc
> FROM
> Sales_Enquiry.dbo.Sales_Details_Extract,
> dbo.Title_Hierarchy,
> dbo.Customers,
> dbo.SCFlattened,
> Sales_Enquiry.dbo.Time_v
> WHERE
> ( Sales_Enquiry.dbo.Sales_Details_Extract.ISBN=dbo.T itle_Hierarchy.ISBN and
> Sales_Enquiry.dbo.Sales_Details_Extract.Source_Sys tem=dbo.Title_Hierarchy.So
> urce_System )
> AND (
> Sales_Enquiry.dbo.Sales_Details_Extract.Customer=d bo.Customers.Customer )
> AND ( Sales_Enquiry.dbo.Sales_Details_Extract.Date between
> Sales_Enquiry.dbo.Time_v.Period_Start and
> Sales_Enquiry.dbo.Time_v.Period_End )
> AND ( dbo.Customers.Sales_Channel=dbo.SCFlattened.SCSubT 6 )
> AND (
> ( (
> Sales_Enquiry.dbo.Time_v.Year ) = ( (select Current_Year from
> BusObj_Control) ) )
> AND Sales_Enquiry.dbo.Sales_Details_Extract.Sale_Type IN (' ', 'S',
> 'U')
> AND Sales_Enquiry.dbo.Sales_Details_Extract.Sale_Categ ory NOT IN ('V',
> 'R')
> AND
> dbo.Customers.Customer NOT IN ('61384 ', '68812 ', '61383 ')
> AND
> dbo.Title_Hierarchy.ISBN NOT IN ('0141014075')
> )
> GROUP BY
> Sales_Enquiry.dbo.Time_v.Year, Sales_Enquiry.dbo.Time_v.Month,
> dbo.SCFlattened.SCSubT2Desc
> The table sizes are as follows:
> Sales_Details_Extract (view containing ~ 70 million rows)
> Title_Hierarchy (Table containing ~180k rows)
> Customers, (Table containing ~60k rows)
> SCFlattened (Table containing ~30k rows)
> Time_v (view containing ~ 80 rows)
> Total DB Size ~ 33GB
> On new hardware running Windows 2003 Server and SQL Server 2000 Enterprise
> Ed. The same query causes tempdb to fill (56GB) before we run out of disk
> resource. The database is a direct copy i.e. db detached and attached. The
> indexes have recently been rebuilt and the 'auto update stats' option is
> selected. The query plan seems to indicate a scan of the entire
> Sales_Details (70 million rows) is taking place, and suspect a Cartesian
> product is the cause of tempdb growing so large.
> We've tried unsuccessfully placing the database including tempdb into 'SQL 7
> Compatibility Mode'
> Any Suggestions would be gratefully received. I have copies of the
> execution plans if interested.
> Regards,
> Ian (ichinds@.hotmail.com)

Optimiser issues between SQL Server 7 and SQL Server 2000 Enterprise

Hello All,
Can anybody throw some light on this problem...
In SQL Server version 7, running on NT 4, the following query (generated by
Business Objects, not me!) can be successfully executed in query analyser
and returns correct results without heavily utilising tembdb. A seek is
performed, based on date, on a clustered index only returning the rows from
Sales_Details_Extract (fact table) that are required:
SELECT
sum(Sales_Enquiry.dbo.Sales_Details_Extract.Value),
(CASE WHEN
(
Sales_Enquiry.dbo.Time_v.Month )=( (SELECT Current_Month FROM
BusObj_Control) )
THEN (
sum(Sales_Enquiry.dbo.Sales_Details_Extract.Value)
)
ELSE 0 END),
(CASE WHEN
(
Sales_Enquiry.dbo.Time_v.Month )=( (SELECT Current_Month FROM
BusObj_Control) )
THEN (
sum(Sales_Enquiry.dbo.Sales_Details_Extract.Quantity)
)
ELSE 0 END),
Sales_Enquiry.dbo.Time_v.Year,
Sales_Enquiry.dbo.Time_v.Month,
dbo.SCFlattened.SCSubT2Desc
FROM
Sales_Enquiry.dbo.Sales_Details_Extract,
dbo.Title_Hierarchy,
dbo.Customers,
dbo.SCFlattened,
Sales_Enquiry.dbo.Time_v
WHERE
( Sales_Enquiry.dbo.Sales_Details_Extract.ISBN=dbo.Title_Hierarchy.ISBN and
Sales_Enquiry.dbo.Sales_Details_Extract.Source_System=dbo.Title_Hierarchy.So
urce_System )
AND (
Sales_Enquiry.dbo.Sales_Details_Extract.Customer=dbo.Customers.Customer )
AND ( Sales_Enquiry.dbo.Sales_Details_Extract.Date between
Sales_Enquiry.dbo.Time_v.Period_Start and
Sales_Enquiry.dbo.Time_v.Period_End )
AND ( dbo.Customers.Sales_Channel=dbo.SCFlattened.SCSubT6 )
AND (
( (
Sales_Enquiry.dbo.Time_v.Year ) = ( (select Current_Year from
BusObj_Control) ) )
AND Sales_Enquiry.dbo.Sales_Details_Extract.Sale_Type IN (' ', 'S',
'U')
AND Sales_Enquiry.dbo.Sales_Details_Extract.Sale_Category NOT IN ('V',
'R')
AND
dbo.Customers.Customer NOT IN ('61384 ', '68812 ', '61383 ')
AND
dbo.Title_Hierarchy.ISBN NOT IN ('0141014075')
)
GROUP BY
Sales_Enquiry.dbo.Time_v.Year, Sales_Enquiry.dbo.Time_v.Month,
dbo.SCFlattened.SCSubT2Desc
The table sizes are as follows:
Sales_Details_Extract (view containing ~ 70 million rows)
Title_Hierarchy (Table containing ~180k rows)
Customers, (Table containing ~60k rows)
SCFlattened (Table containing ~30k rows)
Time_v (view containing ~ 80 rows)
Total DB Size ~ 33GB
On new hardware running Windows 2003 Server and SQL Server 2000 Enterprise
Ed. The same query causes tempdb to fill (56GB) before we run out of disk
resource. The database is a direct copy i.e. db detached and attached. The
indexes have recently been rebuilt and the 'auto update stats' option is
selected. The query plan seems to indicate a scan of the entire
Sales_Details (70 million rows) is taking place, and suspect a Cartesian
product is the cause of tempdb growing so large.
We've tried unsuccessfully placing the database including tempdb into 'SQL 7
Compatibility Mode'
Any Suggestions would be gratefully received. I have copies of the
execution plans if interested.
Regards,
Ian (ichinds@.hotmail.com)Have you tried using query hints to influence the plan? "query hints" in
books online has some description.
If you want to try the compatibility mode, then it should be set on your
database, not tempdb.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ian" <ian.hinds@.pearsontc.co.uk> wrote in message
news:O%23r3sSX0EHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hello All,
>
> Can anybody throw some light on this problem...
>
> In SQL Server version 7, running on NT 4, the following query (generated
> by
> Business Objects, not me!) can be successfully executed in query analyser
> and returns correct results without heavily utilising tembdb. A seek is
> performed, based on date, on a clustered index only returning the rows
> from
> Sales_Details_Extract (fact table) that are required:
>
> SELECT
> sum(Sales_Enquiry.dbo.Sales_Details_Extract.Value),
> (CASE WHEN
> (
> Sales_Enquiry.dbo.Time_v.Month )=( (SELECT Current_Month FROM
> BusObj_Control) )
> THEN (
> sum(Sales_Enquiry.dbo.Sales_Details_Extract.Value)
> )
> ELSE 0 END),
> (CASE WHEN
> (
> Sales_Enquiry.dbo.Time_v.Month )=( (SELECT Current_Month FROM
> BusObj_Control) )
> THEN (
> sum(Sales_Enquiry.dbo.Sales_Details_Extract.Quantity)
> )
> ELSE 0 END),
> Sales_Enquiry.dbo.Time_v.Year,
> Sales_Enquiry.dbo.Time_v.Month,
> dbo.SCFlattened.SCSubT2Desc
> FROM
> Sales_Enquiry.dbo.Sales_Details_Extract,
> dbo.Title_Hierarchy,
> dbo.Customers,
> dbo.SCFlattened,
> Sales_Enquiry.dbo.Time_v
> WHERE
> ( Sales_Enquiry.dbo.Sales_Details_Extract.ISBN=dbo.Title_Hierarchy.ISBN
> and
> Sales_Enquiry.dbo.Sales_Details_Extract.Source_System=dbo.Title_Hierarchy.
So
> urce_System )
> AND (
> Sales_Enquiry.dbo.Sales_Details_Extract.Customer=dbo.Customers.Customer )
> AND ( Sales_Enquiry.dbo.Sales_Details_Extract.Date between
> Sales_Enquiry.dbo.Time_v.Period_Start and
> Sales_Enquiry.dbo.Time_v.Period_End )
> AND ( dbo.Customers.Sales_Channel=dbo.SCFlattened.SCSubT6 )
> AND (
> ( (
> Sales_Enquiry.dbo.Time_v.Year ) = ( (select Current_Year from
> BusObj_Control) ) )
> AND Sales_Enquiry.dbo.Sales_Details_Extract.Sale_Type IN (' ', 'S',
> 'U')
> AND Sales_Enquiry.dbo.Sales_Details_Extract.Sale_Category NOT IN ('V',
> 'R')
> AND
> dbo.Customers.Customer NOT IN ('61384 ', '68812 ', '61383 ')
> AND
> dbo.Title_Hierarchy.ISBN NOT IN ('0141014075')
> )
> GROUP BY
> Sales_Enquiry.dbo.Time_v.Year, Sales_Enquiry.dbo.Time_v.Month,
> dbo.SCFlattened.SCSubT2Desc
>
>
> The table sizes are as follows:
>
> Sales_Details_Extract (view containing ~ 70 million rows)
> Title_Hierarchy (Table containing ~180k rows)
> Customers, (Table containing ~60k rows)
> SCFlattened (Table containing ~30k rows)
> Time_v (view containing ~ 80 rows)
>
> Total DB Size ~ 33GB
>
>
> On new hardware running Windows 2003 Server and SQL Server 2000 Enterprise
> Ed. The same query causes tempdb to fill (56GB) before we run out of disk
> resource. The database is a direct copy i.e. db detached and attached.
> The
> indexes have recently been rebuilt and the 'auto update stats' option is
> selected. The query plan seems to indicate a scan of the entire
> Sales_Details (70 million rows) is taking place, and suspect a Cartesian
> product is the cause of tempdb growing so large.
>
> We've tried unsuccessfully placing the database including tempdb into 'SQL
> 7
> Compatibility Mode'
>
> Any Suggestions would be gratefully received. I have copies of the
> execution plans if interested.
>
> Regards,
>
> Ian (ichinds@.hotmail.com)
>
>
>
>|||Ian,
I don't know how much influence you have over the query or the schema. I
assume you cannot change the query.
First of all, make sure you statistics are up to date. Run UPDATE
STATISTICS when in doubt.
Next, you could check if all join keys are of the same data type. For
example, column Time_v.Year should have the exact same data type (for
optimal performance) as BusObj_Control.Current_Year.
Also check if the columns in the WHERE clause have the same data type as
the literals of the expressions. For example, column Customers.Customer
should be char or varchar in order to match the data type of the literal
'61384 '.
Hope this helps,
Gert-Jan
Ian wrote:
> Hello All,
> Can anybody throw some light on this problem...
> In SQL Server version 7, running on NT 4, the following query (generated b
y
> Business Objects, not me!) can be successfully executed in query analyser
> and returns correct results without heavily utilising tembdb. A seek is
> performed, based on date, on a clustered index only returning the rows fro
m
> Sales_Details_Extract (fact table) that are required:
> SELECT
> sum(Sales_Enquiry.dbo.Sales_Details_Extract.Value),
> (CASE WHEN
> (
> Sales_Enquiry.dbo.Time_v.Month )=( (SELECT Current_Month FROM
> BusObj_Control) )
> THEN (
> sum(Sales_Enquiry.dbo.Sales_Details_Extract.Value)
> )
> ELSE 0 END),
> (CASE WHEN
> (
> Sales_Enquiry.dbo.Time_v.Month )=( (SELECT Current_Month FROM
> BusObj_Control) )
> THEN (
> sum(Sales_Enquiry.dbo.Sales_Details_Extract.Quantity)
> )
> ELSE 0 END),
> Sales_Enquiry.dbo.Time_v.Year,
> Sales_Enquiry.dbo.Time_v.Month,
> dbo.SCFlattened.SCSubT2Desc
> FROM
> Sales_Enquiry.dbo.Sales_Details_Extract,
> dbo.Title_Hierarchy,
> dbo.Customers,
> dbo.SCFlattened,
> Sales_Enquiry.dbo.Time_v
> WHERE
> ( Sales_Enquiry.dbo.Sales_Details_Extract.ISBN=dbo.Title_Hierarchy.ISBN an
d
> Sales_Enquiry.dbo.Sales_Details_Extract.Source_System=dbo.Title_Hierarchy.
So
> urce_System )
> AND (
> Sales_Enquiry.dbo.Sales_Details_Extract.Customer=dbo.Customers.Customer )
> AND ( Sales_Enquiry.dbo.Sales_Details_Extract.Date between
> Sales_Enquiry.dbo.Time_v.Period_Start and
> Sales_Enquiry.dbo.Time_v.Period_End )
> AND ( dbo.Customers.Sales_Channel=dbo.SCFlattened.SCSubT6 )
> AND (
> ( (
> Sales_Enquiry.dbo.Time_v.Year ) = ( (select Current_Year from
> BusObj_Control) ) )
> AND Sales_Enquiry.dbo.Sales_Details_Extract.Sale_Type IN (' ', 'S',
> 'U')
> AND Sales_Enquiry.dbo.Sales_Details_Extract.Sale_Category NOT IN ('V'
,
> 'R')
> AND
> dbo.Customers.Customer NOT IN ('61384 ', '68812 ', '61383 ')
> AND
> dbo.Title_Hierarchy.ISBN NOT IN ('0141014075')
> )
> GROUP BY
> Sales_Enquiry.dbo.Time_v.Year, Sales_Enquiry.dbo.Time_v.Month,
> dbo.SCFlattened.SCSubT2Desc
> The table sizes are as follows:
> Sales_Details_Extract (view containing ~ 70 million rows)
> Title_Hierarchy (Table containing ~180k rows)
> Customers, (Table containing ~60k rows)
> SCFlattened (Table containing ~30k rows)
> Time_v (view containing ~ 80 rows)
> Total DB Size ~ 33GB
> On new hardware running Windows 2003 Server and SQL Server 2000 Enterprise
> Ed. The same query causes tempdb to fill (56GB) before we run out of disk
> resource. The database is a direct copy i.e. db detached and attached. T
he
> indexes have recently been rebuilt and the 'auto update stats' option is
> selected. The query plan seems to indicate a scan of the entire
> Sales_Details (70 million rows) is taking place, and suspect a Cartesian
> product is the cause of tempdb growing so large.
> We've tried unsuccessfully placing the database including tempdb into 'SQL
7
> Compatibility Mode'
> Any Suggestions would be gratefully received. I have copies of the
> execution plans if interested.
> Regards,
> Ian (ichinds@.hotmail.com)

Optimiser and UDF's

I came across something today that hopefully if my explanation is
wrong someone could correct me.
I was trying to speed up a stored proc that took an integer as a
parameter and then ran around a dozen SQL statements to populate a
table variable.
1st query was :-
select <some columns> from A where A.id = @.id
The others were along those lines but joined to several lookup tables
and other combinations. Basically though it was hitting table A with
the same search conditions each time (id = @.id).
Thinking this was a waste I wrote a UDF that took the id as a
parameter and returned a table with just the relevant records from
table A in it. The SQL in the stored proc was then changed as follows
:-
select <some columns> from myUDF(@.id)
The new stored proc now runs twice as fast, uses half the cpu time and
a third of the disk io.
Would I be right in saying that SQL2k has realised that the UDF had
been called a dozen times with the same parameters, ran it once and
cached the results?
Its the only reason I can see for such a big performance improvement.
cheers.No, UDF results are not cached in SQL 2k, but that might be a nice feature
for Yukon... Sybase, for instance, does cache udf results...If your efforts
lowered the number of joins that were being done, I suspect the improvement
lies there.
"Mike Watson" <mike@.prog99.com> wrote in message
news:q1h7lvoluabdh7ulaigatf1ndn05gaofo8@.4ax.com...
> I came across something today that hopefully if my explanation is
> wrong someone could correct me.
> I was trying to speed up a stored proc that took an integer as a
> parameter and then ran around a dozen SQL statements to populate a
> table variable.
> 1st query was :-
> select <some columns> from A where A.id = @.id
> The others were along those lines but joined to several lookup tables
> and other combinations. Basically though it was hitting table A with
> the same search conditions each time (id = @.id).
> Thinking this was a waste I wrote a UDF that took the id as a
> parameter and returned a table with just the relevant records from
> table A in it. The SQL in the stored proc was then changed as follows
> :-
> select <some columns> from myUDF(@.id)
> The new stored proc now runs twice as fast, uses half the cpu time and
> a third of the disk io.
> Would I be right in saying that SQL2k has realised that the UDF had
> been called a dozen times with the same parameters, ran it once and
> cached the results?
> Its the only reason I can see for such a big performance improvement.
> cheers.
>|||On Tue, 2 Sep 2003 07:45:12 -0400, "Wayne Snyder"
<wsnyder@.computeredservices.com> wrote:
>No, UDF results are not cached in SQL 2k, but that might be a nice feature
>for Yukon... Sybase, for instance, does cache udf results...If your efforts
>lowered the number of joins that were being done, I suspect the improvement
>lies there.
>
Thanks for the reply, I'm surprised that the overhead of calling the
UDF around a dozen times is so much cheaper than doing a striaght join
on the table.