Greetings,
I am new to SQL Server and am trying to write a query for an application I
am working on. The table I am working with has three dollar amounts in
seperate columns that are prices on the same product from multiple
distributors. I am having trouble building a query that does a comparison on
these fields and returns a list of records based upon the lowest dollar amount
The results would be then used to populate a new table using only the lowest
price as returned from the SELECT statement
Is there someone who might be able to point me to some sample code that I
could use to help me figure out how this could be written
Thanks,
Joe.SELECT col1, col2, col3, ...
(SELECT MIN(price)
FROM
(SELECT price1 AS price UNION ALL
SELECT price2 UNION ALL
SELECT price3) AS T) AS min_price
FROM YourTable
The three price columns collectively represent a "repeating group". In
relational design this is a serious error and the difficulty you are having
is a consequence of the design problem. Hopefully your intention is to fix
this.
--
David Portas
SQL Server MVP
--|||David,
I assume that MIN will still work in the same manner if the price values
being compared are in seperate tables. I am working on different ways to get
rid of the repeating problem.
Thanks for your help
Joe.
"David Portas" wrote:
> SELECT col1, col2, col3, ...
> (SELECT MIN(price)
> FROM
> (SELECT price1 AS price UNION ALL
> SELECT price2 UNION ALL
> SELECT price3) AS T) AS min_price
> FROM YourTable
> The three price columns collectively represent a "repeating group". In
> relational design this is a serious error and the difficulty you are having
> is a consequence of the design problem. Hopefully your intention is to fix
> this.
> --
> David Portas
> SQL Server MVP
> --
>
>|||MIN retrieves the lowest non-NULL value of a set. If you can join the
additional table into the query then you should be able to make use of MIN.
My point about your design was that it would be easier and more efficient to
do this if your design was correctly normalized.
--
David Portas
SQL Server MVP
--
Showing posts with label application. Show all posts
Showing posts with label application. Show all posts
Friday, March 30, 2012
Query Question
Greetings,
I am new to SQL Server and am trying to write a query for an application I
am working on. The table I am working with has three dollar amounts in
seperate columns that are prices on the same product from multiple
distributors. I am having trouble building a query that does a comparison on
these fields and returns a list of records based upon the lowest dollar amou
nt
The results would be then used to populate a new table using only the lowest
price as returned from the SELECT statement
Is there someone who might be able to point me to some sample code that I
could use to help me figure out how this could be written
Thanks,
Joe.SELECT col1, col2, col3, ...
(SELECT MIN(price)
FROM
(SELECT price1 AS price UNION ALL
SELECT price2 UNION ALL
SELECT price3) AS T) AS min_price
FROM YourTable
The three price columns collectively represent a "repeating group". In
relational design this is a serious error and the difficulty you are having
is a consequence of the design problem. Hopefully your intention is to fix
this.
David Portas
SQL Server MVP
--|||David,
I assume that MIN will still work in the same manner if the price values
being compared are in seperate tables. I am working on different ways to get
rid of the repeating problem.
Thanks for your help
Joe.
"David Portas" wrote:
> SELECT col1, col2, col3, ...
> (SELECT MIN(price)
> FROM
> (SELECT price1 AS price UNION ALL
> SELECT price2 UNION ALL
> SELECT price3) AS T) AS min_price
> FROM YourTable
> The three price columns collectively represent a "repeating group". In
> relational design this is a serious error and the difficulty you are havin
g
> is a consequence of the design problem. Hopefully your intention is to fix
> this.
> --
> David Portas
> SQL Server MVP
> --
>
>|||MIN retrieves the lowest non-NULL value of a set. If you can join the
additional table into the query then you should be able to make use of MIN.
My point about your design was that it would be easier and more efficient to
do this if your design was correctly normalized.
David Portas
SQL Server MVP
--
I am new to SQL Server and am trying to write a query for an application I
am working on. The table I am working with has three dollar amounts in
seperate columns that are prices on the same product from multiple
distributors. I am having trouble building a query that does a comparison on
these fields and returns a list of records based upon the lowest dollar amou
nt
The results would be then used to populate a new table using only the lowest
price as returned from the SELECT statement
Is there someone who might be able to point me to some sample code that I
could use to help me figure out how this could be written
Thanks,
Joe.SELECT col1, col2, col3, ...
(SELECT MIN(price)
FROM
(SELECT price1 AS price UNION ALL
SELECT price2 UNION ALL
SELECT price3) AS T) AS min_price
FROM YourTable
The three price columns collectively represent a "repeating group". In
relational design this is a serious error and the difficulty you are having
is a consequence of the design problem. Hopefully your intention is to fix
this.
David Portas
SQL Server MVP
--|||David,
I assume that MIN will still work in the same manner if the price values
being compared are in seperate tables. I am working on different ways to get
rid of the repeating problem.
Thanks for your help
Joe.
"David Portas" wrote:
> SELECT col1, col2, col3, ...
> (SELECT MIN(price)
> FROM
> (SELECT price1 AS price UNION ALL
> SELECT price2 UNION ALL
> SELECT price3) AS T) AS min_price
> FROM YourTable
> The three price columns collectively represent a "repeating group". In
> relational design this is a serious error and the difficulty you are havin
g
> is a consequence of the design problem. Hopefully your intention is to fix
> this.
> --
> David Portas
> SQL Server MVP
> --
>
>|||MIN retrieves the lowest non-NULL value of a set. If you can join the
additional table into the query then you should be able to make use of MIN.
My point about your design was that it would be easier and more efficient to
do this if your design was correctly normalized.
David Portas
SQL Server MVP
--
Friday, March 23, 2012
Query Plans & Statistics
Gurus,
I'm trying to get an application finished that works like Query Analizer in
terms of returning query plans and statistics.
Problem the co-author is having:
>In using ADO to connect to SQL Server, I'm trying to retrieve multiple
>datasets AND statistics that are usually returned via the OnInfoMessage
>event. For those that are familiar with SQL Server, I need the results
>returned by the SET STATISTICS IO ON and SET STATISTICS PROFILE ON options.
>Anyone had any luck doing this before?
Can anyone shed any light on this please?
Thanks.
BTW if anyone wants to take a look at the tool so far - to see what I'm
delving into:
http://81.130.213.94/myforum/forum_posts.asp?TID=78&PN=1
Much Appreciated!!How about Object Browser pane? This one comes handy very often, you know?|||I'm not sure what you mean by Object Browser Pane - unless you mean something in Microsoft Query analizer...??
The problem I have is that this is a new tool that doesn't have any panes..just pains...arghhh.
Thanks for the help though!|||Here's some more info from the original post
http://81.130.213.94/myforum/forum_posts.asp?TID=26&PN=1
SET STATISTICS IO ON
SELECT 1 FROM sometable
IO statistics are returned as messages in the 2nd (blank) recordset. Good.
SET STATISTICS PROFILE ON
SELECT 1 FROM sometable
Execution plan is returned in the 2nd recordset. Good.
SET STATISTICS IO ON
SET STATISTICS PROFILE ON
SELECT 1 FROM sometable
2 recordsets are returned, the 2nd containing the execution plan. But the
IO statistics are nowhere to be found. Help!
SET STATISTICS IO ON
SET STATISTICS PROFILE ON
SELECT 1 FROM sometable
PRINT 1
3 recordsets are returned. The 2nd one contains the execution plan, the
third is blank but contains '1' as a info message. The IO statistics are
nowhere to be found. Help!sql
I'm trying to get an application finished that works like Query Analizer in
terms of returning query plans and statistics.
Problem the co-author is having:
>In using ADO to connect to SQL Server, I'm trying to retrieve multiple
>datasets AND statistics that are usually returned via the OnInfoMessage
>event. For those that are familiar with SQL Server, I need the results
>returned by the SET STATISTICS IO ON and SET STATISTICS PROFILE ON options.
>Anyone had any luck doing this before?
Can anyone shed any light on this please?
Thanks.
BTW if anyone wants to take a look at the tool so far - to see what I'm
delving into:
http://81.130.213.94/myforum/forum_posts.asp?TID=78&PN=1
Much Appreciated!!How about Object Browser pane? This one comes handy very often, you know?|||I'm not sure what you mean by Object Browser Pane - unless you mean something in Microsoft Query analizer...??
The problem I have is that this is a new tool that doesn't have any panes..just pains...arghhh.
Thanks for the help though!|||Here's some more info from the original post
http://81.130.213.94/myforum/forum_posts.asp?TID=26&PN=1
SET STATISTICS IO ON
SELECT 1 FROM sometable
IO statistics are returned as messages in the 2nd (blank) recordset. Good.
SET STATISTICS PROFILE ON
SELECT 1 FROM sometable
Execution plan is returned in the 2nd recordset. Good.
SET STATISTICS IO ON
SET STATISTICS PROFILE ON
SELECT 1 FROM sometable
2 recordsets are returned, the 2nd containing the execution plan. But the
IO statistics are nowhere to be found. Help!
SET STATISTICS IO ON
SET STATISTICS PROFILE ON
SELECT 1 FROM sometable
PRINT 1
3 recordsets are returned. The 2nd one contains the execution plan, the
third is blank but contains '1' as a info message. The IO statistics are
nowhere to be found. Help!sql
Query plan re-use on views?
Here's the setup:
Client database has a complex view with eight nested subqueries used to return "dashboard" information. The application code uses NHibernate to call and filter the view with three parameters, one of which is the CustomerID.
A certain customer, (the biggest client), has more than ten times the number of records of the next largest customer.
Occasionally, the database reaches a state where when this particular customer tries to run the dashboard view, the application times out.
If I open up the view and re-save it, all is well again for a few days.
What gives?
Views are supposedly not pre-compiled, though I know that 2000 stores bits and pieces of query plans.
Any ideas on what causes this and what to do about it?are you experiencing large amounts of new data on a regular basis throwing off statistics and fragmenting your indices. just cuz they ain't precompiled don;t mean that they do not use that stuff right?|||No. Loaded a ton of historical data when the app was first installed, but inserts have been slow and steady since then.
Client database has a complex view with eight nested subqueries used to return "dashboard" information. The application code uses NHibernate to call and filter the view with three parameters, one of which is the CustomerID.
A certain customer, (the biggest client), has more than ten times the number of records of the next largest customer.
Occasionally, the database reaches a state where when this particular customer tries to run the dashboard view, the application times out.
If I open up the view and re-save it, all is well again for a few days.
What gives?
Views are supposedly not pre-compiled, though I know that 2000 stores bits and pieces of query plans.
Any ideas on what causes this and what to do about it?are you experiencing large amounts of new data on a regular basis throwing off statistics and fragmenting your indices. just cuz they ain't precompiled don;t mean that they do not use that stuff right?|||No. Loaded a ton of historical data when the app was first installed, but inserts have been slow and steady since then.
Query performence
Hi all.
I'm confuse.
I have a sporadic problem.
I have an application that execute a query on my SQL Server. Normally,
the query takes 1 or 2 secondes to execute. The thing that I have some
difficulty to understand, is that sometime, SQL takes 2 or 3 minutes to
execute the same query. I already sent a post about this problem few
month ago and many reply told me to insert a With Recompile option in my
stored procedure. I have insert them but today, with no result.
I think that somebody will tell me to seach for LOCK but I haven't one.
Here you can see the result that I have receive in SQL Profiler when I
have execute the query. The first line represent the result that takes 1
or 2 secondes and the second line is the result that takes 2 or 3
minutes.
Event Class Text Application
Name NT User Name SQL User Name CPU Reads Writes
Duration SPID Start Time
+SQL:BatchCompleted SELECT DISTINCT ORDRE02 MS SQL Query
Analyzer jonathan.chretien 16 219 0
93 66 15:36:07.757
FROM TBLECRAN02 WITH (NOLOCK)
WHERE F4941_LDNM = 10992
ORDER BY ORDRE02
+SQL:BatchCompleted SELECT DISTINCT ORDRE02
Ami002 P-80-125 22797 196112
72 58
FROM TBLECRAN02 WITH (NOLOCK)
WHERE F4941_LDNM = 10992
ORDER BY ORDRE02
We use MS SQL Server 7.0 SP2 Hotfix 851.
Can somebody help me '
(Sorry for my english, I'm french)
Jonathan Chretien
Analyst/Programmerthis may be a shot in the dark, but do you happen to have the table
results open (from the table -- Open Table, Return all Rows)?
Jonathan Chretien wrote:
> Hi all.
> I'm confuse.
> I have a sporadic problem.
> I have an application that execute a query on my SQL Server. Normally,
> the query takes 1 or 2 secondes to execute. The thing that I have some
> difficulty to understand, is that sometime, SQL takes 2 or 3 minutes to
> execute the same query. I already sent a post about this problem few
> month ago and many reply told me to insert a With Recompile option in my
> stored procedure. I have insert them but today, with no result.
> I think that somebody will tell me to seach for LOCK but I haven't one.
> Here you can see the result that I have receive in SQL Profiler when I
> have execute the query. The first line represent the result that takes 1
> or 2 secondes and the second line is the result that takes 2 or 3
> minutes.
>
> Event Class Text Application
> Name NT User Name SQL User Name CPU Reads Writes
> Duration SPID Start Time
> +SQL:BatchCompleted SELECT DISTINCT ORDRE02 MS SQL Query
> Analyzer jonathan.chretien 16 219 0
> 93 66 15:36:07.757
> FROM TBLECRAN02 WITH (NOLOCK)
> WHERE F4941_LDNM = 10992
> ORDER BY ORDRE02
>
> +SQL:BatchCompleted SELECT DISTINCT ORDRE02
> Ami002 P-80-125 22797 196112
> 72 58
> FROM TBLECRAN02 WITH (NOLOCK)
> WHERE F4941_LDNM = 10992
> ORDER BY ORDRE02
>
> We use MS SQL Server 7.0 SP2 Hotfix 851.
> Can somebody help me '
> (Sorry for my english, I'm french)
>
> Jonathan Chretien
> Analyst/Programmer
>|||No.
Me a écrit :
> this may be a shot in the dark, but do you happen to have the table
> results open (from the table -- Open Table, Return all Rows)?
> Jonathan Chretien wrote:
> > Hi all.
> >
> > I'm confuse.
> >
> > I have a sporadic problem.
> >
> > I have an application that execute a query on my SQL Server. Normally,
> > the query takes 1 or 2 secondes to execute. The thing that I have some
> > difficulty to understand, is that sometime, SQL takes 2 or 3 minutes to
> > execute the same query. I already sent a post about this problem few
> > month ago and many reply told me to insert a With Recompile option in my
> > stored procedure. I have insert them but today, with no result.
> >
> > I think that somebody will tell me to seach for LOCK but I haven't one.
> >
> > Here you can see the result that I have receive in SQL Profiler when I
> > have execute the query. The first line represent the result that takes 1
> > or 2 secondes and the second line is the result that takes 2 or 3
> > minutes.
> >
> >
> > Event Class Text Application
> > Name NT User Name SQL User Name CPU Reads Writes
> > Duration SPID Start Time
> > +SQL:BatchCompleted SELECT DISTINCT ORDRE02 MS SQL Query
> > Analyzer jonathan.chretien 16 219 0
> > 93 66 15:36:07.757
> > FROM TBLECRAN02 WITH (NOLOCK)
> > WHERE F4941_LDNM = 10992
> > ORDER BY ORDRE02
> >
> >
> > +SQL:BatchCompleted SELECT DISTINCT ORDRE02
> > Ami002 P-80-125 22797 196112
> > 72 58
> > FROM TBLECRAN02 WITH (NOLOCK)
> > WHERE F4941_LDNM = 10992
> > ORDER BY ORDRE02
> >
> >
> > We use MS SQL Server 7.0 SP2 Hotfix 851.
> >
> > Can somebody help me '
> >
> > (Sorry for my english, I'm french)
> >
> >
> > Jonathan Chretien
> > Analyst/Programmer
> >|||Are you sure that you're server is not getting hammered from time to time? Have you monitored server performance at all?
>--Original Message--
>No.
>Me a =E9crit :
>> this may be a shot in the dark, but do you happen to have the table
>> results open (from the table -- Open Table, Return all Rows)?
>> Jonathan Chretien wrote:
>> > Hi all.
>> >
>> > I'm confuse.
>> >
>> > I have a sporadic problem.
>> >
>> > I have an application that execute a query on my SQL Server. Normally,
>> > the query takes 1 or 2 secondes to execute. The thing that I have some
>> > difficulty to understand, is that sometime, SQL takes 2 or 3 minutes to
>> > execute the same query. I already sent a post about this problem few
>> > month ago and many reply told me to insert a With Recompile option in my
>> > stored procedure. I have insert them but today, with no result.
>> >
>> > I think that somebody will tell me to seach for LOCK but I haven't one.
>> >
>> > Here you can see the result that I have receive in SQL Profiler when I
>> > have execute the query. The first line represent the result that takes 1
>> > or 2 secondes and the second line is the result that takes 2 or 3
>> > minutes.
>> >
>> >
>> > Event Class Text Application
>> > Name NT User Name SQL User Name CPU Reads Writes
>> > Duration SPID Start Time
>> > +SQL:BatchCompleted SELECT DISTINCT ORDRE02 MS SQL Query
>> > Analyzer jonathan.chretien 16 219 0
>> > 93 66 15:36:07.757
>> > FROM TBLECRAN02 WITH (NOLOCK)
>> > WHERE F4941_LDNM =3D 10992
>> > ORDER BY ORDRE02
>> >
>> >
>> > +SQL:BatchCompleted SELECT DISTINCT ORDRE02
>> > Ami002 P-80-
125 22797 196112
>> > 72 58
>> > FROM TBLECRAN02 WITH (NOLOCK)
>> > WHERE F4941_LDNM =3D 10992
>> > ORDER BY ORDRE02
>> >
>> >
>> > We use MS SQL Server 7.0 SP2 Hotfix 851.
>> >
>> > Can somebody help me '
>> >
>> > (Sorry for my english, I'm french)
>> >
>> >
>> > Jonathan Chretien
>> > Analyst/Programmer
>> >
>.
>|||Jonathan
You need to use profiler to capture what is happening. Do
you have either auto close or auto shrink set to on for
the database, as that would explain it. If you do turn
them off.
Regards
John|||Hi CurtM and John.
Thanks for the interrest.
I have start Performance Monitor when the problem occur, the server
wasn't load over 20%.
The only think that I can tell you is that I have made a sp_lock to
seach for some lock and I see that I have an [UPD-STATS] on the same
table that my query is base on. I can't believe that my statistic can
takes 2 or 3 minutes to be update.
I have no Auto close or Auto Shrink enable.
Thanks.
Jonathan Chretien
Analyst/Programmer
John Bandettini a écrit :
> Jonathan
> You need to use profiler to capture what is happening. Do
> you have either auto close or auto shrink set to on for
> the database, as that would explain it. If you do turn
> them off.
> Regards
> John|||Jonathan
If you think it may be update stats, you can turn the
option off and schedule a job to update your stats at a
more suitable time.
Regards
John
I'm confuse.
I have a sporadic problem.
I have an application that execute a query on my SQL Server. Normally,
the query takes 1 or 2 secondes to execute. The thing that I have some
difficulty to understand, is that sometime, SQL takes 2 or 3 minutes to
execute the same query. I already sent a post about this problem few
month ago and many reply told me to insert a With Recompile option in my
stored procedure. I have insert them but today, with no result.
I think that somebody will tell me to seach for LOCK but I haven't one.
Here you can see the result that I have receive in SQL Profiler when I
have execute the query. The first line represent the result that takes 1
or 2 secondes and the second line is the result that takes 2 or 3
minutes.
Event Class Text Application
Name NT User Name SQL User Name CPU Reads Writes
Duration SPID Start Time
+SQL:BatchCompleted SELECT DISTINCT ORDRE02 MS SQL Query
Analyzer jonathan.chretien 16 219 0
93 66 15:36:07.757
FROM TBLECRAN02 WITH (NOLOCK)
WHERE F4941_LDNM = 10992
ORDER BY ORDRE02
+SQL:BatchCompleted SELECT DISTINCT ORDRE02
Ami002 P-80-125 22797 196112
72 58
FROM TBLECRAN02 WITH (NOLOCK)
WHERE F4941_LDNM = 10992
ORDER BY ORDRE02
We use MS SQL Server 7.0 SP2 Hotfix 851.
Can somebody help me '
(Sorry for my english, I'm french)
Jonathan Chretien
Analyst/Programmerthis may be a shot in the dark, but do you happen to have the table
results open (from the table -- Open Table, Return all Rows)?
Jonathan Chretien wrote:
> Hi all.
> I'm confuse.
> I have a sporadic problem.
> I have an application that execute a query on my SQL Server. Normally,
> the query takes 1 or 2 secondes to execute. The thing that I have some
> difficulty to understand, is that sometime, SQL takes 2 or 3 minutes to
> execute the same query. I already sent a post about this problem few
> month ago and many reply told me to insert a With Recompile option in my
> stored procedure. I have insert them but today, with no result.
> I think that somebody will tell me to seach for LOCK but I haven't one.
> Here you can see the result that I have receive in SQL Profiler when I
> have execute the query. The first line represent the result that takes 1
> or 2 secondes and the second line is the result that takes 2 or 3
> minutes.
>
> Event Class Text Application
> Name NT User Name SQL User Name CPU Reads Writes
> Duration SPID Start Time
> +SQL:BatchCompleted SELECT DISTINCT ORDRE02 MS SQL Query
> Analyzer jonathan.chretien 16 219 0
> 93 66 15:36:07.757
> FROM TBLECRAN02 WITH (NOLOCK)
> WHERE F4941_LDNM = 10992
> ORDER BY ORDRE02
>
> +SQL:BatchCompleted SELECT DISTINCT ORDRE02
> Ami002 P-80-125 22797 196112
> 72 58
> FROM TBLECRAN02 WITH (NOLOCK)
> WHERE F4941_LDNM = 10992
> ORDER BY ORDRE02
>
> We use MS SQL Server 7.0 SP2 Hotfix 851.
> Can somebody help me '
> (Sorry for my english, I'm french)
>
> Jonathan Chretien
> Analyst/Programmer
>|||No.
Me a écrit :
> this may be a shot in the dark, but do you happen to have the table
> results open (from the table -- Open Table, Return all Rows)?
> Jonathan Chretien wrote:
> > Hi all.
> >
> > I'm confuse.
> >
> > I have a sporadic problem.
> >
> > I have an application that execute a query on my SQL Server. Normally,
> > the query takes 1 or 2 secondes to execute. The thing that I have some
> > difficulty to understand, is that sometime, SQL takes 2 or 3 minutes to
> > execute the same query. I already sent a post about this problem few
> > month ago and many reply told me to insert a With Recompile option in my
> > stored procedure. I have insert them but today, with no result.
> >
> > I think that somebody will tell me to seach for LOCK but I haven't one.
> >
> > Here you can see the result that I have receive in SQL Profiler when I
> > have execute the query. The first line represent the result that takes 1
> > or 2 secondes and the second line is the result that takes 2 or 3
> > minutes.
> >
> >
> > Event Class Text Application
> > Name NT User Name SQL User Name CPU Reads Writes
> > Duration SPID Start Time
> > +SQL:BatchCompleted SELECT DISTINCT ORDRE02 MS SQL Query
> > Analyzer jonathan.chretien 16 219 0
> > 93 66 15:36:07.757
> > FROM TBLECRAN02 WITH (NOLOCK)
> > WHERE F4941_LDNM = 10992
> > ORDER BY ORDRE02
> >
> >
> > +SQL:BatchCompleted SELECT DISTINCT ORDRE02
> > Ami002 P-80-125 22797 196112
> > 72 58
> > FROM TBLECRAN02 WITH (NOLOCK)
> > WHERE F4941_LDNM = 10992
> > ORDER BY ORDRE02
> >
> >
> > We use MS SQL Server 7.0 SP2 Hotfix 851.
> >
> > Can somebody help me '
> >
> > (Sorry for my english, I'm french)
> >
> >
> > Jonathan Chretien
> > Analyst/Programmer
> >|||Are you sure that you're server is not getting hammered from time to time? Have you monitored server performance at all?
>--Original Message--
>No.
>Me a =E9crit :
>> this may be a shot in the dark, but do you happen to have the table
>> results open (from the table -- Open Table, Return all Rows)?
>> Jonathan Chretien wrote:
>> > Hi all.
>> >
>> > I'm confuse.
>> >
>> > I have a sporadic problem.
>> >
>> > I have an application that execute a query on my SQL Server. Normally,
>> > the query takes 1 or 2 secondes to execute. The thing that I have some
>> > difficulty to understand, is that sometime, SQL takes 2 or 3 minutes to
>> > execute the same query. I already sent a post about this problem few
>> > month ago and many reply told me to insert a With Recompile option in my
>> > stored procedure. I have insert them but today, with no result.
>> >
>> > I think that somebody will tell me to seach for LOCK but I haven't one.
>> >
>> > Here you can see the result that I have receive in SQL Profiler when I
>> > have execute the query. The first line represent the result that takes 1
>> > or 2 secondes and the second line is the result that takes 2 or 3
>> > minutes.
>> >
>> >
>> > Event Class Text Application
>> > Name NT User Name SQL User Name CPU Reads Writes
>> > Duration SPID Start Time
>> > +SQL:BatchCompleted SELECT DISTINCT ORDRE02 MS SQL Query
>> > Analyzer jonathan.chretien 16 219 0
>> > 93 66 15:36:07.757
>> > FROM TBLECRAN02 WITH (NOLOCK)
>> > WHERE F4941_LDNM =3D 10992
>> > ORDER BY ORDRE02
>> >
>> >
>> > +SQL:BatchCompleted SELECT DISTINCT ORDRE02
>> > Ami002 P-80-
125 22797 196112
>> > 72 58
>> > FROM TBLECRAN02 WITH (NOLOCK)
>> > WHERE F4941_LDNM =3D 10992
>> > ORDER BY ORDRE02
>> >
>> >
>> > We use MS SQL Server 7.0 SP2 Hotfix 851.
>> >
>> > Can somebody help me '
>> >
>> > (Sorry for my english, I'm french)
>> >
>> >
>> > Jonathan Chretien
>> > Analyst/Programmer
>> >
>.
>|||Jonathan
You need to use profiler to capture what is happening. Do
you have either auto close or auto shrink set to on for
the database, as that would explain it. If you do turn
them off.
Regards
John|||Hi CurtM and John.
Thanks for the interrest.
I have start Performance Monitor when the problem occur, the server
wasn't load over 20%.
The only think that I can tell you is that I have made a sp_lock to
seach for some lock and I see that I have an [UPD-STATS] on the same
table that my query is base on. I can't believe that my statistic can
takes 2 or 3 minutes to be update.
I have no Auto close or Auto Shrink enable.
Thanks.
Jonathan Chretien
Analyst/Programmer
John Bandettini a écrit :
> Jonathan
> You need to use profiler to capture what is happening. Do
> you have either auto close or auto shrink set to on for
> the database, as that would explain it. If you do turn
> them off.
> Regards
> John|||Jonathan
If you think it may be update stats, you can turn the
option off and schedule a job to update your stats at a
more suitable time.
Regards
John
Wednesday, March 21, 2012
Query Performance Problem
We have an application that's been performing reasonably well for quite some
time. Something seems to have changed one morning last w
, as the entire
application slowed down significantly. I have no idea what could have
caused the problem, as there's nothing out of the ordinary in any of the
logs. I issued a DBCC CHECKDB to verify the integrity of the database, and
that didn't show any problems. I also ran DBCC DBREINDEX, DBCC UPDATEUSAGE,
and UPDATE STATISTICS, but that doesn't appear to have fixed anything
either.
During my investigation I was able to identify one query that was taking an
inordinate amount of time to execute. It is an in-line query (rather than
one that's invoked from within a stored procedure) that should have returned
in a couple of seconds, but was taking over 40 seconds. In trying to figure
out what was going on, I asked the server for an estimated execution plan,
and was surprised to find that it took just under 40 seconds for the server
to provide it. Once the plan was in the cache, asking for it again was
instantaneous and the query would execute in a couple of seconds. After the
query was removed from the cache (or if the query was modified slightly), it
took 40 seconds again.
It was my understanding that the server simply looked at indices and
statistics in order to come up with an ESTIMATED plan. Up until now I've
never seen it take more than a second to do so. Am I wrong about this?
Does this give anyone a clue as to what might be going on? The query is
pretty much a simple join between 3 tables and 3 views. (Each of the views
have 3-5 tables.) Any help would be appreciated.
Thanks!You cannot directly use a SProc inside an if condition.
If you really want to call within the if condition, then create a function.
But remember that the function works because its an extended proc.
CREATE function [dbo].[usp_FileExists] (@.physname nvarchar(260))
returns int
as
begin
DECLARE @.i int
EXEC master.dbo.usp_FileExists @.physname =
'z:\data\sql_data\myData_Data.MDF'
,@.i out
return(SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END)
end
IF (master.dbo. usp_FileExists('D:\data\sql_data\myData_
Data.MDF') = 0)
PRINT 'Does not Exist '
But again, unless you have a pressing reason to call it within the if
condition, I would suggest you call the proc the same way as the extended
proc is called within it..
Like this..
DECLARE @.RC int
DECLARE @.physname nvarchar(260)
set @.physname = 'D:\data\sql_data\myData_Data.MDF'
EXEC @.RC = [master].[dbo].[usp_FileExists1] @.physname
if(@.rc = 0)
PRINT 'Does not Exist '
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Answered in the wrong post... sorry :(
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Omnibuzz" wrote:
> You cannot directly use a SProc inside an if condition.
> If you really want to call within the if condition, then create a function
.
> But remember that the function works because its an extended proc.
> CREATE function [dbo].[usp_FileExists] (@.physname nvarchar(260))
> returns int
> as
> begin
> DECLARE @.i int
> EXEC master.dbo.usp_FileExists @.physname =
> 'z:\data\sql_data\myData_Data.MDF'
> ,@.i out
> return(SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END)
> end
> IF (master.dbo. usp_FileExists('D:\data\sql_data\myData_
Data.MDF') = 0)
> PRINT 'Does not Exist '
> But again, unless you have a pressing reason to call it within the if
> condition, I would suggest you call the proc the same way as the extended
> proc is called within it..
> Like this..
> DECLARE @.RC int
> DECLARE @.physname nvarchar(260)
> set @.physname = 'D:\data\sql_data\myData_Data.MDF'
> EXEC @.RC = [master].[dbo].[usp_FileExists1] @.physname
> if(@.rc = 0)
> PRINT 'Does not Exist '
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Mark Pauker (mpauker@.optonline.net) writes:
> During my investigation I was able to identify one query that was taking
> an inordinate amount of time to execute. It is an in-line query (rather
> than one that's invoked from within a stored procedure) that should have
> returned in a couple of seconds, but was taking over 40 seconds. In
> trying to figure out what was going on, I asked the server for an
> estimated execution plan, and was surprised to find that it took just
> under 40 seconds for the server to provide it. Once the plan was in the
> cache, asking for it again was instantaneous and the query would execute
> in a couple of seconds. After the query was removed from the cache (or
> if the query was modified slightly), it took 40 seconds again.
> It was my understanding that the server simply looked at indices and
> statistics in order to come up with an ESTIMATED plan. Up until now
> I've never seen it take more than a second to do so. Am I wrong about
> this? Does this give anyone a clue as to what might be going on? The
> query is pretty much a simple join between 3 tables and 3 views. (Each
> of the views have 3-5 tables.) Any help would be appreciated.
So 40 seconds for computing a query plan is indeed a bit extraordinary.
Then again a query like:
SELECT * FROM tbl WHERE col (val1, val2, ... val10000)
typically takes 15 seconds to compile on SQL 2000.
Since this is a query submitted from the application, I'm a little
nervous how the code actually looks like. When you submit bare
queries from the client, it's essential that:
o No parameter values are interpolated into the query string.
o All tables are prefixed with owner/schema.
o The query text never changes.
When SQL Server looks up the query plan for a loose query in the cache,
it hashes the text. For this reason the cache is case- and space-sensitive.
That is, if a single space is added to the query, there will be a new
plan.
The reason that the owner/schema is important is that if the query is
SELECT ... FROM tbl
and is run by user joe, SQL Server will first have to check whether there
is a joe.tbl before it tries with dbo.tbl. Since joe.tbl could appear in
any moment, the consequence is that joe gets a private plan in the cache
for the query. If you say "SELECT ... FROM dbo.tbl" this does not happen.
Now, there are other reasons for recompilations as well, for instance
changed statistics. If any of the involved tables are moderate in size,
but updated frequently, recompilation will hit you several times a time.
One option to address this is to add OPTION (KEEPFIXED PLAN) at the end
of the query, to prevent recompiles. But this still will not help if
the query involves a temp table that is recreated each time.
I understand that you would like to know why compilation takes such
long time, but I don't really have an answer to that. I guess I would
need intimate knowledge of the involved tables to tell. And, of course,
which version of SQL Server you are using.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> So 40 seconds for computing a query plan is indeed a bit extraordinary.
The bizarre thing is that this only started last w. Prior to that the
query executed in a few seconds. I'm trying to figure out what could have
caused this. Neither the executable nor any server configurations have
changed.
> Then again a query like:
> SELECT * FROM tbl WHERE col (val1, val2, ... val10000)
> typically takes 15 seconds to compile on SQL 2000.
I've never seen a query take anywhere near this amout of time. Why would it
take so long?
> When you submit bare queries from the client, it's essential that:
> o No parameter values are interpolated into the query string.
> o All tables are prefixed with owner/schema.
> o The query text never changes.
The query is built by the front end, with the "parameterized" data being
appended directly into the query string. I've been re-factoring the app to
remove this type of code, but there's way too much of it for me to get to in
a timely manner. While your suggestions make sense, the bottom line is that
before last w, the system was performing acceptably (even though it was
having to recompile the query each time it was submitted). I'm hoping to
identify a root cause of the slowdown so I can get performance back to the
way it was before all of this started last w. What could cause a server
to generate an estimated execution plan in 40 seonds when that same server
was generating estimated execution plans of one or two seconds just a few
days before?.|||Mark Pauker wrote:
> The query is built by the front end, with the "parameterized" data being
> appended directly into the query string. I've been re-factoring the app t
o
> remove this type of code, but there's way too much of it for me to get to
in
> a timely manner. While your suggestions make sense, the bottom line is th
at
> before last w, the system was performing acceptably (even though it was
> having to recompile the query each time it was submitted). I'm hoping to
> identify a root cause of the slowdown so I can get performance back to the
> way it was before all of this started last w. What could cause a serve
r
> to generate an estimated execution plan in 40 seonds when that same server
> was generating estimated execution plans of one or two seconds just a few
> days before?.
>
How often are you updating statistics on the database? Have you
recently made mass changes to any tables, i.e. deleted a bunch of
records, or imported a bunch? Kind of sounds like a statistics issue to
me...|||The query optimizer will compile based on the estimated cost of the query
(and its complexity). We implement this using "buckets" of optimizations
that we call stages. Cheap queries get only one stage. More expensive get
two stages. The most complex get 3 stages.
A query can get slightly more expensive and go from one bucket to the next.
That next bucket could take a lot longer, especially if you have a lot of
joins (the number of alternatives we have to consider explodes).
Another reason things could change like this is that the initial join order
picked could be less optimal. This could cause a higher estimated cost and
thus cause you to go through more optimization stages. This could be caused
by changes in the underlying data.
Another possible reason for longer compilation time is that something has
changed (which sounds less likely here). If you upgraded from one release
to another, perhaps there is a bug and we're not finding a solution fast
enough.
I usually try to simplify the query (remove pieces of it one at a time)
until I've found the culprit.
Conor
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:OGudU7RmGHA.4032@.TK2MSFTNGP02.phx.gbl...
> Mark Pauker wrote:
> How often are you updating statistics on the database? Have you recently
> made mass changes to any tables, i.e. deleted a bunch of records, or
> imported a bunch? Kind of sounds like a statistics issue to me...|||Or perhaps it is auto-stats kicking in? (Doesn't sound like it from the orig
inal description, but
who knows...)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Conor Cunningham [MS]" <conorc_removeme@.online.microsoft.com> wrote in message
news:eLcx2mTmGHA.4992@.TK2MSFTNGP03.phx.gbl...
> The query optimizer will compile based on the estimated cost of the query
(and its complexity).
> We implement this using "buckets" of optimizations that we call stages. C
heap queries get only
> one stage. More expensive get two stages. The most complex get 3 stages.
> A query can get slightly more expensive and go from one bucket to the next
. That next bucket could
> take a lot longer, especially if you have a lot of joins (the number of al
ternatives we have to
> consider explodes).
> Another reason things could change like this is that the initial join orde
r picked could be less
> optimal. This could cause a higher estimated cost and thus cause you to g
o through more
> optimization stages. This could be caused by changes in the underlying da
ta.
> Another possible reason for longer compilation time is that something has
changed (which sounds
> less likely here). If you upgraded from one release to another, perhaps t
here is a bug and we're
> not finding a solution fast enough.
> I usually try to simplify the query (remove pieces of it one at a time) un
til I've found the
> culprit.
> Conor
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:OGudU7RmGHA.4032@.TK2MSFTNGP02.phx.gbl...
>|||> A query can get slightly more expensive and go from one bucket to the
> next. That next bucket could take a lot longer, especially if you have a
> lot of joins (the number of alternatives we have to consider explodes).
This makes sense, but I still can't understand why this could take 30
seconds or more. Is this within a range that could be "as expected?" It
seems to me that something else must be going on. (The query in question
contains 6 tables and 3 views that are all left-joined togather. Each view
has 4 or 5 tables.)
Keep in mind that the ENTIRE application has slowed down, not just this
query. I brought up the query for illustrative purposes, but it really
appears to be just a symptom of a more systemmic problem. (I could rewrite
the query, but there would be a hundred more places that I'd need to fix...
And even then I wouldn't know that a larger problem wasn't simply being
masked.)
When the system started "misbehaving" last w, the processor utilization
went way up but the disk didn't get any more active. This is what makes me
think that the slow-down is related to the server's generating execution
plans. The query simply seems to bear that out. (Once the execution plan
has been generated, the query appears to execute normally.)
I agree with Tracy's comment that it appears to be statistics-related, but
I'm not sure what to do with that information. I've updated them manually
(with and without FULLSCAN), and verified that they haven't been updated
automatically between requested execution plans. It's almost as if the
server is ignoring the statistics and trying to recalculate the plan from
scratch (although I guess I'd see more disk activity if this were true).
> Another reason things could change like this is that the initial join
> order picked could be less optimal. This could cause a higher estimated
> cost and thus cause you to go through more optimization stages. This
> could be caused by changes in the underlying data.
Is there any way to alleviate this if it turns out to be the case?
> Another possible reason for longer compilation time is that something has
> changed (which sounds less likely here). If you upgraded from one release
> to another, perhaps there is a bug and we're not finding a solution fast
> enough.
Sounds unlikely.
> I usually try to simplify the query (remove pieces of it one at a time)
> until I've found the culprit.
I can certainly do this (and probably will), but as I said, the problem is
occurring system-wide. I was hoping that there would be some known issue or
a suggestion that might address things at a more global level. I think that
the problem may lie in a single, very widely used table (that's involved in
this and a great number of other queries). The table is approaching 10M
records, occupying over 2G of space (independent of indices and statistics).
Any other thoughts would be greatly appreciated.
-- Mark|||Mark Pauker (mpauker@.optonline.net) writes:
> Keep in mind that the ENTIRE application has slowed down, not just this
> query. I brought up the query for illustrative purposes, but it really
> appears to be just a symptom of a more systemmic problem.
But if that query needs to be compiled again and again, since it has
interpolated queries, you could have several processes compiling different
versions of this query. And if there are other similar queries running,
it could all add up to this disaster.
> Is there any way to alleviate this if it turns out to be the case?
Better indexing maybe?
It's awfully hard to give advice without access to the system, not knowing
the tables, the queries or the indexes.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql
time. Something seems to have changed one morning last w
, as the entireapplication slowed down significantly. I have no idea what could have
caused the problem, as there's nothing out of the ordinary in any of the
logs. I issued a DBCC CHECKDB to verify the integrity of the database, and
that didn't show any problems. I also ran DBCC DBREINDEX, DBCC UPDATEUSAGE,
and UPDATE STATISTICS, but that doesn't appear to have fixed anything
either.
During my investigation I was able to identify one query that was taking an
inordinate amount of time to execute. It is an in-line query (rather than
one that's invoked from within a stored procedure) that should have returned
in a couple of seconds, but was taking over 40 seconds. In trying to figure
out what was going on, I asked the server for an estimated execution plan,
and was surprised to find that it took just under 40 seconds for the server
to provide it. Once the plan was in the cache, asking for it again was
instantaneous and the query would execute in a couple of seconds. After the
query was removed from the cache (or if the query was modified slightly), it
took 40 seconds again.
It was my understanding that the server simply looked at indices and
statistics in order to come up with an ESTIMATED plan. Up until now I've
never seen it take more than a second to do so. Am I wrong about this?
Does this give anyone a clue as to what might be going on? The query is
pretty much a simple join between 3 tables and 3 views. (Each of the views
have 3-5 tables.) Any help would be appreciated.
Thanks!You cannot directly use a SProc inside an if condition.
If you really want to call within the if condition, then create a function.
But remember that the function works because its an extended proc.
CREATE function [dbo].[usp_FileExists] (@.physname nvarchar(260))
returns int
as
begin
DECLARE @.i int
EXEC master.dbo.usp_FileExists @.physname =
'z:\data\sql_data\myData_Data.MDF'
,@.i out
return(SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END)
end
IF (master.dbo. usp_FileExists('D:\data\sql_data\myData_
Data.MDF') = 0)
PRINT 'Does not Exist '
But again, unless you have a pressing reason to call it within the if
condition, I would suggest you call the proc the same way as the extended
proc is called within it..
Like this..
DECLARE @.RC int
DECLARE @.physname nvarchar(260)
set @.physname = 'D:\data\sql_data\myData_Data.MDF'
EXEC @.RC = [master].[dbo].[usp_FileExists1] @.physname
if(@.rc = 0)
PRINT 'Does not Exist '
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Answered in the wrong post... sorry :(
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Omnibuzz" wrote:
> You cannot directly use a SProc inside an if condition.
> If you really want to call within the if condition, then create a function
.
> But remember that the function works because its an extended proc.
> CREATE function [dbo].[usp_FileExists] (@.physname nvarchar(260))
> returns int
> as
> begin
> DECLARE @.i int
> EXEC master.dbo.usp_FileExists @.physname =
> 'z:\data\sql_data\myData_Data.MDF'
> ,@.i out
> return(SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END)
> end
> IF (master.dbo. usp_FileExists('D:\data\sql_data\myData_
Data.MDF') = 0)
> PRINT 'Does not Exist '
> But again, unless you have a pressing reason to call it within the if
> condition, I would suggest you call the proc the same way as the extended
> proc is called within it..
> Like this..
> DECLARE @.RC int
> DECLARE @.physname nvarchar(260)
> set @.physname = 'D:\data\sql_data\myData_Data.MDF'
> EXEC @.RC = [master].[dbo].[usp_FileExists1] @.physname
> if(@.rc = 0)
> PRINT 'Does not Exist '
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Mark Pauker (mpauker@.optonline.net) writes:
> During my investigation I was able to identify one query that was taking
> an inordinate amount of time to execute. It is an in-line query (rather
> than one that's invoked from within a stored procedure) that should have
> returned in a couple of seconds, but was taking over 40 seconds. In
> trying to figure out what was going on, I asked the server for an
> estimated execution plan, and was surprised to find that it took just
> under 40 seconds for the server to provide it. Once the plan was in the
> cache, asking for it again was instantaneous and the query would execute
> in a couple of seconds. After the query was removed from the cache (or
> if the query was modified slightly), it took 40 seconds again.
> It was my understanding that the server simply looked at indices and
> statistics in order to come up with an ESTIMATED plan. Up until now
> I've never seen it take more than a second to do so. Am I wrong about
> this? Does this give anyone a clue as to what might be going on? The
> query is pretty much a simple join between 3 tables and 3 views. (Each
> of the views have 3-5 tables.) Any help would be appreciated.
So 40 seconds for computing a query plan is indeed a bit extraordinary.
Then again a query like:
SELECT * FROM tbl WHERE col (val1, val2, ... val10000)
typically takes 15 seconds to compile on SQL 2000.
Since this is a query submitted from the application, I'm a little
nervous how the code actually looks like. When you submit bare
queries from the client, it's essential that:
o No parameter values are interpolated into the query string.
o All tables are prefixed with owner/schema.
o The query text never changes.
When SQL Server looks up the query plan for a loose query in the cache,
it hashes the text. For this reason the cache is case- and space-sensitive.
That is, if a single space is added to the query, there will be a new
plan.
The reason that the owner/schema is important is that if the query is
SELECT ... FROM tbl
and is run by user joe, SQL Server will first have to check whether there
is a joe.tbl before it tries with dbo.tbl. Since joe.tbl could appear in
any moment, the consequence is that joe gets a private plan in the cache
for the query. If you say "SELECT ... FROM dbo.tbl" this does not happen.
Now, there are other reasons for recompilations as well, for instance
changed statistics. If any of the involved tables are moderate in size,
but updated frequently, recompilation will hit you several times a time.
One option to address this is to add OPTION (KEEPFIXED PLAN) at the end
of the query, to prevent recompiles. But this still will not help if
the query involves a temp table that is recreated each time.
I understand that you would like to know why compilation takes such
long time, but I don't really have an answer to that. I guess I would
need intimate knowledge of the involved tables to tell. And, of course,
which version of SQL Server you are using.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> So 40 seconds for computing a query plan is indeed a bit extraordinary.
The bizarre thing is that this only started last w
. Prior to that thequery executed in a few seconds. I'm trying to figure out what could have
caused this. Neither the executable nor any server configurations have
changed.
> Then again a query like:
> SELECT * FROM tbl WHERE col (val1, val2, ... val10000)
> typically takes 15 seconds to compile on SQL 2000.
I've never seen a query take anywhere near this amout of time. Why would it
take so long?
> When you submit bare queries from the client, it's essential that:
> o No parameter values are interpolated into the query string.
> o All tables are prefixed with owner/schema.
> o The query text never changes.
The query is built by the front end, with the "parameterized" data being
appended directly into the query string. I've been re-factoring the app to
remove this type of code, but there's way too much of it for me to get to in
a timely manner. While your suggestions make sense, the bottom line is that
before last w
, the system was performing acceptably (even though it washaving to recompile the query each time it was submitted). I'm hoping to
identify a root cause of the slowdown so I can get performance back to the
way it was before all of this started last w
. What could cause a serverto generate an estimated execution plan in 40 seonds when that same server
was generating estimated execution plans of one or two seconds just a few
days before?.|||Mark Pauker wrote:
> The query is built by the front end, with the "parameterized" data being
> appended directly into the query string. I've been re-factoring the app t
o
> remove this type of code, but there's way too much of it for me to get to
in
> a timely manner. While your suggestions make sense, the bottom line is th
at
> before last w
, the system was performing acceptably (even though it was> having to recompile the query each time it was submitted). I'm hoping to
> identify a root cause of the slowdown so I can get performance back to the
> way it was before all of this started last w
. What could cause a server
> to generate an estimated execution plan in 40 seonds when that same server
> was generating estimated execution plans of one or two seconds just a few
> days before?.
>
How often are you updating statistics on the database? Have you
recently made mass changes to any tables, i.e. deleted a bunch of
records, or imported a bunch? Kind of sounds like a statistics issue to
me...|||The query optimizer will compile based on the estimated cost of the query
(and its complexity). We implement this using "buckets" of optimizations
that we call stages. Cheap queries get only one stage. More expensive get
two stages. The most complex get 3 stages.
A query can get slightly more expensive and go from one bucket to the next.
That next bucket could take a lot longer, especially if you have a lot of
joins (the number of alternatives we have to consider explodes).
Another reason things could change like this is that the initial join order
picked could be less optimal. This could cause a higher estimated cost and
thus cause you to go through more optimization stages. This could be caused
by changes in the underlying data.
Another possible reason for longer compilation time is that something has
changed (which sounds less likely here). If you upgraded from one release
to another, perhaps there is a bug and we're not finding a solution fast
enough.
I usually try to simplify the query (remove pieces of it one at a time)
until I've found the culprit.
Conor
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:OGudU7RmGHA.4032@.TK2MSFTNGP02.phx.gbl...
> Mark Pauker wrote:
> How often are you updating statistics on the database? Have you recently
> made mass changes to any tables, i.e. deleted a bunch of records, or
> imported a bunch? Kind of sounds like a statistics issue to me...|||Or perhaps it is auto-stats kicking in? (Doesn't sound like it from the orig
inal description, but
who knows...)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Conor Cunningham [MS]" <conorc_removeme@.online.microsoft.com> wrote in message
news:eLcx2mTmGHA.4992@.TK2MSFTNGP03.phx.gbl...
> The query optimizer will compile based on the estimated cost of the query
(and its complexity).
> We implement this using "buckets" of optimizations that we call stages. C
heap queries get only
> one stage. More expensive get two stages. The most complex get 3 stages.
> A query can get slightly more expensive and go from one bucket to the next
. That next bucket could
> take a lot longer, especially if you have a lot of joins (the number of al
ternatives we have to
> consider explodes).
> Another reason things could change like this is that the initial join orde
r picked could be less
> optimal. This could cause a higher estimated cost and thus cause you to g
o through more
> optimization stages. This could be caused by changes in the underlying da
ta.
> Another possible reason for longer compilation time is that something has
changed (which sounds
> less likely here). If you upgraded from one release to another, perhaps t
here is a bug and we're
> not finding a solution fast enough.
> I usually try to simplify the query (remove pieces of it one at a time) un
til I've found the
> culprit.
> Conor
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:OGudU7RmGHA.4032@.TK2MSFTNGP02.phx.gbl...
>|||> A query can get slightly more expensive and go from one bucket to the
> next. That next bucket could take a lot longer, especially if you have a
> lot of joins (the number of alternatives we have to consider explodes).
This makes sense, but I still can't understand why this could take 30
seconds or more. Is this within a range that could be "as expected?" It
seems to me that something else must be going on. (The query in question
contains 6 tables and 3 views that are all left-joined togather. Each view
has 4 or 5 tables.)
Keep in mind that the ENTIRE application has slowed down, not just this
query. I brought up the query for illustrative purposes, but it really
appears to be just a symptom of a more systemmic problem. (I could rewrite
the query, but there would be a hundred more places that I'd need to fix...
And even then I wouldn't know that a larger problem wasn't simply being
masked.)
When the system started "misbehaving" last w
, the processor utilizationwent way up but the disk didn't get any more active. This is what makes me
think that the slow-down is related to the server's generating execution
plans. The query simply seems to bear that out. (Once the execution plan
has been generated, the query appears to execute normally.)
I agree with Tracy's comment that it appears to be statistics-related, but
I'm not sure what to do with that information. I've updated them manually
(with and without FULLSCAN), and verified that they haven't been updated
automatically between requested execution plans. It's almost as if the
server is ignoring the statistics and trying to recalculate the plan from
scratch (although I guess I'd see more disk activity if this were true).
> Another reason things could change like this is that the initial join
> order picked could be less optimal. This could cause a higher estimated
> cost and thus cause you to go through more optimization stages. This
> could be caused by changes in the underlying data.
Is there any way to alleviate this if it turns out to be the case?
> Another possible reason for longer compilation time is that something has
> changed (which sounds less likely here). If you upgraded from one release
> to another, perhaps there is a bug and we're not finding a solution fast
> enough.
Sounds unlikely.
> I usually try to simplify the query (remove pieces of it one at a time)
> until I've found the culprit.
I can certainly do this (and probably will), but as I said, the problem is
occurring system-wide. I was hoping that there would be some known issue or
a suggestion that might address things at a more global level. I think that
the problem may lie in a single, very widely used table (that's involved in
this and a great number of other queries). The table is approaching 10M
records, occupying over 2G of space (independent of indices and statistics).
Any other thoughts would be greatly appreciated.
-- Mark|||Mark Pauker (mpauker@.optonline.net) writes:
> Keep in mind that the ENTIRE application has slowed down, not just this
> query. I brought up the query for illustrative purposes, but it really
> appears to be just a symptom of a more systemmic problem.
But if that query needs to be compiled again and again, since it has
interpolated queries, you could have several processes compiling different
versions of this query. And if there are other similar queries running,
it could all add up to this disaster.
> Is there any way to alleviate this if it turns out to be the case?
Better indexing maybe?
It's awfully hard to give advice without access to the system, not knowing
the tables, the queries or the indexes.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql
Query performance issue - Need direction
My company has developed a VB.NET 2003 application which interfaces with the SQL Server database of another software package. We are experiencing serious performance differences at one of our client sites on a particular query that is one of the frequent queries that runs in our software which I will try to outline below.
Our database and the accounting system database are in separate instances on the same SQL Server 2005 box. Our query is a simple SELECT statement with filtering which properly utilizes the built-in indexes for that table. In their case it returns roughly 13000 records (vendor listing). There is no report of slowness regarding other queries as none of them return the volume that this particular query does.
On one offending workstation: if we run this query, it takes less than a second to get results back in SQL Management Studio's query window. This is consistent with the accounting software which takes about 2 seconds to run the query and display the results in a grid (the accounting system is Viewpoint, a leading construction accounting system written in VB6). In our system, attempting to do the same thing as the accounting system, it is taking nearly 16 seconds to retrieve and display the results. This workstation is running on WIndows XP SP2. They are experiencing this same behavior on several workstations.
At the same client site, they have identified two Windows 2000 workstations which can run the query and display the results from our system in approx. 2 seconds - which is what we would expect and is also what we experience internally on our development platform, and 12 other clients who run our software and interface with the same accounting system - regardless of OS on the workstation.
Based on the results in the query window and the results on the 2000 workstations and the success of other clients who have as many records if not more than this particular client - i don't think we have an issue with the query or the code. The results are very consistent on each workstation as well so I don't think it's a network traffic or activity issue. My gut points me at .NET Framework version differences - since the things that would affect this process are framework items - SQL Server OLEDB drivers and objects, DataGrids, Datatables and DataViews,etc.
On one of the XP workstations I had them remove .NET framework 3.0 which had no effect on the problem, but they also have v. 1.0.3705, 1.0 Hotfix (KBB886906), Framework 1.1, 1.1. Hotfix (KBB886903), Framework 2.0 with 2 security updates (KB917823 and BK922770).
Can anyone point me in a direction on things that I can check that might be different between their XP and 2000 workstations which might affect this process? I am working on some caching logic which will reduce the number of times this query is run, but I would like to understand why there is a behavioral difference.
Any help is appreciated. Please let me know if there is any other information I can provide.
Thanks.
PaulJudging from the fact that some workstations are experiencing problems while others are not, I would concentrate on the workstations experiencing the problems, rather than the query or the database server. First thing I would check is what speed and duplex settings are on a machine that performs well, and one that does not perform well. I would wager these are not the same. The reason you only see it on this one query (or a set of queries) is that you are trying to bring back 13,000 rows of data in these queries (a more significant network load than the usual < 100 rows per operation).|||The problem turned out to be an issue with a "chimney offload" setting on one of their NIC cards on the server.
Once this setting was disabled, the slowness disappeared.
See article below:
http://support.microsoft.com/kb/942861/en-us
Our database and the accounting system database are in separate instances on the same SQL Server 2005 box. Our query is a simple SELECT statement with filtering which properly utilizes the built-in indexes for that table. In their case it returns roughly 13000 records (vendor listing). There is no report of slowness regarding other queries as none of them return the volume that this particular query does.
On one offending workstation: if we run this query, it takes less than a second to get results back in SQL Management Studio's query window. This is consistent with the accounting software which takes about 2 seconds to run the query and display the results in a grid (the accounting system is Viewpoint, a leading construction accounting system written in VB6). In our system, attempting to do the same thing as the accounting system, it is taking nearly 16 seconds to retrieve and display the results. This workstation is running on WIndows XP SP2. They are experiencing this same behavior on several workstations.
At the same client site, they have identified two Windows 2000 workstations which can run the query and display the results from our system in approx. 2 seconds - which is what we would expect and is also what we experience internally on our development platform, and 12 other clients who run our software and interface with the same accounting system - regardless of OS on the workstation.
Based on the results in the query window and the results on the 2000 workstations and the success of other clients who have as many records if not more than this particular client - i don't think we have an issue with the query or the code. The results are very consistent on each workstation as well so I don't think it's a network traffic or activity issue. My gut points me at .NET Framework version differences - since the things that would affect this process are framework items - SQL Server OLEDB drivers and objects, DataGrids, Datatables and DataViews,etc.
On one of the XP workstations I had them remove .NET framework 3.0 which had no effect on the problem, but they also have v. 1.0.3705, 1.0 Hotfix (KBB886906), Framework 1.1, 1.1. Hotfix (KBB886903), Framework 2.0 with 2 security updates (KB917823 and BK922770).
Can anyone point me in a direction on things that I can check that might be different between their XP and 2000 workstations which might affect this process? I am working on some caching logic which will reduce the number of times this query is run, but I would like to understand why there is a behavioral difference.
Any help is appreciated. Please let me know if there is any other information I can provide.
Thanks.
PaulJudging from the fact that some workstations are experiencing problems while others are not, I would concentrate on the workstations experiencing the problems, rather than the query or the database server. First thing I would check is what speed and duplex settings are on a machine that performs well, and one that does not perform well. I would wager these are not the same. The reason you only see it on this one query (or a set of queries) is that you are trying to bring back 13,000 rows of data in these queries (a more significant network load than the usual < 100 rows per operation).|||The problem turned out to be an issue with a "chimney offload" setting on one of their NIC cards on the server.
Once this setting was disabled, the slowness disappeared.
See article below:
http://support.microsoft.com/kb/942861/en-us
Tuesday, March 20, 2012
Query Performance
I have a vendor application running on SQL 2000 sp4. I've got a query
that isn't using any indexes:
select hsi.itemdata.itemnum, hsi.itemdata.itemname,
hsi.itemdata.batchnum, hsi.itemdata.status,
hsi.itemdata.itemtypegroupnum,
hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
hsi.itemdata.datestored, hsi.itemdata.usernum,
hsi.itemdata.deleteusernum,
hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
hsi.itemdata.institution, hsi.itemdata.maxdocrev
from hsi.itemdata
where hsi.itemdata.status + 0 = 0 and
( hsi.itemdata.itemtypenum = 101 or
hsi.itemdata.itemtypenum = 102 or
hsi.itemdata.itemtypenum = 103 or
hsi.itemdata.itemtypenum = 329 )
order by 8 desc
The table has 3,105,135 records in test. (There are 22,905,590 in
production.)
CREATE TABLE [hsi].[itemdata] (
[itemnum] [int] NOT NULL ,
[itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[batchnum] [int] NULL ,
[status] [int] NULL ,
[itemtypegroupnum] [int] NULL ,
[itemtypenum] [int] NULL ,
[itrevnum] [int] NULL ,
[itemdate] [datetime] NULL ,
[datestored] [datetime] NULL ,
[usernum] [int] NULL ,
[deleteusernum] [int] NULL ,
[securityvalue] [int] NULL ,
[doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[institution] [int] NULL ,
[maxdocrev] [int] NULL
) ON [DBSpace2]
GO
CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
[itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
GO
CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
[status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
GO
CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
[itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
[DBSpace2i]
GO
CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
[itemnum]) ON [DBSpace2]
GO
I would have thought it would use one of the existing indexes but it
just does a table scan.
I also tried:
CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
[itemtypenum]) ON [PRIMARY]
GO
But that didn't work either.
Any suggestions?
Thank you,
Toni
*** Sent via Developersdex http://www.codecomments.com ***
The information provided raises more questions than it allows answers.
Indexes will only be used if the optimizer calculates that it will be
more efficient than a table scan. This generally depends on how
selective the indexes are, particularly for the values used in the
query.
How selective is itemtypenum? How selective are the specific values
of itemtypenum? What percentage of the table will the numbers
returned by the query represent?
SELECT hsi.itemdata.itemtypenum, count(*) as Rows
FROM hsi.itemdata
WHERE hsi.itemdata.itemtypenum IN (101, 102, 103, 329)
GROUP BY hsi.itemdata.itemtypenum
How selective is the test " hsi.itemdata.status + 0 = 0"? What data
type is hsi.itemdata.status? Why the + 0? It is possible that IF a
relatively small percentage of rows have a status = 0 that including
status in an index might help - but not while the + 0 is there.
Roy Harvey
Beacon Falls, CT
On Thu, 08 Mar 2007 07:24:39 -0800, Toni <teibner@.allinasql.com>
wrote:
>I have a vendor application running on SQL 2000 sp4. I've got a query
>that isn't using any indexes:
>select hsi.itemdata.itemnum, hsi.itemdata.itemname,
>hsi.itemdata.batchnum, hsi.itemdata.status,
>hsi.itemdata.itemtypegroupnum,
>hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
>hsi.itemdata.datestored, hsi.itemdata.usernum,
>hsi.itemdata.deleteusernum,
>hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
>hsi.itemdata.institution, hsi.itemdata.maxdocrev
>from hsi.itemdata
>where hsi.itemdata.status + 0 = 0 and
>( hsi.itemdata.itemtypenum = 101 or
>hsi.itemdata.itemtypenum = 102 or
>hsi.itemdata.itemtypenum = 103 or
>hsi.itemdata.itemtypenum = 329 )
>order by 8 desc
>The table has 3,105,135 records in test. (There are 22,905,590 in
>production.)
>CREATE TABLE [hsi].[itemdata] (
>[itemnum] [int] NOT NULL ,
>[itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[batchnum] [int] NULL ,
>[status] [int] NULL ,
>[itemtypegroupnum] [int] NULL ,
>[itemtypenum] [int] NULL ,
>[itrevnum] [int] NULL ,
>[itemdate] [datetime] NULL ,
>[datestored] [datetime] NULL ,
>[usernum] [int] NULL ,
>[deleteusernum] [int] NULL ,
>[securityvalue] [int] NULL ,
>[doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>,
>[institution] [int] NULL ,
>[maxdocrev] [int] NULL
>) ON [DBSpace2]
>GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
>[itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
>GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
>[status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
>GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
>[itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
>[DBSpace2i]
>GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
>[itemnum]) ON [DBSpace2]
>GO
>I would have thought it would use one of the existing indexes but it
>just does a table scan.
>I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
>[itemtypenum]) ON [PRIMARY]
>GO
>But that didn't work either.
>Any suggestions?
>Thank you,
>Toni
>*** Sent via Developersdex http://www.codecomments.com ***
|||what is the ORDER By 8?
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Toni" <teibner@.allinasql.com> wrote in message
news:ul%23akXZYHHA.992@.TK2MSFTNGP02.phx.gbl...
>I have a vendor application running on SQL 2000 sp4. I've got a query
> that isn't using any indexes:
> select hsi.itemdata.itemnum, hsi.itemdata.itemname,
> hsi.itemdata.batchnum, hsi.itemdata.status,
> hsi.itemdata.itemtypegroupnum,
> hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
> hsi.itemdata.datestored, hsi.itemdata.usernum,
> hsi.itemdata.deleteusernum,
> hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
> hsi.itemdata.institution, hsi.itemdata.maxdocrev
> from hsi.itemdata
> where hsi.itemdata.status + 0 = 0 and
> ( hsi.itemdata.itemtypenum = 101 or
> hsi.itemdata.itemtypenum = 102 or
> hsi.itemdata.itemtypenum = 103 or
> hsi.itemdata.itemtypenum = 329 )
> order by 8 desc
> The table has 3,105,135 records in test. (There are 22,905,590 in
> production.)
> CREATE TABLE [hsi].[itemdata] (
> [itemnum] [int] NOT NULL ,
> [itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [batchnum] [int] NULL ,
> [status] [int] NULL ,
> [itemtypegroupnum] [int] NULL ,
> [itemtypenum] [int] NULL ,
> [itrevnum] [int] NULL ,
> [itemdate] [datetime] NULL ,
> [datestored] [datetime] NULL ,
> [usernum] [int] NULL ,
> [deleteusernum] [int] NULL ,
> [securityvalue] [int] NULL ,
> [doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [institution] [int] NULL ,
> [maxdocrev] [int] NULL
> ) ON [DBSpace2]
> GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
> [itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
> [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
> [itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
> [DBSpace2i]
> GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
> [itemnum]) ON [DBSpace2]
> GO
> I would have thought it would use one of the existing indexes but it
> just does a table scan.
> I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
> [itemtypenum]) ON [PRIMARY]
> GO
> But that didn't work either.
> Any suggestions?
> Thank you,
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***
|||On Thu, 8 Mar 2007 17:03:46 -0000, "Jack Vamvas"
<DEL_TO_REPLY@.del.com> wrote:
>what is the ORDER By 8?
That says to order the eighth column in the SELECT list. My
understanding is that this syntax is deprecated in the SQL standard,
and it certainly has no place in production code, but it is a handy
shortcut when doing quick and dirty SQL.
Roy Harvey
Beacon Falls, CT
|||On Thu, 08 Mar 2007 12:22:58 -0500, Roy Harvey <roy_harvey@.snet.net>
wrote:
>That says to order the eighth column in the SELECT list.
Make that:
That says to order BY the eighth column in the SELECT list.
|||This is code from a vendor application. I don't have any control over
it other than to recommend they make changes. As to the why of some of
the questions, I can answer some of them.
I don't know why the vendor is doing the calculation of
hsi.itemdata.status + 0 = 0, unfortunately.
I guess it's not real selective at all. (There are only 613 different
values in the table in test.) For this specific query, this is what is
in the table:
itemtypenum rows
101 127282
102 100347
103 6901
329 387
I apologize for my lack of knowledge on this. I'm not very experienced
with query tuning.
It sounds like the whole query needs to be rewritten in order to be
efficient and make use of any indexes that might exist on the table. Is
that correct?
I appreciate all the responses!
Thank you so much
Toni
*** Sent via Developersdex http://www.codecomments.com ***
|||Since there are 3.1 million rows in the table, and the query returns
almost 0.3 million, the selectivity on itemtypenum is roughly ten
percent. That really isn't selective enough for an index on
itemtypenum to help this query. Had status been selective, and the
query written without the silly + 0, an index on (status, itemtypenum)
might have been of some use. As it stands I do not see any
oportunities for improvement.
Roy Harvey
Beacon Falls, CT
On Thu, 08 Mar 2007 10:02:41 -0800, Toni <teibner@.allinasql.com>
wrote:
>This is code from a vendor application. I don't have any control over
>it other than to recommend they make changes. As to the why of some of
>the questions, I can answer some of them.
>I don't know why the vendor is doing the calculation of
>hsi.itemdata.status + 0 = 0, unfortunately.
>I guess it's not real selective at all. (There are only 613 different
>values in the table in test.) For this specific query, this is what is
>in the table:
>itemtypenum rows
>101 127282
>102 100347
>103 6901
>329 387
>I apologize for my lack of knowledge on this. I'm not very experienced
>with query tuning.
>It sounds like the whole query needs to be rewritten in order to be
>efficient and make use of any indexes that might exist on the table. Is
>that correct?
>I appreciate all the responses!
>Thank you so much
>Toni
>*** Sent via Developersdex http://www.codecomments.com ***
that isn't using any indexes:
select hsi.itemdata.itemnum, hsi.itemdata.itemname,
hsi.itemdata.batchnum, hsi.itemdata.status,
hsi.itemdata.itemtypegroupnum,
hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
hsi.itemdata.datestored, hsi.itemdata.usernum,
hsi.itemdata.deleteusernum,
hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
hsi.itemdata.institution, hsi.itemdata.maxdocrev
from hsi.itemdata
where hsi.itemdata.status + 0 = 0 and
( hsi.itemdata.itemtypenum = 101 or
hsi.itemdata.itemtypenum = 102 or
hsi.itemdata.itemtypenum = 103 or
hsi.itemdata.itemtypenum = 329 )
order by 8 desc
The table has 3,105,135 records in test. (There are 22,905,590 in
production.)
CREATE TABLE [hsi].[itemdata] (
[itemnum] [int] NOT NULL ,
[itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[batchnum] [int] NULL ,
[status] [int] NULL ,
[itemtypegroupnum] [int] NULL ,
[itemtypenum] [int] NULL ,
[itrevnum] [int] NULL ,
[itemdate] [datetime] NULL ,
[datestored] [datetime] NULL ,
[usernum] [int] NULL ,
[deleteusernum] [int] NULL ,
[securityvalue] [int] NULL ,
[doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[institution] [int] NULL ,
[maxdocrev] [int] NULL
) ON [DBSpace2]
GO
CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
[itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
GO
CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
[status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
GO
CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
[itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
[DBSpace2i]
GO
CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
[itemnum]) ON [DBSpace2]
GO
I would have thought it would use one of the existing indexes but it
just does a table scan.
I also tried:
CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
[itemtypenum]) ON [PRIMARY]
GO
But that didn't work either.
Any suggestions?
Thank you,
Toni
*** Sent via Developersdex http://www.codecomments.com ***
The information provided raises more questions than it allows answers.
Indexes will only be used if the optimizer calculates that it will be
more efficient than a table scan. This generally depends on how
selective the indexes are, particularly for the values used in the
query.
How selective is itemtypenum? How selective are the specific values
of itemtypenum? What percentage of the table will the numbers
returned by the query represent?
SELECT hsi.itemdata.itemtypenum, count(*) as Rows
FROM hsi.itemdata
WHERE hsi.itemdata.itemtypenum IN (101, 102, 103, 329)
GROUP BY hsi.itemdata.itemtypenum
How selective is the test " hsi.itemdata.status + 0 = 0"? What data
type is hsi.itemdata.status? Why the + 0? It is possible that IF a
relatively small percentage of rows have a status = 0 that including
status in an index might help - but not while the + 0 is there.
Roy Harvey
Beacon Falls, CT
On Thu, 08 Mar 2007 07:24:39 -0800, Toni <teibner@.allinasql.com>
wrote:
>I have a vendor application running on SQL 2000 sp4. I've got a query
>that isn't using any indexes:
>select hsi.itemdata.itemnum, hsi.itemdata.itemname,
>hsi.itemdata.batchnum, hsi.itemdata.status,
>hsi.itemdata.itemtypegroupnum,
>hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
>hsi.itemdata.datestored, hsi.itemdata.usernum,
>hsi.itemdata.deleteusernum,
>hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
>hsi.itemdata.institution, hsi.itemdata.maxdocrev
>from hsi.itemdata
>where hsi.itemdata.status + 0 = 0 and
>( hsi.itemdata.itemtypenum = 101 or
>hsi.itemdata.itemtypenum = 102 or
>hsi.itemdata.itemtypenum = 103 or
>hsi.itemdata.itemtypenum = 329 )
>order by 8 desc
>The table has 3,105,135 records in test. (There are 22,905,590 in
>production.)
>CREATE TABLE [hsi].[itemdata] (
>[itemnum] [int] NOT NULL ,
>[itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[batchnum] [int] NULL ,
>[status] [int] NULL ,
>[itemtypegroupnum] [int] NULL ,
>[itemtypenum] [int] NULL ,
>[itrevnum] [int] NULL ,
>[itemdate] [datetime] NULL ,
>[datestored] [datetime] NULL ,
>[usernum] [int] NULL ,
>[deleteusernum] [int] NULL ,
>[securityvalue] [int] NULL ,
>[doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>,
>[institution] [int] NULL ,
>[maxdocrev] [int] NULL
>) ON [DBSpace2]
>GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
>[itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
>GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
>[status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
>GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
>[itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
>[DBSpace2i]
>GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
>[itemnum]) ON [DBSpace2]
>GO
>I would have thought it would use one of the existing indexes but it
>just does a table scan.
>I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
>[itemtypenum]) ON [PRIMARY]
>GO
>But that didn't work either.
>Any suggestions?
>Thank you,
>Toni
>*** Sent via Developersdex http://www.codecomments.com ***
|||what is the ORDER By 8?
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Toni" <teibner@.allinasql.com> wrote in message
news:ul%23akXZYHHA.992@.TK2MSFTNGP02.phx.gbl...
>I have a vendor application running on SQL 2000 sp4. I've got a query
> that isn't using any indexes:
> select hsi.itemdata.itemnum, hsi.itemdata.itemname,
> hsi.itemdata.batchnum, hsi.itemdata.status,
> hsi.itemdata.itemtypegroupnum,
> hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
> hsi.itemdata.datestored, hsi.itemdata.usernum,
> hsi.itemdata.deleteusernum,
> hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
> hsi.itemdata.institution, hsi.itemdata.maxdocrev
> from hsi.itemdata
> where hsi.itemdata.status + 0 = 0 and
> ( hsi.itemdata.itemtypenum = 101 or
> hsi.itemdata.itemtypenum = 102 or
> hsi.itemdata.itemtypenum = 103 or
> hsi.itemdata.itemtypenum = 329 )
> order by 8 desc
> The table has 3,105,135 records in test. (There are 22,905,590 in
> production.)
> CREATE TABLE [hsi].[itemdata] (
> [itemnum] [int] NOT NULL ,
> [itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [batchnum] [int] NULL ,
> [status] [int] NULL ,
> [itemtypegroupnum] [int] NULL ,
> [itemtypenum] [int] NULL ,
> [itrevnum] [int] NULL ,
> [itemdate] [datetime] NULL ,
> [datestored] [datetime] NULL ,
> [usernum] [int] NULL ,
> [deleteusernum] [int] NULL ,
> [securityvalue] [int] NULL ,
> [doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [institution] [int] NULL ,
> [maxdocrev] [int] NULL
> ) ON [DBSpace2]
> GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
> [itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
> [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
> [itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
> [DBSpace2i]
> GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
> [itemnum]) ON [DBSpace2]
> GO
> I would have thought it would use one of the existing indexes but it
> just does a table scan.
> I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
> [itemtypenum]) ON [PRIMARY]
> GO
> But that didn't work either.
> Any suggestions?
> Thank you,
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***
|||On Thu, 8 Mar 2007 17:03:46 -0000, "Jack Vamvas"
<DEL_TO_REPLY@.del.com> wrote:
>what is the ORDER By 8?
That says to order the eighth column in the SELECT list. My
understanding is that this syntax is deprecated in the SQL standard,
and it certainly has no place in production code, but it is a handy
shortcut when doing quick and dirty SQL.
Roy Harvey
Beacon Falls, CT
|||On Thu, 08 Mar 2007 12:22:58 -0500, Roy Harvey <roy_harvey@.snet.net>
wrote:
>That says to order the eighth column in the SELECT list.
Make that:
That says to order BY the eighth column in the SELECT list.
|||This is code from a vendor application. I don't have any control over
it other than to recommend they make changes. As to the why of some of
the questions, I can answer some of them.
I don't know why the vendor is doing the calculation of
hsi.itemdata.status + 0 = 0, unfortunately.
I guess it's not real selective at all. (There are only 613 different
values in the table in test.) For this specific query, this is what is
in the table:
itemtypenum rows
101 127282
102 100347
103 6901
329 387
I apologize for my lack of knowledge on this. I'm not very experienced
with query tuning.
It sounds like the whole query needs to be rewritten in order to be
efficient and make use of any indexes that might exist on the table. Is
that correct?
I appreciate all the responses!
Thank you so much
Toni
*** Sent via Developersdex http://www.codecomments.com ***
|||Since there are 3.1 million rows in the table, and the query returns
almost 0.3 million, the selectivity on itemtypenum is roughly ten
percent. That really isn't selective enough for an index on
itemtypenum to help this query. Had status been selective, and the
query written without the silly + 0, an index on (status, itemtypenum)
might have been of some use. As it stands I do not see any
oportunities for improvement.
Roy Harvey
Beacon Falls, CT
On Thu, 08 Mar 2007 10:02:41 -0800, Toni <teibner@.allinasql.com>
wrote:
>This is code from a vendor application. I don't have any control over
>it other than to recommend they make changes. As to the why of some of
>the questions, I can answer some of them.
>I don't know why the vendor is doing the calculation of
>hsi.itemdata.status + 0 = 0, unfortunately.
>I guess it's not real selective at all. (There are only 613 different
>values in the table in test.) For this specific query, this is what is
>in the table:
>itemtypenum rows
>101 127282
>102 100347
>103 6901
>329 387
>I apologize for my lack of knowledge on this. I'm not very experienced
>with query tuning.
>It sounds like the whole query needs to be rewritten in order to be
>efficient and make use of any indexes that might exist on the table. Is
>that correct?
>I appreciate all the responses!
>Thank you so much
>Toni
>*** Sent via Developersdex http://www.codecomments.com ***
Query Performance
Hi all,
I′m using SQL Server 2000 in the Windows 2003 Server with 2GB RAM
and 2 dual core processors.
There is an application that access the SQL Server.
Several queries are with problems of performance.
I cannot change these queries ′cause the application
was made by other IT company.
[code]
SELECT * FROM SE1010 SE1
WHERE E1_FILIAL = ' '
AND D_E_L_E_T_ <> '*'
AND E1_CLIENTE BETWEEN ' ' AND 'ZZZZZZ'
AND E1_PREFIXO BETWEEN ' ' AND 'ZZZ'
AND E1_NUM BETWEEN ' ' AND 'ZZZZZZ'
AND E1_PORTADO BETWEEN ' ' AND 'ZZZ'
AND E1_VENCREA BETWEEN '20010101' AND '20070228'
AND (E1_MULTNAT = '1' OR (E1_NATUREZ BETWEEN ' ' AND 'ZZZZZZZZZZ'))
AND E1_EMISSAO BETWEEN '20010101' AND '20070228'
AND E1_LOJA BETWEEN ' ' AND 'ZZ'
AND E1_EMISSAO <= '20070301'
AND ((E1_EMIS1 BETWEEN '20060101' AND '20070228')
OR E1_EMISSAO BETWEEN '20060101' AND '20070228')
ORDER BY E1_FILIAL,E1_PREFIXO,E1_NUM,E1_PARCELA,E1_TIPO
The execution plan is:
|--Sort(ORDER BY:([SE1].[E1_PREFIXO] ASC, [SE1].[E1_NUM] ASC,
[SE1].[E1_PARCELA] ASC, [SE1].[E1_TIPO] ASC))
|--Filter(WHERE:((((([SE1].[E1_EMISSAO]>='20010101'
AND [SE1].[E1_EMISSAO]<='20070228') AND [SE1].[E1_LOJA]>=' ')
AND [SE1].[E1_LOJA]<='ZZ') AND [SE1].[E1_EMISSAO]<='20070301')
AND (([SE1].[E1_EMIS1]>='20060101' AND
[SE1].[E1_EMIS1]<='20070228') OR
|--Clustered Index Scan(OBJECT:([DADOSADV].[dbo].[SE1010].[SE1010_PK]
AS [SE1]), WHERE:(((((((((((([SE1].[E1_FILIAL]=' '
AND [SE1].[D_E_L_E_T_]<>'*') AND [SE1].[E1_CLIENTE]>=' ')
AND [SE1].[E1_CLIENTE]<='ZZZZZZ') AND [SE1].[E1_PREFIXO]>=''
[/code]
I′ve created index for the columns that are used by where clause
and I′ve performed the DBCC INDEXDEFRAG and DBREINDEX, but after I′ve performed
theses functions, the performance fell.
What can I do to improve query performance?
thanks!!!!Moving to engine forum.
Query Performance
I have a vendor application running on SQL 2000 sp4. I've got a query
that isn't using any indexes:
select hsi.itemdata.itemnum, hsi.itemdata.itemname,
hsi.itemdata.batchnum, hsi.itemdata.status,
hsi.itemdata.itemtypegroupnum,
hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
hsi.itemdata.datestored, hsi.itemdata.usernum,
hsi.itemdata.deleteusernum,
hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
hsi.itemdata.institution, hsi.itemdata.maxdocrev
from hsi.itemdata
where hsi.itemdata.status + 0 = 0 and
( hsi.itemdata.itemtypenum = 101 or
hsi.itemdata.itemtypenum = 102 or
hsi.itemdata.itemtypenum = 103 or
hsi.itemdata.itemtypenum = 329 )
order by 8 desc
The table has 3,105,135 records in test. (There are 22,905,590 in
production.)
CREATE TABLE [hsi].[itemdata] (
[itemnum] [int] NOT NULL ,
[itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[batchnum] [int] NULL ,
[status] [int] NULL ,
[itemtypegroupnum] [int] NULL ,
[itemtypenum] [int] NULL ,
[itrevnum] [int] NULL ,
[itemdate] [datetime] NULL ,
[datestored] [datetime] NULL ,
[usernum] [int] NULL ,
[deleteusernum] [int] NULL ,
[securityvalue] [int] NULL ,
[doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[institution] [int] NULL ,
[maxdocrev] [int] NULL
) ON [DBSpace2]
GO
CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
[itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
GO
CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
[status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
GO
CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
[itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
[DBSpace2i]
GO
CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
[itemnum]) ON [DBSpace2]
GO
I would have thought it would use one of the existing indexes but it
just does a table scan.
I also tried:
CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
[itemtypenum]) ON [PRIMARY]
GO
But that didn't work either.
Any suggestions?
Thank you,
Toni
*** Sent via Developersdex http://www.developersdex.com ***> where hsi.itemdata.status + 0 = 0
Never do calculation on the column side if you can avoid. Why the + 0? wouldn't it be the same as:
where hsi.itemdata.status = 0
Now, whether an index on that columns can be used depends on the selectivity for the condition.
Also, if you have an index on the itemtypenum column, it might be used (4 times because of the ORs),
but again, whether or not it is used depends on the selectivity.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Toni" <teibner@.allinasql.com> wrote in message news:ul%23akXZYHHA.992@.TK2MSFTNGP02.phx.gbl...
>I have a vendor application running on SQL 2000 sp4. I've got a query
> that isn't using any indexes:
> select hsi.itemdata.itemnum, hsi.itemdata.itemname,
> hsi.itemdata.batchnum, hsi.itemdata.status,
> hsi.itemdata.itemtypegroupnum,
> hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
> hsi.itemdata.datestored, hsi.itemdata.usernum,
> hsi.itemdata.deleteusernum,
> hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
> hsi.itemdata.institution, hsi.itemdata.maxdocrev
> from hsi.itemdata
> where hsi.itemdata.status + 0 = 0 and
> ( hsi.itemdata.itemtypenum = 101 or
> hsi.itemdata.itemtypenum = 102 or
> hsi.itemdata.itemtypenum = 103 or
> hsi.itemdata.itemtypenum = 329 )
> order by 8 desc
> The table has 3,105,135 records in test. (There are 22,905,590 in
> production.)
> CREATE TABLE [hsi].[itemdata] (
> [itemnum] [int] NOT NULL ,
> [itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [batchnum] [int] NULL ,
> [status] [int] NULL ,
> [itemtypegroupnum] [int] NULL ,
> [itemtypenum] [int] NULL ,
> [itrevnum] [int] NULL ,
> [itemdate] [datetime] NULL ,
> [datestored] [datetime] NULL ,
> [usernum] [int] NULL ,
> [deleteusernum] [int] NULL ,
> [securityvalue] [int] NULL ,
> [doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [institution] [int] NULL ,
> [maxdocrev] [int] NULL
> ) ON [DBSpace2]
> GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
> [itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
> [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
> [itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
> [DBSpace2i]
> GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
> [itemnum]) ON [DBSpace2]
> GO
> I would have thought it would use one of the existing indexes but it
> just does a table scan.
> I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
> [itemtypenum]) ON [PRIMARY]
> GO
> But that didn't work either.
> Any suggestions?
> Thank you,
> Toni
> *** Sent via Developersdex http://www.developersdex.com ***|||The information provided raises more questions than it allows answers.
Indexes will only be used if the optimizer calculates that it will be
more efficient than a table scan. This generally depends on how
selective the indexes are, particularly for the values used in the
query.
How selective is itemtypenum? How selective are the specific values
of itemtypenum? What percentage of the table will the numbers
returned by the query represent?
SELECT hsi.itemdata.itemtypenum, count(*) as Rows
FROM hsi.itemdata
WHERE hsi.itemdata.itemtypenum IN (101, 102, 103, 329)
GROUP BY hsi.itemdata.itemtypenum
How selective is the test " hsi.itemdata.status + 0 = 0"? What data
type is hsi.itemdata.status? Why the + 0? It is possible that IF a
relatively small percentage of rows have a status = 0 that including
status in an index might help - but not while the + 0 is there.
Roy Harvey
Beacon Falls, CT
On Thu, 08 Mar 2007 07:24:39 -0800, Toni <teibner@.allinasql.com>
wrote:
>I have a vendor application running on SQL 2000 sp4. I've got a query
>that isn't using any indexes:
>select hsi.itemdata.itemnum, hsi.itemdata.itemname,
>hsi.itemdata.batchnum, hsi.itemdata.status,
>hsi.itemdata.itemtypegroupnum,
>hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
>hsi.itemdata.datestored, hsi.itemdata.usernum,
>hsi.itemdata.deleteusernum,
>hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
>hsi.itemdata.institution, hsi.itemdata.maxdocrev
>from hsi.itemdata
>where hsi.itemdata.status + 0 = 0 and
> ( hsi.itemdata.itemtypenum = 101 or
> hsi.itemdata.itemtypenum = 102 or
> hsi.itemdata.itemtypenum = 103 or
> hsi.itemdata.itemtypenum = 329 )
>order by 8 desc
>The table has 3,105,135 records in test. (There are 22,905,590 in
>production.)
>CREATE TABLE [hsi].[itemdata] (
> [itemnum] [int] NOT NULL ,
> [itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [batchnum] [int] NULL ,
> [status] [int] NULL ,
> [itemtypegroupnum] [int] NULL ,
> [itemtypenum] [int] NULL ,
> [itrevnum] [int] NULL ,
> [itemdate] [datetime] NULL ,
> [datestored] [datetime] NULL ,
> [usernum] [int] NULL ,
> [deleteusernum] [int] NULL ,
> [securityvalue] [int] NULL ,
> [doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>,
> [institution] [int] NULL ,
> [maxdocrev] [int] NULL
>) ON [DBSpace2]
>GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
>[itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
>GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
>[status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
>GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
>[itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
>[DBSpace2i]
>GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
>[itemnum]) ON [DBSpace2]
>GO
>I would have thought it would use one of the existing indexes but it
>just does a table scan.
>I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
>[itemtypenum]) ON [PRIMARY]
>GO
>But that didn't work either.
>Any suggestions?
>Thank you,
>Toni
>*** Sent via Developersdex http://www.developersdex.com ***|||what is the ORDER By 8?
--
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=uk/">http://www.itjobfeed.com">UK IT Jobs</a>
"Toni" <teibner@.allinasql.com> wrote in message
news:ul%23akXZYHHA.992@.TK2MSFTNGP02.phx.gbl...
>I have a vendor application running on SQL 2000 sp4. I've got a query
> that isn't using any indexes:
> select hsi.itemdata.itemnum, hsi.itemdata.itemname,
> hsi.itemdata.batchnum, hsi.itemdata.status,
> hsi.itemdata.itemtypegroupnum,
> hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
> hsi.itemdata.datestored, hsi.itemdata.usernum,
> hsi.itemdata.deleteusernum,
> hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
> hsi.itemdata.institution, hsi.itemdata.maxdocrev
> from hsi.itemdata
> where hsi.itemdata.status + 0 = 0 and
> ( hsi.itemdata.itemtypenum = 101 or
> hsi.itemdata.itemtypenum = 102 or
> hsi.itemdata.itemtypenum = 103 or
> hsi.itemdata.itemtypenum = 329 )
> order by 8 desc
> The table has 3,105,135 records in test. (There are 22,905,590 in
> production.)
> CREATE TABLE [hsi].[itemdata] (
> [itemnum] [int] NOT NULL ,
> [itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [batchnum] [int] NULL ,
> [status] [int] NULL ,
> [itemtypegroupnum] [int] NULL ,
> [itemtypenum] [int] NULL ,
> [itrevnum] [int] NULL ,
> [itemdate] [datetime] NULL ,
> [datestored] [datetime] NULL ,
> [usernum] [int] NULL ,
> [deleteusernum] [int] NULL ,
> [securityvalue] [int] NULL ,
> [doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [institution] [int] NULL ,
> [maxdocrev] [int] NULL
> ) ON [DBSpace2]
> GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
> [itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
> [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
> [itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
> [DBSpace2i]
> GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
> [itemnum]) ON [DBSpace2]
> GO
> I would have thought it would use one of the existing indexes but it
> just does a table scan.
> I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
> [itemtypenum]) ON [PRIMARY]
> GO
> But that didn't work either.
> Any suggestions?
> Thank you,
> Toni
> *** Sent via Developersdex http://www.developersdex.com ***|||On Thu, 8 Mar 2007 17:03:46 -0000, "Jack Vamvas"
<DEL_TO_REPLY@.del.com> wrote:
>what is the ORDER By 8?
That says to order the eighth column in the SELECT list. My
understanding is that this syntax is deprecated in the SQL standard,
and it certainly has no place in production code, but it is a handy
shortcut when doing quick and dirty SQL.
Roy Harvey
Beacon Falls, CT|||On Thu, 08 Mar 2007 12:22:58 -0500, Roy Harvey <roy_harvey@.snet.net>
wrote:
>That says to order the eighth column in the SELECT list.
Make that:
That says to order BY the eighth column in the SELECT list.|||Toni,
it is not a good practice to manipulate columns in the "where" clause. SQL
Server will not try to use statistics for those columnas in case they exists.
> hsi.itemdata.status + 0 = 0
Compare the execution plans between that statement and one using:
...
where
hsi.itemdata.status = 0 and
( hsi.itemdata.itemtypenum = 101 or
hsi.itemdata.itemtypenum = 102 or
hsi.itemdata.itemtypenum = 103 or
hsi.itemdata.itemtypenum = 329 )
...
Also check fragmentation (dbcc showcontig) in that table and, if possible,
create a clustered index. It has some rows for a heap.
AMB
"Toni" wrote:
> I have a vendor application running on SQL 2000 sp4. I've got a query
> that isn't using any indexes:
> select hsi.itemdata.itemnum, hsi.itemdata.itemname,
> hsi.itemdata.batchnum, hsi.itemdata.status,
> hsi.itemdata.itemtypegroupnum,
> hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
> hsi.itemdata.datestored, hsi.itemdata.usernum,
> hsi.itemdata.deleteusernum,
> hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
> hsi.itemdata.institution, hsi.itemdata.maxdocrev
> from hsi.itemdata
> where hsi.itemdata.status + 0 = 0 and
> ( hsi.itemdata.itemtypenum = 101 or
> hsi.itemdata.itemtypenum = 102 or
> hsi.itemdata.itemtypenum = 103 or
> hsi.itemdata.itemtypenum = 329 )
> order by 8 desc
> The table has 3,105,135 records in test. (There are 22,905,590 in
> production.)
> CREATE TABLE [hsi].[itemdata] (
> [itemnum] [int] NOT NULL ,
> [itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [batchnum] [int] NULL ,
> [status] [int] NULL ,
> [itemtypegroupnum] [int] NULL ,
> [itemtypenum] [int] NULL ,
> [itrevnum] [int] NULL ,
> [itemdate] [datetime] NULL ,
> [datestored] [datetime] NULL ,
> [usernum] [int] NULL ,
> [deleteusernum] [int] NULL ,
> [securityvalue] [int] NULL ,
> [doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [institution] [int] NULL ,
> [maxdocrev] [int] NULL
> ) ON [DBSpace2]
> GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
> [itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
> [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
> [itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
> [DBSpace2i]
> GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
> [itemnum]) ON [DBSpace2]
> GO
> I would have thought it would use one of the existing indexes but it
> just does a table scan.
> I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
> [itemtypenum]) ON [PRIMARY]
> GO
> But that didn't work either.
> Any suggestions?
> Thank you,
> Toni
> *** Sent via Developersdex http://www.developersdex.com ***
>|||Jack Vamvas,
> what is the ORDER By 8?
Order by the 8th column in the resultset. Not a good practice to use on
production code.
AMB
"Jack Vamvas" wrote:
> what is the ORDER By 8?
> --
> Jack Vamvas
> ___________________________________
> The latest IT jobs - www.ITjobfeed.com
> <a href="http://links.10026.com/?link=uk/">http://www.itjobfeed.com">UK IT Jobs</a>
>
> "Toni" <teibner@.allinasql.com> wrote in message
> news:ul%23akXZYHHA.992@.TK2MSFTNGP02.phx.gbl...
> >I have a vendor application running on SQL 2000 sp4. I've got a query
> > that isn't using any indexes:
> >
> > select hsi.itemdata.itemnum, hsi.itemdata.itemname,
> > hsi.itemdata.batchnum, hsi.itemdata.status,
> > hsi.itemdata.itemtypegroupnum,
> > hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
> > hsi.itemdata.datestored, hsi.itemdata.usernum,
> > hsi.itemdata.deleteusernum,
> > hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
> > hsi.itemdata.institution, hsi.itemdata.maxdocrev
> > from hsi.itemdata
> > where hsi.itemdata.status + 0 = 0 and
> > ( hsi.itemdata.itemtypenum = 101 or
> > hsi.itemdata.itemtypenum = 102 or
> > hsi.itemdata.itemtypenum = 103 or
> > hsi.itemdata.itemtypenum = 329 )
> > order by 8 desc
> >
> > The table has 3,105,135 records in test. (There are 22,905,590 in
> > production.)
> >
> > CREATE TABLE [hsi].[itemdata] (
> > [itemnum] [int] NOT NULL ,
> > [itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [batchnum] [int] NULL ,
> > [status] [int] NULL ,
> > [itemtypegroupnum] [int] NULL ,
> > [itemtypenum] [int] NULL ,
> > [itrevnum] [int] NULL ,
> > [itemdate] [datetime] NULL ,
> > [datestored] [datetime] NULL ,
> > [usernum] [int] NULL ,
> > [deleteusernum] [int] NULL ,
> > [securityvalue] [int] NULL ,
> > [doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ,
> > [institution] [int] NULL ,
> > [maxdocrev] [int] NULL
> > ) ON [DBSpace2]
> > GO
> >
> > CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
> > [itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> > GO
> >
> > CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
> > [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> > GO
> >
> > CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
> > [itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
> > [DBSpace2i]
> > GO
> >
> > CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
> > [itemnum]) ON [DBSpace2]
> > GO
> >
> > I would have thought it would use one of the existing indexes but it
> > just does a table scan.
> >
> > I also tried:
> > CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
> > [itemtypenum]) ON [PRIMARY]
> > GO
> > But that didn't work either.
> > Any suggestions?
> >
> > Thank you,
> > Toni
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
>
>
that isn't using any indexes:
select hsi.itemdata.itemnum, hsi.itemdata.itemname,
hsi.itemdata.batchnum, hsi.itemdata.status,
hsi.itemdata.itemtypegroupnum,
hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
hsi.itemdata.datestored, hsi.itemdata.usernum,
hsi.itemdata.deleteusernum,
hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
hsi.itemdata.institution, hsi.itemdata.maxdocrev
from hsi.itemdata
where hsi.itemdata.status + 0 = 0 and
( hsi.itemdata.itemtypenum = 101 or
hsi.itemdata.itemtypenum = 102 or
hsi.itemdata.itemtypenum = 103 or
hsi.itemdata.itemtypenum = 329 )
order by 8 desc
The table has 3,105,135 records in test. (There are 22,905,590 in
production.)
CREATE TABLE [hsi].[itemdata] (
[itemnum] [int] NOT NULL ,
[itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[batchnum] [int] NULL ,
[status] [int] NULL ,
[itemtypegroupnum] [int] NULL ,
[itemtypenum] [int] NULL ,
[itrevnum] [int] NULL ,
[itemdate] [datetime] NULL ,
[datestored] [datetime] NULL ,
[usernum] [int] NULL ,
[deleteusernum] [int] NULL ,
[securityvalue] [int] NULL ,
[doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[institution] [int] NULL ,
[maxdocrev] [int] NULL
) ON [DBSpace2]
GO
CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
[itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
GO
CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
[status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
GO
CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
[itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
[DBSpace2i]
GO
CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
[itemnum]) ON [DBSpace2]
GO
I would have thought it would use one of the existing indexes but it
just does a table scan.
I also tried:
CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
[itemtypenum]) ON [PRIMARY]
GO
But that didn't work either.
Any suggestions?
Thank you,
Toni
*** Sent via Developersdex http://www.developersdex.com ***> where hsi.itemdata.status + 0 = 0
Never do calculation on the column side if you can avoid. Why the + 0? wouldn't it be the same as:
where hsi.itemdata.status = 0
Now, whether an index on that columns can be used depends on the selectivity for the condition.
Also, if you have an index on the itemtypenum column, it might be used (4 times because of the ORs),
but again, whether or not it is used depends on the selectivity.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Toni" <teibner@.allinasql.com> wrote in message news:ul%23akXZYHHA.992@.TK2MSFTNGP02.phx.gbl...
>I have a vendor application running on SQL 2000 sp4. I've got a query
> that isn't using any indexes:
> select hsi.itemdata.itemnum, hsi.itemdata.itemname,
> hsi.itemdata.batchnum, hsi.itemdata.status,
> hsi.itemdata.itemtypegroupnum,
> hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
> hsi.itemdata.datestored, hsi.itemdata.usernum,
> hsi.itemdata.deleteusernum,
> hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
> hsi.itemdata.institution, hsi.itemdata.maxdocrev
> from hsi.itemdata
> where hsi.itemdata.status + 0 = 0 and
> ( hsi.itemdata.itemtypenum = 101 or
> hsi.itemdata.itemtypenum = 102 or
> hsi.itemdata.itemtypenum = 103 or
> hsi.itemdata.itemtypenum = 329 )
> order by 8 desc
> The table has 3,105,135 records in test. (There are 22,905,590 in
> production.)
> CREATE TABLE [hsi].[itemdata] (
> [itemnum] [int] NOT NULL ,
> [itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [batchnum] [int] NULL ,
> [status] [int] NULL ,
> [itemtypegroupnum] [int] NULL ,
> [itemtypenum] [int] NULL ,
> [itrevnum] [int] NULL ,
> [itemdate] [datetime] NULL ,
> [datestored] [datetime] NULL ,
> [usernum] [int] NULL ,
> [deleteusernum] [int] NULL ,
> [securityvalue] [int] NULL ,
> [doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [institution] [int] NULL ,
> [maxdocrev] [int] NULL
> ) ON [DBSpace2]
> GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
> [itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
> [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
> [itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
> [DBSpace2i]
> GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
> [itemnum]) ON [DBSpace2]
> GO
> I would have thought it would use one of the existing indexes but it
> just does a table scan.
> I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
> [itemtypenum]) ON [PRIMARY]
> GO
> But that didn't work either.
> Any suggestions?
> Thank you,
> Toni
> *** Sent via Developersdex http://www.developersdex.com ***|||The information provided raises more questions than it allows answers.
Indexes will only be used if the optimizer calculates that it will be
more efficient than a table scan. This generally depends on how
selective the indexes are, particularly for the values used in the
query.
How selective is itemtypenum? How selective are the specific values
of itemtypenum? What percentage of the table will the numbers
returned by the query represent?
SELECT hsi.itemdata.itemtypenum, count(*) as Rows
FROM hsi.itemdata
WHERE hsi.itemdata.itemtypenum IN (101, 102, 103, 329)
GROUP BY hsi.itemdata.itemtypenum
How selective is the test " hsi.itemdata.status + 0 = 0"? What data
type is hsi.itemdata.status? Why the + 0? It is possible that IF a
relatively small percentage of rows have a status = 0 that including
status in an index might help - but not while the + 0 is there.
Roy Harvey
Beacon Falls, CT
On Thu, 08 Mar 2007 07:24:39 -0800, Toni <teibner@.allinasql.com>
wrote:
>I have a vendor application running on SQL 2000 sp4. I've got a query
>that isn't using any indexes:
>select hsi.itemdata.itemnum, hsi.itemdata.itemname,
>hsi.itemdata.batchnum, hsi.itemdata.status,
>hsi.itemdata.itemtypegroupnum,
>hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
>hsi.itemdata.datestored, hsi.itemdata.usernum,
>hsi.itemdata.deleteusernum,
>hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
>hsi.itemdata.institution, hsi.itemdata.maxdocrev
>from hsi.itemdata
>where hsi.itemdata.status + 0 = 0 and
> ( hsi.itemdata.itemtypenum = 101 or
> hsi.itemdata.itemtypenum = 102 or
> hsi.itemdata.itemtypenum = 103 or
> hsi.itemdata.itemtypenum = 329 )
>order by 8 desc
>The table has 3,105,135 records in test. (There are 22,905,590 in
>production.)
>CREATE TABLE [hsi].[itemdata] (
> [itemnum] [int] NOT NULL ,
> [itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [batchnum] [int] NULL ,
> [status] [int] NULL ,
> [itemtypegroupnum] [int] NULL ,
> [itemtypenum] [int] NULL ,
> [itrevnum] [int] NULL ,
> [itemdate] [datetime] NULL ,
> [datestored] [datetime] NULL ,
> [usernum] [int] NULL ,
> [deleteusernum] [int] NULL ,
> [securityvalue] [int] NULL ,
> [doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>,
> [institution] [int] NULL ,
> [maxdocrev] [int] NULL
>) ON [DBSpace2]
>GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
>[itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
>GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
>[status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
>GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
>[itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
>[DBSpace2i]
>GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
>[itemnum]) ON [DBSpace2]
>GO
>I would have thought it would use one of the existing indexes but it
>just does a table scan.
>I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
>[itemtypenum]) ON [PRIMARY]
>GO
>But that didn't work either.
>Any suggestions?
>Thank you,
>Toni
>*** Sent via Developersdex http://www.developersdex.com ***|||what is the ORDER By 8?
--
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=uk/">http://www.itjobfeed.com">UK IT Jobs</a>
"Toni" <teibner@.allinasql.com> wrote in message
news:ul%23akXZYHHA.992@.TK2MSFTNGP02.phx.gbl...
>I have a vendor application running on SQL 2000 sp4. I've got a query
> that isn't using any indexes:
> select hsi.itemdata.itemnum, hsi.itemdata.itemname,
> hsi.itemdata.batchnum, hsi.itemdata.status,
> hsi.itemdata.itemtypegroupnum,
> hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
> hsi.itemdata.datestored, hsi.itemdata.usernum,
> hsi.itemdata.deleteusernum,
> hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
> hsi.itemdata.institution, hsi.itemdata.maxdocrev
> from hsi.itemdata
> where hsi.itemdata.status + 0 = 0 and
> ( hsi.itemdata.itemtypenum = 101 or
> hsi.itemdata.itemtypenum = 102 or
> hsi.itemdata.itemtypenum = 103 or
> hsi.itemdata.itemtypenum = 329 )
> order by 8 desc
> The table has 3,105,135 records in test. (There are 22,905,590 in
> production.)
> CREATE TABLE [hsi].[itemdata] (
> [itemnum] [int] NOT NULL ,
> [itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [batchnum] [int] NULL ,
> [status] [int] NULL ,
> [itemtypegroupnum] [int] NULL ,
> [itemtypenum] [int] NULL ,
> [itrevnum] [int] NULL ,
> [itemdate] [datetime] NULL ,
> [datestored] [datetime] NULL ,
> [usernum] [int] NULL ,
> [deleteusernum] [int] NULL ,
> [securityvalue] [int] NULL ,
> [doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [institution] [int] NULL ,
> [maxdocrev] [int] NULL
> ) ON [DBSpace2]
> GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
> [itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
> [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
> [itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
> [DBSpace2i]
> GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
> [itemnum]) ON [DBSpace2]
> GO
> I would have thought it would use one of the existing indexes but it
> just does a table scan.
> I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
> [itemtypenum]) ON [PRIMARY]
> GO
> But that didn't work either.
> Any suggestions?
> Thank you,
> Toni
> *** Sent via Developersdex http://www.developersdex.com ***|||On Thu, 8 Mar 2007 17:03:46 -0000, "Jack Vamvas"
<DEL_TO_REPLY@.del.com> wrote:
>what is the ORDER By 8?
That says to order the eighth column in the SELECT list. My
understanding is that this syntax is deprecated in the SQL standard,
and it certainly has no place in production code, but it is a handy
shortcut when doing quick and dirty SQL.
Roy Harvey
Beacon Falls, CT|||On Thu, 08 Mar 2007 12:22:58 -0500, Roy Harvey <roy_harvey@.snet.net>
wrote:
>That says to order the eighth column in the SELECT list.
Make that:
That says to order BY the eighth column in the SELECT list.|||Toni,
it is not a good practice to manipulate columns in the "where" clause. SQL
Server will not try to use statistics for those columnas in case they exists.
> hsi.itemdata.status + 0 = 0
Compare the execution plans between that statement and one using:
...
where
hsi.itemdata.status = 0 and
( hsi.itemdata.itemtypenum = 101 or
hsi.itemdata.itemtypenum = 102 or
hsi.itemdata.itemtypenum = 103 or
hsi.itemdata.itemtypenum = 329 )
...
Also check fragmentation (dbcc showcontig) in that table and, if possible,
create a clustered index. It has some rows for a heap.
AMB
"Toni" wrote:
> I have a vendor application running on SQL 2000 sp4. I've got a query
> that isn't using any indexes:
> select hsi.itemdata.itemnum, hsi.itemdata.itemname,
> hsi.itemdata.batchnum, hsi.itemdata.status,
> hsi.itemdata.itemtypegroupnum,
> hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
> hsi.itemdata.datestored, hsi.itemdata.usernum,
> hsi.itemdata.deleteusernum,
> hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
> hsi.itemdata.institution, hsi.itemdata.maxdocrev
> from hsi.itemdata
> where hsi.itemdata.status + 0 = 0 and
> ( hsi.itemdata.itemtypenum = 101 or
> hsi.itemdata.itemtypenum = 102 or
> hsi.itemdata.itemtypenum = 103 or
> hsi.itemdata.itemtypenum = 329 )
> order by 8 desc
> The table has 3,105,135 records in test. (There are 22,905,590 in
> production.)
> CREATE TABLE [hsi].[itemdata] (
> [itemnum] [int] NOT NULL ,
> [itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [batchnum] [int] NULL ,
> [status] [int] NULL ,
> [itemtypegroupnum] [int] NULL ,
> [itemtypenum] [int] NULL ,
> [itrevnum] [int] NULL ,
> [itemdate] [datetime] NULL ,
> [datestored] [datetime] NULL ,
> [usernum] [int] NULL ,
> [deleteusernum] [int] NULL ,
> [securityvalue] [int] NULL ,
> [doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [institution] [int] NULL ,
> [maxdocrev] [int] NULL
> ) ON [DBSpace2]
> GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
> [itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
> [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
> [itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
> [DBSpace2i]
> GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
> [itemnum]) ON [DBSpace2]
> GO
> I would have thought it would use one of the existing indexes but it
> just does a table scan.
> I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
> [itemtypenum]) ON [PRIMARY]
> GO
> But that didn't work either.
> Any suggestions?
> Thank you,
> Toni
> *** Sent via Developersdex http://www.developersdex.com ***
>|||Jack Vamvas,
> what is the ORDER By 8?
Order by the 8th column in the resultset. Not a good practice to use on
production code.
AMB
"Jack Vamvas" wrote:
> what is the ORDER By 8?
> --
> Jack Vamvas
> ___________________________________
> The latest IT jobs - www.ITjobfeed.com
> <a href="http://links.10026.com/?link=uk/">http://www.itjobfeed.com">UK IT Jobs</a>
>
> "Toni" <teibner@.allinasql.com> wrote in message
> news:ul%23akXZYHHA.992@.TK2MSFTNGP02.phx.gbl...
> >I have a vendor application running on SQL 2000 sp4. I've got a query
> > that isn't using any indexes:
> >
> > select hsi.itemdata.itemnum, hsi.itemdata.itemname,
> > hsi.itemdata.batchnum, hsi.itemdata.status,
> > hsi.itemdata.itemtypegroupnum,
> > hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
> > hsi.itemdata.datestored, hsi.itemdata.usernum,
> > hsi.itemdata.deleteusernum,
> > hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
> > hsi.itemdata.institution, hsi.itemdata.maxdocrev
> > from hsi.itemdata
> > where hsi.itemdata.status + 0 = 0 and
> > ( hsi.itemdata.itemtypenum = 101 or
> > hsi.itemdata.itemtypenum = 102 or
> > hsi.itemdata.itemtypenum = 103 or
> > hsi.itemdata.itemtypenum = 329 )
> > order by 8 desc
> >
> > The table has 3,105,135 records in test. (There are 22,905,590 in
> > production.)
> >
> > CREATE TABLE [hsi].[itemdata] (
> > [itemnum] [int] NOT NULL ,
> > [itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [batchnum] [int] NULL ,
> > [status] [int] NULL ,
> > [itemtypegroupnum] [int] NULL ,
> > [itemtypenum] [int] NULL ,
> > [itrevnum] [int] NULL ,
> > [itemdate] [datetime] NULL ,
> > [datestored] [datetime] NULL ,
> > [usernum] [int] NULL ,
> > [deleteusernum] [int] NULL ,
> > [securityvalue] [int] NULL ,
> > [doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ,
> > [institution] [int] NULL ,
> > [maxdocrev] [int] NULL
> > ) ON [DBSpace2]
> > GO
> >
> > CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
> > [itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> > GO
> >
> > CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
> > [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> > GO
> >
> > CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
> > [itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
> > [DBSpace2i]
> > GO
> >
> > CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
> > [itemnum]) ON [DBSpace2]
> > GO
> >
> > I would have thought it would use one of the existing indexes but it
> > just does a table scan.
> >
> > I also tried:
> > CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
> > [itemtypenum]) ON [PRIMARY]
> > GO
> > But that didn't work either.
> > Any suggestions?
> >
> > Thank you,
> > Toni
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
>
>
Query Performance
I have a vendor application running on SQL 2000 sp4. I've got a query
that isn't using any indexes:
select hsi.itemdata.itemnum, hsi.itemdata.itemname,
hsi.itemdata.batchnum, hsi.itemdata.status,
hsi.itemdata.itemtypegroupnum,
hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
hsi.itemdata.datestored, hsi.itemdata.usernum,
hsi.itemdata.deleteusernum,
hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
hsi.itemdata.institution, hsi.itemdata.maxdocrev
from hsi.itemdata
where hsi.itemdata.status + 0 = 0 and
( hsi.itemdata.itemtypenum = 101 or
hsi.itemdata.itemtypenum = 102 or
hsi.itemdata.itemtypenum = 103 or
hsi.itemdata.itemtypenum = 329 )
order by 8 desc
The table has 3,105,135 records in test. (There are 22,905,590 in
production.)
CREATE TABLE [hsi].[itemdata] (
[itemnum] [int] NOT NULL ,
[itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[batchnum] [int] NULL ,
[status] [int] NULL ,
[itemtypegroupnum] [int] NULL ,
[itemtypenum] [int] NULL ,
[itrevnum] [int] NULL ,
[itemdate] [datetime] NULL ,
[datestored] [datetime] NULL ,
[usernum] [int] NULL ,
[deleteusernum] [int] NULL ,
[securityvalue] [int] NULL ,
[doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL
,
[institution] [int] NULL ,
[maxdocrev] [int] NULL
) ON [DBSpace2]
GO
CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum]
,
[itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
GO
CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], &
#91;itemnum],
[status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
GO
CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemn
um],
[itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90
ON
[DBSpace2i]
GO
CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored]
,
[itemnum]) ON [DBSpace2]
GO
I would have thought it would use one of the existing indexes but it
just does a table scan.
I also tried:
CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
[itemtypenum]) ON [PRIMARY]
GO
But that didn't work either.
Any suggestions?
Thank you,
Toni
*** Sent via Developersdex http://www.codecomments.com ***> where hsi.itemdata.status + 0 = 0
Never do calculation on the column side if you can avoid. Why the + 0? would
n't it be the same as:
where hsi.itemdata.status = 0
Now, whether an index on that columns can be used depends on the selectivity
for the condition.
Also, if you have an index on the itemtypenum column, it might be used (4 ti
mes because of the ORs),
but again, whether or not it is used depends on the selectivity.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Toni" <teibner@.allinasql.com> wrote in message news:ul%23akXZYHHA.992@.TK2MSFTNGP02.phx.gbl.
.
>I have a vendor application running on SQL 2000 sp4. I've got a query
> that isn't using any indexes:
> select hsi.itemdata.itemnum, hsi.itemdata.itemname,
> hsi.itemdata.batchnum, hsi.itemdata.status,
> hsi.itemdata.itemtypegroupnum,
> hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
> hsi.itemdata.datestored, hsi.itemdata.usernum,
> hsi.itemdata.deleteusernum,
> hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
> hsi.itemdata.institution, hsi.itemdata.maxdocrev
> from hsi.itemdata
> where hsi.itemdata.status + 0 = 0 and
> ( hsi.itemdata.itemtypenum = 101 or
> hsi.itemdata.itemtypenum = 102 or
> hsi.itemdata.itemtypenum = 103 or
> hsi.itemdata.itemtypenum = 329 )
> order by 8 desc
> The table has 3,105,135 records in test. (There are 22,905,590 in
> production.)
> CREATE TABLE [hsi].[itemdata] (
> [itemnum] [int] NOT NULL ,
> [itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [batchnum] [int] NULL ,
> [status] [int] NULL ,
> [itemtypegroupnum] [int] NULL ,
> [itemtypenum] [int] NULL ,
> [itrevnum] [int] NULL ,
> [itemdate] [datetime] NULL ,
> [datestored] [datetime] NULL ,
> [usernum] [int] NULL ,
> [deleteusernum] [int] NULL ,
> [securityvalue] [int] NULL ,
> [doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> ,
> [institution] [int] NULL ,
> [maxdocrev] [int] NULL
> ) ON [DBSpace2]
> GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenu
m],
> [itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2
i]
> GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum],
[itemnum],
> [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([ite
mnum],
> [itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR =
90 ON
> [DBSpace2i]
> GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestore
d],
> [itemnum]) ON [DBSpace2]
> GO
> I would have thought it would use one of the existing indexes but it
> just does a table scan.
> I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
> [itemtypenum]) ON [PRIMARY]
> GO
> But that didn't work either.
> Any suggestions?
> Thank you,
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***|||The information provided raises more questions than it allows answers.
Indexes will only be used if the optimizer calculates that it will be
more efficient than a table scan. This generally depends on how
selective the indexes are, particularly for the values used in the
query.
How selective is itemtypenum? How selective are the specific values
of itemtypenum? What percentage of the table will the numbers
returned by the query represent?
SELECT hsi.itemdata.itemtypenum, count(*) as Rows
FROM hsi.itemdata
WHERE hsi.itemdata.itemtypenum IN (101, 102, 103, 329)
GROUP BY hsi.itemdata.itemtypenum
How selective is the test " hsi.itemdata.status + 0 = 0"? What data
type is hsi.itemdata.status? Why the + 0? It is possible that IF a
relatively small percentage of rows have a status = 0 that including
status in an index might help - but not while the + 0 is there.
Roy Harvey
Beacon Falls, CT
On Thu, 08 Mar 2007 07:24:39 -0800, Toni <teibner@.allinasql.com>
wrote:
>I have a vendor application running on SQL 2000 sp4. I've got a query
>that isn't using any indexes:
>select hsi.itemdata.itemnum, hsi.itemdata.itemname,
>hsi.itemdata.batchnum, hsi.itemdata.status,
>hsi.itemdata.itemtypegroupnum,
>hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
>hsi.itemdata.datestored, hsi.itemdata.usernum,
>hsi.itemdata.deleteusernum,
>hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
>hsi.itemdata.institution, hsi.itemdata.maxdocrev
>from hsi.itemdata
>where hsi.itemdata.status + 0 = 0 and
> ( hsi.itemdata.itemtypenum = 101 or
> hsi.itemdata.itemtypenum = 102 or
> hsi.itemdata.itemtypenum = 103 or
> hsi.itemdata.itemtypenum = 329 )
>order by 8 desc
>The table has 3,105,135 records in test. (There are 22,905,590 in
>production.)
>CREATE TABLE [hsi].[itemdata] (
> [itemnum] [int] NOT NULL ,
> [itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [batchnum] [int] NULL ,
> [status] [int] NULL ,
> [itemtypegroupnum] [int] NULL ,
> [itemtypenum] [int] NULL ,
> [itrevnum] [int] NULL ,
> [itemdate] [datetime] NULL ,
> [datestored] [datetime] NULL ,
> [usernum] [int] NULL ,
> [deleteusernum] [int] NULL ,
> [securityvalue] [int] NULL ,
> [doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
>,
> [institution] [int] NULL ,
> [maxdocrev] [int] NULL
> ) ON [DBSpace2]
>GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenu
m],
>[itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i
]
>GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum],
[itemnum],
>[status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
>GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([ite
mnum],
>[itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 9
0 ON
>[DBSpace2i]
>GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestore
d],
>[itemnum]) ON [DBSpace2]
>GO
>I would have thought it would use one of the existing indexes but it
>just does a table scan.
>I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
>[itemtypenum]) ON [PRIMARY]
>GO
>But that didn't work either.
>Any suggestions?
>Thank you,
>Toni
>*** Sent via Developersdex http://www.codecomments.com ***|||what is the ORDER By 8?
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Toni" <teibner@.allinasql.com> wrote in message
news:ul%23akXZYHHA.992@.TK2MSFTNGP02.phx.gbl...
>I have a vendor application running on SQL 2000 sp4. I've got a query
> that isn't using any indexes:
> select hsi.itemdata.itemnum, hsi.itemdata.itemname,
> hsi.itemdata.batchnum, hsi.itemdata.status,
> hsi.itemdata.itemtypegroupnum,
> hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
> hsi.itemdata.datestored, hsi.itemdata.usernum,
> hsi.itemdata.deleteusernum,
> hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
> hsi.itemdata.institution, hsi.itemdata.maxdocrev
> from hsi.itemdata
> where hsi.itemdata.status + 0 = 0 and
> ( hsi.itemdata.itemtypenum = 101 or
> hsi.itemdata.itemtypenum = 102 or
> hsi.itemdata.itemtypenum = 103 or
> hsi.itemdata.itemtypenum = 329 )
> order by 8 desc
> The table has 3,105,135 records in test. (There are 22,905,590 in
> production.)
> CREATE TABLE [hsi].[itemdata] (
> [itemnum] [int] NOT NULL ,
> [itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [batchnum] [int] NULL ,
> [status] [int] NULL ,
> [itemtypegroupnum] [int] NULL ,
> [itemtypenum] [int] NULL ,
> [itrevnum] [int] NULL ,
> [itemdate] [datetime] NULL ,
> [datestored] [datetime] NULL ,
> [usernum] [int] NULL ,
> [deleteusernum] [int] NULL ,
> [securityvalue] [int] NULL ,
> [doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> ,
> [institution] [int] NULL ,
> [maxdocrev] [int] NULL
> ) ON [DBSpace2]
> GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenu
m],
> [itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2
i]
> GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum],
[itemnum],
> [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([ite
mnum],
> [itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR =
90 ON
> [DBSpace2i]
> GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestore
d],
> [itemnum]) ON [DBSpace2]
> GO
> I would have thought it would use one of the existing indexes but it
> just does a table scan.
> I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
> [itemtypenum]) ON [PRIMARY]
> GO
> But that didn't work either.
> Any suggestions?
> Thank you,
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***|||On Thu, 8 Mar 2007 17:03:46 -0000, "Jack Vamvas"
<DEL_TO_REPLY@.del.com> wrote:
>what is the ORDER By 8?
That says to order the eighth column in the SELECT list. My
understanding is that this syntax is deprecated in the SQL standard,
and it certainly has no place in production code, but it is a handy
shortcut when doing quick and dirty SQL.
Roy Harvey
Beacon Falls, CT|||On Thu, 08 Mar 2007 12:22:58 -0500, Roy Harvey <roy_harvey@.snet.net>
wrote:
>That says to order the eighth column in the SELECT list.
Make that:
That says to order BY the eighth column in the SELECT list.|||This is code from a vendor application. I don't have any control over
it other than to recommend they make changes. As to the why of some of
the questions, I can answer some of them.
I don't know why the vendor is doing the calculation of
hsi.itemdata.status + 0 = 0, unfortunately.
I guess it's not real selective at all. (There are only 613 different
values in the table in test.) For this specific query, this is what is
in the table:
itemtypenum rows
101 127282
102 100347
103 6901
329 387
I apologize for my lack of knowledge on this. I'm not very experienced
with query tuning.
It sounds like the whole query needs to be rewritten in order to be
efficient and make use of any indexes that might exist on the table. Is
that correct?
I appreciate all the responses!
Thank you so much
Toni
*** Sent via Developersdex http://www.codecomments.com ***|||Since there are 3.1 million rows in the table, and the query returns
almost 0.3 million, the selectivity on itemtypenum is roughly ten
percent. That really isn't selective enough for an index on
itemtypenum to help this query. Had status been selective, and the
query written without the silly + 0, an index on (status, itemtypenum)
might have been of some use. As it stands I do not see any
oportunities for improvement.
Roy Harvey
Beacon Falls, CT
On Thu, 08 Mar 2007 10:02:41 -0800, Toni <teibner@.allinasql.com>
wrote:
>This is code from a vendor application. I don't have any control over
>it other than to recommend they make changes. As to the why of some of
>the questions, I can answer some of them.
>I don't know why the vendor is doing the calculation of
>hsi.itemdata.status + 0 = 0, unfortunately.
>I guess it's not real selective at all. (There are only 613 different
>values in the table in test.) For this specific query, this is what is
>in the table:
>itemtypenum rows
>101 127282
>102 100347
>103 6901
>329 387
>I apologize for my lack of knowledge on this. I'm not very experienced
>with query tuning.
>It sounds like the whole query needs to be rewritten in order to be
>efficient and make use of any indexes that might exist on the table. Is
>that correct?
>I appreciate all the responses!
>Thank you so much
>Toni
>*** Sent via Developersdex http://www.codecomments.com ***|||Toni,
it is not a good practice to manipulate columns in the "where" clause. SQL
Server will not try to use statistics for those columnas in case they exists
.
> hsi.itemdata.status + 0 = 0
Compare the execution plans between that statement and one using:
...
where
hsi.itemdata.status = 0 and
( hsi.itemdata.itemtypenum = 101 or
hsi.itemdata.itemtypenum = 102 or
hsi.itemdata.itemtypenum = 103 or
hsi.itemdata.itemtypenum = 329 )
...
Also check fragmentation (dbcc showcontig) in that table and, if possible,
create a clustered index. It has some rows for a heap.
AMB
"Toni" wrote:
> I have a vendor application running on SQL 2000 sp4. I've got a query
> that isn't using any indexes:
> select hsi.itemdata.itemnum, hsi.itemdata.itemname,
> hsi.itemdata.batchnum, hsi.itemdata.status,
> hsi.itemdata.itemtypegroupnum,
> hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
> hsi.itemdata.datestored, hsi.itemdata.usernum,
> hsi.itemdata.deleteusernum,
> hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
> hsi.itemdata.institution, hsi.itemdata.maxdocrev
> from hsi.itemdata
> where hsi.itemdata.status + 0 = 0 and
> ( hsi.itemdata.itemtypenum = 101 or
> hsi.itemdata.itemtypenum = 102 or
> hsi.itemdata.itemtypenum = 103 or
> hsi.itemdata.itemtypenum = 329 )
> order by 8 desc
> The table has 3,105,135 records in test. (There are 22,905,590 in
> production.)
> CREATE TABLE [hsi].[itemdata] (
> [itemnum] [int] NOT NULL ,
> [itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [batchnum] [int] NULL ,
> [status] [int] NULL ,
> [itemtypegroupnum] [int] NULL ,
> [itemtypenum] [int] NULL ,
> [itrevnum] [int] NULL ,
> [itemdate] [datetime] NULL ,
> [datestored] [datetime] NULL ,
> [usernum] [int] NULL ,
> [deleteusernum] [int] NULL ,
> [securityvalue] [int] NULL ,
> [doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> ,
> [institution] [int] NULL ,
> [maxdocrev] [int] NULL
> ) ON [DBSpace2]
> GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypen
um],
> [itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2
i]
> GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum]
, [itemnum],
> [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([it
emnum],
> [itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR =
90 ON
> [DBSpace2i]
> GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestor
ed],
> [itemnum]) ON [DBSpace2]
> GO
> I would have thought it would use one of the existing indexes but it
> just does a table scan.
> I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
> [itemtypenum]) ON [PRIMARY]
> GO
> But that didn't work either.
> Any suggestions?
> Thank you,
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***
>|||Jack Vamvas,
> what is the ORDER By 8?
Order by the 8th column in the resultset. Not a good practice to use on
production code.
AMB
"Jack Vamvas" wrote:
> what is the ORDER By 8?
> --
> Jack Vamvas
> ___________________________________
> The latest IT jobs - www.ITjobfeed.com
> <a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
>
> "Toni" <teibner@.allinasql.com> wrote in message
> news:ul%23akXZYHHA.992@.TK2MSFTNGP02.phx.gbl...
>
>
that isn't using any indexes:
select hsi.itemdata.itemnum, hsi.itemdata.itemname,
hsi.itemdata.batchnum, hsi.itemdata.status,
hsi.itemdata.itemtypegroupnum,
hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
hsi.itemdata.datestored, hsi.itemdata.usernum,
hsi.itemdata.deleteusernum,
hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
hsi.itemdata.institution, hsi.itemdata.maxdocrev
from hsi.itemdata
where hsi.itemdata.status + 0 = 0 and
( hsi.itemdata.itemtypenum = 101 or
hsi.itemdata.itemtypenum = 102 or
hsi.itemdata.itemtypenum = 103 or
hsi.itemdata.itemtypenum = 329 )
order by 8 desc
The table has 3,105,135 records in test. (There are 22,905,590 in
production.)
CREATE TABLE [hsi].[itemdata] (
[itemnum] [int] NOT NULL ,
[itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[batchnum] [int] NULL ,
[status] [int] NULL ,
[itemtypegroupnum] [int] NULL ,
[itemtypenum] [int] NULL ,
[itrevnum] [int] NULL ,
[itemdate] [datetime] NULL ,
[datestored] [datetime] NULL ,
[usernum] [int] NULL ,
[deleteusernum] [int] NULL ,
[securityvalue] [int] NULL ,
[doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL
,
[institution] [int] NULL ,
[maxdocrev] [int] NULL
) ON [DBSpace2]
GO
CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum]
,
[itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
GO
CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], &
#91;itemnum],
[status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
GO
CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemn
um],
[itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90
ON
[DBSpace2i]
GO
CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored]
,
[itemnum]) ON [DBSpace2]
GO
I would have thought it would use one of the existing indexes but it
just does a table scan.
I also tried:
CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
[itemtypenum]) ON [PRIMARY]
GO
But that didn't work either.
Any suggestions?
Thank you,
Toni
*** Sent via Developersdex http://www.codecomments.com ***> where hsi.itemdata.status + 0 = 0
Never do calculation on the column side if you can avoid. Why the + 0? would
n't it be the same as:
where hsi.itemdata.status = 0
Now, whether an index on that columns can be used depends on the selectivity
for the condition.
Also, if you have an index on the itemtypenum column, it might be used (4 ti
mes because of the ORs),
but again, whether or not it is used depends on the selectivity.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Toni" <teibner@.allinasql.com> wrote in message news:ul%23akXZYHHA.992@.TK2MSFTNGP02.phx.gbl.
.
>I have a vendor application running on SQL 2000 sp4. I've got a query
> that isn't using any indexes:
> select hsi.itemdata.itemnum, hsi.itemdata.itemname,
> hsi.itemdata.batchnum, hsi.itemdata.status,
> hsi.itemdata.itemtypegroupnum,
> hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
> hsi.itemdata.datestored, hsi.itemdata.usernum,
> hsi.itemdata.deleteusernum,
> hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
> hsi.itemdata.institution, hsi.itemdata.maxdocrev
> from hsi.itemdata
> where hsi.itemdata.status + 0 = 0 and
> ( hsi.itemdata.itemtypenum = 101 or
> hsi.itemdata.itemtypenum = 102 or
> hsi.itemdata.itemtypenum = 103 or
> hsi.itemdata.itemtypenum = 329 )
> order by 8 desc
> The table has 3,105,135 records in test. (There are 22,905,590 in
> production.)
> CREATE TABLE [hsi].[itemdata] (
> [itemnum] [int] NOT NULL ,
> [itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [batchnum] [int] NULL ,
> [status] [int] NULL ,
> [itemtypegroupnum] [int] NULL ,
> [itemtypenum] [int] NULL ,
> [itrevnum] [int] NULL ,
> [itemdate] [datetime] NULL ,
> [datestored] [datetime] NULL ,
> [usernum] [int] NULL ,
> [deleteusernum] [int] NULL ,
> [securityvalue] [int] NULL ,
> [doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> ,
> [institution] [int] NULL ,
> [maxdocrev] [int] NULL
> ) ON [DBSpace2]
> GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenu
m],
> [itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2
i]
> GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum],
[itemnum],
> [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([ite
mnum],
> [itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR =
90 ON
> [DBSpace2i]
> GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestore
d],
> [itemnum]) ON [DBSpace2]
> GO
> I would have thought it would use one of the existing indexes but it
> just does a table scan.
> I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
> [itemtypenum]) ON [PRIMARY]
> GO
> But that didn't work either.
> Any suggestions?
> Thank you,
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***|||The information provided raises more questions than it allows answers.
Indexes will only be used if the optimizer calculates that it will be
more efficient than a table scan. This generally depends on how
selective the indexes are, particularly for the values used in the
query.
How selective is itemtypenum? How selective are the specific values
of itemtypenum? What percentage of the table will the numbers
returned by the query represent?
SELECT hsi.itemdata.itemtypenum, count(*) as Rows
FROM hsi.itemdata
WHERE hsi.itemdata.itemtypenum IN (101, 102, 103, 329)
GROUP BY hsi.itemdata.itemtypenum
How selective is the test " hsi.itemdata.status + 0 = 0"? What data
type is hsi.itemdata.status? Why the + 0? It is possible that IF a
relatively small percentage of rows have a status = 0 that including
status in an index might help - but not while the + 0 is there.
Roy Harvey
Beacon Falls, CT
On Thu, 08 Mar 2007 07:24:39 -0800, Toni <teibner@.allinasql.com>
wrote:
>I have a vendor application running on SQL 2000 sp4. I've got a query
>that isn't using any indexes:
>select hsi.itemdata.itemnum, hsi.itemdata.itemname,
>hsi.itemdata.batchnum, hsi.itemdata.status,
>hsi.itemdata.itemtypegroupnum,
>hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
>hsi.itemdata.datestored, hsi.itemdata.usernum,
>hsi.itemdata.deleteusernum,
>hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
>hsi.itemdata.institution, hsi.itemdata.maxdocrev
>from hsi.itemdata
>where hsi.itemdata.status + 0 = 0 and
> ( hsi.itemdata.itemtypenum = 101 or
> hsi.itemdata.itemtypenum = 102 or
> hsi.itemdata.itemtypenum = 103 or
> hsi.itemdata.itemtypenum = 329 )
>order by 8 desc
>The table has 3,105,135 records in test. (There are 22,905,590 in
>production.)
>CREATE TABLE [hsi].[itemdata] (
> [itemnum] [int] NOT NULL ,
> [itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [batchnum] [int] NULL ,
> [status] [int] NULL ,
> [itemtypegroupnum] [int] NULL ,
> [itemtypenum] [int] NULL ,
> [itrevnum] [int] NULL ,
> [itemdate] [datetime] NULL ,
> [datestored] [datetime] NULL ,
> [usernum] [int] NULL ,
> [deleteusernum] [int] NULL ,
> [securityvalue] [int] NULL ,
> [doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
>,
> [institution] [int] NULL ,
> [maxdocrev] [int] NULL
> ) ON [DBSpace2]
>GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenu
m],
>[itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i
]
>GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum],
[itemnum],
>[status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
>GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([ite
mnum],
>[itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 9
0 ON
>[DBSpace2i]
>GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestore
d],
>[itemnum]) ON [DBSpace2]
>GO
>I would have thought it would use one of the existing indexes but it
>just does a table scan.
>I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
>[itemtypenum]) ON [PRIMARY]
>GO
>But that didn't work either.
>Any suggestions?
>Thank you,
>Toni
>*** Sent via Developersdex http://www.codecomments.com ***|||what is the ORDER By 8?
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Toni" <teibner@.allinasql.com> wrote in message
news:ul%23akXZYHHA.992@.TK2MSFTNGP02.phx.gbl...
>I have a vendor application running on SQL 2000 sp4. I've got a query
> that isn't using any indexes:
> select hsi.itemdata.itemnum, hsi.itemdata.itemname,
> hsi.itemdata.batchnum, hsi.itemdata.status,
> hsi.itemdata.itemtypegroupnum,
> hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
> hsi.itemdata.datestored, hsi.itemdata.usernum,
> hsi.itemdata.deleteusernum,
> hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
> hsi.itemdata.institution, hsi.itemdata.maxdocrev
> from hsi.itemdata
> where hsi.itemdata.status + 0 = 0 and
> ( hsi.itemdata.itemtypenum = 101 or
> hsi.itemdata.itemtypenum = 102 or
> hsi.itemdata.itemtypenum = 103 or
> hsi.itemdata.itemtypenum = 329 )
> order by 8 desc
> The table has 3,105,135 records in test. (There are 22,905,590 in
> production.)
> CREATE TABLE [hsi].[itemdata] (
> [itemnum] [int] NOT NULL ,
> [itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [batchnum] [int] NULL ,
> [status] [int] NULL ,
> [itemtypegroupnum] [int] NULL ,
> [itemtypenum] [int] NULL ,
> [itrevnum] [int] NULL ,
> [itemdate] [datetime] NULL ,
> [datestored] [datetime] NULL ,
> [usernum] [int] NULL ,
> [deleteusernum] [int] NULL ,
> [securityvalue] [int] NULL ,
> [doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> ,
> [institution] [int] NULL ,
> [maxdocrev] [int] NULL
> ) ON [DBSpace2]
> GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenu
m],
> [itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2
i]
> GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum],
[itemnum],
> [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([ite
mnum],
> [itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR =
90 ON
> [DBSpace2i]
> GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestore
d],
> [itemnum]) ON [DBSpace2]
> GO
> I would have thought it would use one of the existing indexes but it
> just does a table scan.
> I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
> [itemtypenum]) ON [PRIMARY]
> GO
> But that didn't work either.
> Any suggestions?
> Thank you,
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***|||On Thu, 8 Mar 2007 17:03:46 -0000, "Jack Vamvas"
<DEL_TO_REPLY@.del.com> wrote:
>what is the ORDER By 8?
That says to order the eighth column in the SELECT list. My
understanding is that this syntax is deprecated in the SQL standard,
and it certainly has no place in production code, but it is a handy
shortcut when doing quick and dirty SQL.
Roy Harvey
Beacon Falls, CT|||On Thu, 08 Mar 2007 12:22:58 -0500, Roy Harvey <roy_harvey@.snet.net>
wrote:
>That says to order the eighth column in the SELECT list.
Make that:
That says to order BY the eighth column in the SELECT list.|||This is code from a vendor application. I don't have any control over
it other than to recommend they make changes. As to the why of some of
the questions, I can answer some of them.
I don't know why the vendor is doing the calculation of
hsi.itemdata.status + 0 = 0, unfortunately.
I guess it's not real selective at all. (There are only 613 different
values in the table in test.) For this specific query, this is what is
in the table:
itemtypenum rows
101 127282
102 100347
103 6901
329 387
I apologize for my lack of knowledge on this. I'm not very experienced
with query tuning.
It sounds like the whole query needs to be rewritten in order to be
efficient and make use of any indexes that might exist on the table. Is
that correct?
I appreciate all the responses!
Thank you so much
Toni
*** Sent via Developersdex http://www.codecomments.com ***|||Since there are 3.1 million rows in the table, and the query returns
almost 0.3 million, the selectivity on itemtypenum is roughly ten
percent. That really isn't selective enough for an index on
itemtypenum to help this query. Had status been selective, and the
query written without the silly + 0, an index on (status, itemtypenum)
might have been of some use. As it stands I do not see any
oportunities for improvement.
Roy Harvey
Beacon Falls, CT
On Thu, 08 Mar 2007 10:02:41 -0800, Toni <teibner@.allinasql.com>
wrote:
>This is code from a vendor application. I don't have any control over
>it other than to recommend they make changes. As to the why of some of
>the questions, I can answer some of them.
>I don't know why the vendor is doing the calculation of
>hsi.itemdata.status + 0 = 0, unfortunately.
>I guess it's not real selective at all. (There are only 613 different
>values in the table in test.) For this specific query, this is what is
>in the table:
>itemtypenum rows
>101 127282
>102 100347
>103 6901
>329 387
>I apologize for my lack of knowledge on this. I'm not very experienced
>with query tuning.
>It sounds like the whole query needs to be rewritten in order to be
>efficient and make use of any indexes that might exist on the table. Is
>that correct?
>I appreciate all the responses!
>Thank you so much
>Toni
>*** Sent via Developersdex http://www.codecomments.com ***|||Toni,
it is not a good practice to manipulate columns in the "where" clause. SQL
Server will not try to use statistics for those columnas in case they exists
.
> hsi.itemdata.status + 0 = 0
Compare the execution plans between that statement and one using:
...
where
hsi.itemdata.status = 0 and
( hsi.itemdata.itemtypenum = 101 or
hsi.itemdata.itemtypenum = 102 or
hsi.itemdata.itemtypenum = 103 or
hsi.itemdata.itemtypenum = 329 )
...
Also check fragmentation (dbcc showcontig) in that table and, if possible,
create a clustered index. It has some rows for a heap.
AMB
"Toni" wrote:
> I have a vendor application running on SQL 2000 sp4. I've got a query
> that isn't using any indexes:
> select hsi.itemdata.itemnum, hsi.itemdata.itemname,
> hsi.itemdata.batchnum, hsi.itemdata.status,
> hsi.itemdata.itemtypegroupnum,
> hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
> hsi.itemdata.datestored, hsi.itemdata.usernum,
> hsi.itemdata.deleteusernum,
> hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
> hsi.itemdata.institution, hsi.itemdata.maxdocrev
> from hsi.itemdata
> where hsi.itemdata.status + 0 = 0 and
> ( hsi.itemdata.itemtypenum = 101 or
> hsi.itemdata.itemtypenum = 102 or
> hsi.itemdata.itemtypenum = 103 or
> hsi.itemdata.itemtypenum = 329 )
> order by 8 desc
> The table has 3,105,135 records in test. (There are 22,905,590 in
> production.)
> CREATE TABLE [hsi].[itemdata] (
> [itemnum] [int] NOT NULL ,
> [itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [batchnum] [int] NULL ,
> [status] [int] NULL ,
> [itemtypegroupnum] [int] NULL ,
> [itemtypenum] [int] NULL ,
> [itrevnum] [int] NULL ,
> [itemdate] [datetime] NULL ,
> [datestored] [datetime] NULL ,
> [usernum] [int] NULL ,
> [deleteusernum] [int] NULL ,
> [securityvalue] [int] NULL ,
> [doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> ,
> [institution] [int] NULL ,
> [maxdocrev] [int] NULL
> ) ON [DBSpace2]
> GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypen
um],
> [itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2
i]
> GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum]
, [itemnum],
> [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([it
emnum],
> [itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR =
90 ON
> [DBSpace2i]
> GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestor
ed],
> [itemnum]) ON [DBSpace2]
> GO
> I would have thought it would use one of the existing indexes but it
> just does a table scan.
> I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
> [itemtypenum]) ON [PRIMARY]
> GO
> But that didn't work either.
> Any suggestions?
> Thank you,
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***
>|||Jack Vamvas,
> what is the ORDER By 8?
Order by the 8th column in the resultset. Not a good practice to use on
production code.
AMB
"Jack Vamvas" wrote:
> what is the ORDER By 8?
> --
> Jack Vamvas
> ___________________________________
> The latest IT jobs - www.ITjobfeed.com
> <a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
>
> "Toni" <teibner@.allinasql.com> wrote in message
> news:ul%23akXZYHHA.992@.TK2MSFTNGP02.phx.gbl...
>
>
Query Peformance
Hi all,
There is an application that access the SQL Server.
Several queries are with problems of performance.
I cannot change these queries ′cause the application
was made by other IT company.
[code]
SELECT * FROM SE1010 SE1
WHERE E1_FILIAL = ' '
AND D_E_L_E_T_ <> '*'
AND E1_CLIENTE BETWEEN ' ' AND 'ZZZZZZ'
AND E1_PREFIXO BETWEEN ' ' AND 'ZZZ'
AND E1_NUM BETWEEN ' ' AND 'ZZZZZZ'
AND E1_PORTADO BETWEEN ' ' AND 'ZZZ'
AND E1_VENCREA BETWEEN '20010101' AND '20070228'
AND (E1_MULTNAT = '1' OR (E1_NATUREZ BETWEEN ' ' AND 'ZZZZZZZZZZ'))
AND E1_EMISSAO BETWEEN '20010101' AND '20070228'
AND E1_LOJA BETWEEN ' ' AND 'ZZ'
AND E1_EMISSAO <= '20070301'
AND ((E1_EMIS1 BETWEEN '20060101' AND '20070228')
OR E1_EMISSAO BETWEEN '20060101' AND '20070228')
ORDER BY E1_FILIAL,E1_PREFIXO,E1_NUM,E1_PARCELA,E1_TIPO
The execution plan is:
|--Sort(ORDER BY:([SE1].[E1_PREFIXO] ASC, [SE1].[E1_NUM] ASC,
[SE1].[E1_PARCELA] ASC, [SE1].[E1_TIPO] ASC))
|--Filter(WHERE:((((([SE1].[E1_EMISSAO]>='20010101'
AND [SE1].[E1_EMISSAO]<='20070228') AND [SE1].[E1_LOJA]>=' ')
AND [SE1].[E1_LOJA]<='ZZ') AND [SE1].[E1_EMISSAO]<='20070301')
AND (([SE1].[E1_EMIS1]>='20060101' AND
[SE1].[E1_EMIS1]<='20070228') OR
|--Clustered Index Scan(OBJECT:([DADOSADV].[dbo].[SE1010].[SE1010_PK]
AS [SE1]), WHERE:(((((((((((([SE1].[E1_FILIAL]=' '
AND [SE1].[D_E_L_E_T_]<>'*') AND [SE1].[E1_CLIENTE]>=' ')
AND [SE1].[E1_CLIENTE]<='ZZZZZZ') AND [SE1].[E1_PREFIXO]>=''
[/code]
I′ve created index for the columns that are used by where clause
and I′ve performed the DBCC INDEXDEFRAG and DBREINDEX, but after I′ve performed
theses functions, the performance fell.
What can I do to improve query performance?
thanks!!!!
Hi Tadeu,
are you using datetime or smalldatetime?
If you are using datetime:
With(Index=IndexNameHere) WHERE ....
date >= '20060101' AND date < '20070228'
BTW, for more information read Using Indexed Computed Columns to Improve Performance.
tosc
|||How many rows are in the SE1010 table, and how many rows are eventually returned by the query that you provided?
Chris
|||Hi all,tosc,
Well, the problem is: I cannot change the query ou the tables, the columns, nothing :-(.
The table not contains datetime or smalldatetime columns. Only varchar and float.
Chris Howarth,
The table SE1010 contains 92387 rows and the query returns 9537 rows.
thanks all!!!!|||
SQL Server will almost always perform a table or clustered index scan in this scenario.
As you can't amend the query you'll probably find that there is no little you can do to improve the performance other than moving your database files to faster disks or upgrading your server. You need to monitor the CPU, disks and RAM to see where the bottleneck lies to see what which aspect you need to improve.
Chris
|||Hi,The query is using clustered index scan for this case.
I monitored the CPU, memory, I/O, Network and the SQL Server for 2 days and the
only problem is the disk, where only exists one disk on the server.
Now I will try to use the Index Tuning Wizard and search for index fagmentation.
thank you very much.
[]′s|||only more one question:
Can I to perform DBCC INDEXDEFRAG or DBCC DBREINDEX to internal
tables of SQL Server?
The índices of SQL Server are fragmented.
thanks!!!!|||
It's worth a try before you resign yourself to an upgrade.
Check out this link for more info:
http://www.sql-server-performance.com/rebuilding_indexes.asp
Chris
|||thank you very much!!!!Monday, March 12, 2012
Query optimizer issue
Hi,
I have the following problem:
When I call a stored procedure from a COM+ application I get a different query plan than the one I get when calling the same stored procedure within Query Analyzer. The interesting side effect of it is that the COM+ application runs faster as I expected,
since the query takes much longer to execute in Query Analyzer.
I have noticed that the query plan differs, when I call my SP from COM+. Does anybody know why? Are there any magic/secrets flags, that COM+ sets?
One of the cases is the fact that when I run the query in QA, the plan contains a lookup WITH PREFETCH, which takes longer than without prefetch (COM+) case.
I don't know what can be the factor that causes two different query plans for the same stored procedure...
On the other hand, I use an index hint in one of my queries and I noticed that if I don't use the hint, when running in QA the optimizer will use the wrong index, but when the same SP is called from my COM+ app, the optimizer chooses the right index even
if I don't use the hint.
Does anyone have any ideas, thoughts, hints on this?
I'm really confused... :-)
Thanks in advance,
Florin
A lot can depend on how you call it and how the parameters are interpreted.
This blurb from Bart at MS does a good job of explaining how things like
this can occur.
The reason for the performance difference stems from a feature called
"parameter sniffing". Consider a stored proc defined as follows:
CREATE PROC proc1 @.p1 int AS
SELECT * FROM table1 WHERE c1 = @.p1
GO
Keep in mind that the server has to compile a complete execution plan for
the proc before the proc begins to execute. In 6.5, at compile time SQL
didn't know what the value of @.p1 was, so it had to make a lot of guesses
when compiling a plan. Suppose all of the actual parameter values for
"@.p1 int" that a user ever passed into this stored proc were unique
integers that were greater than 0, but suppose 40% of the [c1] values in
[table1] were, in fact, 0. SQL would use the average density of the
column to estimate the number of rows that this predicate would return;
this would be an overestimate, and SQL would might choose a table scan
over an index seek based on the rowcount estimates. A table scan would
be the best plan if the parameter value was 0, but unfortunately it
happens that users will never or rarely pass @.p1=0, so performance of the
stored proc for more typical parameters suffers.
In SQL 7.0 or 2000, suppose you executed this proc for the first time
(when the sp plan is not in cache) with the command "EXEC proc1 @.p1 =
10". Parameter sniffing allows SQL to insert the known value of
parameter @.p1 into the query at compile time before a plan for the query
is generated. Because SQL knows that the value of @.p1 is not 0, it can
compile a plan that is tailored to the class of parameters that is
actually passed into the proc, so for example it might select an index
seek instead of a table scan based on the smaller estimated rowcount --
this is a good thing if most of the time 0 is not the value passed as
@.p1. Generally speaking, this feature allows more efficient stored proc
execution plans, but a key requirement for everything to work as expected
is that the parameter values used for compilation be "typical".
In your case, the problem is that you have default NULL values for your
parameters ("@.Today DATETIME = NULL, ...") that are not typical because
the parameter values are changed inside the stored proc before they are
used -- as a result NULL will never actually be used to search the
column. If the first execution of this stored proc doesn't pass in an
explicit value for the @.Today parameter, SQL believes that its value will
be NULL. When SQL compiles the plan for this sp it substitutes NULL for
each occurrence of @.Today that is embedded within a query.
Unfortunately, after execution begins the first thing the stored proc
does is change @.Today to a non-NULL value if it is found to be NULL, but
unfortunately SQL doesn't know about this at compile time. Because NULL
is a very atypical parameter value, the plan that SQL generates may not
be a good one for the new value of the parameter that is assigned at
execution time.
So, the bottom line is that if you assign defaults to your sp parameters
and later use those same parameters in a query, the defaults should be
"typical" because they will be used during plan generation. If you must
use defaults and business logic dictates that they be atypical (as may be
the case here if app modifications are not an option), there are two
possible solutions if you determine that the substitution of atypical
parameter values is causing bad plans:
1. "Disable" parameter sniffing by using local DECLARE'd variables that
you SET equal to the parameters inside the stored proc, and use the local
variables instead of the offending parameters in the queries. This is the
solution that you found yourself. SQL can't use parameter sniffing in
this case so it must make some guesses, but in this case the guess based
on average column density is better than the plan based on a specific but
"wrong" parameter value (NULL).
2. Nest the affected queries somehow so that they run within a different
context that will require a distinct execution plan. There are several
possibilities here. for example:
a. Put the affected queries in a different "child" stored proc. If
you execute that stored proc within this one *after* the parameter @.Today
has been changed to its final value, parameter sniffing will suddenly
become your friend because the value SQL uses to compile the queries
inside the child stored proc is the actual value that will be used in the
query.
b. Use sp_executesql to execute the affected queries. The plan won't
be generated until the sp_executesql stmt actually runs, which is of
course after the parameter values have been changed.
c. Use dynamic SQL ("EXEC (@.sql)") to execute the affected queries.
An equivalent approach would be to put the query in a child stored proc
just like 2.a, but execute it within the parent proc with EXEC WITH
RECOMPILE.
Option #1 seems to have worked well for you in this case, although
sometimes one of the options in #2 is a preferable choice. Here are some
guidelines, although when you're dealing with something as complicated as
the query optimizer experimentation is often the best approach <g>:
- If you have only one "class" (defined as values that have similar
density in the table) of actual parameter value that is used within a
query (even if there are other classes of data in the base table that are
never or rarely searched on), 2.a. or 2.b is probably the best option.
This is because these options permit the actual parameter values to be
used during compilation which should result in the most efficient query
plan for that class of parameter.
- If you have multiple "classes" of parameter value (for example, for
the column being searched, half the table data is NULL, the other half
are unique integers, and you may do searches on either class), 2.c can be
effective. The downside is that a new plan for the query must be
compiled on each execution, but the upside is that the plan will always
be tailored to the parameter value being used for that particular
execution. This is best when there is no single execution plan that
provides acceptable execution time for all classes of parameters.
HTH -
Bart
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
Andrew J. Kelly SQL MVP
"fmicle" <fmicle@.hotmail.com> wrote in message
news:5BC62C0B-98E3-4415-A6C8-48A704CFD80B@.microsoft.com...
> Hi,
> I have the following problem:
> When I call a stored procedure from a COM+ application I get a different
query plan than the one I get when calling the same stored procedure within
Query Analyzer. The interesting side effect of it is that the COM+
application runs faster as I expected, since the query takes much longer to
execute in Query Analyzer.
> I have noticed that the query plan differs, when I call my SP from COM+.
Does anybody know why? Are there any magic/secrets flags, that COM+ sets?
> One of the cases is the fact that when I run the query in QA, the plan
contains a lookup WITH PREFETCH, which takes longer than without prefetch
(COM+) case.
> I don't know what can be the factor that causes two different query plans
for the same stored procedure...
> On the other hand, I use an index hint in one of my queries and I noticed
that if I don't use the hint, when running in QA the optimizer will use the
wrong index, but when the same SP is called from my COM+ app, the optimizer
chooses the right index even if I don't use the hint.
> Does anyone have any ideas, thoughts, hints on this?
> I'm really confused... :-)
> Thanks in advance,
> Florin
I have the following problem:
When I call a stored procedure from a COM+ application I get a different query plan than the one I get when calling the same stored procedure within Query Analyzer. The interesting side effect of it is that the COM+ application runs faster as I expected,
since the query takes much longer to execute in Query Analyzer.
I have noticed that the query plan differs, when I call my SP from COM+. Does anybody know why? Are there any magic/secrets flags, that COM+ sets?
One of the cases is the fact that when I run the query in QA, the plan contains a lookup WITH PREFETCH, which takes longer than without prefetch (COM+) case.
I don't know what can be the factor that causes two different query plans for the same stored procedure...
On the other hand, I use an index hint in one of my queries and I noticed that if I don't use the hint, when running in QA the optimizer will use the wrong index, but when the same SP is called from my COM+ app, the optimizer chooses the right index even
if I don't use the hint.
Does anyone have any ideas, thoughts, hints on this?
I'm really confused... :-)
Thanks in advance,
Florin
A lot can depend on how you call it and how the parameters are interpreted.
This blurb from Bart at MS does a good job of explaining how things like
this can occur.
The reason for the performance difference stems from a feature called
"parameter sniffing". Consider a stored proc defined as follows:
CREATE PROC proc1 @.p1 int AS
SELECT * FROM table1 WHERE c1 = @.p1
GO
Keep in mind that the server has to compile a complete execution plan for
the proc before the proc begins to execute. In 6.5, at compile time SQL
didn't know what the value of @.p1 was, so it had to make a lot of guesses
when compiling a plan. Suppose all of the actual parameter values for
"@.p1 int" that a user ever passed into this stored proc were unique
integers that were greater than 0, but suppose 40% of the [c1] values in
[table1] were, in fact, 0. SQL would use the average density of the
column to estimate the number of rows that this predicate would return;
this would be an overestimate, and SQL would might choose a table scan
over an index seek based on the rowcount estimates. A table scan would
be the best plan if the parameter value was 0, but unfortunately it
happens that users will never or rarely pass @.p1=0, so performance of the
stored proc for more typical parameters suffers.
In SQL 7.0 or 2000, suppose you executed this proc for the first time
(when the sp plan is not in cache) with the command "EXEC proc1 @.p1 =
10". Parameter sniffing allows SQL to insert the known value of
parameter @.p1 into the query at compile time before a plan for the query
is generated. Because SQL knows that the value of @.p1 is not 0, it can
compile a plan that is tailored to the class of parameters that is
actually passed into the proc, so for example it might select an index
seek instead of a table scan based on the smaller estimated rowcount --
this is a good thing if most of the time 0 is not the value passed as
@.p1. Generally speaking, this feature allows more efficient stored proc
execution plans, but a key requirement for everything to work as expected
is that the parameter values used for compilation be "typical".
In your case, the problem is that you have default NULL values for your
parameters ("@.Today DATETIME = NULL, ...") that are not typical because
the parameter values are changed inside the stored proc before they are
used -- as a result NULL will never actually be used to search the
column. If the first execution of this stored proc doesn't pass in an
explicit value for the @.Today parameter, SQL believes that its value will
be NULL. When SQL compiles the plan for this sp it substitutes NULL for
each occurrence of @.Today that is embedded within a query.
Unfortunately, after execution begins the first thing the stored proc
does is change @.Today to a non-NULL value if it is found to be NULL, but
unfortunately SQL doesn't know about this at compile time. Because NULL
is a very atypical parameter value, the plan that SQL generates may not
be a good one for the new value of the parameter that is assigned at
execution time.
So, the bottom line is that if you assign defaults to your sp parameters
and later use those same parameters in a query, the defaults should be
"typical" because they will be used during plan generation. If you must
use defaults and business logic dictates that they be atypical (as may be
the case here if app modifications are not an option), there are two
possible solutions if you determine that the substitution of atypical
parameter values is causing bad plans:
1. "Disable" parameter sniffing by using local DECLARE'd variables that
you SET equal to the parameters inside the stored proc, and use the local
variables instead of the offending parameters in the queries. This is the
solution that you found yourself. SQL can't use parameter sniffing in
this case so it must make some guesses, but in this case the guess based
on average column density is better than the plan based on a specific but
"wrong" parameter value (NULL).
2. Nest the affected queries somehow so that they run within a different
context that will require a distinct execution plan. There are several
possibilities here. for example:
a. Put the affected queries in a different "child" stored proc. If
you execute that stored proc within this one *after* the parameter @.Today
has been changed to its final value, parameter sniffing will suddenly
become your friend because the value SQL uses to compile the queries
inside the child stored proc is the actual value that will be used in the
query.
b. Use sp_executesql to execute the affected queries. The plan won't
be generated until the sp_executesql stmt actually runs, which is of
course after the parameter values have been changed.
c. Use dynamic SQL ("EXEC (@.sql)") to execute the affected queries.
An equivalent approach would be to put the query in a child stored proc
just like 2.a, but execute it within the parent proc with EXEC WITH
RECOMPILE.
Option #1 seems to have worked well for you in this case, although
sometimes one of the options in #2 is a preferable choice. Here are some
guidelines, although when you're dealing with something as complicated as
the query optimizer experimentation is often the best approach <g>:
- If you have only one "class" (defined as values that have similar
density in the table) of actual parameter value that is used within a
query (even if there are other classes of data in the base table that are
never or rarely searched on), 2.a. or 2.b is probably the best option.
This is because these options permit the actual parameter values to be
used during compilation which should result in the most efficient query
plan for that class of parameter.
- If you have multiple "classes" of parameter value (for example, for
the column being searched, half the table data is NULL, the other half
are unique integers, and you may do searches on either class), 2.c can be
effective. The downside is that a new plan for the query must be
compiled on each execution, but the upside is that the plan will always
be tailored to the parameter value being used for that particular
execution. This is best when there is no single execution plan that
provides acceptable execution time for all classes of parameters.
HTH -
Bart
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
Andrew J. Kelly SQL MVP
"fmicle" <fmicle@.hotmail.com> wrote in message
news:5BC62C0B-98E3-4415-A6C8-48A704CFD80B@.microsoft.com...
> Hi,
> I have the following problem:
> When I call a stored procedure from a COM+ application I get a different
query plan than the one I get when calling the same stored procedure within
Query Analyzer. The interesting side effect of it is that the COM+
application runs faster as I expected, since the query takes much longer to
execute in Query Analyzer.
> I have noticed that the query plan differs, when I call my SP from COM+.
Does anybody know why? Are there any magic/secrets flags, that COM+ sets?
> One of the cases is the fact that when I run the query in QA, the plan
contains a lookup WITH PREFETCH, which takes longer than without prefetch
(COM+) case.
> I don't know what can be the factor that causes two different query plans
for the same stored procedure...
> On the other hand, I use an index hint in one of my queries and I noticed
that if I don't use the hint, when running in QA the optimizer will use the
wrong index, but when the same SP is called from my COM+ app, the optimizer
chooses the right index even if I don't use the hint.
> Does anyone have any ideas, thoughts, hints on this?
> I'm really confused... :-)
> Thanks in advance,
> Florin
Subscribe to:
Posts (Atom)