Showing posts with label build. Show all posts
Showing posts with label build. Show all posts

Tuesday, March 20, 2012

Query Performance

Good Morning All!
I have a couple questions regarding query performance:
#1 - I have a VB6 program that allows the user to build
the where clause of a query to be ran against a SQL 2000
database. When the where clause is established, it is
sent into a stored procedure where there are 3 different
queries that can be executed based on the criteria being
used. Basically, the join structure of each query is
different. I created an index for a query that had been
running slow that executes 1 of the 3 queries in the
stored procedure. However, now whenever 1 of the other
queries are executed for a different set of criteria it is
3X longer to run. I remove the index, and it is fast
again, but my other query is slow again. Can someone
please explain to me why this happens, and why SQL chooses
a less optimized plan for some of the queries when a new
index is added?
#2 - In the same type of environment as explained above
(i.e. VB6, SQL 2000, and stored procedures), we have a
single user that tests query speed to determine what
indexes need built. We can get a query to run in 5
seconds, but when multiple users try to execute the same
or different queries together, it takes nearly 5X longer.
Once the first user gets results, then the others come
back one right after the other in no time. Does SQL only
allow 1 person to execute a stored procedure at any given
time? What effect does the mult-user environment have in
this scenerio.
Any help would be greatly appreciated, as I am new to
working with indexes.
Thank you,
Heidifor number 1...
Posting the text of the procedure would allow a more precise answer, but I
suspect the following info will help you...
Procedure plans are cached and are not recompiled if there is a plan already
in cache that can be reused. (I'm greatly simplying, but that's accurate
enough for this discussion...)
In your case, you have a proc that might run a variety of different queries
and for each query it sounds like the parameters might be quite different
from run to run. A good plan for one of the queries might not be a good plan
for the other queries. However, if a plan is already cached, it may be
re-used even if it's not the best plan. You might want to experiment with
having the 'top level' procedure call one of 3 other procs where each of the
child procs accepts the parameters.
for number 2...
It's difficult to say without more data. The most likley scenarios are a)
blocking. Do the queries update,delete,insert data? or b) waiting on some
type of resource. Search www.sqlmag.com archieves for an article (with
scripts) by Tom Davidson from MS. Use the script to run dbcc
sqlperfwaitstats and see if you have a high wait in any area. I suspect you
may be having a high wait of pageiolatch_sh. ALso, while the queries are
'running' and are not returning data untilt he first query is done... you
could look at the row in master..sysprocesses for each of the 'waiting'
queries to see what the waittype is.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:756b01c494dc$6bf12be0$a301280a@.phx.gbl...
> Good Morning All!
> I have a couple questions regarding query performance:
> #1 - I have a VB6 program that allows the user to build
> the where clause of a query to be ran against a SQL 2000
> database. When the where clause is established, it is
> sent into a stored procedure where there are 3 different
> queries that can be executed based on the criteria being
> used. Basically, the join structure of each query is
> different. I created an index for a query that had been
> running slow that executes 1 of the 3 queries in the
> stored procedure. However, now whenever 1 of the other
> queries are executed for a different set of criteria it is
> 3X longer to run. I remove the index, and it is fast
> again, but my other query is slow again. Can someone
> please explain to me why this happens, and why SQL chooses
> a less optimized plan for some of the queries when a new
> index is added?
> #2 - In the same type of environment as explained above
> (i.e. VB6, SQL 2000, and stored procedures), we have a
> single user that tests query speed to determine what
> indexes need built. We can get a query to run in 5
> seconds, but when multiple users try to execute the same
> or different queries together, it takes nearly 5X longer.
> Once the first user gets results, then the others come
> back one right after the other in no time. Does SQL only
> allow 1 person to execute a stored procedure at any given
> time? What effect does the mult-user environment have in
> this scenerio.
> Any help would be greatly appreciated, as I am new to
> working with indexes.
> Thank you,
> Heidi|||Also have a look at
http://www.sommarskog.se/dyn-search.html
--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:756b01c494dc$6bf12be0$a301280a@.phx.gbl...
> Good Morning All!
> I have a couple questions regarding query performance:
> #1 - I have a VB6 program that allows the user to build
> the where clause of a query to be ran against a SQL 2000
> database. When the where clause is established, it is
> sent into a stored procedure where there are 3 different
> queries that can be executed based on the criteria being
> used. Basically, the join structure of each query is
> different. I created an index for a query that had been
> running slow that executes 1 of the 3 queries in the
> stored procedure. However, now whenever 1 of the other
> queries are executed for a different set of criteria it is
> 3X longer to run. I remove the index, and it is fast
> again, but my other query is slow again. Can someone
> please explain to me why this happens, and why SQL chooses
> a less optimized plan for some of the queries when a new
> index is added?
> #2 - In the same type of environment as explained above
> (i.e. VB6, SQL 2000, and stored procedures), we have a
> single user that tests query speed to determine what
> indexes need built. We can get a query to run in 5
> seconds, but when multiple users try to execute the same
> or different queries together, it takes nearly 5X longer.
> Once the first user gets results, then the others come
> back one right after the other in no time. Does SQL only
> allow 1 person to execute a stored procedure at any given
> time? What effect does the mult-user environment have in
> this scenerio.
> Any help would be greatly appreciated, as I am new to
> working with indexes.
> Thank you,
> Heidi|||hdsjunk wrote:
> Good Morning All!
> I have a couple questions regarding query performance:
> #1 - I have a VB6 program that allows the user to build
> the where clause of a query to be ran against a SQL 2000
> database. When the where clause is established, it is
> sent into a stored procedure where there are 3 different
> queries that can be executed based on the criteria being
> used. Basically, the join structure of each query is
> different. I created an index for a query that had been
> running slow that executes 1 of the 3 queries in the
> stored procedure. However, now whenever 1 of the other
> queries are executed for a different set of criteria it is
> 3X longer to run. I remove the index, and it is fast
> again, but my other query is slow again. Can someone
> please explain to me why this happens, and why SQL chooses
> a less optimized plan for some of the queries when a new
> index is added?
> #2 - In the same type of environment as explained above
> (i.e. VB6, SQL 2000, and stored procedures), we have a
> single user that tests query speed to determine what
> indexes need built. We can get a query to run in 5
> seconds, but when multiple users try to execute the same
> or different queries together, it takes nearly 5X longer.
> Once the first user gets results, then the others come
> back one right after the other in no time. Does SQL only
> allow 1 person to execute a stored procedure at any given
> time? What effect does the mult-user environment have in
> this scenerio.
> Any help would be greatly appreciated, as I am new to
> working with indexes.
> Thank you,
> Heidi
If a query takes 5 seconds to run on a server with low utlization, then
the query is unlikely running efficiently as it is consuming 5 seconds
of duration and likely something similar in CPU. I would say you need to
performance tune the query to run in much less than 5 seconds. Something
in the 10's of milliseconds should be your goal.
As other posters have mentioned, if it's taking 5 seconds, it's likely
locking pages on the same tables other callers to the same procedure
require.
While 5 seconds may be an appropriate time for an individual end-user to
wait for a response from the application, it usually won't do in a
concurrent database environment. Use Profiler and Query Analyzer to see
what is taking so long in the query to execute and try and tune it.
Also, as others have said, you are risking recompiles sending 3
different where clauses for execution to the same stored procedure. The
way to eliminate this is to use sp_executesql inside the procedure to
execute the dynamic SQL.
David G.

