Optimizing Queries generated at runtime

Hi

We are using SQL SERVER 2005, enterprise/standard edition for an application that generates queries at runtime and normally having many joins in it. But these queries are taking lot of time when they are executed. After setting the database parameter PARAMETERIZATION to FORCED, performance of the queries have improved a lot but still we want to improve them further. Is there any other parameter that we can set for improving the performance of the queries. We have created the indexes on the basic columns that will be used while querying but it may happen that some other columns may also be used for searching in which case queries become quiet slow. Is there anything like skip-scan indexes(of oracle) in sql server 2005.

Regards
Salil

Check if there are columns that participate in WHERE clause and have not indexes.

You say "an application that generates queries at runtime", try to use parametrized stored procedures that build dynamically queries using sp_executesql;

in queries don't use "select * from ..." use "select col1,col2,... from ..."

try to use WHERE clause in queries to force using indexes.

you say your queries have many joins on it, try to use indexed views, see article|||1. We have created the indexes on the main columns that we know will be included in the where clause but there can be some columns, we dont know, that can be included in the where clause.
2. Going for parameterized stored procedures is a nice option but we cant go for them at this stage of our product.
3. We are using column names in the select clause rather than "select * "
4. We have many joins but we dont know join will be made to which table.

Thats why we are looking for something more generic like the PARAMETERIZATION parameter. Is there anything else that we can set at the database level for improving the performance of our queries.

|||

Salil wrote:

1. We have created the indexes on the main columns that we know will be included in the where clause but there can be some columns, we dont know, that can be included in the where clause.
2. Going for parameterized stored procedures is a nice option but we cant go for them at this stage of our product.
3. We are using column names in the select clause rather than "select * "
4. We have many joins but we dont know join will be made to which table.

I think you work with a black box and you can optimize outside it; i think you haven't many choices

Check if database options related to STATISTICS are on "ON" option.

So, I think you have to open that black box....

|||we have already set AUTO_UPDATE_STATISTICS_ASYNC ON at the database level.

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)