Hi All
We have a database with approx. 460 tables linked to a foreign key (UserID)
in a table called Users. After installing SQL SP4, when trying to delete an
unreferenced entry in the Users table (even using Enterprise Manager), we get
the following error: "[Microsoft][ODBC SQL Server Driver][SQL Server]Internal
Query Processor Error: The query processor encountered an unexpected error
during execution."
Using the same database on a SP3a SQL server, it works fine.
Thanks in advance
Johan Fourie
Hi,
Johan Fourie,
Did u restarted ur sql services after installation of sp4.
hope this help
from
killer
|||Hi Johan,
Thanks for your post.
From your descriptions, I understood you SELECT statements will report the
error message after applied SP4. If I have misunderstood your concern,
please feel free to point it out.
Based on my knowledge, SQL Server SP4 should fix a related error message.
Please refer the KB for more detailed information.
FIX: Internal Query Processor Error 8623 When Microsoft SQL Server Tries to
Compile a Plan for a Complex Query
http://support.microsoft.com/kb/818729
Is it possible for you to reproduce it on Northwind database? Would you
please generate a sample table, with which I could reproduct it on my side?
Any more error logs on your side? I understand the information may be
sensitive to you, my direct email address is v-mingqc@.online.microsoft.com
(please remember to remove "online" before click SEND as it's only for
SPAM), you may send the sample script file to me directly and I will keep
secure.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Yes, we did many reboots since the upgrade to SP4
Thanks
Johan Fourie
"doller" wrote:
> Hi,
> Johan Fourie,
> Did u restarted ur sql services after installation of sp4.
> hope this help
> from
> killer
>
|||Hi Michael
Ok, from SQL Query Analyzer the following command "DELETE FROM Users WHERE
UserID = 19" returns the following error: "Server: Msg 8630, Level 17, State
34, Line 1
Internal Query Processor Error: The query processor encountered an
unexpected error during execution."
I do not think it would be possible to simulate this problem on the
Northwind database as you need many FK links to the affected table. As you
can see, it is actually not a complex query, but SQL must determine whether
the entry being deleted is in use by one of the FK relationships (>460)
Thanks and much appreciated
Johan Fourie
"Michael Cheng [MSFT]" wrote:
> Hi Johan,
> Thanks for your post.
> From your descriptions, I understood you SELECT statements will report the
> error message after applied SP4. If I have misunderstood your concern,
> please feel free to point it out.
> Based on my knowledge, SQL Server SP4 should fix a related error message.
> Please refer the KB for more detailed information.
> FIX: Internal Query Processor Error 8623 When Microsoft SQL Server Tries to
> Compile a Plan for a Complex Query
> http://support.microsoft.com/kb/818729
> Is it possible for you to reproduce it on Northwind database? Would you
> please generate a sample table, with which I could reproduct it on my side?
> Any more error logs on your side? I understand the information may be
> sensitive to you, my direct email address is v-mingqc@.online.microsoft.com
> (please remember to remove "online" before click SEND as it's only for
> SPAM), you may send the sample script file to me directly and I will keep
> secure.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Johan,
Thanks for the quick response.
It is very strange the SP4 enroll this new error message. I would like to
check it further. Is it possible for you to send me sample tables or sample
databases? (mdb file or the scripts with your tables)
I understand the information must be sensitive to you. Alternatively, to
efficiently troubleshoot a this issue, we recommend that you contact
Microsoft Customer Service and Support and open a support incident and work
with a dedicated Support Professional.
Please be advised that contacting phone support will be a charged call.
However, if it turn out to be a bug in SP4 then charges are usually
refunded or waived.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default...S;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael
Thanks for the quick response. We took the database again to a SP3a SQL
server and it works fine there, must be a SP4 problem. I'll be sending you
the database via e-mail shortly. Please treat the data and structure of the
database as very sensitive. Hope to hear from you soon.
Regards and many thanks.
Johan Fourie
"Michael Cheng [MSFT]" wrote:
> Hi Johan,
> Thanks for the quick response.
> It is very strange the SP4 enroll this new error message. I would like to
> check it further. Is it possible for you to send me sample tables or sample
> databases? (mdb file or the scripts with your tables)
> I understand the information must be sensitive to you. Alternatively, to
> efficiently troubleshoot a this issue, we recommend that you contact
> Microsoft Customer Service and Support and open a support incident and work
> with a dedicated Support Professional.
> Please be advised that contacting phone support will be a charged call.
> However, if it turn out to be a bug in SP4 then charges are usually
> refunded or waived.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/default...S;PHONENUMBERS
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Johan,
Thanks for your kindly sending me the database files.
After looking into the execution plan, I believe this is a known issue of
SQL Server service pack 4.
Since we add stack overflow checks in SP4, it raise this new problem. For
now, we do not have a pretty workaround for this issue.
However, if you want to apply SP4 and this issue has big business impact to
you. I would like to recommand you opening a grace case with Microsoft
Customer Service and Support (CSS). Please understand that we could not
share you the QFE request in the newsgroup.
Please be advised that contacting phone support will be a charged call.
However, if the support engineer confirmed this is a bug and no other
support then charges are usually refunded or waived.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default...S;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
You could share the information below with the CSS support engineer
Bug# SQL Server 8.0 20000179
I do apologized for this new known issue that caused to you and thanks so
much for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael
Thanks. Is there a quick fix available for this problem? We've advised all
our customers to go to SP4 (when it was released) and they wont be happy if
we tell them to go back to SP3a (as they all have this problem). Can you also
supply us with the KB number for this problem. Would it be possible for you
to send the QFE via e-mail to me?
Thanks in advance.
Johan Fourie
"Michael Cheng [MSFT]" wrote:
> Hi Johan,
> Thanks for your kindly sending me the database files.
> After looking into the execution plan, I believe this is a known issue of
> SQL Server service pack 4.
> Since we add stack overflow checks in SP4, it raise this new problem. For
> now, we do not have a pretty workaround for this issue.
> However, if you want to apply SP4 and this issue has big business impact to
> you. I would like to recommand you opening a grace case with Microsoft
> Customer Service and Support (CSS). Please understand that we could not
> share you the QFE request in the newsgroup.
> Please be advised that contacting phone support will be a charged call.
> However, if the support engineer confirmed this is a bug and no other
> support then charges are usually refunded or waived.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/default...S;PHONENUMBERS
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
> You could share the information below with the CSS support engineer
> Bug# SQL Server 8.0 20000179
> I do apologized for this new known issue that caused to you and thanks so
> much for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Johan,
Thanks for your update.
Unfortunately, I am so sorry to say that there is temporarily no public
Knowledge Base articles and document describing this issue. This issue only
occurs in SQL Server 2000 SP4, SQL Server 2000 SP3 and SQL Server 2005 do
not have this issue.
Unfortunately, I am unable to process the delivery of QFEs via email
directly. However, you should be able to quickly get this QFE by opening
the grace case with CSS.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
Showing posts with label sp4. Show all posts
Showing posts with label sp4. Show all posts
Friday, March 30, 2012
Query Processor Error after SP4
Hi All
We have a database with approx. 460 tables linked to a foreign key (UserID)
in a table called Users. After installing SQL SP4, when trying to delete an
unreferenced entry in the Users table (even using Enterprise Manager), we ge
t
the following error: "[Microsoft][ODBC SQL Server Driver][SQL Se
rver]Internal
Query Processor Error: The query processor encountered an unexpected error
during execution."
Using the same database on a SP3a SQL server, it works fine.
Thanks in advance
--
Johan FourieHi,
Johan Fourie,
Did u restarted ur sql services after installation of sp4.
hope this help
from
killer|||Hi Johan,
Thanks for your post.
From your descriptions, I understood you SELECT statements will report the
error message after applied SP4. If I have misunderstood your concern,
please feel free to point it out.
Based on my knowledge, SQL Server SP4 should fix a related error message.
Please refer the KB for more detailed information.
FIX: Internal Query Processor Error 8623 When Microsoft SQL Server Tries to
Compile a Plan for a Complex Query
http://support.microsoft.com/kb/818729
Is it possible for you to reproduce it on Northwind database? Would you
please generate a sample table, with which I could reproduct it on my side?
Any more error logs on your side? I understand the information may be
sensitive to you, my direct email address is v-mingqc@.online.microsoft.com
(please remember to remove "online" before click SEND as it's only for
SPAM), you may send the sample script file to me directly and I will keep
secure.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Yes, we did many reboots since the upgrade to SP4
Thanks
--
Johan Fourie
"doller" wrote:
> Hi,
> Johan Fourie,
> Did u restarted ur sql services after installation of sp4.
> hope this help
> from
> killer
>|||Hi Michael
Ok, from SQL Query Analyzer the following command "DELETE FROM Users WHERE
UserID = 19" returns the following error: "Server: Msg 8630, Level 17, State
34, Line 1
Internal Query Processor Error: The query processor encountered an
unexpected error during execution."
I do not think it would be possible to simulate this problem on the
Northwind database as you need many FK links to the affected table. As you
can see, it is actually not a complex query, but SQL must determine whether
the entry being deleted is in use by one of the FK relationships (>460)
Thanks and much appreciated
--
Johan Fourie
"Michael Cheng [MSFT]" wrote:
> Hi Johan,
> Thanks for your post.
> From your descriptions, I understood you SELECT statements will report the
> error message after applied SP4. If I have misunderstood your concern,
> please feel free to point it out.
> Based on my knowledge, SQL Server SP4 should fix a related error message.
> Please refer the KB for more detailed information.
> FIX: Internal Query Processor Error 8623 When Microsoft SQL Server Tries t
o
> Compile a Plan for a Complex Query
> http://support.microsoft.com/kb/818729
> Is it possible for you to reproduce it on Northwind database? Would you
> please generate a sample table, with which I could reproduct it on my side
?
> Any more error logs on your side? I understand the information may be
> sensitive to you, my direct email address is v-mingqc@.online.microsoft.com
> (please remember to remove "online" before click SEND as it's only for
> SPAM), you may send the sample script file to me directly and I will keep
> secure.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi Johan,
Thanks for the quick response.
It is very strange the SP4 enroll this new error message. I would like to
check it further. Is it possible for you to send me sample tables or sample
databases? (mdb file or the scripts with your tables)
I understand the information must be sensitive to you. Alternatively, to
efficiently troubleshoot a this issue, we recommend that you contact
Microsoft Customer Service and Support and open a support incident and work
with a dedicated Support Professional.
Please be advised that contacting phone support will be a charged call.
However, if it turn out to be a bug in SP4 then charges are usually
refunded or waived.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael
Thanks for the quick response. We took the database again to a SP3a SQL
server and it works fine there, must be a SP4 problem. I'll be sending you
the database via e-mail shortly. Please treat the data and structure of the
database as very sensitive. Hope to hear from you soon.
Regards and many thanks.
--
Johan Fourie
"Michael Cheng [MSFT]" wrote:
> Hi Johan,
> Thanks for the quick response.
> It is very strange the SP4 enroll this new error message. I would like to
> check it further. Is it possible for you to send me sample tables or sampl
e
> databases? (mdb file or the scripts with your tables)
> I understand the information must be sensitive to you. Alternatively, to
> efficiently troubleshoot a this issue, we recommend that you contact
> Microsoft Customer Service and Support and open a support incident and wor
k
> with a dedicated Support Professional.
> Please be advised that contacting phone support will be a charged call.
> However, if it turn out to be a bug in SP4 then charges are usually
> refunded or waived.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/defaul...US;PHONENUMBERS
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi Johan,
Thanks for your kindly sending me the database files.
After looking into the execution plan, I believe this is a known issue of
SQL Server service pack 4.
Since we add stack overflow checks in SP4, it raise this new problem. For
now, we do not have a pretty workaround for this issue.
However, if you want to apply SP4 and this issue has big business impact to
you. I would like to recommand you opening a grace case with Microsoft
Customer Service and Support (CSS). Please understand that we could not
share you the QFE request in the newsgroup.
Please be advised that contacting phone support will be a charged call.
However, if the support engineer confirmed this is a bug and no other
support then charges are usually refunded or waived.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
You could share the information below with the CSS support engineer
Bug# SQL Server 8.0 20000179
I do apologized for this new known issue that caused to you and thanks so
much for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael
Thanks. Is there a quick fix available for this problem? We've advised all
our customers to go to SP4 (when it was released) and they wont be happy if
we tell them to go back to SP3a (as they all have this problem). Can you als
o
supply us with the KB number for this problem. Would it be possible for you
to send the QFE via e-mail to me?
Thanks in advance.
--
Johan Fourie
"Michael Cheng [MSFT]" wrote:
> Hi Johan,
> Thanks for your kindly sending me the database files.
> After looking into the execution plan, I believe this is a known issue of
> SQL Server service pack 4.
> Since we add stack overflow checks in SP4, it raise this new problem. For
> now, we do not have a pretty workaround for this issue.
> However, if you want to apply SP4 and this issue has big business impact t
o
> you. I would like to recommand you opening a grace case with Microsoft
> Customer Service and Support (CSS). Please understand that we could not
> share you the QFE request in the newsgroup.
> Please be advised that contacting phone support will be a charged call.
> However, if the support engineer confirmed this is a bug and no other
> support then charges are usually refunded or waived.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/defaul...US;PHONENUMBERS
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
> You could share the information below with the CSS support engineer
> Bug# SQL Server 8.0 20000179
> I do apologized for this new known issue that caused to you and thanks so
> much for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi Johan,
Thanks for your update.
Unfortunately, I am so sorry to say that there is temporarily no public
Knowledge Base articles and document describing this issue. This issue only
occurs in SQL Server 2000 SP4, SQL Server 2000 SP3 and SQL Server 2005 do
not have this issue.
Unfortunately, I am unable to process the delivery of QFEs via email
directly. However, you should be able to quickly get this QFE by opening
the grace case with CSS.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
We have a database with approx. 460 tables linked to a foreign key (UserID)
in a table called Users. After installing SQL SP4, when trying to delete an
unreferenced entry in the Users table (even using Enterprise Manager), we ge
t
the following error: "[Microsoft][ODBC SQL Server Driver][SQL Se
rver]Internal
Query Processor Error: The query processor encountered an unexpected error
during execution."
Using the same database on a SP3a SQL server, it works fine.
Thanks in advance
--
Johan FourieHi,
Johan Fourie,
Did u restarted ur sql services after installation of sp4.
hope this help
from
killer|||Hi Johan,
Thanks for your post.
From your descriptions, I understood you SELECT statements will report the
error message after applied SP4. If I have misunderstood your concern,
please feel free to point it out.
Based on my knowledge, SQL Server SP4 should fix a related error message.
Please refer the KB for more detailed information.
FIX: Internal Query Processor Error 8623 When Microsoft SQL Server Tries to
Compile a Plan for a Complex Query
http://support.microsoft.com/kb/818729
Is it possible for you to reproduce it on Northwind database? Would you
please generate a sample table, with which I could reproduct it on my side?
Any more error logs on your side? I understand the information may be
sensitive to you, my direct email address is v-mingqc@.online.microsoft.com
(please remember to remove "online" before click SEND as it's only for
SPAM), you may send the sample script file to me directly and I will keep
secure.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Yes, we did many reboots since the upgrade to SP4
Thanks
--
Johan Fourie
"doller" wrote:
> Hi,
> Johan Fourie,
> Did u restarted ur sql services after installation of sp4.
> hope this help
> from
> killer
>|||Hi Michael
Ok, from SQL Query Analyzer the following command "DELETE FROM Users WHERE
UserID = 19" returns the following error: "Server: Msg 8630, Level 17, State
34, Line 1
Internal Query Processor Error: The query processor encountered an
unexpected error during execution."
I do not think it would be possible to simulate this problem on the
Northwind database as you need many FK links to the affected table. As you
can see, it is actually not a complex query, but SQL must determine whether
the entry being deleted is in use by one of the FK relationships (>460)
Thanks and much appreciated
--
Johan Fourie
"Michael Cheng [MSFT]" wrote:
> Hi Johan,
> Thanks for your post.
> From your descriptions, I understood you SELECT statements will report the
> error message after applied SP4. If I have misunderstood your concern,
> please feel free to point it out.
> Based on my knowledge, SQL Server SP4 should fix a related error message.
> Please refer the KB for more detailed information.
> FIX: Internal Query Processor Error 8623 When Microsoft SQL Server Tries t
o
> Compile a Plan for a Complex Query
> http://support.microsoft.com/kb/818729
> Is it possible for you to reproduce it on Northwind database? Would you
> please generate a sample table, with which I could reproduct it on my side
?
> Any more error logs on your side? I understand the information may be
> sensitive to you, my direct email address is v-mingqc@.online.microsoft.com
> (please remember to remove "online" before click SEND as it's only for
> SPAM), you may send the sample script file to me directly and I will keep
> secure.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi Johan,
Thanks for the quick response.
It is very strange the SP4 enroll this new error message. I would like to
check it further. Is it possible for you to send me sample tables or sample
databases? (mdb file or the scripts with your tables)
I understand the information must be sensitive to you. Alternatively, to
efficiently troubleshoot a this issue, we recommend that you contact
Microsoft Customer Service and Support and open a support incident and work
with a dedicated Support Professional.
Please be advised that contacting phone support will be a charged call.
However, if it turn out to be a bug in SP4 then charges are usually
refunded or waived.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael
Thanks for the quick response. We took the database again to a SP3a SQL
server and it works fine there, must be a SP4 problem. I'll be sending you
the database via e-mail shortly. Please treat the data and structure of the
database as very sensitive. Hope to hear from you soon.
Regards and many thanks.
--
Johan Fourie
"Michael Cheng [MSFT]" wrote:
> Hi Johan,
> Thanks for the quick response.
> It is very strange the SP4 enroll this new error message. I would like to
> check it further. Is it possible for you to send me sample tables or sampl
e
> databases? (mdb file or the scripts with your tables)
> I understand the information must be sensitive to you. Alternatively, to
> efficiently troubleshoot a this issue, we recommend that you contact
> Microsoft Customer Service and Support and open a support incident and wor
k
> with a dedicated Support Professional.
> Please be advised that contacting phone support will be a charged call.
> However, if it turn out to be a bug in SP4 then charges are usually
> refunded or waived.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/defaul...US;PHONENUMBERS
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi Johan,
Thanks for your kindly sending me the database files.
After looking into the execution plan, I believe this is a known issue of
SQL Server service pack 4.
Since we add stack overflow checks in SP4, it raise this new problem. For
now, we do not have a pretty workaround for this issue.
However, if you want to apply SP4 and this issue has big business impact to
you. I would like to recommand you opening a grace case with Microsoft
Customer Service and Support (CSS). Please understand that we could not
share you the QFE request in the newsgroup.
Please be advised that contacting phone support will be a charged call.
However, if the support engineer confirmed this is a bug and no other
support then charges are usually refunded or waived.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
You could share the information below with the CSS support engineer
Bug# SQL Server 8.0 20000179
I do apologized for this new known issue that caused to you and thanks so
much for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael
Thanks. Is there a quick fix available for this problem? We've advised all
our customers to go to SP4 (when it was released) and they wont be happy if
we tell them to go back to SP3a (as they all have this problem). Can you als
o
supply us with the KB number for this problem. Would it be possible for you
to send the QFE via e-mail to me?
Thanks in advance.
--
Johan Fourie
"Michael Cheng [MSFT]" wrote:
> Hi Johan,
> Thanks for your kindly sending me the database files.
> After looking into the execution plan, I believe this is a known issue of
> SQL Server service pack 4.
> Since we add stack overflow checks in SP4, it raise this new problem. For
> now, we do not have a pretty workaround for this issue.
> However, if you want to apply SP4 and this issue has big business impact t
o
> you. I would like to recommand you opening a grace case with Microsoft
> Customer Service and Support (CSS). Please understand that we could not
> share you the QFE request in the newsgroup.
> Please be advised that contacting phone support will be a charged call.
> However, if the support engineer confirmed this is a bug and no other
> support then charges are usually refunded or waived.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/defaul...US;PHONENUMBERS
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
> You could share the information below with the CSS support engineer
> Bug# SQL Server 8.0 20000179
> I do apologized for this new known issue that caused to you and thanks so
> much for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi Johan,
Thanks for your update.
Unfortunately, I am so sorry to say that there is temporarily no public
Knowledge Base articles and document describing this issue. This issue only
occurs in SQL Server 2000 SP4, SQL Server 2000 SP3 and SQL Server 2005 do
not have this issue.
Unfortunately, I am unable to process the delivery of QFEs via email
directly. However, you should be able to quickly get this QFE by opening
the grace case with CSS.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
Query Processor Error after SP4
Hi All
We have a database with approx. 460 tables linked to a foreign key (UserID)
in a table called Users. After installing SQL SP4, when trying to delete an
unreferenced entry in the Users table (even using Enterprise Manager), we get
the following error: "[Microsoft][ODBC SQL Server Driver][SQL Server]Internal
Query Processor Error: The query processor encountered an unexpected error
during execution."
Using the same database on a SP3a SQL server, it works fine.
Thanks in advance
--
Johan FourieHi,
Johan Fourie,
Did u restarted ur sql services after installation of sp4.
hope this help
from
killer|||Hi Johan,
Thanks for your post.
From your descriptions, I understood you SELECT statements will report the
error message after applied SP4. If I have misunderstood your concern,
please feel free to point it out.
Based on my knowledge, SQL Server SP4 should fix a related error message.
Please refer the KB for more detailed information.
FIX: Internal Query Processor Error 8623 When Microsoft SQL Server Tries to
Compile a Plan for a Complex Query
http://support.microsoft.com/kb/818729
Is it possible for you to reproduce it on Northwind database? Would you
please generate a sample table, with which I could reproduct it on my side?
Any more error logs on your side? I understand the information may be
sensitive to you, my direct email address is v-mingqc@.online.microsoft.com
(please remember to remove "online" before click SEND as it's only for
SPAM), you may send the sample script file to me directly and I will keep
secure.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Yes, we did many reboots since the upgrade to SP4
Thanks
--
Johan Fourie
"doller" wrote:
> Hi,
> Johan Fourie,
> Did u restarted ur sql services after installation of sp4.
> hope this help
> from
> killer
>|||Hi Michael
Ok, from SQL Query Analyzer the following command "DELETE FROM Users WHERE
UserID = 19" returns the following error: "Server: Msg 8630, Level 17, State
34, Line 1
Internal Query Processor Error: The query processor encountered an
unexpected error during execution."
I do not think it would be possible to simulate this problem on the
Northwind database as you need many FK links to the affected table. As you
can see, it is actually not a complex query, but SQL must determine whether
the entry being deleted is in use by one of the FK relationships (>460)
Thanks and much appreciated
--
Johan Fourie
"Michael Cheng [MSFT]" wrote:
> Hi Johan,
> Thanks for your post.
> From your descriptions, I understood you SELECT statements will report the
> error message after applied SP4. If I have misunderstood your concern,
> please feel free to point it out.
> Based on my knowledge, SQL Server SP4 should fix a related error message.
> Please refer the KB for more detailed information.
> FIX: Internal Query Processor Error 8623 When Microsoft SQL Server Tries to
> Compile a Plan for a Complex Query
> http://support.microsoft.com/kb/818729
> Is it possible for you to reproduce it on Northwind database? Would you
> please generate a sample table, with which I could reproduct it on my side?
> Any more error logs on your side? I understand the information may be
> sensitive to you, my direct email address is v-mingqc@.online.microsoft.com
> (please remember to remove "online" before click SEND as it's only for
> SPAM), you may send the sample script file to me directly and I will keep
> secure.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Johan,
Thanks for the quick response.
It is very strange the SP4 enroll this new error message. I would like to
check it further. Is it possible for you to send me sample tables or sample
databases? (mdb file or the scripts with your tables)
I understand the information must be sensitive to you. Alternatively, to
efficiently troubleshoot a this issue, we recommend that you contact
Microsoft Customer Service and Support and open a support incident and work
with a dedicated Support Professional.
Please be advised that contacting phone support will be a charged call.
However, if it turn out to be a bug in SP4 then charges are usually
refunded or waived.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael
Thanks for the quick response. We took the database again to a SP3a SQL
server and it works fine there, must be a SP4 problem. I'll be sending you
the database via e-mail shortly. Please treat the data and structure of the
database as very sensitive. Hope to hear from you soon.
Regards and many thanks.
--
Johan Fourie
"Michael Cheng [MSFT]" wrote:
> Hi Johan,
> Thanks for the quick response.
> It is very strange the SP4 enroll this new error message. I would like to
> check it further. Is it possible for you to send me sample tables or sample
> databases? (mdb file or the scripts with your tables)
> I understand the information must be sensitive to you. Alternatively, to
> efficiently troubleshoot a this issue, we recommend that you contact
> Microsoft Customer Service and Support and open a support incident and work
> with a dedicated Support Professional.
> Please be advised that contacting phone support will be a charged call.
> However, if it turn out to be a bug in SP4 then charges are usually
> refunded or waived.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Johan,
Thanks for your kindly sending me the database files.
After looking into the execution plan, I believe this is a known issue of
SQL Server service pack 4.
Since we add stack overflow checks in SP4, it raise this new problem. For
now, we do not have a pretty workaround for this issue.
However, if you want to apply SP4 and this issue has big business impact to
you. I would like to recommand you opening a grace case with Microsoft
Customer Service and Support (CSS). Please understand that we could not
share you the QFE request in the newsgroup.
Please be advised that contacting phone support will be a charged call.
However, if the support engineer confirmed this is a bug and no other
support then charges are usually refunded or waived.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
You could share the information below with the CSS support engineer
Bug# SQL Server 8.0 20000179
I do apologized for this new known issue that caused to you and thanks so
much for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael
Thanks. Is there a quick fix available for this problem? We've advised all
our customers to go to SP4 (when it was released) and they wont be happy if
we tell them to go back to SP3a (as they all have this problem). Can you also
supply us with the KB number for this problem. Would it be possible for you
to send the QFE via e-mail to me?
Thanks in advance.
--
Johan Fourie
"Michael Cheng [MSFT]" wrote:
> Hi Johan,
> Thanks for your kindly sending me the database files.
> After looking into the execution plan, I believe this is a known issue of
> SQL Server service pack 4.
> Since we add stack overflow checks in SP4, it raise this new problem. For
> now, we do not have a pretty workaround for this issue.
> However, if you want to apply SP4 and this issue has big business impact to
> you. I would like to recommand you opening a grace case with Microsoft
> Customer Service and Support (CSS). Please understand that we could not
> share you the QFE request in the newsgroup.
> Please be advised that contacting phone support will be a charged call.
> However, if the support engineer confirmed this is a bug and no other
> support then charges are usually refunded or waived.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
> You could share the information below with the CSS support engineer
> Bug# SQL Server 8.0 20000179
> I do apologized for this new known issue that caused to you and thanks so
> much for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Johan,
Thanks for your update.
Unfortunately, I am so sorry to say that there is temporarily no public
Knowledge Base articles and document describing this issue. This issue only
occurs in SQL Server 2000 SP4, SQL Server 2000 SP3 and SQL Server 2005 do
not have this issue.
Unfortunately, I am unable to process the delivery of QFEs via email
directly. However, you should be able to quickly get this QFE by opening
the grace case with CSS.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks. I'll open a case with CSS.
Regards.
--
Johan Fourie
"Michael Cheng [MSFT]" wrote:
> Hi Johan,
> Thanks for your update.
> Unfortunately, I am so sorry to say that there is temporarily no public
> Knowledge Base articles and document describing this issue. This issue only
> occurs in SQL Server 2000 SP4, SQL Server 2000 SP3 and SQL Server 2005 do
> not have this issue.
> Unfortunately, I am unable to process the delivery of QFEs via email
> directly. However, you should be able to quickly get this QFE by opening
> the grace case with CSS.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Johan,
Thanks for your cooperation.
I just wanted to follow up to let you know that the Support Engineer that
you are working with in CSS has contacted me directly to let me know that
the hotfix I recommended is not currently available to the public. I
apologize that I was not previously aware of this situation.
Please continue to work with the CSS Support Engineer for a workaround or
resolution to your issue.
Again, I am sorry that I was unaware that this hotfix is not available to
the public.
Thank you for your patience and cooperation.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
We have a database with approx. 460 tables linked to a foreign key (UserID)
in a table called Users. After installing SQL SP4, when trying to delete an
unreferenced entry in the Users table (even using Enterprise Manager), we get
the following error: "[Microsoft][ODBC SQL Server Driver][SQL Server]Internal
Query Processor Error: The query processor encountered an unexpected error
during execution."
Using the same database on a SP3a SQL server, it works fine.
Thanks in advance
--
Johan FourieHi,
Johan Fourie,
Did u restarted ur sql services after installation of sp4.
hope this help
from
killer|||Hi Johan,
Thanks for your post.
From your descriptions, I understood you SELECT statements will report the
error message after applied SP4. If I have misunderstood your concern,
please feel free to point it out.
Based on my knowledge, SQL Server SP4 should fix a related error message.
Please refer the KB for more detailed information.
FIX: Internal Query Processor Error 8623 When Microsoft SQL Server Tries to
Compile a Plan for a Complex Query
http://support.microsoft.com/kb/818729
Is it possible for you to reproduce it on Northwind database? Would you
please generate a sample table, with which I could reproduct it on my side?
Any more error logs on your side? I understand the information may be
sensitive to you, my direct email address is v-mingqc@.online.microsoft.com
(please remember to remove "online" before click SEND as it's only for
SPAM), you may send the sample script file to me directly and I will keep
secure.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Yes, we did many reboots since the upgrade to SP4
Thanks
--
Johan Fourie
"doller" wrote:
> Hi,
> Johan Fourie,
> Did u restarted ur sql services after installation of sp4.
> hope this help
> from
> killer
>|||Hi Michael
Ok, from SQL Query Analyzer the following command "DELETE FROM Users WHERE
UserID = 19" returns the following error: "Server: Msg 8630, Level 17, State
34, Line 1
Internal Query Processor Error: The query processor encountered an
unexpected error during execution."
I do not think it would be possible to simulate this problem on the
Northwind database as you need many FK links to the affected table. As you
can see, it is actually not a complex query, but SQL must determine whether
the entry being deleted is in use by one of the FK relationships (>460)
Thanks and much appreciated
--
Johan Fourie
"Michael Cheng [MSFT]" wrote:
> Hi Johan,
> Thanks for your post.
> From your descriptions, I understood you SELECT statements will report the
> error message after applied SP4. If I have misunderstood your concern,
> please feel free to point it out.
> Based on my knowledge, SQL Server SP4 should fix a related error message.
> Please refer the KB for more detailed information.
> FIX: Internal Query Processor Error 8623 When Microsoft SQL Server Tries to
> Compile a Plan for a Complex Query
> http://support.microsoft.com/kb/818729
> Is it possible for you to reproduce it on Northwind database? Would you
> please generate a sample table, with which I could reproduct it on my side?
> Any more error logs on your side? I understand the information may be
> sensitive to you, my direct email address is v-mingqc@.online.microsoft.com
> (please remember to remove "online" before click SEND as it's only for
> SPAM), you may send the sample script file to me directly and I will keep
> secure.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Johan,
Thanks for the quick response.
It is very strange the SP4 enroll this new error message. I would like to
check it further. Is it possible for you to send me sample tables or sample
databases? (mdb file or the scripts with your tables)
I understand the information must be sensitive to you. Alternatively, to
efficiently troubleshoot a this issue, we recommend that you contact
Microsoft Customer Service and Support and open a support incident and work
with a dedicated Support Professional.
Please be advised that contacting phone support will be a charged call.
However, if it turn out to be a bug in SP4 then charges are usually
refunded or waived.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael
Thanks for the quick response. We took the database again to a SP3a SQL
server and it works fine there, must be a SP4 problem. I'll be sending you
the database via e-mail shortly. Please treat the data and structure of the
database as very sensitive. Hope to hear from you soon.
Regards and many thanks.
--
Johan Fourie
"Michael Cheng [MSFT]" wrote:
> Hi Johan,
> Thanks for the quick response.
> It is very strange the SP4 enroll this new error message. I would like to
> check it further. Is it possible for you to send me sample tables or sample
> databases? (mdb file or the scripts with your tables)
> I understand the information must be sensitive to you. Alternatively, to
> efficiently troubleshoot a this issue, we recommend that you contact
> Microsoft Customer Service and Support and open a support incident and work
> with a dedicated Support Professional.
> Please be advised that contacting phone support will be a charged call.
> However, if it turn out to be a bug in SP4 then charges are usually
> refunded or waived.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Johan,
Thanks for your kindly sending me the database files.
After looking into the execution plan, I believe this is a known issue of
SQL Server service pack 4.
Since we add stack overflow checks in SP4, it raise this new problem. For
now, we do not have a pretty workaround for this issue.
However, if you want to apply SP4 and this issue has big business impact to
you. I would like to recommand you opening a grace case with Microsoft
Customer Service and Support (CSS). Please understand that we could not
share you the QFE request in the newsgroup.
Please be advised that contacting phone support will be a charged call.
However, if the support engineer confirmed this is a bug and no other
support then charges are usually refunded or waived.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
You could share the information below with the CSS support engineer
Bug# SQL Server 8.0 20000179
I do apologized for this new known issue that caused to you and thanks so
much for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael
Thanks. Is there a quick fix available for this problem? We've advised all
our customers to go to SP4 (when it was released) and they wont be happy if
we tell them to go back to SP3a (as they all have this problem). Can you also
supply us with the KB number for this problem. Would it be possible for you
to send the QFE via e-mail to me?
Thanks in advance.
--
Johan Fourie
"Michael Cheng [MSFT]" wrote:
> Hi Johan,
> Thanks for your kindly sending me the database files.
> After looking into the execution plan, I believe this is a known issue of
> SQL Server service pack 4.
> Since we add stack overflow checks in SP4, it raise this new problem. For
> now, we do not have a pretty workaround for this issue.
> However, if you want to apply SP4 and this issue has big business impact to
> you. I would like to recommand you opening a grace case with Microsoft
> Customer Service and Support (CSS). Please understand that we could not
> share you the QFE request in the newsgroup.
> Please be advised that contacting phone support will be a charged call.
> However, if the support engineer confirmed this is a bug and no other
> support then charges are usually refunded or waived.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
> You could share the information below with the CSS support engineer
> Bug# SQL Server 8.0 20000179
> I do apologized for this new known issue that caused to you and thanks so
> much for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Johan,
Thanks for your update.
Unfortunately, I am so sorry to say that there is temporarily no public
Knowledge Base articles and document describing this issue. This issue only
occurs in SQL Server 2000 SP4, SQL Server 2000 SP3 and SQL Server 2005 do
not have this issue.
Unfortunately, I am unable to process the delivery of QFEs via email
directly. However, you should be able to quickly get this QFE by opening
the grace case with CSS.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks. I'll open a case with CSS.
Regards.
--
Johan Fourie
"Michael Cheng [MSFT]" wrote:
> Hi Johan,
> Thanks for your update.
> Unfortunately, I am so sorry to say that there is temporarily no public
> Knowledge Base articles and document describing this issue. This issue only
> occurs in SQL Server 2000 SP4, SQL Server 2000 SP3 and SQL Server 2005 do
> not have this issue.
> Unfortunately, I am unable to process the delivery of QFEs via email
> directly. However, you should be able to quickly get this QFE by opening
> the grace case with CSS.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Johan,
Thanks for your cooperation.
I just wanted to follow up to let you know that the Support Engineer that
you are working with in CSS has contacted me directly to let me know that
the hotfix I recommended is not currently available to the public. I
apologize that I was not previously aware of this situation.
Please continue to work with the CSS Support Engineer for a workaround or
resolution to your issue.
Again, I am sorry that I was unaware that this hotfix is not available to
the public.
Thank you for your patience and cooperation.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Friday, March 23, 2012
Query Plan Question
I'm running SQL Server 2000 Std SP3 on Windows 2000 Standard SP4.
I have the following query:
declare @.FromDate as DATETIME
SET @.FromDate = '2004-03-10'
Select Count(*)
From URLS
Where (URLString like '%homepage%')
And AddedOn >= @.FromDate
Select Count(*)
From URLS
Where (URLString like '%homepage%')
And AddedOn >= '2004-03-10'
The first query which uses @.FromDate does a table scan. The second query th
at has the date hard coded uses the index on the AddedOn date field.
My question is why doesn't the first query also use the index? There are 10
Million + rows in the table.
Thanks,
StephenIf you use a variable in a WHERE clause, then the optimizer doesn't know
what value you are looking for (the optimizer optimizes statement by
statement). So, it will have to guess number of rows to be returned. I don't
recall the values it guesses (you find them in the Inside SQL Server book),
but I think that it is either 10% or 25% for greater then. Say you have 10
million rows, this means that SQL Server will read 1 million rows. Say you
have an NC index, then SQL Server will potentially need to jump to a data
page for each row. This means 1 million data page accesses.
Above is just to give you an understand about how the optimizer works. Note
that using a variable and a stored procedure parameter are two different
things!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Stephen Schissler" <anonymous@.discussions.microsoft.com> wrote in message
news:4A78A446-AC10-4F41-9EED-A048B3B5864D@.microsoft.com...
> I'm running SQL Server 2000 Std SP3 on Windows 2000 Standard SP4.
> I have the following query:
> declare @.FromDate as DATETIME
> SET @.FromDate = '2004-03-10'
> Select Count(*)
> From URLS
> Where (URLString like '%homepage%')
> And AddedOn >= @.FromDate
>
> Select Count(*)
> From URLS
> Where (URLString like '%homepage%')
> And AddedOn >= '2004-03-10'
> The first query which uses @.FromDate does a table scan. The second query
that has the date hard coded uses the index on the AddedOn date field.
> My question is why doesn't the first query also use the index? There are
10 Million + rows in the table.
> Thanks,
> Stephen|||It guesses?
So if I have 1 record of 10 million where the AddedOn date is equal to '2004
-03-10' and I pass this value as a variable it will do a table scan? That s
eems to me to be the wrong thing to do. I've updated the statistics on the
AddedOn date field using th
e FULLSCAN option and it still does a table scan which I find very disturbin
g. I'm now wondering how many other queries that use variables as a paramet
er are choosing the wrong query plan due to this.
Thanks,
Stephen|||Stephen Schissler wrote:
> It guesses?
> So if I have 1 record of 10 million where the AddedOn date is equal to '2004-03-10
' and I pass this value as a variable it will do a table scan? That seems to me to
be the wrong thing to do. I've updated the statistics on the AddedOn date field usi
ng
the FULLSCAN option and it still does a table scan which I find very disturbing. I'm now w
ondering how many other queries that use variables as a parameter are choosing the wrong qu
ery plan due to this.
> Thanks,
> Stephen
>
If you run it as a stored procedure and use the where clause as a
parameter you will notice different results.
Aaron Weiker
http://blogs.sqladvice.com/aweiker
http://aaronweiker.com/|||> It guesses?
What else can it do? Well, not a wild guess, it has its rules. The optimizer
does not know the value of the variable, as it optimizes statement by
statement. (Yes, one could question why that it, but it is the way SQL
Server work.) As I said, for different predicates, SQL Server estimates to
return different percentage of rows. Details in Inside SQL Server.
Statistics has nothing to do with this.
If you use a constant or a stored procedure parameter, it s a different
thing, though!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Stephen Schissler" <anonymous@.discussions.microsoft.com> wrote in message
news:23380C0C-0820-4155-BA43-D574741DC19C@.microsoft.com...
> It guesses?
> So if I have 1 record of 10 million where the AddedOn date is equal to
'2004-03-10' and I pass this value as a variable it will do a table scan?
That seems to me to be the wrong thing to do. I've updated the statistics
on the AddedOn date field using the FULLSCAN option and it still does a
table scan which I find very disturbing. I'm now wondering how many other
queries that use variables as a parameter are choosing the wrong query plan
due to this.
> Thanks,
> Stephen
I have the following query:
declare @.FromDate as DATETIME
SET @.FromDate = '2004-03-10'
Select Count(*)
From URLS
Where (URLString like '%homepage%')
And AddedOn >= @.FromDate
Select Count(*)
From URLS
Where (URLString like '%homepage%')
And AddedOn >= '2004-03-10'
The first query which uses @.FromDate does a table scan. The second query th
at has the date hard coded uses the index on the AddedOn date field.
My question is why doesn't the first query also use the index? There are 10
Million + rows in the table.
Thanks,
StephenIf you use a variable in a WHERE clause, then the optimizer doesn't know
what value you are looking for (the optimizer optimizes statement by
statement). So, it will have to guess number of rows to be returned. I don't
recall the values it guesses (you find them in the Inside SQL Server book),
but I think that it is either 10% or 25% for greater then. Say you have 10
million rows, this means that SQL Server will read 1 million rows. Say you
have an NC index, then SQL Server will potentially need to jump to a data
page for each row. This means 1 million data page accesses.
Above is just to give you an understand about how the optimizer works. Note
that using a variable and a stored procedure parameter are two different
things!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Stephen Schissler" <anonymous@.discussions.microsoft.com> wrote in message
news:4A78A446-AC10-4F41-9EED-A048B3B5864D@.microsoft.com...
> I'm running SQL Server 2000 Std SP3 on Windows 2000 Standard SP4.
> I have the following query:
> declare @.FromDate as DATETIME
> SET @.FromDate = '2004-03-10'
> Select Count(*)
> From URLS
> Where (URLString like '%homepage%')
> And AddedOn >= @.FromDate
>
> Select Count(*)
> From URLS
> Where (URLString like '%homepage%')
> And AddedOn >= '2004-03-10'
> The first query which uses @.FromDate does a table scan. The second query
that has the date hard coded uses the index on the AddedOn date field.
> My question is why doesn't the first query also use the index? There are
10 Million + rows in the table.
> Thanks,
> Stephen|||It guesses?
So if I have 1 record of 10 million where the AddedOn date is equal to '2004
-03-10' and I pass this value as a variable it will do a table scan? That s
eems to me to be the wrong thing to do. I've updated the statistics on the
AddedOn date field using th
e FULLSCAN option and it still does a table scan which I find very disturbin
g. I'm now wondering how many other queries that use variables as a paramet
er are choosing the wrong query plan due to this.
Thanks,
Stephen|||Stephen Schissler wrote:
> It guesses?
> So if I have 1 record of 10 million where the AddedOn date is equal to '2004-03-10
' and I pass this value as a variable it will do a table scan? That seems to me to
be the wrong thing to do. I've updated the statistics on the AddedOn date field usi
ng
the FULLSCAN option and it still does a table scan which I find very disturbing. I'm now w
ondering how many other queries that use variables as a parameter are choosing the wrong qu
ery plan due to this.
> Thanks,
> Stephen
>
If you run it as a stored procedure and use the where clause as a
parameter you will notice different results.
Aaron Weiker
http://blogs.sqladvice.com/aweiker
http://aaronweiker.com/|||> It guesses?
What else can it do? Well, not a wild guess, it has its rules. The optimizer
does not know the value of the variable, as it optimizes statement by
statement. (Yes, one could question why that it, but it is the way SQL
Server work.) As I said, for different predicates, SQL Server estimates to
return different percentage of rows. Details in Inside SQL Server.
Statistics has nothing to do with this.
If you use a constant or a stored procedure parameter, it s a different
thing, though!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Stephen Schissler" <anonymous@.discussions.microsoft.com> wrote in message
news:23380C0C-0820-4155-BA43-D574741DC19C@.microsoft.com...
> It guesses?
> So if I have 1 record of 10 million where the AddedOn date is equal to
'2004-03-10' and I pass this value as a variable it will do a table scan?
That seems to me to be the wrong thing to do. I've updated the statistics
on the AddedOn date field using the FULLSCAN option and it still does a
table scan which I find very disturbing. I'm now wondering how many other
queries that use variables as a parameter are choosing the wrong query plan
due to this.
> Thanks,
> Stephen
Query Performance SQL 7.0 SP2 / SP4
Hi Gurus
I m using SQL Server7.0 with SP2 on Compaq Prolient ML310 PIV 2.2Ghz
Now the problem is when i m executing the "select * statment " on a table with 21000 rows it is taking ard 1min to result for the same.and if i m using the same query without any service pack the result is coming in 4-5 Secs. but with the base system ( without service pack) my system get freeze frequently.
REPLY URGENT
TIAHave you tried downloading the latest service pack? (SP4 for 7.0 I believe)
What changed between having the sp and not having it?|||Originally posted by rhigdon
Have you tried downloading the latest service pack? (SP4 for 7.0 I believe)
What changed between having the sp and not having it?
I have checked with the SP4 even , but the back to square one.sql
I m using SQL Server7.0 with SP2 on Compaq Prolient ML310 PIV 2.2Ghz
Now the problem is when i m executing the "select * statment " on a table with 21000 rows it is taking ard 1min to result for the same.and if i m using the same query without any service pack the result is coming in 4-5 Secs. but with the base system ( without service pack) my system get freeze frequently.
REPLY URGENT
TIAHave you tried downloading the latest service pack? (SP4 for 7.0 I believe)
What changed between having the sp and not having it?|||Originally posted by rhigdon
Have you tried downloading the latest service pack? (SP4 for 7.0 I believe)
What changed between having the sp and not having it?
I have checked with the SP4 even , but the back to square one.sql
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
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...
>
>
Subscribe to:
Posts (Atom)