Query performance

Hello,
I wrote the query below, but I was wondering whether there was a better way
of doing so. I am basically trying to build a recursive process.
Any suggestion would be greatly appreciated.
Thanks.
Mike
****************************************
************************************
*****************
****************************************
************************************
*****************
CREATE PROCEDURE dbo.CustomersFiltered
(
@.filter varchar(50)
)
AS
create table #t
(
dummyId bigint IDENTITY(1,1) PRIMARY KEY,
customerId bigint,
parentId bigint,
levelId int
)
INSERT #t (customerId, parentId, levelId)
SELECT customerAutoId, parentCustomerAutoId, 0
FROM customers
WHERE [name] like @.filter
declare @.dummyId bigint
select @.dummyId = 1
declare @.customerId bigint
declare @.parentId bigint
declare @.levelId bigint
WHILE (SELECT COUNT(*) FROM #t WHERE dummyId = @.dummyId) > 0
BEGIN
select @.customerId = customerId,
@.parentId = parentId,
@.levelId = levelId
FROM #t
WHERE dummyId = @.dummyId
insert into #t (customerId, parentId, levelId)
select CustomerAutoId,
parentCustomerAutoId,
@.levelId - 1
from customers
where parentCustomerAutoId = @.customerId
group by CustomerAutoId,
parentCustomerAutoId
if (@.parentId is not null)
insert into #t (customerId, parentId, levelId)
select c.CustomerAutoId,
c.parentCustomerAutoId,
@.levelId + 1
from customers c
where c.customerAutoId = @.parentId
and not exists (select * from #t where c.customerAutoId = customerId)
group by CustomerAutoId,
parentCustomerAutoId
select @.dummyId = @.dummyId + 1
END
select t.levelId as LevelId,
t.customerId as CustomerAutoId,
c.[name] as [Name],
c.sic as SIC,
c.ParentCustomerAutoId as ParentCustomerAutoId
from #t t
inner join customers c on c.customerAutoId = t.customerId
group by t.levelId,
t.customerId,
c.[name],
c.sic,
c.ParentCustomerAutoId
order by levelId desc,
c.ParentCustomerAutoId,
c.[name]
drop table #t
GOIf you're using SQL Server 2005, you should consider using Common Table
Expressions (CTE) instead. Temporary tables suffer from writing data to
the tempdb database, whereas CTEs are in memory only.
A good walktrough is available at the MSDN site:
http://msdn2.microsoft.com/en-us/library/ms186243.aspx
Hope this helps
"Mike" <none@.none.com> wrote in
news:uMSJXrFrGHA.4112@.TK2MSFTNGP02.phx.gbl:

> I wrote the query below, but I was wondering whether there was a
> better way of doing so. I am basically trying to build a recursive
> process.
> Any suggestion would be greatly appreciated.
> Thanks.
> Mike
>
> ****************************************
******************************
*
> **********************
> ****************************************
******************************
*
> **********************
> CREATE PROCEDURE dbo.CustomersFiltered
> (
> @.filter varchar(50)
> )
> AS
>
> create table #t
> (
> dummyId bigint IDENTITY(1,1) PRIMARY KEY,
> customerId bigint,
> parentId bigint,
> levelId int
> )
>
> INSERT #t (customerId, parentId, levelId)
> SELECT customerAutoId, parentCustomerAutoId, 0
> FROM customers
> WHERE [name] like @.filter
>
> declare @.dummyId bigint
> select @.dummyId = 1
>
> declare @.customerId bigint
> declare @.parentId bigint
> declare @.levelId bigint
> WHILE (SELECT COUNT(*) FROM #t WHERE dummyId = @.dummyId) > 0
> BEGIN
> select @.customerId = customerId,
> @.parentId = parentId,
> @.levelId = levelId
> FROM #t
> WHERE dummyId = @.dummyId
> insert into #t (customerId, parentId, levelId)
> select CustomerAutoId,
> parentCustomerAutoId,
> @.levelId - 1
> from customers
> where parentCustomerAutoId = @.customerId
> group by CustomerAutoId,
> parentCustomerAutoId
> if (@.parentId is not null)
> insert into #t (customerId, parentId, levelId)
> select c.CustomerAutoId,
> c.parentCustomerAutoId,
> @.levelId + 1
> from customers c
> where c.customerAutoId = @.parentId
> and not exists (select * from #t where c.customerAutoId =
> customerId)
> group by CustomerAutoId,
> parentCustomerAutoId
>
> select @.dummyId = @.dummyId + 1
> END
>
> select t.levelId as LevelId,
> t.customerId as CustomerAutoId,
> c.[name] as [Name],
> c.sic as SIC,
> c.ParentCustomerAutoId as ParentCustomerAutoId
> from #t t
> inner join customers c on c.customerAutoId = t.customerId
> group by t.levelId,
> t.customerId,
> c.[name],
> c.sic,
> c.ParentCustomerAutoId
> order by levelId desc,
> c.ParentCustomerAutoId,
> c.[name]
>
> drop table #t
> GO
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||I am using SQL Server 2000 SP4.
Is there anything that I could improve in my query? I checked all indexes,
even in the temp table, but I ran out of ideas.
Thanks.
Mike
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns98075D16535A0olekristianbangaas@.
207.46.248.16...
> If you're using SQL Server 2005, you should consider using Common Table
> Expressions (CTE) instead. Temporary tables suffer from writing data to
> the tempdb database, whereas CTEs are in memory only.
> A good walktrough is available at the MSDN site:
> http://msdn2.microsoft.com/en-us/library/ms186243.aspx
> Hope this helps
>
> "Mike" <none@.none.com> wrote in
> news:uMSJXrFrGHA.4112@.TK2MSFTNGP02.phx.gbl:
>
> *
> *
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP

Monday, March 12, 2012

Query optimizer not using index!

I have a test sql 7 and test sql 2000 database with the
same table structure and number of rows loaded.
When I add a new column and build an index on it in sql 7,
the optimizer uses the index and the query returns in 5
seconds. However, when I add the new column and build an
index on it i sql 2000, the optimizer does not use the new
index and runs over 5 minutes. If I put an Index hint on
the query then it uses the index and runs in 5 seconds.
Why will not sql 2000 use this new index but sql 7 uses it
without specifying the hint'
Thanks RichYou'll need to post a lot more information before anyone could take a guess.
But first, have you made sure that the statistics on the SQL Server 2000
database have been updated?
If that doesn't solve your problem, then what do the table and index
definitions look like? Can you post the DDL? What does the data actually
look like? You mention that both databases have the same number of rows
loaded, but are the values in the rows actually identical?
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"Rich" <rstoll@.cadencenet.com> wrote in message
news:04cf01c36111$680d3460$a301280a@.phx.gbl...
> I have a test sql 7 and test sql 2000 database with the
> same table structure and number of rows loaded.
> When I add a new column and build an index on it in sql 7,
> the optimizer uses the index and the query returns in 5
> seconds. However, when I add the new column and build an
> index on it i sql 2000, the optimizer does not use the new
> index and runs over 5 minutes. If I put an Index hint on
> the query then it uses the index and runs in 5 seconds.
> Why will not sql 2000 use this new index but sql 7 uses it
> without specifying the hint'
> Thanks Rich|||put the two queries (with and without index hint)
in a Query Analyzer window
and use the Display Estimated Execution Plan
does the plan for not using the index show up as lower
cost? even though it took much longer to run
if so, this is a deliberate decision by the optimizer to
switch to a table instead of an index seek with bookmark
lookups
also, do the two systems have different amount of memory,
specically, is one <=1GB and another >1GB
this would also affect when the optimizer switches from a
index to a scan
>--Original Message--
>I have a test sql 7 and test sql 2000 database with the
>same table structure and number of rows loaded.
>When I add a new column and build an index on it in sql
7,
>the optimizer uses the index and the query returns in 5
>seconds. However, when I add the new column and build an
>index on it i sql 2000, the optimizer does not use the
new
>index and runs over 5 minutes. If I put an Index hint on
>the query then it uses the index and runs in 5 seconds.
>Why will not sql 2000 use this new index but sql 7 uses
it
>without specifying the hint'
>Thanks Rich
>.
>|||Hal,
I created the statistics on the CustID. The tables have
the same no. of rows and values. I used DTS to copy the
data from sql 7 to sql 2000.
Here is the query (with the hint specified to make it run
fast):
Select Distinct uab.BillDocID, bs.BilLSrvID,bs.SrvType
FROM tbl_bill_srv bs,
tbl_chrg_non_elec_mtr cn with (index(ix_custid)),
tbl_util_acct_bill uab
Where (uab.DateRowCreated >= '2003-06-01' AND
uab.DateRowCreated <= '2003-07-01')
and cn.custid = 169
AND uab.BilLDocID = cn.BilLDocID
And cn.BillSrvID = bs.BilLSrvID
Here is the DDL:
CREATE TABLE [dbo].[tbl_chrg_non_elec_mtr] (
[ChrgNonElecMtrID] [int] NOT NULL ,
[CustID] [int] NOT NULL ,
[BillSrvID] [int] NOT NULL ,
[AcctngPer] [datetime] NOT NULL ,
[AcctID] [int] NOT NULL ,
[UtilID] [int] NOT NULL ,
[AcctNo] [varchar] (22) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BillDocID] [int] NOT NULL ,
[CsmtnPer] [datetime] NULL ,
[StartDate] [datetime] NULL ,
[StopDate] [datetime] NULL ,
[NMtrChrgType] [varchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TaxCode] [bit] NOT NULL ,
[Qty] [float] NULL ,
[QtyUM] [char] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Chrg] [money] NOT NULL ,
[ModBy] [char] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ModDate] [datetime] NOT NULL ,
[timestamp] [timestamp] NULL ,
[Chrg_CurrencyTypeID] [tinyint] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_chrg_non_elec_mtr] WITH NOCHECK ADD
CONSTRAINT [PK_chrg_non_elec_mtr] PRIMARY KEY
CLUSTERED
(
[ChrgNonElecMtrID]
) WITH FILLFACTOR = 85 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_chrg_non_elec_mtr] ADD
CONSTRAINT [DF_NON_Chrg_CurrencyTypeID] DEFAULT
(1) FOR [Chrg_CurrencyTypeID]
GO
/****** The index created by the following statement is
for internal use only. ******/
/****** It is not a real index but exists as statistics
only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_202_8] ON [dbo].
[tbl_chrg_non_elec_mtr] ([CsmtnPer]) ')
GO
CREATE INDEX [i1_tbl_chrg_non_elec_mtr] ON [dbo].
[tbl_chrg_non_elec_mtr]([BillDocID]) WITH FILLFACTOR =85, PAD_INDEX ON [PRIMARY]
GO
CREATE INDEX [i2_tbl_chrg_non_elec_mtr] ON [dbo].
[tbl_chrg_non_elec_mtr]([BillSrvID]) WITH FILLFACTOR =85, PAD_INDEX ON [PRIMARY]
GO
CREATE INDEX [i3_tbl_chrg_non_elec_mtr] ON [dbo].
[tbl_chrg_non_elec_mtr]([AcctID]) WITH FILLFACTOR = 85,
PAD_INDEX ON [PRIMARY]
GO
CREATE INDEX [i4_tbl_chrg_non_elec_mtr] ON [dbo].
[tbl_chrg_non_elec_mtr]([CsmtnPer]) WITH FILLFACTOR =85, PAD_INDEX ON [PRIMARY]
GO
CREATE INDEX [i5_tbl_chrg_non_elec_mtr] ON [dbo].
[tbl_chrg_non_elec_mtr]([BillDocID], [CsmtnPer],
[StartDate], [StopDate]) ON [PRIMARY]
GO
CREATE INDEX [IX_custid] ON [dbo].[tbl_chrg_non_elec_mtr]
([CustID]) ON [PRIMARY]
GO
/****** The index created by the following statement is
for internal use only. ******/
/****** It is not a real index but exists as statistics
only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_custid] ON [dbo].
[tbl_chrg_non_elec_mtr] ([CustID]) ')
GO
>--Original Message--
>You'll need to post a lot more information before anyone
could take a guess.
>But first, have you made sure that the statistics on the
SQL Server 2000
>database have been updated?
>If that doesn't solve your problem, then what do the
table and index
>definitions look like? Can you post the DDL? What does
the data actually
>look like? You mention that both databases have the same
number of rows
>loaded, but are the values in the rows actually identical?
>--
>Hal Berenson, SQL Server MVP
>True Mountain Group LLC
>
>"Rich" <rstoll@.cadencenet.com> wrote in message
>news:04cf01c36111$680d3460$a301280a@.phx.gbl...
>> I have a test sql 7 and test sql 2000 database with the
>> same table structure and number of rows loaded.
>> When I add a new column and build an index on it in sql
7,
>> the optimizer uses the index and the query returns in 5
>> seconds. However, when I add the new column and build an
>> index on it i sql 2000, the optimizer does not use the
new
>> index and runs over 5 minutes. If I put an Index hint on
>> the query then it uses the index and runs in 5 seconds.
>> Why will not sql 2000 use this new index but sql 7 uses
it
>> without specifying the hint'
>> Thanks Rich
>
>.
>|||Joe,
The sql 2000 server has 128meg of ram and 1 665mhz cpu.
The sql 7 server had 1gig of ram and 2 450mhz cpu's.
>--Original Message--
>put the two queries (with and without index hint)
>in a Query Analyzer window
>and use the Display Estimated Execution Plan
>does the plan for not using the index show up as lower
>cost? even though it took much longer to run
>if so, this is a deliberate decision by the optimizer to
>switch to a table instead of an index seek with bookmark
>lookups
>also, do the two systems have different amount of memory,
>specically, is one <=1GB and another >1GB
>this would also affect when the optimizer switches from a
>index to a scan
>>--Original Message--
>>I have a test sql 7 and test sql 2000 database with the
>>same table structure and number of rows loaded.
>>When I add a new column and build an index on it in sql
>7,
>>the optimizer uses the index and the query returns in 5
>>seconds. However, when I add the new column and build an
>>index on it i sql 2000, the optimizer does not use the
>new
>>index and runs over 5 minutes. If I put an Index hint on
>>the query then it uses the index and runs in 5 seconds.
>>Why will not sql 2000 use this new index but sql 7 uses
>it
>>without specifying the hint'
>>Thanks Rich
>>.
>.
>|||Well, the first thing to try is running on a system with comparable memory.
If that doesn't do it, then you could post the showplan_all for the query on
both systems.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"rich" <rstoll@.cadencenet.com> wrote in message
news:0ee401c36195$e5164850$a301280a@.phx.gbl...
> Hal,
> I created the statistics on the CustID. The tables have
> the same no. of rows and values. I used DTS to copy the
> data from sql 7 to sql 2000.
> Here is the query (with the hint specified to make it run
> fast):
> Select Distinct uab.BillDocID, bs.BilLSrvID,bs.SrvType
> FROM tbl_bill_srv bs,
> tbl_chrg_non_elec_mtr cn with (index(ix_custid)),
> tbl_util_acct_bill uab
> Where (uab.DateRowCreated >= '2003-06-01' AND
> uab.DateRowCreated <= '2003-07-01')
> and cn.custid = 169
> AND uab.BilLDocID = cn.BilLDocID
> And cn.BillSrvID = bs.BilLSrvID
>
> Here is the DDL:
> CREATE TABLE [dbo].[tbl_chrg_non_elec_mtr] (
> [ChrgNonElecMtrID] [int] NOT NULL ,
> [CustID] [int] NOT NULL ,
> [BillSrvID] [int] NOT NULL ,
> [AcctngPer] [datetime] NOT NULL ,
> [AcctID] [int] NOT NULL ,
> [UtilID] [int] NOT NULL ,
> [AcctNo] [varchar] (22) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [BillDocID] [int] NOT NULL ,
> [CsmtnPer] [datetime] NULL ,
> [StartDate] [datetime] NULL ,
> [StopDate] [datetime] NULL ,
> [NMtrChrgType] [varchar] (25) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [TaxCode] [bit] NOT NULL ,
> [Qty] [float] NULL ,
> [QtyUM] [char] (5) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Chrg] [money] NOT NULL ,
> [ModBy] [char] (8) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ModDate] [datetime] NOT NULL ,
> [timestamp] [timestamp] NULL ,
> [Chrg_CurrencyTypeID] [tinyint] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tbl_chrg_non_elec_mtr] WITH NOCHECK ADD
> CONSTRAINT [PK_chrg_non_elec_mtr] PRIMARY KEY
> CLUSTERED
> (
> [ChrgNonElecMtrID]
> ) WITH FILLFACTOR = 85 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tbl_chrg_non_elec_mtr] ADD
> CONSTRAINT [DF_NON_Chrg_CurrencyTypeID] DEFAULT
> (1) FOR [Chrg_CurrencyTypeID]
> GO
> /****** The index created by the following statement is
> for internal use only. ******/
> /****** It is not a real index but exists as statistics
> only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [hind_202_8] ON [dbo].
> [tbl_chrg_non_elec_mtr] ([CsmtnPer]) ')
> GO
> CREATE INDEX [i1_tbl_chrg_non_elec_mtr] ON [dbo].
> [tbl_chrg_non_elec_mtr]([BillDocID]) WITH FILLFACTOR => 85, PAD_INDEX ON [PRIMARY]
> GO
> CREATE INDEX [i2_tbl_chrg_non_elec_mtr] ON [dbo].
> [tbl_chrg_non_elec_mtr]([BillSrvID]) WITH FILLFACTOR => 85, PAD_INDEX ON [PRIMARY]
> GO
> CREATE INDEX [i3_tbl_chrg_non_elec_mtr] ON [dbo].
> [tbl_chrg_non_elec_mtr]([AcctID]) WITH FILLFACTOR = 85,
> PAD_INDEX ON [PRIMARY]
> GO
> CREATE INDEX [i4_tbl_chrg_non_elec_mtr] ON [dbo].
> [tbl_chrg_non_elec_mtr]([CsmtnPer]) WITH FILLFACTOR => 85, PAD_INDEX ON [PRIMARY]
> GO
> CREATE INDEX [i5_tbl_chrg_non_elec_mtr] ON [dbo].
> [tbl_chrg_non_elec_mtr]([BillDocID], [CsmtnPer],
> [StartDate], [StopDate]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_custid] ON [dbo].[tbl_chrg_non_elec_mtr]
> ([CustID]) ON [PRIMARY]
> GO
> /****** The index created by the following statement is
> for internal use only. ******/
> /****** It is not a real index but exists as statistics
> only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [hind_custid] ON [dbo].
> [tbl_chrg_non_elec_mtr] ([CustID]) ')
> GO
>
> >--Original Message--
> >You'll need to post a lot more information before anyone
> could take a guess.
> >But first, have you made sure that the statistics on the
> SQL Server 2000
> >database have been updated?
> >
> >If that doesn't solve your problem, then what do the
> table and index
> >definitions look like? Can you post the DDL? What does
> the data actually
> >look like? You mention that both databases have the same
> number of rows
> >loaded, but are the values in the rows actually identical?
> >
> >--
> >Hal Berenson, SQL Server MVP
> >True Mountain Group LLC
> >
> >
> >"Rich" <rstoll@.cadencenet.com> wrote in message
> >news:04cf01c36111$680d3460$a301280a@.phx.gbl...
> >> I have a test sql 7 and test sql 2000 database with the
> >> same table structure and number of rows loaded.
> >>
> >> When I add a new column and build an index on it in sql
> 7,
> >> the optimizer uses the index and the query returns in 5
> >> seconds. However, when I add the new column and build an
> >> index on it i sql 2000, the optimizer does not use the
> new
> >> index and runs over 5 minutes. If I put an Index hint on
> >> the query then it uses the index and runs in 5 seconds.
> >>
> >> Why will not sql 2000 use this new index but sql 7 uses
> it
> >> without specifying the hint'
> >>
> >> Thanks Rich
> >
> >
> >.
> >|||This typically happens if you have a query in the form
SELECT .. FROM ... WHERE Column = @.value
If @.value has a higher Data Type Precedence (see BOL for more
information), then in SQL2000 Colomn is implicitely converted to the
data type of @.value (which basically makes it impossible to use the
index). SQL7 behaved differently.
So make sure your values have the same data type as the column you are
filtering. When in doubt, you can CAST the value to the data type of the
column.
Hope this helps,
Gert-Jan
Rich wrote:
> I have a test sql 7 and test sql 2000 database with the
> same table structure and number of rows loaded.
> When I add a new column and build an index on it in sql 7,
> the optimizer uses the index and the query returns in 5
> seconds. However, when I add the new column and build an
> index on it i sql 2000, the optimizer does not use the new
> index and runs over 5 minutes. If I put an Index hint on
> the query then it uses the index and runs in 5 seconds.
> Why will not sql 2000 use this new index but sql 7 uses it
> without specifying the hint'
> Thanks Rich|||In the example he posted the predicate is "cn.custid = 169" and custid is
defined as an int, so I don't think the change in conversions is the
problem..
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3F3A753D.2DD83C37@.toomuchspamalready.nl...
> This typically happens if you have a query in the form
> SELECT .. FROM ... WHERE Column = @.value
> If @.value has a higher Data Type Precedence (see BOL for more
> information), then in SQL2000 Colomn is implicitely converted to the
> data type of @.value (which basically makes it impossible to use the
> index). SQL7 behaved differently.
> So make sure your values have the same data type as the column you are
> filtering. When in doubt, you can CAST the value to the data type of the
> column.
> Hope this helps,
> Gert-Jan
>
> Rich wrote:
> >
> > I have a test sql 7 and test sql 2000 database with the
> > same table structure and number of rows loaded.
> >
> > When I add a new column and build an index on it in sql 7,
> > the optimizer uses the index and the query returns in 5
> > seconds. However, when I add the new column and build an
> > index on it i sql 2000, the optimizer does not use the new
> > index and runs over 5 minutes. If I put an Index hint on
> > the query then it uses the index and runs in 5 seconds.
> >
> > Why will not sql 2000 use this new index but sql 7 uses it
> > without specifying the hint'
> >
> > Thanks Rich|||It only applies in this case if the 'real' query does not use a literal
(in this case 169), but a variable defined as bigint, smallmoney, money,
decimal, real, float or sql_variant. Since so many programmers seem to
be using floats in all kinds of situations, it would not surprise me if
this was the case here...
Gert-Jan
Hal Berenson wrote:
> In the example he posted the predicate is "cn.custid = 169" and custid is
> defined as an int, so I don't think the change in conversions is the
> problem..
> --
> Hal Berenson, SQL Server MVP
> True Mountain Group LLC
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:3F3A753D.2DD83C37@.toomuchspamalready.nl...
> > This typically happens if you have a query in the form
> >
> > SELECT .. FROM ... WHERE Column = @.value
> >
> > If @.value has a higher Data Type Precedence (see BOL for more
> > information), then in SQL2000 Colomn is implicitely converted to the
> > data type of @.value (which basically makes it impossible to use the
> > index). SQL7 behaved differently.
> >
> > So make sure your values have the same data type as the column you are
> > filtering. When in doubt, you can CAST the value to the data type of the
> > column.
> >
> > Hope this helps,
> > Gert-Jan
> >
> >
> > Rich wrote:
> > >
> > > I have a test sql 7 and test sql 2000 database with the
> > > same table structure and number of rows loaded.
> > >
> > > When I add a new column and build an index on it in sql 7,
> > > the optimizer uses the index and the query returns in 5
> > > seconds. However, when I add the new column and build an
> > > index on it i sql 2000, the optimizer does not use the new
> > > index and runs over 5 minutes. If I put an Index hint on
> > > the query then it uses the index and runs in 5 seconds.
> > >
> > > Why will not sql 2000 use this new index but sql 7 uses it
> > > without specifying the hint'
> > >
> > > Thanks Rich