Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Friday, March 30, 2012
Resolving function USER
If I log in to Query Analyzer using Windows Auth and run "PRINT USER", it returns "dbo". Is there any way to have the USER function actually return <Domain>\<login> for myself?Look up suser_name and suser_sname.
Resolving
Hello!
I am having trouble connection to my SQL 2000 SP4 using TCP IP.
I can only connect specifying the port number in for instance query analyzer. What do I need to do to get it connecting without port number? Do I unblock the resolution protocol UDP 1434 in my firewall?
Carl
Norway
What is error message. If the error is something close to ""Error Locating Server/Instance Specified", you need to enable the sql browser and make sure the udp port 1434 is not blocked by the firewall.
The following link have plenty of the content on troubleshooting connectivity issues.
http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx
cheers,
|||Thanks!
But that was concerning SQL2005. My server is 2000 SP4.
But UDP in the firewall is the only possible thing? Because I can connect spesifying the port.
Carl
|||You need connectivity to sqlbrowser in order to resolve the port number for each instance. It applies to both 2000 and 2005.|||How can I identify the Browser Service?
I have two servers running. One where everything works fine, and this newly installed one where port resolution does not work.
As of now I can not see any difference in the configuration.
sqlResolving
Hello!
I am having trouble connection to my SQL 2000 SP4 using TCP IP.
I can only connect specifying the port number in for instance query analyzer. What do I need to do to get it connecting without port number? Do I unblock the resolution protocol UDP 1434 in my firewall?
Carl
Norway
What is error message. If the error is something close to ""Error Locating Server/Instance Specified", you need to enable the sql browser and make sure the udp port 1434 is not blocked by the firewall.
The following link have plenty of the content on troubleshooting connectivity issues.
http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx
cheers,
|||Thanks!
But that was concerning SQL2005. My server is 2000 SP4.
But UDP in the firewall is the only possible thing? Because I can connect spesifying the port.
Carl
|||You need connectivity to sqlbrowser in order to resolve the port number for each instance. It applies to both 2000 and 2005.|||How can I identify the Browser Service?
I have two servers running. One where everything works fine, and this newly installed one where port resolution does not work.
As of now I can not see any difference in the configuration.
Resolving
Hello!
I am having trouble connection to my SQL 2000 SP4 using TCP IP.
I can only connect specifying the port number in for instance query analyzer. What do I need to do to get it connecting without port number? Do I unblock the resolution protocol UDP 1434 in my firewall?
Carl
Norway
What is error message. If the error is something close to ""Error Locating Server/Instance Specified", you need to enable the sql browser and make sure the udp port 1434 is not blocked by the firewall.
The following link have plenty of the content on troubleshooting connectivity issues.
http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx
cheers,
|||Thanks!
But that was concerning SQL2005. My server is 2000 SP4.
But UDP in the firewall is the only possible thing? Because I can connect spesifying the port.
Carl
|||You need connectivity to sqlbrowser in order to resolve the port number for each instance. It applies to both 2000 and 2005.|||How can I identify the Browser Service?
I have two servers running. One where everything works fine, and this newly installed one where port resolution does not work.
As of now I can not see any difference in the configuration.
RESOLVED Re: SQL Server 7: Query fails with error 7607
It turned out that I had to reapply SP4 once I installed FTS over
a previously installed server. Now it works fine. Thanks for
your assistance.
Ernie Limperis wrote:
> John,
> Looks like I misspoke. The build number on the machine in question
> is 7.00.1063. The machine with 1094 is our production box and I
> have't tried running FTS on it yet (can't build the index during
> business hours, and anyhow nobody got around to installing FTS on
> that machine). I know it's hard to say, but do you think the issue
> would go away if I install the security patch on this machine?
>
> ohn Kane wrote:
>
>
==============================================
Ernie Limperis home 510-525-8801
ernie@.sonic.net cell 510-682-6373
==============================================
You're welcome, Ernie,
No, I don't think that just installing the post-SP4 security hotfix roll-up
would of resolved this issue, as hotfixes are specific and this one is
specific to security issues. However, this post-SP4 security hotfix roll-up
does prevent you from re-installing SP4 once the security hotfix is
installed.
I'm glad you were able to resolve this via my original recommendation and
that I asked you what exact build you were on and you provided the info
(@.@.version) is most important in resolving SQL FTS issues!
Regards,
John
"Ernie Limperis" <ernie@.dnai.com> wrote in message
news:#nwevpV4EHA.1976@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> It turned out that I had to reapply SP4 once I installed FTS over
> a previously installed server. Now it works fine. Thanks for
> your assistance.
> Ernie Limperis wrote:
most[vbcol=seagreen]
a[vbcol=seagreen]
had[vbcol=seagreen]
or[vbcol=seagreen]
security[vbcol=seagreen]
especially[vbcol=seagreen]
table[vbcol=seagreen]
'insurance')
>
> --
> ==============================================
> Ernie Limperis home 510-525-8801
> ernie@.sonic.net cell 510-682-6373
> ==============================================
>
sql
a previously installed server. Now it works fine. Thanks for
your assistance.
Ernie Limperis wrote:
> John,
> Looks like I misspoke. The build number on the machine in question
> is 7.00.1063. The machine with 1094 is our production box and I
> have't tried running FTS on it yet (can't build the index during
> business hours, and anyhow nobody got around to installing FTS on
> that machine). I know it's hard to say, but do you think the issue
> would go away if I install the security patch on this machine?
>
> ohn Kane wrote:
>
>
==============================================
Ernie Limperis home 510-525-8801
ernie@.sonic.net cell 510-682-6373
==============================================
You're welcome, Ernie,
No, I don't think that just installing the post-SP4 security hotfix roll-up
would of resolved this issue, as hotfixes are specific and this one is
specific to security issues. However, this post-SP4 security hotfix roll-up
does prevent you from re-installing SP4 once the security hotfix is
installed.
I'm glad you were able to resolve this via my original recommendation and
that I asked you what exact build you were on and you provided the info
(@.@.version) is most important in resolving SQL FTS issues!
Regards,
John
"Ernie Limperis" <ernie@.dnai.com> wrote in message
news:#nwevpV4EHA.1976@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> It turned out that I had to reapply SP4 once I installed FTS over
> a previously installed server. Now it works fine. Thanks for
> your assistance.
> Ernie Limperis wrote:
most[vbcol=seagreen]
a[vbcol=seagreen]
had[vbcol=seagreen]
or[vbcol=seagreen]
security[vbcol=seagreen]
especially[vbcol=seagreen]
table[vbcol=seagreen]
'insurance')
>
> --
> ==============================================
> Ernie Limperis home 510-525-8801
> ernie@.sonic.net cell 510-682-6373
> ==============================================
>
sql
RESOLVED - Help with SQL Query - "The multi-part identifier "alias field Name" co
Hi Everyone
This is the query and I am getting follwoing error message
This is the query and I am getting follwoing error message
"The multi-part identifier "InvDate.Account Reference" could not be bound."
SELECT MAX([DATE NOTE ADDED]) AS LASTDATE,By the way its SQL Server 2005 Environment.
CC.[COMPANY],
CC.[ACCOUNT REFERENCE],
INVDATE.[LASTORDERDATE]
FROM CUSTOMERCONTACTNOTES AS CCN,
(SELECT *
FROM CUSTOMER) AS CC,
(SELECT MAX([INVOICE DATE]) AS LASTORDERDATE,
[ACCOUNT REFERENCE]
FROM INVOICEDATA
GROUP BY [ACCOUNT REFERENCE]) AS INVDATE
WHERE CCN.[COMPANY] = CC.[COMPANY]
AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS IN (SELECT DISTINCT ([ACCOUNT REFERENCE])
FROM INVOICEDATA)
AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS = INVDATE.[ACCOUNT REFERENCE]
GROUP BY CC.[COMPANY],CC.[ACCOUNT REFERENCE]
ORDER BY CC.COMPANY ASC
Mitesh
Well how about getting rid of:
- (select * from customer) -- just use a simple join to customer
- get rid of the collate statements in your where clauses.
Also, you'll need to add INVDATE.[LASTORDERDATE] to your group by statement.
SELECT MAX([DATE NOTE ADDED]) AS LASTDATE,
CC.[COMPANY],
CC.[ACCOUNT REFERENCE],
INVDATE.[LASTORDERDATE]
FROM CUSTOMERCONTACTNOTES AS CCN,
CUSTOMER AS CC,
(SELECT MAX([INVOICE DATE]) AS LASTORDERDATE,
[ACCOUNT REFERENCE]
FROM INVOICEDATA
GROUP BY [ACCOUNT REFERENCE]) AS INVDATE
WHERE CCN.[COMPANY] = CC.[COMPANY]
AND CC.[ACCOUNT REFERENCE] IN (SELECT DISTINCT ([ACCOUNT REFERENCE]) FROM INVOICEDATA)
AND CC.[ACCOUNT REFERENCE] = INVDATE.[ACCOUNT REFERENCE]
GROUP BY CC.[COMPANY],CC.[ACCOUNT REFERENCE], INVDATE.[LASTORDERDATE]
ORDER BY CC.COMPANY ASC|||Thanks Phill,
Your solution was just spot on.
Just out of curosity, how do you read any SQL Query, for e.g. like mine and find what is wrong.
Mitesh|||Experience, I guess. When you work with it enough, you can just "read" SQL and understand what's going on.
I really don't think you need the "select distinct [account reference] from invoicedata" query in your where clause though. You already have a distinct list from the INVDATE query in your FROM statement. Your where clause should probably be:
WHERE CCN.[COMPANY] = CC.[COMPANY]
AND CC.[ACCOUNT REFERENCE] = INVDATE.[ACCOUNT REFERENCE]
Wednesday, March 21, 2012
Reset Autonumber Value in MS SQL 2k
I am learning the differences between Access and SQL the hard way.
SQL will not simply allow me, through an append query. to reset the starting value of my issueing ID field (autonumber). The new value is always higher than the last sequenced range.
I do not have experience with triggers, and from what I am reading this may be my solution.
Basically, I have a reference table that issues ID's when an ID is needed (table B). I only know the current range (min and max) numbers. Once this range is completely allocated. A new range of numbers is entered. New ranges can be 700 IDs or 7000 IDs.. never know until they are issued from other source.
How to reset the value for my current autonumber (increment) field in SQL.I am assuming that you are referring to an identity column, if so have a read about: DBCC CHECKIDENT in BOL.
Hope that helps.
Originally posted by jctrips
I am learning the differences between Access and SQL the hard way.
SQL will not simply allow me, through an append query. to reset the starting value of my issueing ID field (autonumber). The new value is always higher than the last sequenced range.
I do not have experience with triggers, and from what I am reading this may be my solution.
Basically, I have a reference table that issues ID's when an ID is needed (table B). I only know the current range (min and max) numbers. Once this range is completely allocated. A new range of numbers is entered. New ranges can be 700 IDs or 7000 IDs.. never know until they are issued from other source.
How to reset the value for my current autonumber (increment) field in SQL.|||THis definitely helps for identifying the seed. And I can see how to RESEED.
But how now to automate this based on new Range inputted by Admin User on Front End?|||DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value) can be specified as per the parameters you accept.
SQL will not simply allow me, through an append query. to reset the starting value of my issueing ID field (autonumber). The new value is always higher than the last sequenced range.
I do not have experience with triggers, and from what I am reading this may be my solution.
Basically, I have a reference table that issues ID's when an ID is needed (table B). I only know the current range (min and max) numbers. Once this range is completely allocated. A new range of numbers is entered. New ranges can be 700 IDs or 7000 IDs.. never know until they are issued from other source.
How to reset the value for my current autonumber (increment) field in SQL.I am assuming that you are referring to an identity column, if so have a read about: DBCC CHECKIDENT in BOL.
Hope that helps.
Originally posted by jctrips
I am learning the differences between Access and SQL the hard way.
SQL will not simply allow me, through an append query. to reset the starting value of my issueing ID field (autonumber). The new value is always higher than the last sequenced range.
I do not have experience with triggers, and from what I am reading this may be my solution.
Basically, I have a reference table that issues ID's when an ID is needed (table B). I only know the current range (min and max) numbers. Once this range is completely allocated. A new range of numbers is entered. New ranges can be 700 IDs or 7000 IDs.. never know until they are issued from other source.
How to reset the value for my current autonumber (increment) field in SQL.|||THis definitely helps for identifying the seed. And I can see how to RESEED.
But how now to automate this based on new Range inputted by Admin User on Front End?|||DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value) can be specified as per the parameters you accept.
Reserved words
Is there a query that i can run to find reserved words in the tables present
in my database?
Thanks!HP wrote on Tue, 3 Jan 2006 09:21:03 -0800:
> Is there a query that i can run to find reserved words in the tables
> present in my database?
> Thanks!
Much easier to just make sure you put [] around all table and column names
in your views/stored procs/queries, that way you don't risk problems in the
future when you migrate to the next SQL Server version and find they've
added more reserved words.
Dan|||HP (HP@.discussions.microsoft.com) writes:
> Is there a query that i can run to find reserved words in the tables
> present in my database?
There is a page in Books Online with all the reserved keywords, and
two more lists of ODBC keywords and possible keywords for the future.
With some cutting and pasting, and then finding and replacing in a
text editor, you can get those words into a table.
Then you can run this query:
SELECT object_name(c.id), c.name
FROM syscolumns c
WHERE EXISTS (SELECT *
FROM keywords k
WHERE c.name = k.keyword)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql
in my database?
Thanks!HP wrote on Tue, 3 Jan 2006 09:21:03 -0800:
> Is there a query that i can run to find reserved words in the tables
> present in my database?
> Thanks!
Much easier to just make sure you put [] around all table and column names
in your views/stored procs/queries, that way you don't risk problems in the
future when you migrate to the next SQL Server version and find they've
added more reserved words.
Dan|||HP (HP@.discussions.microsoft.com) writes:
> Is there a query that i can run to find reserved words in the tables
> present in my database?
There is a page in Books Online with all the reserved keywords, and
two more lists of ODBC keywords and possible keywords for the future.
With some cutting and pasting, and then finding and replacing in a
text editor, you can get those words into a table.
Then you can run this query:
SELECT object_name(c.id), c.name
FROM syscolumns c
WHERE EXISTS (SELECT *
FROM keywords k
WHERE c.name = k.keyword)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql
Tuesday, March 20, 2012
Re-Running Query
Presumably, an RDL data set is only queried once before generating a report. And presumably, this is true even if the data set feeds a list control. However, I'd like to know if I can cause a data set to be queried repeatedly from a list control. I need to do this, because the data set contains aggregate functions that would be impacted by each "pass" the list control would make.
Is it possible to make a data set queried repeatedly in this fashion?
What list control, what environment, what event of the control are you looking to handle here?
IAC "presumably" you want to change a parameter to the report based on the currently-selected item or items in this list, right?
You re-send the parameters and (usually) call ReportRefresh()... but it is hard to say exactly without, um, presuming too much about your scenario <s>.
>L<
requirements to connect to a named instance of MSDE 2.0
What is the propery syntax to establish a connection with a named instance
of MSDE on a server from a client workstation using the SQL Query analyzer
(or EM for that matter)?
I have tried "myServer\myInstance" to no positive effect.
Could I do this via TCP from a remote PC? What would be the syntax for
that?
Thanks.
Hi
"myServer\myInstance, portnumber"
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"astro" <astro@.bcmn.com> wrote in message
news:VorVd.24398$Sq5.7292@.twister.rdc-kc.rr.com...
> What is the propery syntax to establish a connection with a named instance
> of MSDE on a server from a client workstation using the SQL Query analyzer
> (or EM for that matter)?
> I have tried "myServer\myInstance" to no positive effect.
> Could I do this via TCP from a remote PC? What would be the syntax for
> that?
> Thanks.
>
>
of MSDE on a server from a client workstation using the SQL Query analyzer
(or EM for that matter)?
I have tried "myServer\myInstance" to no positive effect.
Could I do this via TCP from a remote PC? What would be the syntax for
that?
Thanks.
Hi
"myServer\myInstance, portnumber"
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"astro" <astro@.bcmn.com> wrote in message
news:VorVd.24398$Sq5.7292@.twister.rdc-kc.rr.com...
> What is the propery syntax to establish a connection with a named instance
> of MSDE on a server from a client workstation using the SQL Query analyzer
> (or EM for that matter)?
> I have tried "myServer\myInstance" to no positive effect.
> Could I do this via TCP from a remote PC? What would be the syntax for
> that?
> Thanks.
>
>
requirements to connect to a named instance of MSDE 2.0
What is the propery syntax to establish a connection with a named instance
of MSDE on a server from a client workstation using the SQL Query analyzer
(or EM for that matter)?
I have tried "myServer\myInstance" to no positive effect.
Could I do this via TCP from a remote PC? What would be the syntax for
that?
Thanks.Hi
"myServer\myInstance, portnumber"
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"astro" <astro@.bcmn.com> wrote in message
news:VorVd.24398$Sq5.7292@.twister.rdc-kc.rr.com...
> What is the propery syntax to establish a connection with a named instance
> of MSDE on a server from a client workstation using the SQL Query analyzer
> (or EM for that matter)?
> I have tried "myServer\myInstance" to no positive effect.
> Could I do this via TCP from a remote PC? What would be the syntax for
> that?
> Thanks.
>
>
of MSDE on a server from a client workstation using the SQL Query analyzer
(or EM for that matter)?
I have tried "myServer\myInstance" to no positive effect.
Could I do this via TCP from a remote PC? What would be the syntax for
that?
Thanks.Hi
"myServer\myInstance, portnumber"
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"astro" <astro@.bcmn.com> wrote in message
news:VorVd.24398$Sq5.7292@.twister.rdc-kc.rr.com...
> What is the propery syntax to establish a connection with a named instance
> of MSDE on a server from a client workstation using the SQL Query analyzer
> (or EM for that matter)?
> I have tried "myServer\myInstance" to no positive effect.
> Could I do this via TCP from a remote PC? What would be the syntax for
> that?
> Thanks.
>
>
Monday, March 12, 2012
requesting query optimization help
so here are the tables, with a little info about each one:
create table areas (
areaid uniqueidentifier primary key not null, -- PK, clustered
areanumber int identity(1, 1) not null, -- indexed
areaname varchar(100) not null,
regionnumber int not null) -- FK to regions table PK
the areas table has about 16000 rows, and three unindexed columns not
listed here.
create table regions (
regionnumber int identity(1, 1) primary key not null, -- PK, clustered
regionname varchar(100) not null,
parentregionnumber int not null) -- FK to regions table PK
the regions table has about 800 rows, and about 12 columns not listed
here
create table regionrelationships (
regionnumber int primary key not null,
relatedregionnumber int primary key not null) -- PK is clustered
the regionrelationships table has about 1700 rows, and no unlisted
columns
and here are the various versions of the stored procedure query i'm
trying to optimize with the execution plan costs listed with each one.
the original stored procedure body looked liked this:
SELECT DISTINCT a.areanumber, a.areaname
FROM areas AS a WITH (NOLOCK)
WHERE a.regionnumber = 56
ORDER BY a.areaname
cost: .205
then business rules caused a change in the stored procedure body, and
the cost went way up:
SELECT DISTINCT a.areanumber, a.areaname
FROM areas AS a WITH (NOLOCK)
WHERE a.regionnumber = 56 OR a.regionnumber IN (
SELECT relatedregionnumber
FROM regionrelationships
WHERE regionnumber = 56)
ORDER BY a.areaname
cost: .518
i've tweaked a bunch of stuff, and this is the best i've been able to
do so far:
SELECT DISTINCT a.areanumber, a.areaname
FROM areas AS a WITH (NOLOCK)
LEFT JOIN regionrelationships AS r
ON r.relatedregionnumber = a.regionnumber
WHERE a.regionnumber = 56 OR r.regionnumber = 56
ORDER BY areaname
cost: .417
most of the remaining cost is in a hash right outer join (35%, mostly
CPU) and a cluster scan on areas (44%, mostly I/O)
i've tried a lot of things, including:
* using the areanumber as the primary key of the areas table instead of
the areaid, to make the cluster index smaller, to try to make cluster
scans faster (no effect at all)
* creating clustered indexes on columns other than the primary keys,
such as the regionrelationships regionnumber column, to try to make
hash scans faster (no effect at all)
so i'm just wondering, i'm new to this aspect of optimization. is there
anything here that stands out as something i can do to improve the
performance of this stored procedure? hopefully i've provided enough
information.
thanks in advance for any help
jasonOn 15 Mar 2006 13:45:49 -0800, jason wrote:
(snip)
>then business rules caused a change in the stored procedure body, and
>the cost went way up:
>SELECT DISTINCT a.areanumber, a.areaname
>FROM areas AS a WITH (NOLOCK)
>WHERE a.regionnumber = 56 OR a.regionnumber IN (
> SELECT relatedregionnumber
> FROM regionrelationships
> WHERE regionnumber = 56)
>ORDER BY a.areaname
>cost: .518
>
>i've tweaked a bunch of stuff, and this is the best i've been able to
>do so far:
(snip)
Hi Jason,
Try this version instead:
SELECT a.areanumber, a.areaname
FROM areas AS a WITH (NOLOCK)
WHERE a.regionnumber = 56
UNION ALL
SELECT a.areanumber, a.areaname
FROM areas AS a
INNER JOIN regionrelationships AS r
ON r.relatedregionnumber = a.regionnumber
WHERE r.regionnumber = 56
AND r.relatedregionnumber <> 56
ORDER BY areaname
Indexing suggestions:
- In regionrelationships, make sure that regionnumber is the first of
the two columns in the primary key.
- In areas, have a clustered index with regionnumber as first column. Or
(maybe even better) a nonclustered index on (regionnumber, areaname,
areanumber).
Hugo Kornelis, SQL Server MVP|||thank you very much sir!
create table areas (
areaid uniqueidentifier primary key not null, -- PK, clustered
areanumber int identity(1, 1) not null, -- indexed
areaname varchar(100) not null,
regionnumber int not null) -- FK to regions table PK
the areas table has about 16000 rows, and three unindexed columns not
listed here.
create table regions (
regionnumber int identity(1, 1) primary key not null, -- PK, clustered
regionname varchar(100) not null,
parentregionnumber int not null) -- FK to regions table PK
the regions table has about 800 rows, and about 12 columns not listed
here
create table regionrelationships (
regionnumber int primary key not null,
relatedregionnumber int primary key not null) -- PK is clustered
the regionrelationships table has about 1700 rows, and no unlisted
columns
and here are the various versions of the stored procedure query i'm
trying to optimize with the execution plan costs listed with each one.
the original stored procedure body looked liked this:
SELECT DISTINCT a.areanumber, a.areaname
FROM areas AS a WITH (NOLOCK)
WHERE a.regionnumber = 56
ORDER BY a.areaname
cost: .205
then business rules caused a change in the stored procedure body, and
the cost went way up:
SELECT DISTINCT a.areanumber, a.areaname
FROM areas AS a WITH (NOLOCK)
WHERE a.regionnumber = 56 OR a.regionnumber IN (
SELECT relatedregionnumber
FROM regionrelationships
WHERE regionnumber = 56)
ORDER BY a.areaname
cost: .518
i've tweaked a bunch of stuff, and this is the best i've been able to
do so far:
SELECT DISTINCT a.areanumber, a.areaname
FROM areas AS a WITH (NOLOCK)
LEFT JOIN regionrelationships AS r
ON r.relatedregionnumber = a.regionnumber
WHERE a.regionnumber = 56 OR r.regionnumber = 56
ORDER BY areaname
cost: .417
most of the remaining cost is in a hash right outer join (35%, mostly
CPU) and a cluster scan on areas (44%, mostly I/O)
i've tried a lot of things, including:
* using the areanumber as the primary key of the areas table instead of
the areaid, to make the cluster index smaller, to try to make cluster
scans faster (no effect at all)
* creating clustered indexes on columns other than the primary keys,
such as the regionrelationships regionnumber column, to try to make
hash scans faster (no effect at all)
so i'm just wondering, i'm new to this aspect of optimization. is there
anything here that stands out as something i can do to improve the
performance of this stored procedure? hopefully i've provided enough
information.
thanks in advance for any help
jasonOn 15 Mar 2006 13:45:49 -0800, jason wrote:
(snip)
>then business rules caused a change in the stored procedure body, and
>the cost went way up:
>SELECT DISTINCT a.areanumber, a.areaname
>FROM areas AS a WITH (NOLOCK)
>WHERE a.regionnumber = 56 OR a.regionnumber IN (
> SELECT relatedregionnumber
> FROM regionrelationships
> WHERE regionnumber = 56)
>ORDER BY a.areaname
>cost: .518
>
>i've tweaked a bunch of stuff, and this is the best i've been able to
>do so far:
(snip)
Hi Jason,
Try this version instead:
SELECT a.areanumber, a.areaname
FROM areas AS a WITH (NOLOCK)
WHERE a.regionnumber = 56
UNION ALL
SELECT a.areanumber, a.areaname
FROM areas AS a
INNER JOIN regionrelationships AS r
ON r.relatedregionnumber = a.regionnumber
WHERE r.regionnumber = 56
AND r.relatedregionnumber <> 56
ORDER BY areaname
Indexing suggestions:
- In regionrelationships, make sure that regionnumber is the first of
the two columns in the primary key.
- In areas, have a clustered index with regionnumber as first column. Or
(maybe even better) a nonclustered index on (regionnumber, areaname,
areanumber).
Hugo Kornelis, SQL Server MVP|||thank you very much sir!
Requesting Advanced T-SQL help
Need some guru help with a monster T-SQL query. Trying to make it more
efficient, possibly less code, and anything else I can do to it to make it
run faster. This is an SP and Tables created by the person I replaced, but
I need to deal with it...for now.
I have a Status table that has 50 columns and currently contains 3.3+
million records in it. I don't have the luxury of altering the table, so I
am stuck with this thing (table structure attached at the end of this post).
Still being relatively new here, I wouldn't even know where to begin
changing it if I could. Anyhow...
Quick description: Our website provides a search utility that deals with
this one table, and is a typical search in that you can search by name, a
date range, member ID, etc. There is also a ListBox that displays Doctors
the user view info on, which allows multiple-selection. Making the
appropriate selections, providing any other search criteria, the search app
queries against this rather large table and returns a listing of associated
records. That's the goal.
The ugly truth...
The query that fires off from this search is equally gargantuan (SP code
posted below) and was taking for-ev-er to run. I was able to play around
with the indexes on the table (one clustered and one non-clustered) which
dropped the execution time of a single run to an average of 5 seconds. If I
tried any additional indexes, or modifying these two, it only seemed to make
the execution take longer. In order to search on multiple doctor numbers
(that pesky multi-select listbox), the .asp code runs the stored proc once
for each doctor number and then appends each set of records that comes back
and displays them in a 'list' on the webpage. But when it takes at best 4~5
seconds per sp execution, selecting 3 or 4 Doctors will have the users
frustrated beyond belief waiting for the search results.
So, I ask for the help of T-SQL Gurus here on improving this monster. I
have tried, but am not getting anywhere. Here is the code, and I hope to
get some suggestions. Thanks
-- Andrew
P.S.: I hope the below is enough as I cannot share the data in the table due
to privacy concerns.
Table Structure
CREATE TABLE [MedMC_Claim_Status] (
[TMBR#] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MMFNAM] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MMINIT] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MMLNAM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TADOC#] [decimal](9, 0) NULL ,
[AHCCCS_ID] [decimal](9, 0) NULL ,
[DFNAM] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DINIT] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DLNAM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DSSN#] [decimal](9, 0) NULL ,
[DFTAX#] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TPAYE#] [decimal](9, 0) NULL ,
[PDFNAM] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDINIT] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDLNAM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TCLAIM] [decimal](9, 0) NOT NULL ,
[TSRVDT] [smalldatetime] NULL ,
[TSRVTDT] [smalldatetime] NULL ,
[TSTATS] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TSTATS_DESC] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TFLAG4] [decimal](6, 0) NOT NULL ,
[TRCVDT] [smalldatetime] NULL ,
[TPROC] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TMOD2] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TMOD3] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TREVCD] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TQTY] [decimal](7, 0) NULL ,
[TAUTH#] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TADDDT] [smalldatetime] NULL ,
[TCHRGS] [decimal](8, 2) NULL ,
[TAUTH] [decimal](8, 2) NULL ,
[TCOPAY] [decimal](8, 2) NULL ,
[TCOB] [decimal](8, 2) NULL ,
[TFLD16] [decimal](8, 2) NULL ,
[TWHIAM] [decimal](8, 2) NULL ,
[TDIS] [decimal](8, 2) NULL ,
[TDED] [decimal](8, 2) NULL ,
[TPADDT] [smalldatetime] NULL ,
[TCHK#] [decimal](6, 0) NULL ,
[TINV#] [decimal](6, 0) NULL ,
[DENITY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDENITY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HLDESC] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HFLAG2] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DADR1] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DADR2] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DCITY] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DSTAT] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DZIPC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dtmCrea_Date] [datetime] NULL CONSTRAINT
[DF_MedMC_Claim_Status_dtmCrea_Date] DEFAULT (getdate())
) ON [PRIMARY]
GO
Table Indexes
CREATE CLUSTERED INDEX [CL_MedMCClaimStatus_TmbrNum,TDocNum,TCl
aim,TFlag4]
ON [dbo].[MedMC_Claim_Status]([TMBR#], [TADOC#], [TCLAIM], [TFLAG4]) ON
[PRIMARY]
CREATE INDEX [NCL_MedMCClaimStatus_MMFnam,MMLnam,AHCC
CSID,DFTaxNum] ON
[dbo].[MedMC_Claim_Status]([MMFNAM], [MMLNAM], [AHCCCS_ID], [DFTAX#]) ON
[PRIMARY]
GO
Search Query
CREATE PROCEDURE stp_MedMC_Claim_1st_Level_Retrieve
@.TADOC# DECIMAL(9,0) = NULL,
@.DFTAX# char(12) = NULL,
@.TSRVDTfrom datetime = NULL, --'1/1/2005'
@.TSRVDTto datetime = NULL,
@.FINAL_STATUS VARCHAR(20) = null,
@.TMBR# VARCHAR(12) = NULL,
@.MMFNAM VARCHAR(12) = NULL,
@.MMLNAM VARCHAR(12) = NULL,
@.Loc decimal(9,0) = null
AS
set nocount on
SELECT * FROM
(
select
A.TMBR#,
A.TCLAIM,
A.TFLAG4,
C.TOTALCOUNT,
REPLACE(RTRIM(LTRIM(A.MMFNAM)) + ' ' + RTRIM(LTRIM(A.MMINIT)) + '. ', ' .
', ' ') + RTRIM(LTRIM(A.MMLNAM)) AS MMNAME,
A.TADOC#,
A.DFTAX# ,
A.AHCCCS_ID,
A.DADR1,
CASE
WHEN A.DENITY = 'P' THEN REPLACE(RTRIM(LTRIM(A.DFNAM)) + ' ' +
RTRIM(LTRIM(A.DINIT)) + '. ', ' . ', ' ') + RTRIM(LTRIM(A.DLNAM))
ELSE RTRIM(LTRIM(A.DLNAM)) + RTRIM(LTRIM(A.DFNAM))
END AS DNAME,
A.TPAYE#,
CASE
WHEN A.PDENITY = 'P' AND RTRIM(LTRIM(A.PDINIT)) <> '' THEN
REPLACE(RTRIM(LTRIM(A.PDFNAM)) + ' ' + RTRIM(LTRIM(A.PDINIT)) + '. ', ' . ',
' ') + RTRIM(LTRIM(A.PDLNAM))
ELSE RTRIM(LTRIM(A.PDLNAM)) + RTRIM(LTRIM(A.PDFNAM))
END AS PDNAME,
CONVERT(varchar(10), A.TSRVDT, 101) as TSRVDT,
A.TSTATS,
A.TSTATS_DESC,
B.TTFLD16,
case
when D.S1 > 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 = 0 AND D.S5 = 0 then
'RECEIVED'
when D.S1 = 0 AND D.S2 > 0 then 'PROCESSING'
when D.S1 = 0 AND D.S2 = 0 AND D.S3 > 0 then 'PAID'
when D.S1 = 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 > 0 then 'DENIED'
when D.S1 = 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 = 0 AND D.S5 > 0 then
'VOID'
END AS FINAL_STATUS
from MedMC.dbo.MedMC_Claim_Status A
INNER JOIN
(
select TMBR#, TCLAIM, MAX(TFLAG4) AS TFLAG4
from MedMC.dbo.MedMC_Claim_Status
WHERE AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND DFTAX# = COALESCE(@.DFTAX#, DFTAX#)
and TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and coalesce(@.TSRVDTto,
@.TSRVDTfrom, TSRVDT)
AND TMBR# = COALESCE(@.TMBR#, TMBR#)
AND MMFNAM LIKE COALESCE(@.MMFNAM, MMFNAM)
AND MMLNAM LIKE COALESCE(@.MMLNAM, MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
GROUP BY TMBR#, TCLAIM
) E
INNER JOIN
(
SELECT TMBR#, TCLAIM, SUM(TFLD16) AS TTFLD16
FROM MedMC.dbo.MedMC_Claim_Status
WHERE AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND DFTAX# = COALESCE(@.DFTAX#, DFTAX#)
and TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and coalesce(@.TSRVDTto,
@.TSRVDTfrom, TSRVDT)
AND TMBR# = COALESCE(@.TMBR#, TMBR#)
AND MMFNAM LIKE COALESCE(@.MMFNAM, MMFNAM)
AND MMLNAM LIKE COALESCE(@.MMLNAM, MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
GROUP BY TMBR#, TCLAIM
) B
INNER JOIN
(
select TMBR#, TCLAIM, COUNT(*) AS TOTALCOUNT
from MedMC.dbo.MedMC_Claim_Status
WHERE AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND DFTAX# = COALESCE(@.DFTAX#, DFTAX#)
and TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and coalesce(@.TSRVDTto,
@.TSRVDTfrom, TSRVDT)
AND TMBR# = COALESCE(@.TMBR#, TMBR#)
AND MMFNAM LIKE COALESCE(@.MMFNAM, MMFNAM)
AND MMLNAM LIKE COALESCE(@.MMLNAM, MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
GROUP BY TMBR#, TCLAIM
) C
INNER JOIN
(
select
TMBR#, TCLAIM,
SUM(CASE when TSTATS = '1' then 1 else 0 END) AS S1,
SUM(CASE when TSTATS = '2' then 1 else 0 END) AS S2,
SUM(CASE when TSTATS = '3' then 1 else 0 END) AS S3,
SUM(CASE when TSTATS = '4' then 1 else 0 END) AS S4,
SUM(CASE when TSTATS = '5' then 1 else 0 END) AS S5
from MedMC.dbo.MedMC_Claim_Status
WHERE AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND DFTAX# = COALESCE(@.DFTAX#, DFTAX#)
and TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and coalesce(@.TSRVDTto,
@.TSRVDTfrom, TSRVDT)
AND TMBR# = COALESCE(@.TMBR#, TMBR#)
AND MMFNAM LIKE COALESCE(@.MMFNAM, MMFNAM)
AND MMLNAM LIKE COALESCE(@.MMLNAM, MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
GROUP BY TMBR#, TCLAIM
) D
ON C.TMBR# = D.TMBR# AND C.TCLAIM = D.TCLAIM
ON B.TMBR# = C.TMBR# and B.TCLAIM = C.TCLAIM
ON E.TMBR# = B.TMBR# and E.TCLAIM = B.TCLAIM
ON A.TMBR# = E.TMBR# AND A.TCLAIM = E.TCLAIM AND A.TFLAG4 = E.TFLAG4
where 1 = 1
AND A.AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND A.DFTAX# = COALESCE(@.DFTAX#, A.DFTAX#)
AND TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and coalesce(@.TSRVDTto,
@.TSRVDTfrom, TSRVDT)
AND A.MMFNAM LIKE COALESCE(@.MMFNAM, A.MMFNAM)
AND A.MMLNAM LIKE COALESCE(@.MMLNAM, A.MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
) T
where 1 = 1
AND FINAL_STATUS = COALESCE(UPPER(@.FINAL_STATUS), FINAL_STATUS)
ORDER BY TSRVDT desc
set nocount offOn Thu, 11 May 2006 15:41:13 -0700, Andrew wrote:
>Need some guru help with a monster T-SQL query. Trying to make it more
>efficient, possibly less code, and anything else I can do to it to make it
>run faster. This is an SP and Tables created by the person I replaced, but
>I need to deal with it...for now.
>I have a Status table that has 50 columns and currently contains 3.3+
>million records in it. I don't have the luxury of altering the table, so I
>am stuck with this thing (table structure attached at the end of this post)
.
>Still being relatively new here, I wouldn't even know where to begin
>changing it if I could. Anyhow...
Hi Andrew,
First some quick comments on the table. Keep them until you DO have time
to improve the schema.
* FIND AND DECLARE THE KEY!!!!!! You curently have two nonunique
indexes, but no PRIMARY KEY constraint, no UNIQUE constraint and no
UNIQUE indexes. This is an open invitation to duplicated data. It also
deprives the query optimizer of essential knowledge about your data that
it can use to find more efficient execution plans. And it limits my
possibilities to modify your query.
* Most of the columns allow NULLs. Allowing NULLs on such a high
fraction of the columns is quite unlikely. Change as many of them to NOT
NULL as you can.
* Consider changing the decimal (x, 0) columns to int.
* Change the varchar(1) columns to char(1), unless you really need to
store the difference between '' (empty string) and ' ' (one space). Each
varchar column incurs two bytes of overhead to store the length. No
problem for longer varchar columns (you'll save more than two bytes on
average), but a waste for short strings.
>Quick description: Our website provides a search utility that deals with
>this one table, and is a typical search in that you can search by name, a
>date range, member ID, etc.
Read Erlands article: http://www.sommarskog.se/dyn-search.html. There
are many ways to improve performance of flexible search queries such as
yours. In the rest of my reply, I won't address this part of your query
since Erland already covered that very well.
One thing I will mention because it can really make a HUGE difference:
do you really need a LIKE search in all these cases? Searching for
equality is often lots faster. A LIKE search for a pattern that starts
with % or _ (or a pattern unknown to the optimizer, e.g. because of a
variable) almost always results in table or index scans.
(snip)
>The query that fires off from this search is equally gargantuan (SP code
>posted below) and was taking for-ev-er to run. I was able to play around
>with the indexes on the table (one clustered and one non-clustered) which
>dropped the execution time of a single run to an average of 5 seconds.
Congratulations - that's a great achievement! But no reason to stop
there.
> If I
>tried any additional indexes, or modifying these two, it only seemed to mak
e
>the execution take longer.
Then you have probably found the best set of indexes for this query. I
haven't looked at the indexes and their usability; I concentrated on
rewriting the query itself. Maybe you should fiddle the indexes some
more after testing my version of the query (below).
> In order to search on multiple doctor numbers
>(that pesky multi-select listbox), the .asp code runs the stored proc once
>for each doctor number and then appends each set of records that comes back
>and displays them in a 'list' on the webpage. But when it takes at best 4~
5
>seconds per sp execution, selecting 3 or 4 Doctors will have the users
>frustrated beyond belief waiting for the search results.
Indeed. Luckily, Erland has covered this common problem as well. Check
out this article: http://www.sommarskog.se/arrays-in-sql.html. I suggest
that you use one of the techniques to get the selected doctor numbers in
a table, then join against that table. This is another aspect of your
question that I won't cover any further.
>So, I ask for the help of T-SQL Gurus here on improving this monster. I
>have tried, but am not getting anywhere. Here is the code, and I hope to
>get some suggestions. Thanks
(snip H U G E query)
The first thing that struck me is that you have a totall of four derived
tables that all use the EXACT same FROM and WHERE clause (unless I am
overlooking some minor difference?). It looks as if your predecessor
thought that you were limited to one aggregate per subquery. You can
replace these four subqueries with one. That also rids you of the (in
this case) unneeded non-standard ordering of JOIN and ON clauses. Was
your predecessor coding for job security?
The main query also repeats the exact same WHERE clause (apart from the
totally unneeded 1 = 1), but it lacks the GROUP BY that is present in
the derived tables. I can't say that I like this - you might end up with
several detail rows that each show the same aggregated results in
addition to the different details. I guess "AND A.TFLAG4 = E.TFLAG4" is
intended to solve this, but I'm not sure if this is failsafe. This is
where I miss knowing the keys!
Another suspicious part is the CASE statement for the status:
> case
> when D.S1 > 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 = 0 AND D.S5 = 0 then
>'RECEIVED'
> when D.S1 = 0 AND D.S2 > 0 then 'PROCESSING'
> when D.S1 = 0 AND D.S2 = 0 AND D.S3 > 0 then 'PAID'
> when D.S1 = 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 > 0 then 'DENIED'
> when D.S1 = 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 = 0 AND D.S5 > 0 then
>'VOID'
> END AS FINAL_STATUS
Are you aware that many combinations of S1, S2, ..., and S5 will fall
through all the WHEN clauses and yield a NULL result? (For instance if
both S1 and S2 are >0, or when all are =0, to name just two examples). I
don't know your data, so you'll have to decide if this is a problem. A
possible rewrite (depending on your data -- don't copy blindly, it is
not the exact same logic!!) that takes advantage of the order of
evaluation of the CASE expression would be:
CASE
WHEN D.S1 > 0 THEN 'Received'
WHEN D.S2 > 0 THEN 'Processing'
WHEN D.S3 > 0 THEN 'Paid'
WHEN D.S4 > 0 THEN 'Denied'
ELSE 'Void'
END
But in the rewritten query bellow, I'll retain the original.
The only "safe" improvement of your query that I see is the removal of
the duplicated derived tables. I expect a good performance improvement
from that. I'm quite sure that there are more possibilities to optimize
the query even further, but they require a better understanding of the
data than I have (at least knowledge of the keys!).
Here's my suggestion for a rewritten query:
SELECT * FROM
(
select
A.TMBR#,
A.TCLAIM,
A.TFLAG4,
B.TOTALCOUNT,
REPLACE(RTRIM(LTRIM(A.MMFNAM)) + ' ' + RTRIM(LTRIM(A.MMINIT)) + '. ',
' . ', ' ') + RTRIM(LTRIM(A.MMLNAM)) AS MMNAME,
A.TADOC#,
A.DFTAX# ,
A.AHCCCS_ID,
A.DADR1,
CASE
WHEN A.DENITY = 'P'
THEN REPLACE(RTRIM(LTRIM(A.DFNAM)) + ' ' + RTRIM(LTRIM(A.DINIT)) + '.
', ' . ', ' ') + RTRIM(LTRIM(A.DLNAM))
ELSE RTRIM(LTRIM(A.DLNAM)) + RTRIM(LTRIM(A.DFNAM))
END AS DNAME,
A.TPAYE#,
CASE
WHEN A.PDENITY = 'P' AND RTRIM(LTRIM(A.PDINIT)) <> ''
THEN REPLACE(RTRIM(LTRIM(A.PDFNAM)) + ' ' + RTRIM(LTRIM(A.PDINIT)) +
'. ', ' . ', ' ') + RTRIM(LTRIM(A.PDLNAM))
ELSE RTRIM(LTRIM(A.PDLNAM)) + RTRIM(LTRIM(A.PDFNAM))
END AS PDNAME,
CONVERT(varchar(10), A.TSRVDT, 101) as TSRVDT,
A.TSTATS,
A.TSTATS_DESC,
B.TTFLD16,
case
when B.S1 > 0 AND B.S2 = 0 AND B.S3 = 0 AND B.S4 = 0 AND B.S5 = 0
then 'RECEIVED'
when B.S1 = 0 AND B.S2 > 0 then 'PROCESSING'
when B.S1 = 0 AND B.S2 = 0 AND B.S3 > 0 then 'PAID'
when B.S1 = 0 AND B.S2 = 0 AND B.S3 = 0 AND B.S4 > 0 then 'DENIED'
when B.S1 = 0 AND B.S2 = 0 AND B.S3 = 0 AND B.S4 = 0 AND B.S5 > 0
then 'VOID'
END AS FINAL_STATUS
from MedMC.dbo.MedMC_Claim_Status A
INNER JOIN
(
select TMBR#, TCLAIM,
MAX(TFLAG4) AS TFLAG4,
SUM(TFLD16) AS TTFLD16,
COUNT(*) AS TOTALCOUNT,
SUM(CASE when TSTATS = '1' then 1 else 0 END) AS S1,
SUM(CASE when TSTATS = '2' then 1 else 0 END) AS S2,
SUM(CASE when TSTATS = '3' then 1 else 0 END) AS S3,
SUM(CASE when TSTATS = '4' then 1 else 0 END) AS S4,
SUM(CASE when TSTATS = '5' then 1 else 0 END) AS S5
from MedMC.dbo.MedMC_Claim_Status
WHERE AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND DFTAX# = COALESCE(@.DFTAX#, DFTAX#)
and TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and
coalesce(@.TSRVDTto, @.TSRVDTfrom, TSRVDT)
AND TMBR# = COALESCE(@.TMBR#, TMBR#)
AND MMFNAM LIKE COALESCE(@.MMFNAM, MMFNAM)
AND MMLNAM LIKE COALESCE(@.MMLNAM, MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
GROUP BY TMBR#, TCLAIM
) B
ON A.TMBR# = B.TMBR# AND A.TCLAIM = B.TCLAIM AND A.TFLAG4 = B.TFLAG4
WHERE
A.AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND A.DFTAX# = COALESCE(@.DFTAX#, A.DFTAX#)
AND TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and
coalesce(@.TSRVDTto, @.TSRVDTfrom, TSRVDT)
AND A.MMFNAM LIKE COALESCE(@.MMFNAM, A.MMFNAM)
AND A.MMLNAM LIKE COALESCE(@.MMLNAM, A.MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
) T
where FINAL_STATUS = COALESCE(UPPER(@.FINAL_STATUS), FINAL_STATUS)
ORDER BY TSRVDT desc
(Untested, since you didn't post INSERT statements with sample data to
test on and expected results to verify my results).
Hugo Kornelis, SQL Server MVP|||In addition to what Hugo posted, I grabbed the query execution plan and
noticed a lot of table scans and sort operations. If you can figure out a
way to tighten up the WHERE clauses and the indexing a little more to match
these queries, you can probably improve performance dramatically. All those
LTRIM(RTRIM()) and REPLACE() statements in the WHERE clause don't help,
since it has to perform those operations on every single row. The RTRIM's
are superfluous, and why not LTRIM() it at insert/update time?
Take a look at the query execution plan for the best ideas on how to
optimize.
You probably already know it's a bad idea to use a # character in
variable/parameter/column names - I guess the guy you replaced wasn't aware
:)
"Andrew" <AndrewR2k1@.hotmail.com> wrote in message
news:unyFEvUdGHA.5048@.TK2MSFTNGP04.phx.gbl...
> Need some guru help with a monster T-SQL query. Trying to make it more
> efficient, possibly less code, and anything else I can do to it to make it
...|||Another option to explore (after you look at Hugo's and Mike's) is the
issue of your clustered index. You didn't say how often data is
inserted into the primary table, or what your maintenance plan is , but
I'd be willing to bet that with such a wide clustered index, you are
looking at some heavy duty fragmentation. You might try moving your
clustered index to the last column in the table (the one that defaults
to GETDATE()) to see if that improves response; rebuild the current
clustered index as a nonclustered index on those same columns.
Of course, if you rarely insert data, then disregard my advice;
instead, focus on a good defragmentation plan that runs often enough to
keep your indexes in shape.
Stu
efficient, possibly less code, and anything else I can do to it to make it
run faster. This is an SP and Tables created by the person I replaced, but
I need to deal with it...for now.
I have a Status table that has 50 columns and currently contains 3.3+
million records in it. I don't have the luxury of altering the table, so I
am stuck with this thing (table structure attached at the end of this post).
Still being relatively new here, I wouldn't even know where to begin
changing it if I could. Anyhow...
Quick description: Our website provides a search utility that deals with
this one table, and is a typical search in that you can search by name, a
date range, member ID, etc. There is also a ListBox that displays Doctors
the user view info on, which allows multiple-selection. Making the
appropriate selections, providing any other search criteria, the search app
queries against this rather large table and returns a listing of associated
records. That's the goal.
The ugly truth...
The query that fires off from this search is equally gargantuan (SP code
posted below) and was taking for-ev-er to run. I was able to play around
with the indexes on the table (one clustered and one non-clustered) which
dropped the execution time of a single run to an average of 5 seconds. If I
tried any additional indexes, or modifying these two, it only seemed to make
the execution take longer. In order to search on multiple doctor numbers
(that pesky multi-select listbox), the .asp code runs the stored proc once
for each doctor number and then appends each set of records that comes back
and displays them in a 'list' on the webpage. But when it takes at best 4~5
seconds per sp execution, selecting 3 or 4 Doctors will have the users
frustrated beyond belief waiting for the search results.
So, I ask for the help of T-SQL Gurus here on improving this monster. I
have tried, but am not getting anywhere. Here is the code, and I hope to
get some suggestions. Thanks
-- Andrew
P.S.: I hope the below is enough as I cannot share the data in the table due
to privacy concerns.
Table Structure
CREATE TABLE [MedMC_Claim_Status] (
[TMBR#] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MMFNAM] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MMINIT] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MMLNAM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TADOC#] [decimal](9, 0) NULL ,
[AHCCCS_ID] [decimal](9, 0) NULL ,
[DFNAM] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DINIT] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DLNAM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DSSN#] [decimal](9, 0) NULL ,
[DFTAX#] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TPAYE#] [decimal](9, 0) NULL ,
[PDFNAM] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDINIT] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDLNAM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TCLAIM] [decimal](9, 0) NOT NULL ,
[TSRVDT] [smalldatetime] NULL ,
[TSRVTDT] [smalldatetime] NULL ,
[TSTATS] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TSTATS_DESC] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TFLAG4] [decimal](6, 0) NOT NULL ,
[TRCVDT] [smalldatetime] NULL ,
[TPROC] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TMOD2] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TMOD3] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TREVCD] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TQTY] [decimal](7, 0) NULL ,
[TAUTH#] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TADDDT] [smalldatetime] NULL ,
[TCHRGS] [decimal](8, 2) NULL ,
[TAUTH] [decimal](8, 2) NULL ,
[TCOPAY] [decimal](8, 2) NULL ,
[TCOB] [decimal](8, 2) NULL ,
[TFLD16] [decimal](8, 2) NULL ,
[TWHIAM] [decimal](8, 2) NULL ,
[TDIS] [decimal](8, 2) NULL ,
[TDED] [decimal](8, 2) NULL ,
[TPADDT] [smalldatetime] NULL ,
[TCHK#] [decimal](6, 0) NULL ,
[TINV#] [decimal](6, 0) NULL ,
[DENITY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDENITY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HLDESC] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HFLAG2] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DADR1] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DADR2] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DCITY] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DSTAT] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DZIPC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dtmCrea_Date] [datetime] NULL CONSTRAINT
[DF_MedMC_Claim_Status_dtmCrea_Date] DEFAULT (getdate())
) ON [PRIMARY]
GO
Table Indexes
CREATE CLUSTERED INDEX [CL_MedMCClaimStatus_TmbrNum,TDocNum,TCl
aim,TFlag4]
ON [dbo].[MedMC_Claim_Status]([TMBR#], [TADOC#], [TCLAIM], [TFLAG4]) ON
[PRIMARY]
CREATE INDEX [NCL_MedMCClaimStatus_MMFnam,MMLnam,AHCC
CSID,DFTaxNum] ON
[dbo].[MedMC_Claim_Status]([MMFNAM], [MMLNAM], [AHCCCS_ID], [DFTAX#]) ON
[PRIMARY]
GO
Search Query
CREATE PROCEDURE stp_MedMC_Claim_1st_Level_Retrieve
@.TADOC# DECIMAL(9,0) = NULL,
@.DFTAX# char(12) = NULL,
@.TSRVDTfrom datetime = NULL, --'1/1/2005'
@.TSRVDTto datetime = NULL,
@.FINAL_STATUS VARCHAR(20) = null,
@.TMBR# VARCHAR(12) = NULL,
@.MMFNAM VARCHAR(12) = NULL,
@.MMLNAM VARCHAR(12) = NULL,
@.Loc decimal(9,0) = null
AS
set nocount on
SELECT * FROM
(
select
A.TMBR#,
A.TCLAIM,
A.TFLAG4,
C.TOTALCOUNT,
REPLACE(RTRIM(LTRIM(A.MMFNAM)) + ' ' + RTRIM(LTRIM(A.MMINIT)) + '. ', ' .
', ' ') + RTRIM(LTRIM(A.MMLNAM)) AS MMNAME,
A.TADOC#,
A.DFTAX# ,
A.AHCCCS_ID,
A.DADR1,
CASE
WHEN A.DENITY = 'P' THEN REPLACE(RTRIM(LTRIM(A.DFNAM)) + ' ' +
RTRIM(LTRIM(A.DINIT)) + '. ', ' . ', ' ') + RTRIM(LTRIM(A.DLNAM))
ELSE RTRIM(LTRIM(A.DLNAM)) + RTRIM(LTRIM(A.DFNAM))
END AS DNAME,
A.TPAYE#,
CASE
WHEN A.PDENITY = 'P' AND RTRIM(LTRIM(A.PDINIT)) <> '' THEN
REPLACE(RTRIM(LTRIM(A.PDFNAM)) + ' ' + RTRIM(LTRIM(A.PDINIT)) + '. ', ' . ',
' ') + RTRIM(LTRIM(A.PDLNAM))
ELSE RTRIM(LTRIM(A.PDLNAM)) + RTRIM(LTRIM(A.PDFNAM))
END AS PDNAME,
CONVERT(varchar(10), A.TSRVDT, 101) as TSRVDT,
A.TSTATS,
A.TSTATS_DESC,
B.TTFLD16,
case
when D.S1 > 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 = 0 AND D.S5 = 0 then
'RECEIVED'
when D.S1 = 0 AND D.S2 > 0 then 'PROCESSING'
when D.S1 = 0 AND D.S2 = 0 AND D.S3 > 0 then 'PAID'
when D.S1 = 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 > 0 then 'DENIED'
when D.S1 = 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 = 0 AND D.S5 > 0 then
'VOID'
END AS FINAL_STATUS
from MedMC.dbo.MedMC_Claim_Status A
INNER JOIN
(
select TMBR#, TCLAIM, MAX(TFLAG4) AS TFLAG4
from MedMC.dbo.MedMC_Claim_Status
WHERE AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND DFTAX# = COALESCE(@.DFTAX#, DFTAX#)
and TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and coalesce(@.TSRVDTto,
@.TSRVDTfrom, TSRVDT)
AND TMBR# = COALESCE(@.TMBR#, TMBR#)
AND MMFNAM LIKE COALESCE(@.MMFNAM, MMFNAM)
AND MMLNAM LIKE COALESCE(@.MMLNAM, MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
GROUP BY TMBR#, TCLAIM
) E
INNER JOIN
(
SELECT TMBR#, TCLAIM, SUM(TFLD16) AS TTFLD16
FROM MedMC.dbo.MedMC_Claim_Status
WHERE AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND DFTAX# = COALESCE(@.DFTAX#, DFTAX#)
and TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and coalesce(@.TSRVDTto,
@.TSRVDTfrom, TSRVDT)
AND TMBR# = COALESCE(@.TMBR#, TMBR#)
AND MMFNAM LIKE COALESCE(@.MMFNAM, MMFNAM)
AND MMLNAM LIKE COALESCE(@.MMLNAM, MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
GROUP BY TMBR#, TCLAIM
) B
INNER JOIN
(
select TMBR#, TCLAIM, COUNT(*) AS TOTALCOUNT
from MedMC.dbo.MedMC_Claim_Status
WHERE AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND DFTAX# = COALESCE(@.DFTAX#, DFTAX#)
and TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and coalesce(@.TSRVDTto,
@.TSRVDTfrom, TSRVDT)
AND TMBR# = COALESCE(@.TMBR#, TMBR#)
AND MMFNAM LIKE COALESCE(@.MMFNAM, MMFNAM)
AND MMLNAM LIKE COALESCE(@.MMLNAM, MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
GROUP BY TMBR#, TCLAIM
) C
INNER JOIN
(
select
TMBR#, TCLAIM,
SUM(CASE when TSTATS = '1' then 1 else 0 END) AS S1,
SUM(CASE when TSTATS = '2' then 1 else 0 END) AS S2,
SUM(CASE when TSTATS = '3' then 1 else 0 END) AS S3,
SUM(CASE when TSTATS = '4' then 1 else 0 END) AS S4,
SUM(CASE when TSTATS = '5' then 1 else 0 END) AS S5
from MedMC.dbo.MedMC_Claim_Status
WHERE AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND DFTAX# = COALESCE(@.DFTAX#, DFTAX#)
and TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and coalesce(@.TSRVDTto,
@.TSRVDTfrom, TSRVDT)
AND TMBR# = COALESCE(@.TMBR#, TMBR#)
AND MMFNAM LIKE COALESCE(@.MMFNAM, MMFNAM)
AND MMLNAM LIKE COALESCE(@.MMLNAM, MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
GROUP BY TMBR#, TCLAIM
) D
ON C.TMBR# = D.TMBR# AND C.TCLAIM = D.TCLAIM
ON B.TMBR# = C.TMBR# and B.TCLAIM = C.TCLAIM
ON E.TMBR# = B.TMBR# and E.TCLAIM = B.TCLAIM
ON A.TMBR# = E.TMBR# AND A.TCLAIM = E.TCLAIM AND A.TFLAG4 = E.TFLAG4
where 1 = 1
AND A.AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND A.DFTAX# = COALESCE(@.DFTAX#, A.DFTAX#)
AND TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and coalesce(@.TSRVDTto,
@.TSRVDTfrom, TSRVDT)
AND A.MMFNAM LIKE COALESCE(@.MMFNAM, A.MMFNAM)
AND A.MMLNAM LIKE COALESCE(@.MMLNAM, A.MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
) T
where 1 = 1
AND FINAL_STATUS = COALESCE(UPPER(@.FINAL_STATUS), FINAL_STATUS)
ORDER BY TSRVDT desc
set nocount offOn Thu, 11 May 2006 15:41:13 -0700, Andrew wrote:
>Need some guru help with a monster T-SQL query. Trying to make it more
>efficient, possibly less code, and anything else I can do to it to make it
>run faster. This is an SP and Tables created by the person I replaced, but
>I need to deal with it...for now.
>I have a Status table that has 50 columns and currently contains 3.3+
>million records in it. I don't have the luxury of altering the table, so I
>am stuck with this thing (table structure attached at the end of this post)
.
>Still being relatively new here, I wouldn't even know where to begin
>changing it if I could. Anyhow...
Hi Andrew,
First some quick comments on the table. Keep them until you DO have time
to improve the schema.
* FIND AND DECLARE THE KEY!!!!!! You curently have two nonunique
indexes, but no PRIMARY KEY constraint, no UNIQUE constraint and no
UNIQUE indexes. This is an open invitation to duplicated data. It also
deprives the query optimizer of essential knowledge about your data that
it can use to find more efficient execution plans. And it limits my
possibilities to modify your query.
* Most of the columns allow NULLs. Allowing NULLs on such a high
fraction of the columns is quite unlikely. Change as many of them to NOT
NULL as you can.
* Consider changing the decimal (x, 0) columns to int.
* Change the varchar(1) columns to char(1), unless you really need to
store the difference between '' (empty string) and ' ' (one space). Each
varchar column incurs two bytes of overhead to store the length. No
problem for longer varchar columns (you'll save more than two bytes on
average), but a waste for short strings.
>Quick description: Our website provides a search utility that deals with
>this one table, and is a typical search in that you can search by name, a
>date range, member ID, etc.
Read Erlands article: http://www.sommarskog.se/dyn-search.html. There
are many ways to improve performance of flexible search queries such as
yours. In the rest of my reply, I won't address this part of your query
since Erland already covered that very well.
One thing I will mention because it can really make a HUGE difference:
do you really need a LIKE search in all these cases? Searching for
equality is often lots faster. A LIKE search for a pattern that starts
with % or _ (or a pattern unknown to the optimizer, e.g. because of a
variable) almost always results in table or index scans.
(snip)
>The query that fires off from this search is equally gargantuan (SP code
>posted below) and was taking for-ev-er to run. I was able to play around
>with the indexes on the table (one clustered and one non-clustered) which
>dropped the execution time of a single run to an average of 5 seconds.
Congratulations - that's a great achievement! But no reason to stop
there.
> If I
>tried any additional indexes, or modifying these two, it only seemed to mak
e
>the execution take longer.
Then you have probably found the best set of indexes for this query. I
haven't looked at the indexes and their usability; I concentrated on
rewriting the query itself. Maybe you should fiddle the indexes some
more after testing my version of the query (below).
> In order to search on multiple doctor numbers
>(that pesky multi-select listbox), the .asp code runs the stored proc once
>for each doctor number and then appends each set of records that comes back
>and displays them in a 'list' on the webpage. But when it takes at best 4~
5
>seconds per sp execution, selecting 3 or 4 Doctors will have the users
>frustrated beyond belief waiting for the search results.
Indeed. Luckily, Erland has covered this common problem as well. Check
out this article: http://www.sommarskog.se/arrays-in-sql.html. I suggest
that you use one of the techniques to get the selected doctor numbers in
a table, then join against that table. This is another aspect of your
question that I won't cover any further.
>So, I ask for the help of T-SQL Gurus here on improving this monster. I
>have tried, but am not getting anywhere. Here is the code, and I hope to
>get some suggestions. Thanks
(snip H U G E query)
The first thing that struck me is that you have a totall of four derived
tables that all use the EXACT same FROM and WHERE clause (unless I am
overlooking some minor difference?). It looks as if your predecessor
thought that you were limited to one aggregate per subquery. You can
replace these four subqueries with one. That also rids you of the (in
this case) unneeded non-standard ordering of JOIN and ON clauses. Was
your predecessor coding for job security?
The main query also repeats the exact same WHERE clause (apart from the
totally unneeded 1 = 1), but it lacks the GROUP BY that is present in
the derived tables. I can't say that I like this - you might end up with
several detail rows that each show the same aggregated results in
addition to the different details. I guess "AND A.TFLAG4 = E.TFLAG4" is
intended to solve this, but I'm not sure if this is failsafe. This is
where I miss knowing the keys!
Another suspicious part is the CASE statement for the status:
> case
> when D.S1 > 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 = 0 AND D.S5 = 0 then
>'RECEIVED'
> when D.S1 = 0 AND D.S2 > 0 then 'PROCESSING'
> when D.S1 = 0 AND D.S2 = 0 AND D.S3 > 0 then 'PAID'
> when D.S1 = 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 > 0 then 'DENIED'
> when D.S1 = 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 = 0 AND D.S5 > 0 then
>'VOID'
> END AS FINAL_STATUS
Are you aware that many combinations of S1, S2, ..., and S5 will fall
through all the WHEN clauses and yield a NULL result? (For instance if
both S1 and S2 are >0, or when all are =0, to name just two examples). I
don't know your data, so you'll have to decide if this is a problem. A
possible rewrite (depending on your data -- don't copy blindly, it is
not the exact same logic!!) that takes advantage of the order of
evaluation of the CASE expression would be:
CASE
WHEN D.S1 > 0 THEN 'Received'
WHEN D.S2 > 0 THEN 'Processing'
WHEN D.S3 > 0 THEN 'Paid'
WHEN D.S4 > 0 THEN 'Denied'
ELSE 'Void'
END
But in the rewritten query bellow, I'll retain the original.
The only "safe" improvement of your query that I see is the removal of
the duplicated derived tables. I expect a good performance improvement
from that. I'm quite sure that there are more possibilities to optimize
the query even further, but they require a better understanding of the
data than I have (at least knowledge of the keys!).
Here's my suggestion for a rewritten query:
SELECT * FROM
(
select
A.TMBR#,
A.TCLAIM,
A.TFLAG4,
B.TOTALCOUNT,
REPLACE(RTRIM(LTRIM(A.MMFNAM)) + ' ' + RTRIM(LTRIM(A.MMINIT)) + '. ',
' . ', ' ') + RTRIM(LTRIM(A.MMLNAM)) AS MMNAME,
A.TADOC#,
A.DFTAX# ,
A.AHCCCS_ID,
A.DADR1,
CASE
WHEN A.DENITY = 'P'
THEN REPLACE(RTRIM(LTRIM(A.DFNAM)) + ' ' + RTRIM(LTRIM(A.DINIT)) + '.
', ' . ', ' ') + RTRIM(LTRIM(A.DLNAM))
ELSE RTRIM(LTRIM(A.DLNAM)) + RTRIM(LTRIM(A.DFNAM))
END AS DNAME,
A.TPAYE#,
CASE
WHEN A.PDENITY = 'P' AND RTRIM(LTRIM(A.PDINIT)) <> ''
THEN REPLACE(RTRIM(LTRIM(A.PDFNAM)) + ' ' + RTRIM(LTRIM(A.PDINIT)) +
'. ', ' . ', ' ') + RTRIM(LTRIM(A.PDLNAM))
ELSE RTRIM(LTRIM(A.PDLNAM)) + RTRIM(LTRIM(A.PDFNAM))
END AS PDNAME,
CONVERT(varchar(10), A.TSRVDT, 101) as TSRVDT,
A.TSTATS,
A.TSTATS_DESC,
B.TTFLD16,
case
when B.S1 > 0 AND B.S2 = 0 AND B.S3 = 0 AND B.S4 = 0 AND B.S5 = 0
then 'RECEIVED'
when B.S1 = 0 AND B.S2 > 0 then 'PROCESSING'
when B.S1 = 0 AND B.S2 = 0 AND B.S3 > 0 then 'PAID'
when B.S1 = 0 AND B.S2 = 0 AND B.S3 = 0 AND B.S4 > 0 then 'DENIED'
when B.S1 = 0 AND B.S2 = 0 AND B.S3 = 0 AND B.S4 = 0 AND B.S5 > 0
then 'VOID'
END AS FINAL_STATUS
from MedMC.dbo.MedMC_Claim_Status A
INNER JOIN
(
select TMBR#, TCLAIM,
MAX(TFLAG4) AS TFLAG4,
SUM(TFLD16) AS TTFLD16,
COUNT(*) AS TOTALCOUNT,
SUM(CASE when TSTATS = '1' then 1 else 0 END) AS S1,
SUM(CASE when TSTATS = '2' then 1 else 0 END) AS S2,
SUM(CASE when TSTATS = '3' then 1 else 0 END) AS S3,
SUM(CASE when TSTATS = '4' then 1 else 0 END) AS S4,
SUM(CASE when TSTATS = '5' then 1 else 0 END) AS S5
from MedMC.dbo.MedMC_Claim_Status
WHERE AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND DFTAX# = COALESCE(@.DFTAX#, DFTAX#)
and TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and
coalesce(@.TSRVDTto, @.TSRVDTfrom, TSRVDT)
AND TMBR# = COALESCE(@.TMBR#, TMBR#)
AND MMFNAM LIKE COALESCE(@.MMFNAM, MMFNAM)
AND MMLNAM LIKE COALESCE(@.MMLNAM, MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
GROUP BY TMBR#, TCLAIM
) B
ON A.TMBR# = B.TMBR# AND A.TCLAIM = B.TCLAIM AND A.TFLAG4 = B.TFLAG4
WHERE
A.AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND A.DFTAX# = COALESCE(@.DFTAX#, A.DFTAX#)
AND TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and
coalesce(@.TSRVDTto, @.TSRVDTfrom, TSRVDT)
AND A.MMFNAM LIKE COALESCE(@.MMFNAM, A.MMFNAM)
AND A.MMLNAM LIKE COALESCE(@.MMLNAM, A.MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
) T
where FINAL_STATUS = COALESCE(UPPER(@.FINAL_STATUS), FINAL_STATUS)
ORDER BY TSRVDT desc
(Untested, since you didn't post INSERT statements with sample data to
test on and expected results to verify my results).
Hugo Kornelis, SQL Server MVP|||In addition to what Hugo posted, I grabbed the query execution plan and
noticed a lot of table scans and sort operations. If you can figure out a
way to tighten up the WHERE clauses and the indexing a little more to match
these queries, you can probably improve performance dramatically. All those
LTRIM(RTRIM()) and REPLACE() statements in the WHERE clause don't help,
since it has to perform those operations on every single row. The RTRIM's
are superfluous, and why not LTRIM() it at insert/update time?
Take a look at the query execution plan for the best ideas on how to
optimize.
You probably already know it's a bad idea to use a # character in
variable/parameter/column names - I guess the guy you replaced wasn't aware
:)
"Andrew" <AndrewR2k1@.hotmail.com> wrote in message
news:unyFEvUdGHA.5048@.TK2MSFTNGP04.phx.gbl...
> Need some guru help with a monster T-SQL query. Trying to make it more
> efficient, possibly less code, and anything else I can do to it to make it
...|||Another option to explore (after you look at Hugo's and Mike's) is the
issue of your clustered index. You didn't say how often data is
inserted into the primary table, or what your maintenance plan is , but
I'd be willing to bet that with such a wide clustered index, you are
looking at some heavy duty fragmentation. You might try moving your
clustered index to the last column in the table (the one that defaults
to GETDATE()) to see if that improves response; rebuild the current
clustered index as a nonclustered index on those same columns.
Of course, if you rarely insert data, then disregard my advice;
instead, focus on a good defragmentation plan that runs often enough to
keep your indexes in shape.
Stu
request Stored procedure filtered by Today
I am using SQL2005.There si a field called"EXPDATE". I need a query that shows the table info, if the date that is exist on "EXPDATE" is greater than today. In summary How to write a code that if EXPDATE> "today (I do not know what to put instead of today)" then show the contents of date
In SqlServer, today's function is GetDate()|||Yes in T-SQL we use GETDATE() to get today'date, but remember that the GETDATE() function will return a DATETIME value which also contains time. So if you just want to compare date, you may need something like this (suppose the EXPDATE column is also DATETIME data type):
select * fromyourTable
WHERE DATEDIFF(d,GETDATE(),EXPDATE)>0
Wednesday, March 7, 2012
representative sub-data
Since indexed views aren't going to work for me, I'm working on
revamping a large, ugly query that hits a large, ugly database.
The database stores inventory.
Some items in inventory are single items (e.g. a calendar), while
others are grouped (e.g. a shirt with several color/size options).
The schema of the relevant tables looks like:
product:
--
ID
sku
Color
Description
Price
productGroup:
--
ID
sku
Name
productGroupElement:
--
ID
productID
productGroupID
I need a query that returns both the standalone and grouped items
together. I'm doing this with a union. The tricky part (for me) is in
getting the grouped items, grabbing representative data from one of the
standalone items that comprise the group.
Here's the current query I'm using:
select 'standalone' as flag, id, sku, color, description
from product
where id NOT IN (select productID from productGroupElement)
UNION
select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id =
pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id = (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON
pge1.productID = p1.id
WHERE pge1.productGroupID =
pge.productGroupID
)
So what I'd like to know is:
1) Is the union approach a reasonably efficient way to combine
standalone and grouped items?
2) is the sub query in the WHERE clause of the second query the best
way to grab representative data?
Thanks,
ErikHI Erik
"voldengen@.gmail.com" wrote:
> Since indexed views aren't going to work for me, I'm working on
> revamping a large, ugly query that hits a large, ugly database.
> The database stores inventory.
> Some items in inventory are single items (e.g. a calendar), while
> others are grouped (e.g. a shirt with several color/size options).
> The schema of the relevant tables looks like:
> product:
> --
> ID
> sku
> Color
> Description
> Price
>
> productGroup:
> --
> ID
> sku
> Name
>
> productGroupElement:
> --
> ID
> productID
> productGroupID
>
You should check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to
post useful DDL and sample data
> I need a query that returns both the standalone and grouped items
> together. I'm doing this with a union. The tricky part (for me) is in
> getting the grouped items, grabbing representative data from one of the
> standalone items that comprise the group.
> Here's the current query I'm using:
> select 'standalone' as flag, id, sku, color, description
> from product
> where id NOT IN (select productID from productGroupElement)
> UNION
> select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
> from product p inner join productGroupElement pge on p.id =
> pge.productID
> INNER JOIN productGroup pg on pg.id = pge.productGroupID
> where p.id = (
> select max(pge1.ItemID)
> FROM productGroupElement pge1
> INNER JOIN product p1 ON
> pge1.productID = p1.id
> WHERE pge1.productGroupID =
> pge.productGroupID
> )
>
What happens to the items returned by:
select 'others' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id =
pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id <> (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON pge1.productID = p1.id
WHERE pge1.productGroupID = pge.productGroupID
)
> Thanks,
> Erik
Would this give what you want?
SELECT CASE WHEN g.productID IS NULL THEN 'standalone' ELSE 'grouped' END AS
flag, p.id, p.sku, p.color, p.description
from product p
LEFT JOIN productGroupElement g ON p.id = g.productID
John|||On 29 Dec 2006 11:59:13 -0800, voldengen@.gmail.com wrote:
(snip)
>1) Is the union approach a reasonably efficient way to combine
>standalone and grouped items?
Hi Erik,
Since the constants in the SELECT lists makes the two sides of the UNION
disjunct, you should change it to UNION ALL. Without the ALL, SQL Server
will do extra work to find and remove duplicates.
>2) is the sub query in the WHERE clause of the second query the best
>way to grab representative data?
Only if a sample that is achieved by always using the highest ItemID can
be considered representative. Most statisticians would disagree. (Would
you trust a political popularity poll that is done by interviewing the
oldest member of each household?)
Hugo Kornelis, SQL Server MVP
revamping a large, ugly query that hits a large, ugly database.
The database stores inventory.
Some items in inventory are single items (e.g. a calendar), while
others are grouped (e.g. a shirt with several color/size options).
The schema of the relevant tables looks like:
product:
--
ID
sku
Color
Description
Price
productGroup:
--
ID
sku
Name
productGroupElement:
--
ID
productID
productGroupID
I need a query that returns both the standalone and grouped items
together. I'm doing this with a union. The tricky part (for me) is in
getting the grouped items, grabbing representative data from one of the
standalone items that comprise the group.
Here's the current query I'm using:
select 'standalone' as flag, id, sku, color, description
from product
where id NOT IN (select productID from productGroupElement)
UNION
select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id =
pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id = (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON
pge1.productID = p1.id
WHERE pge1.productGroupID =
pge.productGroupID
)
So what I'd like to know is:
1) Is the union approach a reasonably efficient way to combine
standalone and grouped items?
2) is the sub query in the WHERE clause of the second query the best
way to grab representative data?
Thanks,
ErikHI Erik
"voldengen@.gmail.com" wrote:
> Since indexed views aren't going to work for me, I'm working on
> revamping a large, ugly query that hits a large, ugly database.
> The database stores inventory.
> Some items in inventory are single items (e.g. a calendar), while
> others are grouped (e.g. a shirt with several color/size options).
> The schema of the relevant tables looks like:
> product:
> --
> ID
> sku
> Color
> Description
> Price
>
> productGroup:
> --
> ID
> sku
> Name
>
> productGroupElement:
> --
> ID
> productID
> productGroupID
>
You should check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to
post useful DDL and sample data
> I need a query that returns both the standalone and grouped items
> together. I'm doing this with a union. The tricky part (for me) is in
> getting the grouped items, grabbing representative data from one of the
> standalone items that comprise the group.
> Here's the current query I'm using:
> select 'standalone' as flag, id, sku, color, description
> from product
> where id NOT IN (select productID from productGroupElement)
> UNION
> select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
> from product p inner join productGroupElement pge on p.id =
> pge.productID
> INNER JOIN productGroup pg on pg.id = pge.productGroupID
> where p.id = (
> select max(pge1.ItemID)
> FROM productGroupElement pge1
> INNER JOIN product p1 ON
> pge1.productID = p1.id
> WHERE pge1.productGroupID =
> pge.productGroupID
> )
>
What happens to the items returned by:
select 'others' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id =
pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id <> (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON pge1.productID = p1.id
WHERE pge1.productGroupID = pge.productGroupID
)
> Thanks,
> Erik
Would this give what you want?
SELECT CASE WHEN g.productID IS NULL THEN 'standalone' ELSE 'grouped' END AS
flag, p.id, p.sku, p.color, p.description
from product p
LEFT JOIN productGroupElement g ON p.id = g.productID
John|||On 29 Dec 2006 11:59:13 -0800, voldengen@.gmail.com wrote:
(snip)
>1) Is the union approach a reasonably efficient way to combine
>standalone and grouped items?
Hi Erik,
Since the constants in the SELECT lists makes the two sides of the UNION
disjunct, you should change it to UNION ALL. Without the ALL, SQL Server
will do extra work to find and remove duplicates.
>2) is the sub query in the WHERE clause of the second query the best
>way to grab representative data?
Only if a sample that is achieved by always using the highest ItemID can
be considered representative. Most statisticians would disagree. (Would
you trust a political popularity poll that is done by interviewing the
oldest member of each household?)
Hugo Kornelis, SQL Server MVP
representative sub-data
Since indexed views aren't going to work for me, I'm working on
revamping a large, ugly query that hits a large, ugly database.
The database stores inventory.
Some items in inventory are single items (e.g. a calendar), while
others are grouped (e.g. a shirt with several color/size options).
The schema of the relevant tables looks like:
product:
ID
sku
Color
Description
Price
productGroup:
ID
sku
Name
productGroupElement:
ID
productID
productGroupID
I need a query that returns both the standalone and grouped items
together. I'm doing this with a union. The tricky part (for me) is in
getting the grouped items, grabbing representative data from one of the
standalone items that comprise the group.
Here's the current query I'm using:
select 'standalone' as flag, id, sku, color, description
from product
where id NOT IN (select productID from productGroupElement)
UNION
select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id =
pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id = (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON
pge1.productID = p1.id
WHERE pge1.productGroupID =
pge.productGroupID
)
So what I'd like to know is:
1) Is the union approach a reasonably efficient way to combine
standalone and grouped items?
2) is the sub query in the WHERE clause of the second query the best
way to grab representative data?
Thanks,
Erik
HI Erik
"voldengen@.gmail.com" wrote:
> Since indexed views aren't going to work for me, I'm working on
> revamping a large, ugly query that hits a large, ugly database.
> The database stores inventory.
> Some items in inventory are single items (e.g. a calendar), while
> others are grouped (e.g. a shirt with several color/size options).
> The schema of the relevant tables looks like:
> product:
> --
> ID
> sku
> Color
> Description
> Price
>
> productGroup:
> --
> ID
> sku
> Name
>
> productGroupElement:
> --
> ID
> productID
> productGroupID
>
You should check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to
post useful DDL and sample data
> I need a query that returns both the standalone and grouped items
> together. I'm doing this with a union. The tricky part (for me) is in
> getting the grouped items, grabbing representative data from one of the
> standalone items that comprise the group.
> Here's the current query I'm using:
> select 'standalone' as flag, id, sku, color, description
> from product
> where id NOT IN (select productID from productGroupElement)
> UNION
> select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
> from product p inner join productGroupElement pge on p.id =
> pge.productID
> INNER JOIN productGroup pg on pg.id = pge.productGroupID
> where p.id = (
> select max(pge1.ItemID)
> FROM productGroupElement pge1
> INNER JOIN product p1 ON
> pge1.productID = p1.id
> WHERE pge1.productGroupID =
> pge.productGroupID
> )
>
What happens to the items returned by:
select 'others' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id =
pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id <> (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON pge1.productID = p1.id
WHERE pge1.productGroupID = pge.productGroupID
)
> Thanks,
> Erik
Would this give what you want?
SELECT CASE WHEN g.productID IS NULL THEN 'standalone' ELSE 'grouped' END AS
flag, p.id, p.sku, p.color, p.description
from product p
LEFT JOIN productGroupElement g ON p.id = g.productID
John
revamping a large, ugly query that hits a large, ugly database.
The database stores inventory.
Some items in inventory are single items (e.g. a calendar), while
others are grouped (e.g. a shirt with several color/size options).
The schema of the relevant tables looks like:
product:
ID
sku
Color
Description
Price
productGroup:
ID
sku
Name
productGroupElement:
ID
productID
productGroupID
I need a query that returns both the standalone and grouped items
together. I'm doing this with a union. The tricky part (for me) is in
getting the grouped items, grabbing representative data from one of the
standalone items that comprise the group.
Here's the current query I'm using:
select 'standalone' as flag, id, sku, color, description
from product
where id NOT IN (select productID from productGroupElement)
UNION
select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id =
pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id = (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON
pge1.productID = p1.id
WHERE pge1.productGroupID =
pge.productGroupID
)
So what I'd like to know is:
1) Is the union approach a reasonably efficient way to combine
standalone and grouped items?
2) is the sub query in the WHERE clause of the second query the best
way to grab representative data?
Thanks,
Erik
HI Erik
"voldengen@.gmail.com" wrote:
> Since indexed views aren't going to work for me, I'm working on
> revamping a large, ugly query that hits a large, ugly database.
> The database stores inventory.
> Some items in inventory are single items (e.g. a calendar), while
> others are grouped (e.g. a shirt with several color/size options).
> The schema of the relevant tables looks like:
> product:
> --
> ID
> sku
> Color
> Description
> Price
>
> productGroup:
> --
> ID
> sku
> Name
>
> productGroupElement:
> --
> ID
> productID
> productGroupID
>
You should check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to
post useful DDL and sample data
> I need a query that returns both the standalone and grouped items
> together. I'm doing this with a union. The tricky part (for me) is in
> getting the grouped items, grabbing representative data from one of the
> standalone items that comprise the group.
> Here's the current query I'm using:
> select 'standalone' as flag, id, sku, color, description
> from product
> where id NOT IN (select productID from productGroupElement)
> UNION
> select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
> from product p inner join productGroupElement pge on p.id =
> pge.productID
> INNER JOIN productGroup pg on pg.id = pge.productGroupID
> where p.id = (
> select max(pge1.ItemID)
> FROM productGroupElement pge1
> INNER JOIN product p1 ON
> pge1.productID = p1.id
> WHERE pge1.productGroupID =
> pge.productGroupID
> )
>
What happens to the items returned by:
select 'others' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id =
pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id <> (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON pge1.productID = p1.id
WHERE pge1.productGroupID = pge.productGroupID
)
> Thanks,
> Erik
Would this give what you want?
SELECT CASE WHEN g.productID IS NULL THEN 'standalone' ELSE 'grouped' END AS
flag, p.id, p.sku, p.color, p.description
from product p
LEFT JOIN productGroupElement g ON p.id = g.productID
John
representative sub-data
Since indexed views aren't going to work for me, I'm working on
revamping a large, ugly query that hits a large, ugly database.
The database stores inventory.
Some items in inventory are single items (e.g. a calendar), while
others are grouped (e.g. a shirt with several color/size options).
The schema of the relevant tables looks like:
product:
--
ID
sku
Color
Description
Price
productGroup:
--
ID
sku
Name
productGroupElement:
--
ID
productID
productGroupID
I need a query that returns both the standalone and grouped items
together. I'm doing this with a union. The tricky part (for me) is in
getting the grouped items, grabbing representative data from one of the
standalone items that comprise the group.
Here's the current query I'm using:
select 'standalone' as flag, id, sku, color, description
from product
where id NOT IN (select productID from productGroupElement)
UNION
select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id = pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id = (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON
pge1.productID = p1.id
WHERE pge1.productGroupID = pge.productGroupID
)
So what I'd like to know is:
1) Is the union approach a reasonably efficient way to combine
standalone and grouped items?
2) is the sub query in the WHERE clause of the second query the best
way to grab representative data?
Thanks,
ErikHI Erik
"voldengen@.gmail.com" wrote:
> Since indexed views aren't going to work for me, I'm working on
> revamping a large, ugly query that hits a large, ugly database.
> The database stores inventory.
> Some items in inventory are single items (e.g. a calendar), while
> others are grouped (e.g. a shirt with several color/size options).
> The schema of the relevant tables looks like:
> product:
> --
> ID
> sku
> Color
> Description
> Price
>
> productGroup:
> --
> ID
> sku
> Name
>
> productGroupElement:
> --
> ID
> productID
> productGroupID
>
You should check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to
post useful DDL and sample data
> I need a query that returns both the standalone and grouped items
> together. I'm doing this with a union. The tricky part (for me) is in
> getting the grouped items, grabbing representative data from one of the
> standalone items that comprise the group.
> Here's the current query I'm using:
> select 'standalone' as flag, id, sku, color, description
> from product
> where id NOT IN (select productID from productGroupElement)
> UNION
> select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
> from product p inner join productGroupElement pge on p.id => pge.productID
> INNER JOIN productGroup pg on pg.id = pge.productGroupID
> where p.id = (
> select max(pge1.ItemID)
> FROM productGroupElement pge1
> INNER JOIN product p1 ON
> pge1.productID = p1.id
> WHERE pge1.productGroupID => pge.productGroupID
> )
>
What happens to the items returned by:
select 'others' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id =pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id <> (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON pge1.productID = p1.id
WHERE pge1.productGroupID = pge.productGroupID
)
> Thanks,
> Erik
Would this give what you want?
SELECT CASE WHEN g.productID IS NULL THEN 'standalone' ELSE 'grouped' END AS
flag, p.id, p.sku, p.color, p.description
from product p
LEFT JOIN productGroupElement g ON p.id = g.productID
John|||On 29 Dec 2006 11:59:13 -0800, voldengen@.gmail.com wrote:
(snip)
>1) Is the union approach a reasonably efficient way to combine
>standalone and grouped items?
Hi Erik,
Since the constants in the SELECT lists makes the two sides of the UNION
disjunct, you should change it to UNION ALL. Without the ALL, SQL Server
will do extra work to find and remove duplicates.
>2) is the sub query in the WHERE clause of the second query the best
>way to grab representative data?
Only if a sample that is achieved by always using the highest ItemID can
be considered representative. Most statisticians would disagree. (Would
you trust a political popularity poll that is done by interviewing the
oldest member of each household?)
--
Hugo Kornelis, SQL Server MVP
revamping a large, ugly query that hits a large, ugly database.
The database stores inventory.
Some items in inventory are single items (e.g. a calendar), while
others are grouped (e.g. a shirt with several color/size options).
The schema of the relevant tables looks like:
product:
--
ID
sku
Color
Description
Price
productGroup:
--
ID
sku
Name
productGroupElement:
--
ID
productID
productGroupID
I need a query that returns both the standalone and grouped items
together. I'm doing this with a union. The tricky part (for me) is in
getting the grouped items, grabbing representative data from one of the
standalone items that comprise the group.
Here's the current query I'm using:
select 'standalone' as flag, id, sku, color, description
from product
where id NOT IN (select productID from productGroupElement)
UNION
select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id = pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id = (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON
pge1.productID = p1.id
WHERE pge1.productGroupID = pge.productGroupID
)
So what I'd like to know is:
1) Is the union approach a reasonably efficient way to combine
standalone and grouped items?
2) is the sub query in the WHERE clause of the second query the best
way to grab representative data?
Thanks,
ErikHI Erik
"voldengen@.gmail.com" wrote:
> Since indexed views aren't going to work for me, I'm working on
> revamping a large, ugly query that hits a large, ugly database.
> The database stores inventory.
> Some items in inventory are single items (e.g. a calendar), while
> others are grouped (e.g. a shirt with several color/size options).
> The schema of the relevant tables looks like:
> product:
> --
> ID
> sku
> Color
> Description
> Price
>
> productGroup:
> --
> ID
> sku
> Name
>
> productGroupElement:
> --
> ID
> productID
> productGroupID
>
You should check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to
post useful DDL and sample data
> I need a query that returns both the standalone and grouped items
> together. I'm doing this with a union. The tricky part (for me) is in
> getting the grouped items, grabbing representative data from one of the
> standalone items that comprise the group.
> Here's the current query I'm using:
> select 'standalone' as flag, id, sku, color, description
> from product
> where id NOT IN (select productID from productGroupElement)
> UNION
> select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
> from product p inner join productGroupElement pge on p.id => pge.productID
> INNER JOIN productGroup pg on pg.id = pge.productGroupID
> where p.id = (
> select max(pge1.ItemID)
> FROM productGroupElement pge1
> INNER JOIN product p1 ON
> pge1.productID = p1.id
> WHERE pge1.productGroupID => pge.productGroupID
> )
>
What happens to the items returned by:
select 'others' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id =pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id <> (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON pge1.productID = p1.id
WHERE pge1.productGroupID = pge.productGroupID
)
> Thanks,
> Erik
Would this give what you want?
SELECT CASE WHEN g.productID IS NULL THEN 'standalone' ELSE 'grouped' END AS
flag, p.id, p.sku, p.color, p.description
from product p
LEFT JOIN productGroupElement g ON p.id = g.productID
John|||On 29 Dec 2006 11:59:13 -0800, voldengen@.gmail.com wrote:
(snip)
>1) Is the union approach a reasonably efficient way to combine
>standalone and grouped items?
Hi Erik,
Since the constants in the SELECT lists makes the two sides of the UNION
disjunct, you should change it to UNION ALL. Without the ALL, SQL Server
will do extra work to find and remove duplicates.
>2) is the sub query in the WHERE clause of the second query the best
>way to grab representative data?
Only if a sample that is achieved by always using the highest ItemID can
be considered representative. Most statisticians would disagree. (Would
you trust a political popularity poll that is done by interviewing the
oldest member of each household?)
--
Hugo Kornelis, SQL Server MVP
Saturday, February 25, 2012
Repost: Latches that don't release
I have encountered an intermittent problem where a stored procedure or query
will create a latch and hold onto it indefinitely. I have no explanation
for why the offending process fails to complete. Since the latch is on a
frequently used table, all subsequent insert operations are blocked until
the stubborn process is manually killed.
I've run across it about 4 times in the last month on two instances of a
similar database (SQL2000 latest SP, Simple Recovery Model). The type of
latch and the type of process that created it has varied.
Latch Type Process that caused it
============== ====================================== LATCH_EX -- From a stored proc. with a single INSERT
statement
PAGELATCH_EX -- From a stored proc. with a single INSERT statement
NETWORKIO -- From a SELECT query
Again, I haven't tracked down a legitimate reason for why the offending
process gets stuck. The table where the problems are occurring is
essentially a message log receiving ~400,000 records/day. It has 4 indices
and I've seen some postings regarding timeout problems on heavily indexed
tables with lots of activity. There are no operations that take place in
the database that are suspect for deadlocking. The vast majority of the
operations are simple one record inserts into a single table. All other
operations are occasional SELECT queries coming from a web report. Under
one occasion, the problem was caused by a select query but the others have
been caused by the insert stored procedure.
As a temporary work-around, I'm periodically polling the sysprocesses table
for waiting processes and performing a kill on the process if it is one of
the three latch types above and it has been waiting more than 10 seconds
(for my situation, accidentally killing a innocent process has little
impact).
Since it is not occurring in frequently, I have not been able to catch it
under a SQL Profiler log but I'll try to capture the behavior in the future.Hi Paul,
Thanks for using MSDN Newsgroup!
From your descriptions, I understood that some latches will not release 4
times in two instance. Have I understood you? If there is anything I
misunderstood, please feel free to let me know:)
First of all, I think compared with your large amount of data processing, 4
times is relatively a small number, isn't it? So it would be hard for us to
troubleshooting.
Secondly, we will have to collect the following information to make further
troubleshooting regarding blocking issue. The following documents will
show you how to collect the information when blocking
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
You will find we need the result from sp_blocker_pss80, performance log
from performance monitor and SQL Server error log.
I fully understood you will have to wait until the block happened again and
it may be hard to get the error messsage from KB: 271509. However, we need
all these information to do troubleshooting.
Unfortunately, according to our Newsgroup policy I was not able to monitor
this post thread too long and looking the nature of this issue, it would
require intensive troubleshooting which would be done quickly and
effectively with direct assistance from a Microsoft Support Professional
through Microsoft Product Support Services.
BTW, You can contact Microsoft Product Support directly to discuss
additional support options you may have available, by contacting us at
1-(800)936-5800 or by choosing one of the options listed at
http://support.microsoft.com/default.aspx?scid=sz;en-us;top. If this is not
an urgent issue and your would like us to create an incident for you and
have Microsoft Customer Service Representative contact you directly, please
send email to (remove "online." from this no Spam email address):
mailto:dscommhf@.online.microsoft.com with the following information,
Anyway, you could continue post all the information listed in the KB:
271509 here. I will be glad to serve you until it is resolved:)
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Mingqing,
Thanks for the info. I am running the additional logging and trace
mechanisms described in the KB article you refered to. When the problem
occurs again, I will hopefully have enough info to identify the cause.
From the list of the 6 common categories of blocks listed in KB: 271509 I
suspect #6 Blocking Caused by Orphaned Connection. Although the article (or
SQL BO) doesn't describe the scenario in detail, my guess is that a client
getting a dropped connection due to a network failure or Server performance
bottleneck and that connection is never has its locks released. If this is
the case, the artcle suggests that the only way to deal with it is to kill
the process (I guess my temporary workaround may become permanent).
I will repost if the logs turn up new info when the problem occurs next.
--Paul
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:kfwgvbCWEHA.3440@.cpmsftngxa10.phx.gbl...
> Hi Paul,
> Thanks for using MSDN Newsgroup!
> From your descriptions, I understood that some latches will not release 4
> times in two instance. Have I understood you? If there is anything I
> misunderstood, please feel free to let me know:)
> First of all, I think compared with your large amount of data processing,
4
> times is relatively a small number, isn't it? So it would be hard for us
to
> troubleshooting.
> Secondly, we will have to collect the following information to make
further
> troubleshooting regarding blocking issue. The following documents will
> show you how to collect the information when blocking
> INF: How to Monitor SQL Server 2000 Blocking
> http://support.microsoft.com/?id=271509
> You will find we need the result from sp_blocker_pss80, performance log
> from performance monitor and SQL Server error log.
> I fully understood you will have to wait until the block happened again
and
> it may be hard to get the error messsage from KB: 271509. However, we need
> all these information to do troubleshooting.
> Unfortunately, according to our Newsgroup policy I was not able to monitor
> this post thread too long and looking the nature of this issue, it would
> require intensive troubleshooting which would be done quickly and
> effectively with direct assistance from a Microsoft Support Professional
> through Microsoft Product Support Services.
> BTW, You can contact Microsoft Product Support directly to discuss
> additional support options you may have available, by contacting us at
> 1-(800)936-5800 or by choosing one of the options listed at
> http://support.microsoft.com/default.aspx?scid=sz;en-us;top. If this is
not
> an urgent issue and your would like us to create an incident for you and
> have Microsoft Customer Service Representative contact you directly,
please
> send email to (remove "online." from this no Spam email address):
> mailto:dscommhf@.online.microsoft.com with the following information,
> Anyway, you could continue post all the information listed in the KB:
> 271509 here. I will be glad to serve you until it is resolved:)
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
will create a latch and hold onto it indefinitely. I have no explanation
for why the offending process fails to complete. Since the latch is on a
frequently used table, all subsequent insert operations are blocked until
the stubborn process is manually killed.
I've run across it about 4 times in the last month on two instances of a
similar database (SQL2000 latest SP, Simple Recovery Model). The type of
latch and the type of process that created it has varied.
Latch Type Process that caused it
============== ====================================== LATCH_EX -- From a stored proc. with a single INSERT
statement
PAGELATCH_EX -- From a stored proc. with a single INSERT statement
NETWORKIO -- From a SELECT query
Again, I haven't tracked down a legitimate reason for why the offending
process gets stuck. The table where the problems are occurring is
essentially a message log receiving ~400,000 records/day. It has 4 indices
and I've seen some postings regarding timeout problems on heavily indexed
tables with lots of activity. There are no operations that take place in
the database that are suspect for deadlocking. The vast majority of the
operations are simple one record inserts into a single table. All other
operations are occasional SELECT queries coming from a web report. Under
one occasion, the problem was caused by a select query but the others have
been caused by the insert stored procedure.
As a temporary work-around, I'm periodically polling the sysprocesses table
for waiting processes and performing a kill on the process if it is one of
the three latch types above and it has been waiting more than 10 seconds
(for my situation, accidentally killing a innocent process has little
impact).
Since it is not occurring in frequently, I have not been able to catch it
under a SQL Profiler log but I'll try to capture the behavior in the future.Hi Paul,
Thanks for using MSDN Newsgroup!
From your descriptions, I understood that some latches will not release 4
times in two instance. Have I understood you? If there is anything I
misunderstood, please feel free to let me know:)
First of all, I think compared with your large amount of data processing, 4
times is relatively a small number, isn't it? So it would be hard for us to
troubleshooting.
Secondly, we will have to collect the following information to make further
troubleshooting regarding blocking issue. The following documents will
show you how to collect the information when blocking
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
You will find we need the result from sp_blocker_pss80, performance log
from performance monitor and SQL Server error log.
I fully understood you will have to wait until the block happened again and
it may be hard to get the error messsage from KB: 271509. However, we need
all these information to do troubleshooting.
Unfortunately, according to our Newsgroup policy I was not able to monitor
this post thread too long and looking the nature of this issue, it would
require intensive troubleshooting which would be done quickly and
effectively with direct assistance from a Microsoft Support Professional
through Microsoft Product Support Services.
BTW, You can contact Microsoft Product Support directly to discuss
additional support options you may have available, by contacting us at
1-(800)936-5800 or by choosing one of the options listed at
http://support.microsoft.com/default.aspx?scid=sz;en-us;top. If this is not
an urgent issue and your would like us to create an incident for you and
have Microsoft Customer Service Representative contact you directly, please
send email to (remove "online." from this no Spam email address):
mailto:dscommhf@.online.microsoft.com with the following information,
Anyway, you could continue post all the information listed in the KB:
271509 here. I will be glad to serve you until it is resolved:)
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Mingqing,
Thanks for the info. I am running the additional logging and trace
mechanisms described in the KB article you refered to. When the problem
occurs again, I will hopefully have enough info to identify the cause.
From the list of the 6 common categories of blocks listed in KB: 271509 I
suspect #6 Blocking Caused by Orphaned Connection. Although the article (or
SQL BO) doesn't describe the scenario in detail, my guess is that a client
getting a dropped connection due to a network failure or Server performance
bottleneck and that connection is never has its locks released. If this is
the case, the artcle suggests that the only way to deal with it is to kill
the process (I guess my temporary workaround may become permanent).
I will repost if the logs turn up new info when the problem occurs next.
--Paul
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:kfwgvbCWEHA.3440@.cpmsftngxa10.phx.gbl...
> Hi Paul,
> Thanks for using MSDN Newsgroup!
> From your descriptions, I understood that some latches will not release 4
> times in two instance. Have I understood you? If there is anything I
> misunderstood, please feel free to let me know:)
> First of all, I think compared with your large amount of data processing,
4
> times is relatively a small number, isn't it? So it would be hard for us
to
> troubleshooting.
> Secondly, we will have to collect the following information to make
further
> troubleshooting regarding blocking issue. The following documents will
> show you how to collect the information when blocking
> INF: How to Monitor SQL Server 2000 Blocking
> http://support.microsoft.com/?id=271509
> You will find we need the result from sp_blocker_pss80, performance log
> from performance monitor and SQL Server error log.
> I fully understood you will have to wait until the block happened again
and
> it may be hard to get the error messsage from KB: 271509. However, we need
> all these information to do troubleshooting.
> Unfortunately, according to our Newsgroup policy I was not able to monitor
> this post thread too long and looking the nature of this issue, it would
> require intensive troubleshooting which would be done quickly and
> effectively with direct assistance from a Microsoft Support Professional
> through Microsoft Product Support Services.
> BTW, You can contact Microsoft Product Support directly to discuss
> additional support options you may have available, by contacting us at
> 1-(800)936-5800 or by choosing one of the options listed at
> http://support.microsoft.com/default.aspx?scid=sz;en-us;top. If this is
not
> an urgent issue and your would like us to create an incident for you and
> have Microsoft Customer Service Representative contact you directly,
please
> send email to (remove "online." from this no Spam email address):
> mailto:dscommhf@.online.microsoft.com with the following information,
> Anyway, you could continue post all the information listed in the KB:
> 271509 here. I will be glad to serve you until it is resolved:)
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
Repost: Between vs >= and <=
Which is better to use? I heard that <= and >= are better because it allows
SQL Server to retain the query plan in cache, or something like that. This i
s
in reference to a date comparison.
SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <= <end_date>
Performance wise they use they same indexes and are not different. But is
there an internal reason to use one over the other like plan reuse, or
caching?
Basically, I am looking for any information on caching or query plan reuse i
n
reference to using between or >+ and <=. Also, I am
looking for information pertaining to the fact that SQL Server converts a
Between to a >= and <= pair, and if you resubmit the same between statement,
it converts it again and regenerates it plan. If you use the >= and <=
instead, it will reuse the existing plan to re-execute the statement. Is thi
s
true? and if so, is there documentation on it?On Fri, 7 Oct 2005 14:52:02 -0700, John Barr wrote:
>Which is better to use? I heard that <= and >= are better because it allows
>SQL Server to retain the query plan in cache, or something like that. This
is
>in reference to a date comparison.
>SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
>SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <= <end_date>
>Performance wise they use they same indexes and are not different. But is
>there an internal reason to use one over the other like plan reuse, or
>caching?
>Basically, I am looking for any information on caching or query plan reuse
in
>reference to using between or >+ and <=. Also, I am
>looking for information pertaining to the fact that SQL Server converts a
>Between to a >= and <= pair, and if you resubmit the same between statement
,
>it converts it again and regenerates it plan. If you use the >= and <=
>instead, it will reuse the existing plan to re-execute the statement. Is th
is
>true? and if so, is there documentation on it?
Hi John,
See my reply to your original question.
Also, please don't repost the same question three times within hours
after each other. Reposting if you still have no answer after two or
three days is okay; reposting more often is showing impatience.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
SQL Server to retain the query plan in cache, or something like that. This i
s
in reference to a date comparison.
SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <= <end_date>
Performance wise they use they same indexes and are not different. But is
there an internal reason to use one over the other like plan reuse, or
caching?
Basically, I am looking for any information on caching or query plan reuse i
n
reference to using between or >+ and <=. Also, I am
looking for information pertaining to the fact that SQL Server converts a
Between to a >= and <= pair, and if you resubmit the same between statement,
it converts it again and regenerates it plan. If you use the >= and <=
instead, it will reuse the existing plan to re-execute the statement. Is thi
s
true? and if so, is there documentation on it?On Fri, 7 Oct 2005 14:52:02 -0700, John Barr wrote:
>Which is better to use? I heard that <= and >= are better because it allows
>SQL Server to retain the query plan in cache, or something like that. This
is
>in reference to a date comparison.
>SELECT * FROM <table> WHERE <date> between <start_date> and <end_date>
>SELECT * FROM <table> WHERE <date> >= <start_date> and <date> <= <end_date>
>Performance wise they use they same indexes and are not different. But is
>there an internal reason to use one over the other like plan reuse, or
>caching?
>Basically, I am looking for any information on caching or query plan reuse
in
>reference to using between or >+ and <=. Also, I am
>looking for information pertaining to the fact that SQL Server converts a
>Between to a >= and <= pair, and if you resubmit the same between statement
,
>it converts it again and regenerates it plan. If you use the >= and <=
>instead, it will reuse the existing plan to re-execute the statement. Is th
is
>true? and if so, is there documentation on it?
Hi John,
See my reply to your original question.
Also, please don't repost the same question three times within hours
after each other. Reposting if you still have no answer after two or
three days is okay; reposting more often is showing impatience.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Re-Post Sum of Sum from 2 tables
Thanks, it worked.
Is there a way to add another column to the query that shows the total of 1
column:
pieces volume Pieces Free Volume Free Invoiced TotInvoiced
1 1 0 0 10
50
1 2 0 0 20
50
1 1 0 0 20
50
Thanks in advance
Eric
"Mike Gemmell" <MikeGemmell@.discussions.microsoft.com> wrote in message
news:5D4193EA-C33B-4135-918B-9F78E13907A0@.microsoft.com...
> try
> select sum(QTUM1) as pieces, sum(QTUM2) as volume, sum(QTUM1O) as [Pieces
> free], sum(QTUM2O) as [Volume Free], sum(FATTM) as Invoiced from
> (
> select substring(productcode,1,1) code ,QTUM1, QTUM2, QTUM10, QTUM20,
> FATTM from stat001f where (myYear = 2004 and myMonth = 9)
> union all
> select substring(productcode,1,1) code, QTUM1, QTUM2, QTUM10, QTUM20,
FATTM
> from swstat0f where (myYear = 2004 and myMonth = 9)
> ) x
> group by code
>
> "ericvdb" wrote:
>
tables:
[Pieces
[PiecesTo get what you want you could do the following (there is probably a better
way of doing this though):
select sum(QTUM1) as pieces, sum(QTUM2) as volume, sum(QTUM1O) as [Pieces
free], sum(QTUM2O) as [Volume Free], sum(FATTM) as Invoiced, (select
sum(FATTM) from stat001f where (myYear = 2004 and myMonth = 9) + (select
sum(FATTM) from swstat0f where (myYear = 2004 and myMonth = 9) TotInvoiced
from
(
select substring(productcode,1,1) code ,QTUM1, QTUM2, QTUM10, QTUM20,
FATTM from stat001f where (myYear = 2004 and myMonth = 9)
union all
select substring(productcode,1,1) code, QTUM1, QTUM2, QTUM10, QTUM20, FATTM
from swstat0f where (myYear = 2004 and myMonth = 9)
) x
group by code
"ericvdb" wrote:
> Thanks, it worked.
> Is there a way to add another column to the query that shows the total of
1
> column:
> pieces volume Pieces Free Volume Free Invoiced TotInvoiced
> 1 1 0 0 10
> 50
> 1 2 0 0 20
> 50
> 1 1 0 0 20
> 50
>
> Thanks in advance
> Eric
> "Mike Gemmell" <MikeGemmell@.discussions.microsoft.com> wrote in message
> news:5D4193EA-C33B-4135-918B-9F78E13907A0@.microsoft.com...
> FATTM
> tables:
> [Pieces
> [Pieces
>
>
Is there a way to add another column to the query that shows the total of 1
column:
pieces volume Pieces Free Volume Free Invoiced TotInvoiced
1 1 0 0 10
50
1 2 0 0 20
50
1 1 0 0 20
50
Thanks in advance
Eric
"Mike Gemmell" <MikeGemmell@.discussions.microsoft.com> wrote in message
news:5D4193EA-C33B-4135-918B-9F78E13907A0@.microsoft.com...
> try
> select sum(QTUM1) as pieces, sum(QTUM2) as volume, sum(QTUM1O) as [Pieces
> free], sum(QTUM2O) as [Volume Free], sum(FATTM) as Invoiced from
> (
> select substring(productcode,1,1) code ,QTUM1, QTUM2, QTUM10, QTUM20,
> FATTM from stat001f where (myYear = 2004 and myMonth = 9)
> union all
> select substring(productcode,1,1) code, QTUM1, QTUM2, QTUM10, QTUM20,
FATTM
> from swstat0f where (myYear = 2004 and myMonth = 9)
> ) x
> group by code
>
> "ericvdb" wrote:
>
tables:
[Pieces
[PiecesTo get what you want you could do the following (there is probably a better
way of doing this though):
select sum(QTUM1) as pieces, sum(QTUM2) as volume, sum(QTUM1O) as [Pieces
free], sum(QTUM2O) as [Volume Free], sum(FATTM) as Invoiced, (select
sum(FATTM) from stat001f where (myYear = 2004 and myMonth = 9) + (select
sum(FATTM) from swstat0f where (myYear = 2004 and myMonth = 9) TotInvoiced
from
(
select substring(productcode,1,1) code ,QTUM1, QTUM2, QTUM10, QTUM20,
FATTM from stat001f where (myYear = 2004 and myMonth = 9)
union all
select substring(productcode,1,1) code, QTUM1, QTUM2, QTUM10, QTUM20, FATTM
from swstat0f where (myYear = 2004 and myMonth = 9)
) x
group by code
"ericvdb" wrote:
> Thanks, it worked.
> Is there a way to add another column to the query that shows the total of
1
> column:
> pieces volume Pieces Free Volume Free Invoiced TotInvoiced
> 1 1 0 0 10
> 50
> 1 2 0 0 20
> 50
> 1 1 0 0 20
> 50
>
> Thanks in advance
> Eric
> "Mike Gemmell" <MikeGemmell@.discussions.microsoft.com> wrote in message
> news:5D4193EA-C33B-4135-918B-9F78E13907A0@.microsoft.com...
> FATTM
> tables:
> [Pieces
> [Pieces
>
>
Subscribe to:
Posts (Atom)